S h o r t S t o r i e s

// Tales from software development

Archive for the ‘MySQL’ Category

MySQL BLOB columns are always case sensitive

leave a comment »

I don’t often use BLOB columns but a recent project required storing EDI type data messages in a database so this seemed the to be the correct column type to use.

However, half way through the development of the project I noticed that I couldn’t find a set of messages that I knew were in the database when I executed a

SELECT * FROM `message` WHERE `message_body` LIKE '%something';

query against the table. The text value that I was looking for was an uppercase value, i.e. ‘SOMETHING’, but the default collation for the schema is latin1_general_ci (i.e. case insensitive) so I expected the query to return the rows containing message_body column values containing the ‘SOMETHING’ text even when I specified LIKE ‘%something%’.

If I changed the query to

SELECT * FROM `message` WHERE `message_body` LIKE '%SOMETHING';

then it returned the rows I expected.

I assumed that adding a COLLATE clause would resolve the problem:

SELECT * FROM `message` WHERE `message_body` COLLATE latin1_general_ci LIKE '%something';

but this is not valid for a BLOB column and results in an error message:

Error Code: 1253. COLLATION ‘latin1_general_ci’ is not valid for CHARACTER SET ‘binary’

This is because the BLOB column type uses a pseudo character set called binary that cannot be coerced to other character sets such as latin1. There isn’t a case insensitive collation of the binary character set either.

However, there is another solution – the TEXT column types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) are essentially the same as the BLOB column types (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB) but they don’t use the binary character set. So, changing the column type of the message_body column from BLOB to TEXT gave me the behaviour I wanted.

This (nearly 20 year old) post on the MySQL Developer Zone web site explains the differences between the TEXT and BLOB data types:




Written by Sea Monkey

April 3, 2018 at 7:00 am

Posted in Development, MySQL

Tagged with ,

Using the word ‘function’ in a comment in a MySQL stored procedure causes the RESTORE command to fail

leave a comment »

Recently our support team had a call from a customer who suspected that their nightly MySQL backup/restore job wasn’t working.

At most of our customer sites, once the nightly backup has been run, there is a job to restore the backup to a read-only copy of the live database that is used for reports.

Examining the logs confirmed that the RESTORE command was failing with this error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘deficiency if result data is available */’ at line 35212

The syntax being rejected looked suspiciously like a user comment in a stored procedure or user defined function and I vaguely recalled that we’d encountered a similar problem a few years ago where the use of the word ‘function’ caused problems with MySQL’s BACKUP and RESTORE commands. At the time we’d circumvented the problem by changing the comment to remove the word ‘function’.

I ran the following MySQL query to identify the stored procedure that contained the comment:

SELECT * FROM `information_schema`.`routines` WHERE `routine_definition` LIKE ‘%deficiency if result data%’ AND `routine_schema`=DATABASE();

The stored procedure did indeed contain the following comment:

/* Check for functional deficiency if result data is available */

I needed to see what was in the BACKUP file created by mysqldump but opening it in Notepad.exe wasn’t an option as it was 10Gb. Instead, I used a file splitting utility that I wrote a few years ago to extract 1000 lines from the backup file starting at 900 lines before the line number in the error message. I then looked for the start of the stored procedure in the backup extract. I expected to see this:

CREATE DEFINER=`root`@`%` PROCEDURE `sp_analyze_results`()
/* Check for functional deficiency if result data is available */

but instead the backup file contained this:

