Jump to content
mfoland

Database Structure issue

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.

Share this post


Link to post
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.

Share this post


Link to post
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. 

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

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