Bug #55579 'CREATE TABLE IF NOT EXISTS' behaves different with jdbc Vs. mysql client

• Submitted: 27 Jul 2010 8:42
• Modified: 27 Jul 2010 8:57
• Reporter: Nidhi Shrotriya
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.49
• OS: Any
• CPU Architecture: Any

[27 Jul 2010 8:42] Nidhi Shrotriya

Description:
With mysql client:
--------------------

mysql> CREATE TABLE IF NOT EXISTS testdb_wl5370.t7_1 SELECT 'hello' as a, 'world' as b; 
Query OK, 1 row affected, 3 warnings (0.00 sec) 
Records: 1  Duplicates: 0  Warnings: 0 

mysql> show warnings; 
+---------+------+----------------------------------------------------------+ 
| Level   | Code | Message                                                  | 
+---------+------+----------------------------------------------------------+ 
| Note    | 1050 | Table 't7_1' already exists                              | 
| Warning | 1366 | Incorrect integer value: 'hello' for column 'a' at row 1 | 
| Warning | 1366 | Incorrect integer value: 'world' for column 'b' at row 1 | 
+---------+------+----------------------------------------------------------+ 
3 rows in set (0.00 sec) 

mysql> SELECT * FROM testdb_wl5370.t7_1; 
+------+------+ 
| a    | b    | 
+------+------+ 
|    0 |    0 | 
+------+------+ 
1 row in set (0.00 sec) 

With jdbc:
----------
CREATE TABLE IF NOT EXISTS testdb_wl5370.t7_1 SELECT 'hello' as a, 'world' as b; 
stmt. gives Error 1366 (instead of warning) and inserts no rows. 

How to repeat:
DROP DATABASE IF EXISTS testdb_wl5370;
CREATE DATABASE testdb_wl5370;
CREATE TABLE testdb_wl5370.t7_1(a INT,b INT); 
CREATE TABLE IF NOT EXISTS testdb_wl5370.t7_1 SELECT 'hello' as a, 'world' as b; 
SELECT * FROM testdb_wl5370.t7_1;

[27 Jul 2010 8:57] Tonci Grgin

Hi Nadhi and thanks for your report.

First of all, please do not set "Verified" by yourself.

Next, do read http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html especially the part about jdbcCompliantTruncation and next time, do post *complete* test case so I do not have to guess.