Database Structure issue

[mfoland]

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.

[Kian]

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.

[mfoland]

@Kian

Here's the value for Created At

0000-00-00 00:00:00	

 

[mfoland]

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. 

[brian!]

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.

[Kian]

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 September 4, 2019 by Kian

[mfoland]

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.

[mfoland]

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.

[Kian]

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.

[mfoland]

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.

[mfoland]

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 September 5, 2019 by mfoland