Jump to content

Database Structure issue


mfoland

Recommended Posts

Hey all! I'm making a feature for my WHMCS for license auto upgrades, and I'm running into an error when adding to the database structure for tblclients.

SQL query:


ALTER TABLE `tblclients` ADD `enable_auto_upgrades` INT NOT NULL DEFAULT '0' AFTER `marketing_emails_opt_in`
MySQL said: Documentation

#1067 - Invalid default value for 'created_at'

Can I get some guidance as to why I'd be getting the invalid default value when I'm just trying to add a custom field to the row. This specific field I don't want to show on the client details page, as I'm making a specific page for this.

Link to comment
Share on other sites

As far as I know the problem is that created_at columns uses 0000-00-00 00:00:00 as default value that is not correct. Timestamp data type supports dates from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. The 0000-00-00 00:00:00 used by WHMCS as default is clearly out of range hence the error.

Link to comment
Share on other sites

1 hour ago, Kian said:

As far as I know the problem is that created_at columns uses 0000-00-00 00:00:00 as default value that is not correct. Timestamp data type supports dates from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. The 0000-00-00 00:00:00 used by WHMCS as default is clearly out of range hence the error.

It's not even letting me update the db lol. 

Link to comment
Share on other sites

17 hours ago, mfoland said:

Can I get some guidance as to why I'd be getting the invalid default value when I'm just trying to add a custom field to the row. This specific field I don't want to show on the client details page, as I'm making a specific page for this.

I don't know if this is going to help you much, but that line worked without issue for me in phpmyadmin - no errors and it added the new field.

Link to comment
Share on other sites

8 minutes ago, brian! said:

I don't know if this is going to help you much, but that line worked without issue for me in phpmyadmin - no errors and it added the new field.

I think it depends on sql_mode NO_ZERO_DATE. With this setting I can't run the query for the same reason (I cannot change my sql_mode atm).

Edited by Kian
Link to comment
Share on other sites

1 hour ago, Kian said:

I think it depends on sql_mode NO_ZERO_DATE. With this setting I can't run the query for the same reason (I cannot change my sql_mode atm).

I can't change my sql mode either, and I tried @brian!'s method, and it says the next field has the same issue. Tried changing them at the same time, and no go. lol. I have WHMCS working on it as well. Hope they can figure this mess out.

Link to comment
Share on other sites

Changed DB structure temporarily:

 

54	created_at	timestamp			No	CURRENT_TIMESTAMP			 Change Change	 Drop Drop	
More More
	55	updated_at	timestamp			No	CURRENT_TIMESTAMP			 Change Change	 Drop Drop	
More More
	56	pwresetexpiry	timestamp			Yes	NULL		

I have an open ticket at WHMCS too, we'll see what happens, and what they recommend.

Link to comment
Share on other sites

1 hour ago, Kian said:

Alternatively you could create a new table (tblmfoland 😀) and store all your stuff there. You could retreive everything with a simple JOIN by User ID.

Sounds fun.. NOT lol. I did the above, and was able to create the enable_auto_upgrades. Now playing with ajax to see if I can have it auto change the db lol.

 

<script>
function getXMLHTTPRequest() {
   var req =  false;
   try {
      /* for Firefox */
      req = new XMLHttpRequest(); 
   } catch (err) {
      try {
         /* for some versions of IE */
         req = new ActiveXObject("Msxml2.XMLHTTP");
      } catch (err) {
         try {
            /* for some other versions of IE */
            req = new ActiveXObject("Microsoft.XMLHTTP");
         } catch (err) {
            req = false;
         }
     }
   }
   return req;
}
function updateToggle(obj,id){
    obj.style.backgroundColor = '#cc0000';
    var updReq = getXMLHTTPRequest();
    var url = 'autoupgStatus.php';
    var vars = 'id='+id+'&value='+obj.value;
    updReq.open('POST', url, true);
    updReq.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
    updReq.onreadystatechange = function() {
	//Call a function when the state changes.
        if(updReq.readyState == 4 && updReq.status == 200) {
            if(updReq.responseText == 'N'){
                obj.style.backgroundColor = '#cc8800';
            }else{
                if(obj.value == '1'){
                    obj.value = '0';
                }else{
                    obj.value = '1';
                }
                obj.src = updReq.responseText;
                obj.style.backgroundColor = '#0000cc';
            }
        }
    }
    updReq.send(vars);
}
</script>

Problem is, it's not working lol. I can go to the status page direct and change the value in the php code lol.. but the ajax part is being a twit! I even have the form with the onclick event.. and nerp.. it's not working lol. It changes to OFF or ON when you click respectively, but doesn't update the db as it should lol.

Link to comment
Share on other sites

Well -- couldn't figure out the ajax part.. but I was able to make the autoupgrade.php like WHMCS has, however, to submit the data, you gotta click a submit button. WHMCS does not have that, and it's something they need to look into, since there's doesn't function right.

 

Edited by mfoland
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated