Websites moved from MySQL 5 to MySQL 8 are failing

Need help installing Joomla! 3.x? For all Joomla! 3.x installation issues please use this forum.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10
Post Reply
User avatar
Toolie
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Sun Jan 02, 2011 12:53 am

Websites moved from MySQL 5 to MySQL 8 are failing

Post by Toolie » Tue Feb 25, 2020 11:06 pm

I have 19 existing, up-to-date Joomla 3.9.15 websites that I have moved:

From a Bitnami Linux LAMP stack on an AWS server containing:
PHP 7.1.16
MySQL 5.7.21
phpMyAdmin 4.7.9

To a Bitnami Linux LAMP stack on an AWS containing:
PHP 7.3.14
MySQL 8.0.18
phpMyAdmin 5.0.1

I copied all the files to the new server then used PHPMyAdmin (4.7.9) to export the MySQL 5.7.21 database contents to an SQL script (.sql) file. Next I imported all those .sql files to my new MySQL 8.0.18 database using phpMyAdmin 5.0.1. I am now getting errors every time I try to install anything into those existing Joomla sites. For example, this is what I saw when I tried to install a new component:

Image

Before you ask, I tried installing onto the new server an entirely new Joomla site, and had no errors installing add-on components, templates, or plugins. So I suspect that there is something amiss in the export from MySQL 5.x and import into MySQL 8.x using PHPMyAdmin.

I spoke at length to the Bitnami folks about this issue, and we think it's something affecting Joomla transfers, either errors introduced by PHPMyAdmin itself or the differences between MySQL 5 and MySQL 8. I am unable to install anything or update Joomla, which puts all these sites at risk.

Has anyone encountered errors like this?
Last edited by toivo on Wed Feb 26, 2020 12:14 am, edited 1 time in total.
Reason: mod note: moved, not related to 3.x Migrating and Upgrading
Toolie Garner
www.toolie.com

gws
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4173
Joined: Tue Aug 23, 2005 1:56 pm
Location: South coast, UK
Contact:

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by gws » Wed Feb 26, 2020 8:39 am


kafkaian
Joomla! Apprentice
Joomla! Apprentice
Posts: 29
Joined: Mon Jan 09, 2006 2:48 am

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by kafkaian » Tue Oct 06, 2020 7:03 pm

We really need to get a LAMP download for linux that can work with Joomla out of the box. Spent an entire day with mysql 8.0 and cli user creations etc before realising it's not really that compatible and the workaround doesn't work for me. Also can't get phpmyadmin to work with mySQL 8.0 either.

Taskel only provides mySQL 8.0

Wish the specs made it clear from the beginning

7EIRISH
Joomla! Apprentice
Joomla! Apprentice
Posts: 48
Joined: Sun May 03, 2009 11:53 am

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by 7EIRISH » Wed Oct 14, 2020 7:20 pm

gws wrote:
Wed Feb 26, 2020 8:39 am
see https://docs.joomla.org/Joomla_and_MySQL_8
Yeah, I tried the suggested workaround using both a j3.9.22 current and fresh install on on a server as well as my local and these workarounds do not completely work.

I can however get the site and modules to load but no article content is displayed to users and I cannot submit new articles.

MarkRidsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Sat May 28, 2016 8:25 pm

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by MarkRidsdale » Thu Oct 15, 2020 9:21 am

Hi there,

I'm doing something very similar right now. My live site is hosted via Hostgator and is:

Joomla: 3.9.22
OS: Linux g
PHP: 7.4.11
MySQLi: 5.6.41-84.1

In parallel, I am running on AWS Lightsail, LAMP stack:

Joomla: 3.9.22
OS: Linux i
PHP: 7.4.7
MySQLi: 8.0.20

Every week, I export the data from Hostgator using phpMyAdmin & load into AWS, and it works just fine. So, to me it looks like an option issue with your export/import. I had to select TRUNCATE on export & ensure FOREIGN KEYS CHECK not set on import. It works just fine.

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 39126
Joined: Sat Apr 05, 2008 9:58 pm

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by Webdongle » Thu Oct 15, 2020 9:58 am

Anyone tried Akeeba to transfer the site to use a different database?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein.

MarkRidsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Sat May 28, 2016 8:25 pm

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by MarkRidsdale » Thu Oct 15, 2020 10:15 am

Akeeba is what I used originally to transfer the site across, and that worked just fine.

7EIRISH
Joomla! Apprentice
Joomla! Apprentice
Posts: 48
Joined: Sun May 03, 2009 11:53 am

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by 7EIRISH » Fri Oct 16, 2020 12:38 am

Its not the foregin key check.

The real issue is that Joomla is using "0000-00-00 00:00:00" as the default for DATETIME columns and newer MYSQL versions DO NOT ALLOW THIS. So all table columns with this "0000-00-00 00:00:00" default DATETIME will have to be updated to NULL or the issue will still be there.

The joomla docs are correct in that the mysql password must be used but does not mention anything about the table DATETIME columns need altered.

Cheers!

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 39126
Joined: Sat Apr 05, 2008 9:58 pm

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by Webdongle » Fri Oct 16, 2020 9:25 am

@7EIRISH
It's a wiki you can edit it to add that info.

When using Akeeba to move the site does it auto change the values or give a check box/field to alter them?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein.

waarnemer
Joomla! Hero
Joomla! Hero
Posts: 2780
Joined: Sun May 04, 2008 12:37 pm

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by waarnemer » Fri Oct 16, 2020 10:11 am

If that is, than that is an issue for Github isn't it?

https://github.com/joomla/joomla-cms/issues

7EIRISH
Joomla! Apprentice
Joomla! Apprentice
Posts: 48
Joined: Sun May 03, 2009 11:53 am

Re: Websites moved from MySQL 5 to MySQL 8 are failing

Post by 7EIRISH » Fri Oct 16, 2020 8:38 pm

Webdongle wrote:
Fri Oct 16, 2020 9:25 am
@7EIRISH
It's a wiki you can edit it to add that info.

When using Akeeba to move the site does it auto change the values or give a check box/field to alter them?
I don't use Akeeba. It may be a bit more work but I manually save my backups of files and data. Have had permissions issues in the past using alternative backup tools and moving to different environments. No thanks..

If the wiki can be updated then it all needs to be updated. Haven't found much in there that is of help or is current.
If that is, than that is an issue for Github isn't it?

https://github.com/joomla/joomla-cms/issues
Other then the possibility that not many people are using mysql 8 I find it hard to believe the joomla team does not know this. The docs mention this issue on joomla 3.8 or 3.9 so its not that knew of an issue.

Cheers!


Post Reply

Return to “Installation Joomla! 3.x”