MySql Error 3995 - how to diagnose?

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
Locked
ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

MySql Error 3995 - how to diagnose?

Post by ridsdale » Tue Mar 09, 2021 9:32 am

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

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2863
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: MySql Error 3995 - how to diagnose?

Post by SharkyKZ » Tue Mar 09, 2021 9:45 am

Enable debug to see stack trace.

ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

Re: MySql Error 3995 - how to diagnose?

Post by ridsdale » Tue Mar 09, 2021 10:54 am

Yes - first thing I tried. Nothing.

But I appreciate the response!

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2863
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: MySql Error 3995 - how to diagnose?

Post by SharkyKZ » Tue Mar 09, 2021 12:09 pm

Your template doesn't show debug information. Use Protostar.

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 12781
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: MySql Error 3995 - how to diagnose?

Post by brian » Tue Mar 09, 2021 12:22 pm

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
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

Re: MySql Error 3995 - how to diagnose?

Post by ridsdale » Tue Mar 09, 2021 12:22 pm

Thanks! Nice one!!

Looks like there is an issue with Jevents.

I appreciate the help.

ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

Re: MySql Error 3995 - how to diagnose?

Post by ridsdale » Tue Mar 09, 2021 12:24 pm

brian wrote:
Tue Mar 09, 2021 12:22 pm
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
Thanks, Brian. I will take a look at that.

I just love this forum!!

ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

Re: MySql Error 3995 - how to diagnose?

Post by ridsdale » Tue Mar 09, 2021 1:45 pm

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!!

ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

Fault in system tagMeta plugin in MySQL 8.0.23?

Post by ridsdale » Wed Mar 10, 2021 11:39 am

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
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

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: MySql Error 3995 - how to diagnose?

Post by toivo » Wed Mar 10, 2021 4:58 pm

ridsdale wrote:
Wed Mar 10, 2021 11:39 am
Is this a fault in Joomla, and if so, how do I raise it?
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

ridsdale
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Tue Mar 09, 2021 9:01 am

Re: MySql Error 3995 - how to diagnose?

Post by ridsdale » Wed Mar 10, 2021 11:59 pm

toivo wrote:
Wed Mar 10, 2021 4:58 pm
ridsdale wrote:
Wed Mar 10, 2021 11:39 am
Is this a fault in Joomla, and if so, how do I raise it?
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.
Thanks. I will do that.


Locked

Return to “Installation Joomla! 3.x”