my provider offered a transfer to a new database with MYSQL 5.7 (also possible MYSQL 8, or MariaDB 10.6) and I tried the update. I got these errors:
Fehler!
Database error in line 363: Incorrect datetime value: '0000-00-00 00:00:00' for column 'edited' at row 1
Database error in line 378: Incorrect date value: '0000-00-00' for column 'birthday' at row 1
Database error in line 381: Incorrect date value: '0000-00-00' for column 'birthday' at row 1
Well, I can give an update to this issues.
This is what I did:
- first I made a backup from the database and the php stuff. And indeed, at some step in the following trial-and-error-game I made a mistake and corrupted the database. So I needed the SQL dump!
- since I had already some difficulties, I duplicated both, i.e. the database and the web files. One instance for testing some changes at the database and one instance for porting back in case of success.
- technically spoken, the main point was to obtain a database which is updatable. In my case, I found three obstacles (after migrating from MYSQL 5.6 to MYSQL7 or higher):
-- first: the table PREFIX_userdata contains email the address for every account. In the past, it was no problem to have two different accounts with the same email address. The update script however does not accept this (as I mentioned already somewhere), so this table must contain every email address only once. Google pointed me to this MYSQL snipplet, which lists all email addresses with more than one occurrence:
mysql> select
-> user_email,
-> count(user_email)
-> From
-> PREFIX_userdata
-> group by (user_email)
-> having count(user_email) > 1;
Then, the "double" accounts must removed.
-- second: if the update script does finish with with errors, it may already have changed the database. Especially, it creates some new tables. Loading the backup SQL dump does not remove the newly created tables, but a second run of the update script leads to an error, because it relys on a clean database. Therefore, the newly created tables must be dropped. To my knowledge this are four tables. "drop table PREFIX_akismet_rating,PREFIX_b8_rating,PREFIX_b8_wordlist,PREFIX_uploads" should do the job.
-- third is the most challenging one. Look at the quoted error. The update script (or better, the database) causes problems when some entries with wrong values are present. In my case, this showed up at the "edited" row of table PREFIX_entries and the "birthday" row of table PREFIX_userdata. In both cases, I had some entries with all zeroed, see above. For some reason, SQL has a problem, depending on the particular settings of the database.
It looks like that these date variables have some default value, if not set. In former times (parsimony?) it was probably "all zero". Now, it seems to be NULL, which is a SQL key word. My SQL database seems to accept either "NULL" for 'not set' or, if set, a certain, realistic value, but not 'all zero'.
Unfortunately, my database let me not change the "all zero" values to an other value. It even refused to accept "all zero" in a query - at least with default settings.
Google pointed me to some database settings, i.e. the "sql_mode". It can be queried like that:
mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Here, "NO_ZERO_IN_DATE,NO_ZERO_DATE" is causing the issue.
However, I read warnings when changing the settings in a database which is in use. So I created a new database, changed the settings
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
(thats just the old settings without "NO_ZERO_IN_DATE,NO_ZERO_DATE"), imported the SQL dump, and changed the "all zero" entries to NULL:
UPDATE PREFIX_entries SET edited = NULL WHERE edited = '0000-00-00 00:00:00';
UPDATE PREFIX_userdata SET birthday = NULL WHERE birthday = '0000-00-00';
Now, a check should not find a "all zero" value any more:
select edited from PREFIX_entries where edited = '0000-00-00 00:00:00';
select birthday from PREFIX_userdata where birthday = '0000-00-00';
I made a SQL dump from this change and used it in my testing (and also in my productive) database. It worked without errors.
As indicated, this depends on your individual database settings, so DO NOT SIMPLY COPY&PASTE my hints without looking at your own situation!
After that, I was able to perform the update from 2.4.20 to 20220803.1. At least, the update script finished without error.
, Friday, September 23, 2022, 20:06 (181 days ago)
I will remove the double entry.