Comportement du mysql-connector-java selon la variable sql_mode='STRICT_TRANS_TABLES'
Par PlaceOweb le mardi, novembre 20 2007, 00:22 - JAVA - Lien permanent
Selon les versions du connecteur java MySQL ou MariaDB, le comportement diffère et cela est principalement du au "sql_mode" utilisé par le connecteur.
Méthodes de connexion de l'API SQL vers un serveur MySQL
Impact du mode STRICT_TRANS_TABLES sur les champs NOT NULL
D'après les logs décrites ci après, on constate que PHP et seul le vieux connecteur java (3.0.17) ne définissent pas la variable sql_mode='STRICT_TRANS_TABLES'
Alors si vous vous connectez en mode STRICT_TRANS_TABLES et que n'avez pas définit de valeur par défaut pour des champs non null, ou que vous essayez d'insérer/remplacer (INSERT/REPLACE) un enregistrement sans spécifier toutes les colonnes non nulles de votre table, vous obtiendrez une erreur java.sql.SQLException: Field 'champsNonDefinit' doesn't have a default value
Pour plus d'info consultez la doc de Mysql a propos des Contraintes sur les valeurs invalides :
Si vous essayez de stocker NULL dans une colonne qui n'accepte pas la valeur NULL, MySQL stocke 0 ou '' (la chaîne vide). Ce dernier comportement peut, pour des insertions de ligne unique, être modifié par l'option de compilation -DDONT_USE_DEFAULT_FIELDS. See Section 2.4.2, « Options habituelles de configure ». Cela fait que les commandes INSERT génèreront une erreur à moins que vous ne spécifiez explicitement les valeurs pour toutes les colonnes qui requièrent une valeur non-NULL
Et pour plus d'info sur l'importance de STRICT_ALL_TABLES et STRICT_TRANS_TABLES.
Au bilan en java, pour être compatible avec les nouveaux connecteurs Java MySql, vous devez choisir parmi ces 2 possibilités :
- Définir à NULL la valeur de vos champs
- Définir à NOT NULL et définir la valeur par défaut tel que DEFAULT '' pour un type string,char,varchar (le type blob et text ne pouvant pas avoir de valeur par défaut)
Ceci est dû aux vérifications codées en dur dans les connecteurs > mysql-connector-java-3.0.17, ajoutant à la variable session sql_mode la valeur STRICT_TRANS_TABLES seulement si la variable sql_mode n'est pas définie (ou vide) sur votre serveur MySql.
Vous pouvez toute fois affecter la valeur false au paramètre jdbcCompliantTruncation lors de la connexion à MySQL pour outre passer ce problème. Cela vous permettra d'insérer un enregistrement sans spécifier tous les champs, bien que des champs non insérés n'aient pas de valeur par défaut définie.
jdbc:mysql://localhost/database?jdbcCompliantTruncation=false
Impact du mode STRICT_TRANS_TABLES sur les champs de taille inférieure à la donnée à stocker : DataTruncation
JDBC : Comment éviter le problème de DataTruncation survenant très souvent avec le MySQL Connector/J : com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column champsTropCourt at row 1
Des pertes de précisions, overflows, ... peuvent avoir lieu quand vous convertissez des valeurs numériques d'un type java vers MySql. Ceci va lever des exceptions, parfois non désirées.
Si vous voulez éviter ces désagréments, vous pouvez affecter la valeur false au paramètre jdbcCompliantTruncation lors de la connexion à MySQL. (Idéal pour un DataSource Jboss)
jdbc:mysql://localhost/database?jdbcCompliantTruncation=false
Ou dans l'objet Properties appelé lors de votre connexion sql.
Impact du mode STRICT_TRANS_TABLES sur les champs de type DATE
Egalement, il vous arrivera peut être de lire une date ou datetime NOT NULL dont la valeur par default est '0000-00-00 00:00:00'. Le problème est que cela lève une exception lorsque vous lirez le champs avec un rs.getDate('champsDate'): java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
Sur la fin de l'article, il reporté une note de la documentation du Connector/J :
Datetimes with all-zero components ("0000-00-00 ") - These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the " zeroDateTimeBehavior " configuration property. The allowable values are: "exception" (the default), which throws a SQLException with a SQLState of 'S1009', 'convertToNull', which returns NULL instead of the date, and "round", which rounds the date to the nearest closest value which is '0001-01-01'.
La solution est donc de typé a NULL son champs. Sinon en le laissant à NOT NULL il faut préciser le paramètre zeroDateTimeBehavior à convertToNull dans les propriétés de connexion du connecteur/J.
jdbc:mysql://localhost/database?user=root&password=secret&zeroDateTimeBehavior=convertToNull
Les logs
Voici les logs d'un serveur SQL avec divers connecteurs MySQL ou MariaDB pour la requête :
$pdo = new PDO('mysql:host=mariadb.devel;dbname=maBase', $user, $pass);
$stmt = $pdo->prepare("UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'");
$stmt->execute();
Class.forName("com.mysql.jdbc.Driver").newInstance();
Class.forName("com.mysql.cj.jdbc.Driver"); // changed from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-api-changes.html
Class.forName("org.mariadb.jdbc.Driver").newInstance();
connectionSQL = java.sql.DriverManager.getConnection(urlJdbc);
PreparedStatement psUpdate = connectionSQL.prepareStatement("UPDATE tableDeTest SET valeur = ? WHERE clef = ?");
psUpdate.setString(1, "laValeur");
psUpdate.setString(2, "laClef");
psUpdate.executeUpdate();
psUpdate.close();
connectionSQL.close()
#-- # Les logs des différentes API connecteur MySQL/MariaDB #--
-- En PHP avec un connecteur PDO
071107 16:11:57 49278 Connect phpmyadmin@127.0.0.1 on maBase
49278 Init DB maBase
49278 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
49278 Quit
-- En JAVA, connecteurs MySQL
Java 3.0 ( mysql-connector-java-3.0.17-ga-bin.jar )
071107 16:13:20 49279 Connect phpmyadmin@127.0.0.1 on maBase
49279 Init DB maBase
49279 Query SET NAMES latin1
49279 Query SET character_set_results = NULL
49279 Query select round('inf'), round('-inf'), round('nan')
49279 Query SHOW VARIABLES
49279 Query SHOW COLLATION
49279 Query SET autocommit=1
49279 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
Java 3.1 ( mysql-connector-java-3.1.14-bin.jar )
071107 16:25:49 49280 Connect phpmyadmin@127.0.0.1 on maBase
49280 Query SET NAMES latin1
49280 Query SET character_set_results = NULL
49280 Query SHOW VARIABLES
49280 Query SHOW COLLATION
49280 Query SET autocommit=1
49280 Query SET sql_mode='STRICT_TRANS_TABLES'
49280 Prepare [1] UPDATE tableDeTest SET valeur = ? WHERE clef = ?
49280 Execute [1] UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
Java 5.0 ( mysql-connector-java-5.0.8-bin.jar )
071107 16:26:42 49281 Connect phpmyadmin@127.0.0.1 on maBase
49281 Query SHOW SESSION VARIABLES
49281 Query SHOW COLLATION
49281 Query SET character_set_results = NULL
49281 Query SET autocommit=1
49281 Query SET sql_mode='STRICT_TRANS_TABLES'
49281 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
Java 5.1.x ( mysql-connector-java- 5.1.15, 5.1.17, 5.1.18, 5.1.19 et 5.1.21, 5.1.22 , 5.1.23, 5.1.25, 5.1.26, 5.1.28 -bin.jar )
26281 Query /* mysql-connector-java-5.1.23 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
26281 Query /* mysql-connector-java-5.1.23 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
26281 Query SHOW COLLATION
26281 Query SET NAMES latin1
26281 Query SET character_set_results = NULL
26281 Query SET autocommit=1
26281 Query SET sql_mode='STRICT_TRANS_TABLES'
130206 10:45:57 26281 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
26281 Quit
151325 Query /* mysql-connector-java-5.1.25 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
151326 Query /* mysql-connector-java-5.1.26 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
151328 Query /* mysql-connector-java-5.1.28 ( Revision: alexander.soklakov@oracle.com-20131125092425-yvejy3xvci77ru3k ) */SELECT @@session.auto_increment_increment
Java 5.1.x ( mysql-connector-java- 5.1.29-bin.jar )
151201 13:22:02 217 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
217 Query /* mysql-connector-java-5.1.29 ( Revision: alexander.soklakov@oracle.com-20140120140810-s44574olh90i6i4l ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
217 Query /* mysql-connector-java-5.1.29 ( Revision: alexander.soklakov@oracle.com-20140120140810-s44574olh90i6i4l ) */SELECT @@session.auto_increment_increment
217 Query SET NAMES latin1
217 Query SET character_set_results = NULL
217 Query SET autocommit=1
217 Query SET sql_mode='STRICT_TRANS_TABLES'
217 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
217 Quit
Java 5.1.x ( mysql-connector-java- 5.1.38-bin.jar )
160402 18:48:56 308818 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
308818 Query /* mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
308818 Query SET NAMES latin1
308818 Query SET character_set_results = NULL
308818 Query SET autocommit=1
308818 Query SET sql_mode='STRICT_TRANS_TABLES'
308818 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
308818 Quit
Java 5.1.x ( mysql-connector-java- 5.1.48-bin.jar )
191217 10:44:54 237375 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
237375 Query /* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
237375 Query SET NAMES latin1
237375 Query SET character_set_results = NULL
237375 Query SET autocommit=1
191217 10:44:55 237375 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
237375 Quit
Java 8.0.x ( mysql-connector-java-8.0.18.jar )
191217 10:48:38 237468 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
237468 Query /* mysql-connector-java-8.0.18 (Revision: fef2894d751d47223192b706977b4a5bc41e6be4) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
191217 10:48:39 237468 Query SET NAMES latin1
237468 Query SET character_set_results = NULL
237468 Query SET autocommit=1
237468 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
237468 Quit
Java 8.0.x ( mysql-connector-java-8.0.27.jar )
181021 11:11:11 237496 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
237496 Query /* mysql-connector-java-8.0.27 (Revision: e920b979015ae7117d60d72bcc8f077a839cd791) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
237496 Query SET character_set_results = NULL
237496 Query SET autocommit=1
237496 Query SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
237496 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
237496 Quit
Java 9.0.x ( mysql-connector-java-9.0.0.jar )
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| command_type | argument |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Connect | admin_xxxxxxx@xxxxxxxxxx.xxxxxxxxxxxxx.fr on maBase using SSL/TLS |
| Query | /* mysql-connector-j-9.0.0 (Revision: e0e8e3461e5257ba4aa19e6b3614a2685b298947) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout |
| Query | SET character_set_results = NULL |
| Query | SELECT @@session.autocommit |
| Query | SET autocommit=1 |
| Query | UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef' |
| Quit | |
+ -------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- En JAVA, connecteurs MariaDB Connector/J
MariaDB : mariadb-java-client-1.3.0.jar (du 1.3.0 jusqu''au 1.3.6 inclus)
151201 13:22:24 215 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
215 Query SELECT @@max_allowed_packet, @@system_time_zone, @@time_zone
215 Prepare UPDATE tableDeTest SET valeur = ? WHERE clef = ?
215 Execute UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
215 Close stmt
215 Quit
MariaDB : mariadb-java-client-1.3.7.jar
160401 16:36:15 307587 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
307587 Query SELECT @@max_allowed_packet, @@system_time_zone, @@time_zone
307587 Prepare UPDATE tableDeTest SET valeur = ? WHERE clef = ?
307587 Execute UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
307587 Quit
MariaDB : mariadb-java-client-1.4.0.jar (du 1.4.0 jusqu''à au moins 1.4.3 inclus)
160401 17:15:31 307670 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
307670 Query SELECT @@max_allowed_packet, @@system_time_zone, @@time_zone, @@sql_mode
307670 Query set session autocommit=1,sql_mode='IGNORE_SPACE,STRICT_TRANS_TABLES'
307670 Prepare UPDATE tableDeTest SET valeur = ? WHERE clef = ?
307670 Execute UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
307670 Quit
MariaDB : mariadb-java-client-1.4.5.jar
191217 11:06:01 237709 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
237709 Query SHOW VARIABLES WHERE Variable_name in ('max_allowed_packet', 'system_time_zone', 'time_zone', 'sql_mode')
237709 Query set session autocommit=1
237709 Prepare UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
237709 Execute UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
237709 Prepare SELECT VERSION()
237709 Execute SELECT VERSION()
237709 Quit
MariaDB : 1.8.0 / 2.4.4 / 2.52 : mariadb-java-client-1.8.0.jar / mariadb-java-client-2.4.4.jar / mariadb-java-client-2.5.2.jar
191217 11:08:31 237712 Connect phpmyadmin@127.0.0.1 as anonymous on maBase
237712 Query set autocommit=1, session_track_schema=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')
237712 Query SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment
237712 Query UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
237712 Query SELECT VERSION()
237712 Quit
La table SQL
CREATE DATABASE `maBase`;
CREATE TABLE `maBase`.`tableDeTest` (
`clef` VARCHAR( 20 ) NULL DEFAULT NULL ,
`valeur` VARCHAR( 20 ) NULL DEFAULT NULL
) ENGINE = MYISAM COMMENT = 'Table de test';
INSERT INTO `maBase`.`tableDeTest` (`clef`, `valeur`) VALUES ('laClef', NULL);