Category: MySQL

Posted on

MySQL JASON_VALUE fails with a key starting with a number

SELECT JSON_VALUE('{"dateTime": "2024-07-21 08:11:07 -0600", "60SecInches": 0.00, "DailyInches": 0.01, "TotalInches": 10.61}', "$.60SecInches");

MySQL said: #3143 – Invalid JSON path expression. The error is around character position 13.  That seems to point to the end of this string.

"$.60SecInches"

If I remove the “60” from the key, the above SELECT works. But the following works as well.

SELECT JSON_KEYS('{"dateTime": "2024-07-21 08:11:07 -0600", "60SecInches": 0.00, "DailyInches": 0.01, "TotalInches": 10.61}');

["dateTime", "60SecInches", "DailyInches", "TotalInches"]

According to JSON.org, a string is a sequence of zero or more Unicode characters, wrapped in double quotes, using backslash escapes. Any valid string can be used as a JSON key.

I tried backslashes in various places, but that did not work.

The solution is the key name must be separately enclosed in double-quotes and the whole path must be enclosed with single-quotes as

'$."60SecInches"'

This can be used for other “special characters” as well. For example “*” or “#” or number only keys.  I am not sure why a number is a special character, but it seems to be in this case.

Posted on

Upgrading Drupal and blocked by MySQL library version

Recently I was upgrading a Drupal installation to version 9.5.11 and when in maintenance mode, received the error:

10.5.20-MariaDB-cll-lve-log
The libmysqlclient driver version 3.1.21 is less than the minimum required version. Upgrade to libmysqlclient version 5.5.3 or up, or alternatively switch mysql drivers to MySQLnd version 5.0.9 or up.

Since this installation was on a shared hosting account, I have limited control over the software version.  I could switch to the MySQLnd driver. The MySQL native driver for PHP (mysqlnd) is a drop-in replacement for the MySQL Client Library (libmysql) for the PHP script language.

To make the change in cPanel, find the “Select PHP Version” in the Software section and click to select.  In the “PHP Extension” tab make the following changes.

PHP Extensions image

After doing this you should be able to continue your Drupal upgrade with no error.

Posted on

MySQL/MariaDB needs upgrading after upgrading

Recently I was reviewing log files for one of my servers and came across the following error message:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': Cannot load from mysql.proc. The table is probably corrupted (1728)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mydatabase'': Cannot load from mysql.proc. The table is probably corrupted (1728)

Worried that I was having some kind of corruption in all my databases, I immediately search the web for those error messages.  What I found was after I had upgraded my server’s software I needed to run a MySQL/MariaDB specific upgrade command to upgrade the file organization for the new version.

Why the software cannot recognize the older format and suggest the following I do not know.  But here is what you need to do after upgrading the system’s software:

sudo mysql_upgrade -u root -p

The user specified does not need to be root, but it does need full access to all the databases on your system. The sudo is need to avoid the following error.

Could not create the upgrade info file '/var/lib/mysql/mysql_upgrade_info' in the MariaDB Servers datadir, errno: 13

This error showed up on a Debian 11 (bullseye) system. On my Ubuntu systems I have not seen this error, so the package management upgrade may handle the package specific upgrade.

Posted on

MySQL and UTF-8

I recently had a problem in a web application that I created where the UTF-8 characters were not interpreted correctly by browsers.  The biggest issue was this did not happen in all instances of presenting these UTF-8 strings.

After tracing the strings through several libraries, I found the culprit.  The following SQL statement was the source of the text.

SELECT ID, CONCAT(sDescription, ' (', ID, ')') FROM ProdFamily;

Where ID is a integer key for the table and sDescription is a varchar column.  The result of the CONCAT function is a “binary string” because of the integer column as one of the operands.  The end result is that this “binary string” was treated differently than a regular UTF-8 string and characters outside the normal ASCII ones were not display correctly.  To fix this issue the CAST function must be added to set the type of the ID column to string as follows.

SELECT ID, CONCAT(sDescription, ' (', CAST(ID AS CHAR), ')') FROM ProdFamily

See MySQL CONCAT or CAST function documentation for more information.

Posted on

MySQL: Setting date field default to current date

In MySQL, creating a date/time stamp field that defaults to the current date/time has been a problem in the past.  MySQL restricted you to one timestamp field per table that is automatically updated with the current date/time.  Early versions of MySQL allowed multiple updating timestamp fields contrary to the specification, but this was “fixed” in later versions.  Then triggers were added to MySQL v5.0.2 and can be used as a solution for this common issue.

CREATE TRIGGER `TableName_dtColumn_default` BEFORE INSERT ON `TableName`  FOR EACH ROW SET NEW.dtColumn = CURRENT_TIMESTAMP;

Replace “TableName” with your table’s name and “dtColumn” with your date/time column’s name.  Also the trigger name, “TableName_dtColumn_default” in this example, must be unique within a schema.

MySQL triggers are a very versatile feature.  For more details about creating and using triggers see the MySQL documentation for CREATE TRIGGER syntax.