MySql Error 3995 - how to diagnose?
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
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
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
MySql Error 3995 - how to diagnose?
Hi there,
I am after some help in diagnosing where my fault is. The error I get is " Character set 'utf8mb4_0900_ai_ci' cannot be used in conjunction with 'binary' in call to regexp_like". The error is thrown when I access the front page of my site (http://35.177.172.147/) - the admin side appears just fine.
I run my Joomla site on AWS Lightsail, using their standard LAMP stack. The current working config has PHP 7.4.10/MySQL 8.0.21. That works just fine. AWS published a more up-to-date stack, so I thought I would give it a go. That has PHP 7.4.15/MySQL 8.0.23. My Joomla version is 3.9.25.
So, I created a new instance of the LAMP stack & installed the standard Joomla zip file. That all worked fine, both back and front end. I then transferred over a backup from my working system to the new server, using Akeeba Backup. That installed just fine, but accessing the front end triggers the error above; the backend looks ok.
So, I don't believe this is a core Joomla error, but I would welcome guidance on how to track down the problem in my system.
Tia,
Ridsdale
I am after some help in diagnosing where my fault is. The error I get is " Character set 'utf8mb4_0900_ai_ci' cannot be used in conjunction with 'binary' in call to regexp_like". The error is thrown when I access the front page of my site (http://35.177.172.147/) - the admin side appears just fine.
I run my Joomla site on AWS Lightsail, using their standard LAMP stack. The current working config has PHP 7.4.10/MySQL 8.0.21. That works just fine. AWS published a more up-to-date stack, so I thought I would give it a go. That has PHP 7.4.15/MySQL 8.0.23. My Joomla version is 3.9.25.
So, I created a new instance of the LAMP stack & installed the standard Joomla zip file. That all worked fine, both back and front end. I then transferred over a backup from my working system to the new server, using Akeeba Backup. That installed just fine, but accessing the front end triggers the error above; the backend looks ok.
So, I don't believe this is a core Joomla error, but I would welcome guidance on how to track down the problem in my system.
Tia,
Ridsdale
-
- Joomla! Hero
- Posts: 2863
- Joined: Fri Jul 05, 2013 10:35 am
- Location: Parts Unknown
Re: MySql Error 3995 - how to diagnose?
Enable debug to see stack trace.
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
Re: MySql Error 3995 - how to diagnose?
Yes - first thing I tried. Nothing.
But I appreciate the response!
But I appreciate the response!
-
- Joomla! Hero
- Posts: 2863
- Joined: Fri Jul 05, 2013 10:35 am
- Location: Parts Unknown
Re: MySql Error 3995 - how to diagnose?
Your template doesn't show debug information. Use Protostar.
- brian
- Joomla! Master
- Posts: 12781
- Joined: Fri Aug 12, 2005 7:19 am
- Location: Leeds, UK
- Contact:
Re: MySql Error 3995 - how to diagnose?
Your database table is using a collation of 'utf8mb4_0900_ai_ci' - it should be using a collation of utf8_general_ci
When you restore from your akeeba backup there is an option in kickstart regarding this. Sorry I forget exactly where but the documentation for kickstart on the akeeba website will give you all the information you need
When you restore from your akeeba backup there is an option in kickstart regarding this. Sorry I forget exactly where but the documentation for kickstart on the akeeba website will give you all the information you need
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
Re: MySql Error 3995 - how to diagnose?
Thanks! Nice one!!
Looks like there is an issue with Jevents.
I appreciate the help.
Looks like there is an issue with Jevents.
I appreciate the help.
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
Re: MySql Error 3995 - how to diagnose?
Thanks, Brian. I will take a look at that.brian wrote: ↑Tue Mar 09, 2021 12:22 pmYour database table is using a collation of 'utf8mb4_0900_ai_ci' - it should be using a collation of utf8_general_ci
When you restore from your akeeba backup there is an option in kickstart regarding this. Sorry I forget exactly where but the documentation for kickstart on the akeeba website will give you all the information you need
I just love this forum!!
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
Re: MySql Error 3995 - how to diagnose?
So I reran the Akeeba Backup load, forcing the database tables to utf8_general_ci collation. I still get the error. When I look at the database, it all looks ok, but the Joomla Connection Collation is held at utf8mb4_0900_ai_ci.
So, from system information, I see:
Database Collation: utf8_general_ci
Database Connection Collation: utf8mb4_0900_ai_ci
I also edited the MySQL my.conf to specify:
character_set_server=utf8
collation_server=utf8_general_ci
How do I force the Database Connection Collation to match the Database Collation? How does Joomla decide its Connection Collation to use?
I'm confused!!
So, from system information, I see:
Database Collation: utf8_general_ci
Database Connection Collation: utf8mb4_0900_ai_ci
I also edited the MySQL my.conf to specify:
character_set_server=utf8
collation_server=utf8_general_ci
How do I force the Database Connection Collation to match the Database Collation? How does Joomla decide its Connection Collation to use?
I'm confused!!
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
Fault in system tagMeta plugin in MySQL 8.0.23?
Hi there,
I raised a separate forum query (here: viewtopic.php?f=707&p=3626361#p3626361) about a fault I was hitting when transferring my system over to the new AWS Lamp stack (PHP 7.4.15/MySQL 8.0.23). I has some useful feedback that helped me to dig a little deeper. I naturally assumed it was somewhere in my system, but tracked it down to the system tagMeta plugin.
The code uses a REGEXP BINARY statement (line 1019 of /plugins/system/tagmeta/tagmeta.php), which conflicts with the Database Connection Collation of utf8mb4_0900_ai_ci. I couldn't see a way of forcing the connection to change (but not convinced that was necessary as the same collation is used in 8.0.21 & that works ok)
So, this code works perfectly well on 8.0.21, so I assume there has been a change in MySQL 8.0.23.
For now, I have just edited the query to take out the 'BINARY' part of the SQL. Obviously, not ideal, but it will do for now.
Is this a fault in Joomla, and if so, how do I raise it? Or is there a way to configure Joomla to NOT use that collation? I am running 3.9.25.
Cheers,
Ridsdale
I raised a separate forum query (here: viewtopic.php?f=707&p=3626361#p3626361) about a fault I was hitting when transferring my system over to the new AWS Lamp stack (PHP 7.4.15/MySQL 8.0.23). I has some useful feedback that helped me to dig a little deeper. I naturally assumed it was somewhere in my system, but tracked it down to the system tagMeta plugin.
The code uses a REGEXP BINARY statement (line 1019 of /plugins/system/tagmeta/tagmeta.php), which conflicts with the Database Connection Collation of utf8mb4_0900_ai_ci. I couldn't see a way of forcing the connection to change (but not convinced that was necessary as the same collation is used in 8.0.21 & that works ok)
So, this code works perfectly well on 8.0.21, so I assume there has been a change in MySQL 8.0.23.
For now, I have just edited the query to take out the 'BINARY' part of the SQL. Obviously, not ideal, but it will do for now.
Is this a fault in Joomla, and if so, how do I raise it? Or is there a way to configure Joomla to NOT use that collation? I am running 3.9.25.
Cheers,
Ridsdale
Last edited by toivo on Wed Mar 10, 2021 12:30 pm, edited 1 time in total.
Reason: mod note: merged with the current topic
Reason: mod note: merged with the current topic
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia
Re: MySql Error 3995 - how to diagnose?
You can post to the sub forum Joomla! 3.x Bug Reporting to receive feedback from other members of the forum. That forum can be followed by members of the Joomla! Bug Squad, and others who can advice.
Confirmed bugs in the Joomla core are reported as new issues in Joomla! Issues Tracker - CMS, where the Joomla core team works on maintaining the 3.x version and developing the forthcoming Joomla 4.0, currently in Beta testing.
Toivo Talikka, Global Moderator
-
- Joomla! Apprentice
- Posts: 8
- Joined: Tue Mar 09, 2021 9:01 am
Re: MySql Error 3995 - how to diagnose?
Thanks. I will do that.toivo wrote: ↑Wed Mar 10, 2021 4:58 pmYou can post to the sub forum Joomla! 3.x Bug Reporting to receive feedback from other members of the forum. That forum can be followed by members of the Joomla! Bug Squad, and others who can advice.