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.

jdbc:mysql://localhost/database?user=root&password=secret&zeroDateTimeBehavior=convertToNull
Les logs

Voici les logs d'un serveur MySql avec divers connecteurs MySql pour la requête :

UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
#--
# 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 ( mysql-connector-java-5.1.5-bin.jar )
071107 16:28:05   49282 Connect     phpmyadmin@192.168.30.204 on maBase
                  49282 Query       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'
                  49282 Query       SHOW COLLATION
                  49282 Query       SET NAMES latin1
                  49282 Query       SET character_set_results = NULL
                  49282 Query       SET autocommit=1
                  49282 Query       SET sql_mode='STRICT_TRANS_TABLES'
                  49282 Query       UPDATE tableDeTest SET valeur = 'laValeur' WHERE clef = 'laClef'
Ressources