/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%` PROCEDURE `sp_test`()
/* Check for*/ /*!50003 functional deficiency if result data is available */

It appears that the BACKUP command is incorrectly parsing the string ‘function’ when it appears in a user comment.

I experimented to see precisely what triggers the bug and it appears to be the exact string ‘ function’, i.e. a space character followed by the eight characters ‘function’ followed by anything else. For example, ‘ function’, ‘ functional’, ‘ functionality’ will all trigger the bug but ‘*function’ won’t.

Note that although it’s the RESTORE command that fails, it’s the invalid backup file data written by the BACKUP command that is the problem.

I thought it would be worthwhile checking to see if the problem is limited to the FUNCTION keyword or whether other keywords are also affected. I created a test stored procedure that contained each of the several hundred MySQL keywords in its own comment on its own line. The bug is only triggered by the FUNCTION keyword but in the process of conducting the test I also found that it’s only the first occurrence of the FUNCTION keyword that triggers the bug.

Now that I had a reasonably good understanding of what triggers the bug, the next step was to search the MySQL bug reports for a match.

The affected customer has MySQL 5.5.16 installed and MySQL bug report 66035 (applies to MySQL 5.5.23 / .26) and describes the issue exactly. Note the comment in the report, “This is a regression bug, as mysqldump from 5.1.64 does not have this problem.” https://bugs.mysql.com/bug.php?id=66035

Through trial and error I’ve found that the bug is fixed in mysqldump version 5.5.30.

Written by Sea Monkey

August 25, 2016 at 8:00 pm

Posted in MySQL

Tagged with

MySQL covering indexes for performance

leave a comment »

Vitality’s application has a function to create clinical letters. While most customers print a physical copy of the letter and post it, increasingly we are asked to implement an interface to a document management system that sends the letter electronically to the recipient. Typically, this is the patient’s GP.

The interface executes a SELECT query on the correspondence table in the Vitality database similar to this:

    correspondence AS c
    INNER JOIN codesets AS cs ON cs.CodeId = c.Type
    c.Approved = true
    AND c.Processed = NULL
    AND cs.CodeSetNo = 20;

Note that the columns referenced on the correspondence table are FileName (of the Microsoft Word document), Type (the type of letter), Approved (indicating the letter is complete and may be sent), and Processed (indicating whether the interface has processed the letter). Codeset 20 contains a list of the letter templates and their descriptions.

This SELECT query has executed in less than a second for the past five years that the interface has been installed at one of our largest customers where approximately 60,000 letters are created each year.

However, a few weeks ago it was noticed that the interface was intermittently failing to send letters. Investigation showed that the SELECT query was sometimes taking in excess of 90 seconds to execute. The command was timing out and throwing an exception that caused the interface to suspend processing. The puzzle was, why was a query that typically executed in less than a second now taking over 90 seconds ?

I’m not sure that we’re ever going to fully understand the change in behaviour but, in part, it appears to be that the MySQL query planner changed the execution plan as the size of the correspondence table grew to over 300,000 rows. After rebuilding the indexes on the table and repairing it the execution time was consistently around 10 seconds.

We realised that whilst we had always thought that the execution time was less than a second and the interface’s processing logs usually showed this, this was likely to be because much of the time MySQL was using a cached query result.

Because the correspondence table contains a summary of the letter content, each row is quite large and a table scan is expensive. With over 300,000 rows in the table, a table scan is very expensive.

So we started to look at how the query could be optimised. Initially we thought that a suitable index would help the query engine to efficiently access the table data but after a number of failed experiments we realised that whilst this was better than a table scan the performance was still quite poor.

The last resort was to try creating a covering index. This is an index in which all the referenced columns are contained in the index so that the query engine doesn’t need to access the table data at all. Our first attempt didn’t show any improvement until we realised that whilst we had included the FileName, Approved, and Processed columns referenced in the SELECT column list and WHERE clause, we had overlooked the reference to the Type column in the INNER JOIN condition. As soon as this column was added to the index there was a massive improvement and the query now consistently executes in 0.3-06 seconds with caching disabled.

Using EXPLAIN to display the query plan shows that the correspondence table is now not being access at all. Instead the covering index is providing all the correspondence column data referenced in the query.

Written by Sea Monkey

April 13, 2016 at 8:00 pm

Posted in MySQL

Tagged with

MySQL Server 5.1 install fails to set EVENT and TRIGGER permissions in remote root login

leave a comment »

I’ve just installed MySQL Server 5.1 on a Windows Server 2003 machine to create an environment similar to the one being used by a customer reporting a problem with our software.

The installer presented an option to allow remote access using the root login and I clicked this to enable it.

The first thing I needed to do after installing MySQL was restore a copy of the customer’s database. I used MySQL Workbench from my desktop PC to do this but the restore failed with an error message indicating that the root@% account did not have TRIGGER permission to create the triggers used by one of the tables being restored.

When I checked the account’s global privileges I could see that all were enabled except for EVENT and TRIGGER. The root@localhost account had all global privileges enabled. So, it appears that the installer had not correctly enabled all global privileges on the remote account.

I thought I’d try enabling the EVENT and TRIGGER privileges from within MySQL Workbench on my desktop PC but, logically enough, this isn’t permitted. So, I installed Workbench on the server only to find that the program would not start as it expects a version of Windows that equates to Windows 7, or higher. In desperation I tried installing a copy of the now defunct MySQL Administrator but this would not show the root@% account.

So I did what I should have done first:

1. Open a command window on the server.

2. Change the current directory to C:\Program Files\MySQL\MySQL Server 5.1\bin

3. Run mysql.exe with the command line arguments for the root account and password: mysql -uroot -paardvark

4. At the MySQL prompt enter the command to grant the TRIGGER global privilege to the root account when used remotely:

GRANT TRIGGER ON *.* TO 'root'@'%';

and again for the EVENT privilege:

GRANT EVENT ON *.* TO 'root'@'%';

I retried the restore in MySQL Workbench and it completed with no errors.

Written by Sea Monkey

March 16, 2015 at 8:00 pm

Posted in MySQL

Tagged with