[#25452] - Question: Joomla 3/4 and MySQL 8
- Closed
- 21 Jul 2019
- Medium
- Build: staging
- # 25452
Yes, I know this doc: https://docs.joomla.org/Joomla_and_MySQL_8
But I don't understand "PHP 7.3 (alpha) is supporting MySQL 8 though.".
Because of a forum request after a fresh installation of Joomla 3.9.8 concerning database fix with lots of errors that don't go away and because I found older threads in other forums with similiar finds:
Is it correct to tell people "Joomla 3 and Joomla 4 are fully compatible with PHP7.3 + Mysql 8"?
Or is the PHP version completely irrelevant?
Or is the database fix a known issue and the error messages there can be ignored?
Hoster in this case is webgo.de. They also provide a Joomla software installer where the same errors occur after installation. No difference if you use the official Joomla download.
Sorry for German messages:
Die Tabelle „'x65qv_languages'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'sitename'“ mit Typ „VARCHAR(1024)“. (Von Datei: „2.5.0-2012-01-14.sql“.)
Die Tabelle „'x65qv_associations'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'id'“ mit Typ „INT(11)“. (Von Datei: „3.0.3.sql“.)
Die Tabelle „'x65qv_user_profiles'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'profile_value'“ mit Typ „TEXT“. (Von Datei: „3.3.4-2014-08-03.sql“.)
Die Tabelle „'x65qv_redirect_links'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'old_url'“ mit Typ „VARCHAR(2048)“. (Von Datei: „3.5.0-2016-03-01.sql“.)
Die Tabelle „'x65qv_redirect_links'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'new_url'“ mit Typ „VARCHAR(2048)“. (Von Datei: „3.5.0-2016-03-01.sql“.)
Die Tabelle „'x65qv_redirect_links'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'referer'“ mit Typ „VARCHAR(2048)“. (Von Datei: „3.5.0-2016-03-01.sql“.)
Die Tabelle „'x65qv_redirect_links'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'new_url'“ mit Typ „VARCHAR(2048)“. (Von Datei: „3.6.0-2016-04-06.sql“.)
Die Tabelle „'x65qv_newsfeeds'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'link'“ mit Typ „VARCHAR(2048)“. (Von Datei: „3.6.3-2016-08-15.sql“.)
Die Tabelle „'x65qv_extensions'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'enabled'“ mit Typ „TINYINT(3)“. (Von Datei: „3.7.0-2016-11-04.sql“.)
Die Tabelle „'x65qv_modules'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'content'“ mit Typ „text“. (Von Datei: „3.7.0-2016-11-27.sql“.)
Die Tabelle „'x65qv_ucm_content'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'core_body'“ mit Typ „mediumtext“. (Von Datei: „3.7.0-2017-01-08.sql“.)
Die Tabelle „'x65qv_ucm_content'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'core_params'“ mit Typ „text“. (Von Datei: „3.7.0-2017-01-08.sql“.)
Die Tabelle „'x65qv_ucm_content'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'core_images'“ mit Typ „text“. (Von Datei: „3.7.0-2017-01-08.sql“.)
Die Tabelle „'x65qv_ucm_content'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'core_urls'“ mit Typ „text“. (Von Datei: „3.7.0-2017-01-08.sql“.)
Die Tabelle „'x65qv_ucm_content'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'core_metakey'“ mit Typ „text“. (Von Datei: „3.7.0-2017-01-08.sql“.)
Die Tabelle „'x65qv_ucm_content'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'core_metadesc'“ mit Typ „text“. (Von Datei: „3.7.0-2017-01-08.sql“.)
Die Tabelle „'x65qv_categories'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'description'“ mit Typ „mediumtext“. (Von Datei: „3.7.0-2017-01-09.sql“.)
Die Tabelle „'x65qv_categories'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'params'“ mit Typ „text“. (Von Datei: „3.7.0-2017-01-09.sql“.)
Die Tabelle „'x65qv_finder_links'“ hat den falschen Typ oder die falschen Attribute für die Spalte „'description'“ mit Typ „text“. (Von Datei: „3.7.0-2017-03-19.sql“.)
Thanks a lot for clarification!
joomla-cms-bot
- | Labels |
Added:
?
|
||
| Labels |
Added:
J3 Issue
|
||
| Status | New | ⇒ | Discussion |
@ReLater Do I understand right that these error messages are shown at the "Extensions -> Manage -> Database" view after a new, clean installation of Joomla CMS 3.9.8? If so, I've never observed that, neither in my local testing environments nor on my testing sites at 1and1 shared hosting.
The messages say that the data types of the reported columns as found in the schema update scripts does not match the data types in the database, which come from joomla.sql.
When checking for the last reported error as an example, I see e.g. for MySQL that in file 3.7.0-2017-03-19.sql we have "ALTER TABLE `#__finder_links` MODIFY `description` text", i.e. no default value, while in joomla.sql we have "`description` text DEFAULT NULL" for that column.
The database schema checker then reports that difference in a bit misleading way, because it just tells the column definition found in the schema update sql but not the one which it has found in database, so there is no info about default null in the error message.
Questions are now: Why doesn't that happen to everybody making a new clean installation of current staging or 3.9.9 RC or 3.9.8? And why haven't old schema updates be changed in order not to report that difference (i.e. add the default null to those columns. This is one of the rare cases when it is required to change old schema updates.) plus adding new schema updates for adding the default null to these columns?
Before I do big research with GitHub's blame function: @alikon Do you know who has added default null to particular columns in joomla.sql without having done schema update stuff for that?
At least we have a to do list if the above list of error messages is complete. @ReLater Is that the case? Are they complete?
@mbabker What do you mean with "Every other problem is basically the same problems that haven't been addressed since MySQL 5.7 shipped"? Was it the schema update thing and how database schema check works, which causes the problems I've described in this comment here above, or was it soemthing else?
Hmm, a possible explanation why it affects only a few people and how it could be related to MySQL8 could be that in MySQL8 there are different defaults for handling missing default null when modifying database columns with ALTER TABLE ... MODIFY .... But this would happen on update, not on new installation.
Now I am even more confused because for the 1st error (column sitename of table #__languages) I can't see any difference between joomla.sql and 2.5.0-2012-01-14.sql for that column.
Maybe SHOW COLUMNS SQL command which is used by the schema checker works differently in MySQL8 regarding empty default values of columns?
What do you mean with "Every other problem is basically the same problems that haven't been addressed since MySQL 5.7 shipped"? Was it the schema update thing and how database schema check works, which causes the problems I've described in this comment here above, or was it soemthing else?
AFAIK there are no B/C breaking changes in how MySQL 8 parses database schema that would impact the (IMO flawed) Joomla\CMS\Schema library.
(and that every other MySQL schema related problem that is still a problem is a result of changes in MySQL 5.7, not MySQL 8)
I got an additional information: The thread opener used PHP7.1.
Do I understand right that these error messages are shown at the "Extensions -> Manage -> Database" view after a new, clean installation of Joomla CMS 3.9.8?
Yes.
The user reported that he tried 3 installations with the software installer at webgo.de in different sapces and databaes. Always the same result.
I told him then to try again with an official stable Joomla package. Same result.
He asked webgo.de. They told him that it's a database issue and Joomla is not MySQL8 ready and that he must change to MariaDB. (I just wonder about the software installer of the provider that runs into same issue but that's another thing).
German forum: https://forum.joomla.de/thread/8035-datenbankfehler-bei-joomla-neu-installationen-durch-reparaturfunktion-nicht-behe/
Could you ever reproduce that problem?
No. I'm on MySQL 5.7 and XAMPP changed to MariaDB.
Well, I am German so I will read it.
Well I just see the forum post starts with "Habe hier drei Joomla Neuinstallationen. Alle drei wurden auch auf 3.9.8 upgedatet."
That means "I have three Joomla new installations. All three have been updated to 3.9.8.".
So is it really about new install, or does that provider install some older version and then the update to 3.9.8 runs? I am more confused now. So or so the error messages from database check look more to me like a failed update than a new installation.
Ah I see, hoster installs a 3.9.7 and this is then updated to 3.9.8.
Read on please. The whole thread is a bit ping-pong before we got all infos.
And he tried also an official stable 3.9.8 after failures with the installer of the provider.
Hmm, yes, you are right. Am even more confused now. Maybe an ugly bug in our database schema checks happening only under certain conditions?
Do you know who has added default null to particular columns in joomla.sql without having done schema update stuff for that
not sure if the schema update has not been done , but pr that changed defualt null date to null are:
Another older thread: https://forum.joomla.org/viewtopic.php?t=967019
The messages are the same like in german forum starting and ending with
Table 'ddbb_languages' has the wrong type or attributes for column 'sitename' with type VARCHAR(1024). (From file 2.5.0-2012-01-14.sql.)
...
Table 'ddbb_finder_links' has the wrong type or attributes for column 'description' with type text. (From file 3.7.0-2017-03-19.sql.)
Confirmation of another user in January: https://forum.joomla.org/viewtopic.php?t=967019#p3555030
Exact same behavior. Clean install of Joomla 3.9.8, with MySQL 8. Database fix has no effect on the following errors:
Table 'eqv1a_languages' has the wrong type or attributes for column 'sitename' with type VARCHAR(1024). (From file 2.5.0-2012-01-14.sql.)
Table 'eqv1a_associations' has the wrong type or attributes for column 'id' with type INT(11). (From file 3.0.3.sql.)
Table 'eqv1a_user_profiles' has the wrong type or attributes for column 'profile_value' with type TEXT. (From file 3.3.4-2014-08-03.sql.)
Table 'eqv1a_redirect_links' has the wrong type or attributes for column 'old_url' with type VARCHAR(2048). (From file 3.5.0-2016-03-01.sql.)
Table 'eqv1a_redirect_links' has the wrong type or attributes for column 'new_url' with type VARCHAR(2048). (From file 3.5.0-2016-03-01.sql.)
Table 'eqv1a_redirect_links' has the wrong type or attributes for column 'referer' with type VARCHAR(2048). (From file 3.5.0-2016-03-01.sql.)
Table 'eqv1a_redirect_links' has the wrong type or attributes for column 'new_url' with type VARCHAR(2048). (From file 3.6.0-2016-04-06.sql.)
Table 'eqv1a_newsfeeds' has the wrong type or attributes for column 'link' with type VARCHAR(2048). (From file 3.6.3-2016-08-15.sql.)
Table 'eqv1a_extensions' has the wrong type or attributes for column 'enabled' with type TINYINT(3). (From file 3.7.0-2016-11-04.sql.)
Table 'eqv1a_modules' has the wrong type or attributes for column 'content' with type text. (From file 3.7.0-2016-11-27.sql.)
Table 'eqv1a_ucm_content' has the wrong type or attributes for column 'core_body' with type mediumtext. (From file 3.7.0-2017-01-08.sql.)
Table 'eqv1a_ucm_content' has the wrong type or attributes for column 'core_params' with type text. (From file 3.7.0-2017-01-08.sql.)
Table 'eqv1a_ucm_content' has the wrong type or attributes for column 'core_images' with type text. (From file 3.7.0-2017-01-08.sql.)
Table 'eqv1a_ucm_content' has the wrong type or attributes for column 'core_urls' with type text. (From file 3.7.0-2017-01-08.sql.)
Table 'eqv1a_ucm_content' has the wrong type or attributes for column 'core_metakey' with type text. (From file 3.7.0-2017-01-08.sql.)
Table 'eqv1a_ucm_content' has the wrong type or attributes for column 'core_metadesc' with type text. (From file 3.7.0-2017-01-08.sql.)
Table 'eqv1a_categories' has the wrong type or attributes for column 'description' with type mediumtext. (From file 3.7.0-2017-01-09.sql.)
Table 'eqv1a_categories' has the wrong type or attributes for column 'params' with type text. (From file 3.7.0-2017-01-09.sql.)
Table 'eqv1a_finder_links' has the wrong type or attributes for column 'description' with type text. (From file 3.7.0-2017-03-19.sql.)
I am getting this EXACT same issue.
We need to get this working with MySQL 8 asap.
We need to get this working with MySQL 8 asap.
Maybe you can help by find the Reasons or creating a Pull Request so ths get fixed as soon as possible?
It will take a while until I have time and set up a testing environment with MySQL 8 without destroying the one I have with MySQL 5.7. I hope I can do that on weekend and then investigate. If someone is faster: Please go on.
I found this about default values in MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html.
Can it be that this together with different handling of strict mode as default in MySQL 8 could cause these problems? @mbabker Michael, what do you think?
@ReLater What could help here would be an SQL export of the database made with PhpMyAdmin, only for the structure, not for the data, if data is sensible and so secret. Can you organise that in the forum? You could send me then by email (richardfatht-online.de) and I could verify with what should come out from a clean install on my MySQL 5.7 where I don't have this problem.
There was no data. It were installations without sample data steps.
I will ask the thread opener via private message.
Can you provide a databse dump like described in #25452 (comment) ?
Yeah, I forgot, was new clean install. No data is good.
To all: I have meanwhile managed to set up a PHP 7.3 + MySQL 8 environment and can reproduce the error. No need for database exports or so. I will investigate.
@brianteeman We all know that link. It is not related to this issue here, but of course a requirement to be able to use MySQL 8, and so I have mentioned it of course, and the others who had this issue here mentioned it, too. But thanks for the link, you can never have enough remonders for that.
What I can see is that the sitename column of the language table has been created in the database in the right way, so the database checker should not report an error. This means creating database works well, but the SHOW COLUMNS command used by the checker returns something else or does not work (maybe due to privileges)?
Alpha 4 definitely worked with mysql8 and the instructions on the wiki. It was tested by the mysql community manager see https://lefred.be/content/joomla-and-mysql-8-0-12/
Yeah that was J4. Currently am testing staging because people have this issue also with 3.9.x.
Problem is definitely the database checker reporting false errors. Database is ok. People in principle could just ignore the db errors and work with Joomla on MySQL 8 and wait for the next release for the fix of the database checker.
Guys, I have found the reson: In MySQL 8, all type names returned by SHOW COLUMNS are in lowercase, and the database checker checks for exact match e.g. 'VARCHAR(1024)'. I will fix that.
Fix found and tested, PR will be created soon.
| Status | Discussion | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2019-07-20 13:52:23 |
| Closed_By | ⇒ | franz-wohlkoenig |
@franz-wohlkoeing As this is an issue for both staging and 4.0-dev, it should be closed then it is fixed in both. Currently my PR is for staging, for 4.0-dev I am waiting for reply from @wilsonge if I shall make a separate PR. So I think this issue should be reopened until I've got that reply.
| Status | Closed | ⇒ | New |
| Closed_Date | 2019-07-20 13:52:23 | ⇒ | |
| Closed_By | franz-wohlkoenig | ⇒ |
| Status | New | ⇒ | Discussion |
Alpha 4 definitely worked with mysql8 and the instructions on the wiki. It was tested by the mysql community manager see https://lefred.be/content/joomla-and-mysql-8-0-12/
@brianteeman The only explanation for this can be that they haven't checked if the database checker shows errors after successful finish of the installation, because 1. I could replicate the error with 4.0-dev, and 2. I have tested the fix for staging (PR #25658 ) also witrh 4.0-dev, and 3. I have found the reason as described in PR #25658 . The only other explanation would be that the behavioral change of the handling of case-sensitivity in "SHOW COLUMNS" statements has happened somewhere in the middle of MySQL version 8 history, i.e. from 8.0.x to 8.0.y.
@franz-wohlkoenig I've just got feedback from George, it does not need an extra fix for 4.0-dev. So the PR for staging is sufficient, you can close this issue.
Quy
- | Status | Discussion | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2019-07-21 00:12:52 |
| Closed_By | ⇒ | Quy |
The auth plugin fix in your MySQL config is going to be independent of PHP version.
IIRC, PHP 7.3 added support for whatever the default MySQL 8 ships. That's the only change in PHP I'm aware of.
Every other problem is basically the same problems that haven't been addressed since MySQL 5.7 shipped.