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 :

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
#--
PHP
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
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

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'à l'actuel 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

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);

Ressources