[CONJ-287] Invalid query generated on prepared statement - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 1.3.7, 1.4.2
Fix Version/s: 1.4.3
Component/s: Other
Labels:
None

Description

This is my first issue here, so if I forgot to add some information, just ask
I've tested the following with 1.3.7 and 1.4.2. On 1.3.6, the behaviour is correct.

I have a prepared statement as follows:

INSERT INTO  `tableX`  (`textA`, `dateB`, `textC`, `longD`, `tinyintE`) VALUES  (?, ?, ?, ?, ?) ,  (?, ?, ?, ?, ?) ,  (?, ?, ?, ?, ?) ,  (?, ?, ?, ?, ?)

And I add the following parameters (with the Java classes between parentheses):

150 (String), 2016-04-23 16:40:20.804 (Timestamp), 1:142:14895 (String), 5528 (Long), 0 (Integer),

null, 2016-04-23 16:40:20.804 (Timestamp), 1:102:1712 (String), 5528 (Long), 0 (Integer),

null, 2016-04-23 16:40:20.804 (Timestamp), 1:102:1159 (String), 5528 (Long), 0 (Integer),

[] (String), 2016-04-23 16:40:20.804 (Timestamp), 1:142:4142 (String), 5528 (Long), 0 (Integer)

Nevertheless, I get the following error (in v1.4.2):

org.mariadb.jdbc.internal.util.dao.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL ,  ('2016-04-23 16:40:20.804', '1:102:1712', 5528, 0, NULL) ,  ('2016-04-23' at line 1

Query is : INSERT INTO  `tableX`  (`textA`, `dateB`, `textC`, `longD`, `tinyintE`) VALUES ('150', '2016-04-23 16:40:20.804', '1:142:14895', 5528, 0)<null> ,  ('2016-04-23 16:40:20.804', '1:102:1712', 5528, 0, <null>) ,  ('2016-04-23 16:40:20.804', '1:102:1159', 5528, 0, '[]') ,  ('2016-04-23 16:40:20.804', '1:142:4142', 5528, 0, )

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:870)

                at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQueries(AbstractQueryProtocol.java:789)

                at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:498)

                at org.mariadb.jdbc.internal.failover.AbstractMastersListener.invoke(AbstractMastersListener.java:314)

                at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:155)

                at com.sun.proxy.$Proxy22.executeQueries(Unknown Source)

                at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:177)

I can't completely figure out why (maybe the null value?) but my initial prepared statement gets changed into an invalid one. As I said, I have encountered this error with Connector/J versions 1.3.7 and 1.4.2 in (as it seems) all queries that use this multiple INSERT syntax. In version 1.3.6 it works as it should.

I hope you can fix this, because this is preventing me from upgrading to the latest version.

Attachments

Issue Links

duplicates

CONJ-283 MariaDbClientPreparedStatement - syntax error on insert values

Closed

relates to

CONJ-277 Incorrect error when dealing with typo in on duplicate key query

Closed

Activity

Ascending order - Click to sort in descending order

Bart Laarhoven created issue - 2016-04-23 15:24

Bart Laarhoven made changes - 2016-04-23 15:28

