php - German Umlaute not considered in JSON_CONTAINS() with MariaDB

first time posting here.

I am facing a problem with unpredicted behavior on my PROD server and my local environment.

Here is some background on the situation: In my application (backend Laravel 7, frontend regular html/javascript) I need to search for entries in a particular table based on JSON data stored in one of the columns:

Table: flights
columns: id, date, passengers, ... pilot_id, second_pilot_id, flight_data, updated_at, created_at
Copy code

There are flights, that are directly linked to either a pilot or a second pilot via pilot_id or second_pilot_id. That is fine so far, because I can easily query them. However there are also flight entries, where no registered user is doing the entry and they are only represented by a name that is entered. This works only if the name doesn't contain special characters, in particular the german Umlaute (Г¶, Г¤, Гј), also doesn't work for other specials like Гў or Гџ or Г©, ГЁ etc. But ONLY ON PROD, on Local everything works even with special characters.

flight_data has the data type "JSON" in my migration files.

$table->json('flight_data') ... 
Copy code

Now the problem:

On my local environment I can run the following and will get results returned:

... ->where(function($q) use ($r) {
$q->whereRaw("IF(payee = 2, JSON_CONTAINS(flight_data, '{\"second_pilotname\":\"$r\"}'), JSON_CONTAINS(flight_data, '{\"pilotname\":\"$r\"}'))");
})->...
Copy code

This will get me my example results without issues, as expected

($r is filled a particular name of a pilot, in my example he is called "Jöhn Düe")

If I run this on my PROD system I will get no retuns. I tracked it down to the JSON_CONTAINS() function, that prevents the results. I also tried playing around with "Joehn Duee", which would be found correctly, so it basically comes down to the german Umlaute (Г¶, Г¤, Гј) not being handled correctly somehow.

I also tried some SQL statements in phpmyadmin and these are the results:

LOCAL

select id, flight_data, comments, updated_at from logbook where JSON_CONTAINS(flight_data, '{"pilotname": "Juehn Duee"}')
Copy code

1 result found

select id, flight_data, comments, updated_at from logbook where JSON_CONTAINS(flight_data, '{"pilotname": "JГјhn DГјe"}')
Copy code

1 result found

PROD

select id, flight_data, comments, updated_at from logbook where JSON_CONTAINS(flight_data, '{"pilotname": "Juehn Duee"}')
Copy code

1 result found

select id, flight_data, comments, updated_at from logbook where JSON_CONTAINS(flight_data, '{"pilotname": "JГјhn DГјe"}')
Copy code

0 result found

I also checked the raw data that is stored:

PROD:

Undefined asked

2021-12-6

---

---

---

Share solution ↓

Additional Information:

Date the issue was resolved:

2021-12-6

Link To Source
Link To Answer People are also looking for solutions of the problem: mysqli::real_connect(): (hy000/2002): connection refused

---

Similar questions

Find the answer in similar questions on our website.