The last user who signed up to one of my sites was 96 days ago. I've had GoDaddy hosting for 124 days.
I haven't changed anything in this time, except for a few CSS changes on unrelated pages.
The issue is with INSERT queries, they no longer function. I would INSERT data into several fields and it worked fine. Now, these same queries are failing, saying fields do not have a DEFAULT set.
I spoke to support on the phone and he advised me to set NULL as DEFAULT for each field. This will take ages
It was all working fine...I've had the same queries and SQL set-up for years without trouble.
Is there a way to mass set NULL for every field (except ID of course)?
Or a way for them to work like they were previously?
@ryemck can you post your table structure?
I'm not sure how to post it, but here's a summary if it helps..
User table, fields like username/password etc. Another field called "lastkilledby", which is basically the ID of the player who last killed them (its a game).
When I create a new user, I would only INSERT the username/password/email etc, leaving out "lastkilledby". So when a user registered, "lastkilledby" would be blank and it'd update when someone attacked them.
The exact same INSERT command throws an error "lastkilledby" has no default value and the INSERT command fails.
If I manually set "lastkilledby" field to default NULL, the INSERT command will work.
But I can't go through every table and set each one to null, would take days
@ryemck ok, what is your datatype for lastkilledby? Int? varchar?
Data type is INT, but INSERT queries show the same error for all data types.
I've read it's due to a STRICT SQL rule on GoDaddy Shared Hosting, although it seemingly did work for like a month after I joined GoDaddy
@ryemck that's odd. Maybe they performed an upgrade, or changed something. Since they now support PHP7, maybe they made some changes to MySQL as well.
Can you try this?:
ALTER TABLE table_name CHANGE COLUMN `column_name` `column_name` int(10) unsigned DEFAULT NULL;
Where table_name is your users table and column_name is "lastkilledby".
If your int length is different that 10, you should change it also. For example int(5), or int(8).
Hope it helps!
I can't really sit there writing every column of every table I'd like a way to do this quickly, like a mass command or something.
If there's no way then no probs, hosting runs out next week anyway lol
@ryemck you run that command for your users table, and it modifies the structure of the table itself. So it would allow NULL values as the default value.
You don't have to go for every player.
But I'd advise you to perform a DB backup before altering the table.
However, if you don't want to do it, don't do it.
Hope it helps!
Yeahh I will have to type every column though, it's not just "lastkilledby" that's the problem (was just an example), there are like 20-30 tables with 10+ columns each, all of which would need to be edited
@ryemck can't you do it via phpMyAdmin? I think you can select the columns and allow null. That way you don't have to type.
Hope it helps!
Yeahh I had to do that to get functionality, took me a good 20 minutes of tedious clicking..
Another problem now though haha The NULL default is not working well at all with a large number of my UPDATE queries (like one that increases a value, but obviously NULL cannot be increased)
The literal only way to get around this is to make sure every single column has a "proper" default set (0 for numbers, NULL for text).
So frustrating -___-