Field	Original Value	New Value
Description	This is my first issue here, so if I forgot to add some information, just ask ;) I've tested the following with 1.3.7 and 1.4.2. On 1.3.6, the behaviour is correct. I have a prepared statement as follows: {code:sql} INSERT INTO `tableX` (`textA`, `dateB`, `textC`, `longD`, `tinyintE`) VALUES (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?){code} And I add the following parameters (with the Java classes between parentheses): {noformat} 150 (String), 2016-04-23 16:40:20.804 (Timestamp), 1:142:14895 (String), 5528 (Long), 0 (Integer), null, 2016-04-23 16:40:20.804 (Timestamp), 1:102:1712 (String), 5528 (Long), 0 (Integer), null, 2016-04-23 16:40:20.804 (Timestamp), 1:102:1159 (String), 5528 (Long), 0 (Integer), [] (String), 2016-04-23 16:40:20.804 (Timestamp), 1:142:4142 (String), 5528 (Long), 0 (Integer) {noformat} Nevertheless, I get the following error (in v1.4.2): {noformat} org.mariadb.jdbc.internal.util.dao.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0 , (5531, NULL, '2016-04-23 16:50:08.065', '1:102:1697', 0) , (5531, 0, '2016' at line 1 Query is : INSERT INTO `tableX` (`textA`, `dateB`, `textC`, `longD`, `tinyintE`) VALUES (0, 5531, 0, '2016-04-23 16:50:08.065', '1:142:30042')0 , (5531, <null>, '2016-04-23 16:50:08.065', '1:102:1697', 0) , (5531, 0, '2016-04-23 16:50:08.065', '1:142:30011', 0) , (5531, <null>, '2016-04-23 16:50:08.065', '1:142:6666', 0) , (5531, 'Test a', '2016-04-23 16:50:08.065', '1:102:1663', 0) , (5531, '[]', '2016-04-23 16:50:08.065', '1:142:30311', 0) , (5531, <null>, '2016-04-23 16:50:08.065', '1:102:130', 0) , (5531, '[]', '2016-04-23 16:50:08.065', '1:142:4387', 0) , (5531, <null>, '2016-04-23 16:50:08.065', '1:102:1704', 0) , (5531, 'casetype', '2016-04-23 16:50:08.065', '1:102:129', 0) , (5531, 'Test', '2016-04-23 16:50:08.065', '1:1:4', ) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:870) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQueries(AbstractQueryProtocol.java:789) at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mariadb.jdbc.internal.failover.AbstractMastersListener.invoke(AbstractMastersListener.java:314) at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:155) at com.sun.proxy.$Proxy22.executeQueries(Unknown Source) at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:177) {noformat} I can't completely figure out why (maybe the null value?) but my initial prepared statement gets changed into an invalid one. As I said, I have encountered this error with Connector/J versions 1.3.7 and 1.4.2 in (as it seems) all queries that use this multiple INSERT syntax. In version 1.3.6 it works as it should. I hope you can fix this, because this is preventing me from upgrading to the latest version.	This is my first issue here, so if I forgot to add some information, just ask ;) I've tested the following with 1.3.7 and 1.4.2. On 1.3.6, the behaviour is correct. I have a prepared statement as follows: {code:sql} INSERT INTO `tableX` (`textA`, `dateB`, `textC`, `longD`, `tinyintE`) VALUES (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?){code} And I add the following parameters (with the Java classes between parentheses): {noformat} 150 (String), 2016-04-23 16:40:20.804 (Timestamp), 1:142:14895 (String), 5528 (Long), 0 (Integer), null, 2016-04-23 16:40:20.804 (Timestamp), 1:102:1712 (String), 5528 (Long), 0 (Integer), null, 2016-04-23 16:40:20.804 (Timestamp), 1:102:1159 (String), 5528 (Long), 0 (Integer), [] (String), 2016-04-23 16:40:20.804 (Timestamp), 1:142:4142 (String), 5528 (Long), 0 (Integer) {noformat} Nevertheless, I get the following error (in v1.4.2): {noformat} org.mariadb.jdbc.internal.util.dao.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL , ('2016-04-23 16:40:20.804', '1:102:1712', 5528, 0, NULL) , ('2016-04-23' at line 1 Query is : INSERT INTO `tableX` (`textA`, `dateB`, `textC`, `longD`, `tinyintE`) VALUES ('150', '2016-04-23 16:40:20.804', '1:142:14895', 5528, 0)<null> , ('2016-04-23 16:40:20.804', '1:102:1712', 5528, 0, <null>) , ('2016-04-23 16:40:20.804', '1:102:1159', 5528, 0, '[]') , ('2016-04-23 16:40:20.804', '1:142:4142', 5528, 0, ) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:870) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQueries(AbstractQueryProtocol.java:789) at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mariadb.jdbc.internal.failover.AbstractMastersListener.invoke(AbstractMastersListener.java:314) at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:155) at com.sun.proxy.$Proxy22.executeQueries(Unknown Source) at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:177) {noformat} I can't completely figure out why (maybe the null value?) but my initial prepared statement gets changed into an invalid one. As I said, I have encountered this error with Connector/J versions 1.3.7 and 1.4.2 in (as it seems) all queries that use this multiple INSERT syntax. In version 1.3.6 it works as it should. I hope you can fix this, because this is preventing me from upgrading to the latest version.

Bart Laarhoven added a comment - 2016-04-23 15:34

Same syntax behavior in ~~CONJ-277~~

Bart Laarhoven added a comment - 2016-04-23 15:34 Same syntax behavior in CONJ-277

Bart Laarhoven made changes - 2016-04-23 15:34

Link

This issue relates to ~~CONJ-277~~ [ ~~CONJ-277~~ ]

Diego Dupin made changes - 2016-04-25 06:52

Link

This issue duplicates ~~CONJ-283~~ [ ~~CONJ-283~~ ]

Diego Dupin added a comment - 2016-04-25 06:54

solved in 1.4.3. see ~~CONJ-283~~

Diego Dupin added a comment - 2016-04-25 06:54 solved in 1.4.3. see CONJ-283

Diego Dupin made changes - 2016-04-25 06:55

Component/s		Other [ 12201 ]
Fix Version/s		1.4.3 [ 22011 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:27

Workflow

MariaDB v3 [ 75433 ]

MariaDB v4 [ 134864 ]

People

Assignee:: Diego Dupin

Reporter:: Bart Laarhoven

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2016-04-23 15:24

Updated:: 2016-04-25 06:55

Resolved:: 2016-04-25 06:55

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Connector/J