Bug #16454 Server crashes when concatted invalid in a mixed insert/select statement

• Submitted: 12 Jan 2006 15:04
• Modified: 12 Jan 2006 15:41
• Reporter: Michiel van de Vis
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.10
• OS: Windows (Windows 2003 Server)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[12 Jan 2006 15:04] Michiel van de Vis

Description:
MySQL crashes when concatting strings by using str + str in a combined INSERT / SELECT statement.

str + str is not allowed, but I dont think it should crash a server...

How to repeat:
I repeated this code several times, and the servers keeps crashing.
Also when I execute the query 1 at the time.

But when I use CONCAT(username, '1') instead of username + '1' everything is ok.

[code]

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `username` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `pass` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `firstname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `initials` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `lastname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `active` enum('0','1') character set utf8 collate utf8_unicode_ci NOT NULL default '0',
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- 
-- Gegevens worden uitgevoerd voor tabel `users`
-- 

INSERT INTO `users` VALUES (1, 'user1', 'test', 'John', 'J.', 'Doe', '1');
INSERT INTO `users` VALUES (2, 'user2', 'test', 'Jane', 'J.', 'Doe', '1');
INSERT INTO `users` VALUES (3, 'user3', 'test', 'Jack', 'J.', 'Doe', '1');

INSERT INTO users
SELECT user_id+3, username+'1', pass, firstname, initials, lastname, active
FROM users;
[/code]

[12 Jan 2006 15:41] MySQL Verification Team

I was unable to repeat using the latest released version 4.1.16. Could
you please upgrade and test it:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `users` (
    ->   `user_id` int(11) NOT NULL auto_increment,
    ->   `username` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL
    -> default '',
    ->   `pass` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL
    -> default '',
    ->   `firstname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL
    -> default '',
    ->   `initials` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL
    -> default '',
    ->   `lastname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL
    -> default '',
    ->   `active` enum('0','1') character set utf8 collate utf8_unicode_ci NOT NULL
    -> default '0',
    ->   PRIMARY KEY  (`user_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO `users` VALUES (1, 'user1', 'test', 'John', 'J.', 'Doe', '1');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO `users` VALUES (2, 'user2', 'test', 'Jane', 'J.', 'Doe', '1');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `users` VALUES (3, 'user3', 'test', 'Jack', 'J.', 'Doe', '1');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> INSERT INTO users
    -> SELECT user_id+3, username+'1', pass, firstname, initials, lastname, active
    -> FROM users;
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0