Méthodes de connexion de l'API SQL vers un serveur MySQL

Impact du mode STRICTTRANSTABLES 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 sqlmode='STRICTTRANS_TABLES'

Alors si vous vous connectez en mode STRICTTRANSTABLES 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 -DDONTUSEDEFAULT_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 STRICTTRANSTABLES 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 STRICTTRANSTABLES 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 STRICTTRANSTABLES 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("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


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

Ressources