Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.3
-
None
-
Debian GNU/Linux 12 (bookworm)
Description
Testing two identical "INSERT ... ON DUPLICATE KEY UPDATE" SQL query into Spider table.
First try - success.
Second try - error:
SQL Error [1136] [21S01]: (conn=121265) Column count doesn't match value count at row 1
|
Please note, that this simple query could be executed successfully multiple times on the first (original) table. But on Spider table - only once (while the original table is still empty). The UPDATE part is not working on Spider table.
Environment: two databases on the same server, with spider enabled. Both contains similar table (one original, and one spider table, linked with the first one).
Prepare environment:
DROP USER IF EXISTS 'test_user';
|
DROP DATABASE IF EXISTS `test_db1`;
|
DROP DATABASE IF EXISTS `test_db2`;
|
DROP SERVER IF EXISTS `test_srv1`;
|
|
CREATE DATABASE `test_db1`;
|
CREATE DATABASE `test_db2`;
|
|
CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';
|
|
GRANT ALL PRIVILEGES ON `test_db1`.* TO 'test_user'@'%';
|
GRANT ALL PRIVILEGES ON `test_db2`.* TO 'test_user'@'%';
|
|
CREATE SERVER `test_srv1` FOREIGN DATA WRAPPER mysql OPTIONS(
|
HOST '127.0.0.1',
|
USER 'test_user',
|
PASSWORD 'password',
|
PORT 3306);
|
|
USE `test_db1`;
|
|
CREATE TABLE `table1` (
|
`id` bigint(20) unsigned NOT NULL,
|
`source` int(10) unsigned NOT NULL,
|
`data` int(10) unsigned DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB;
|
|
USE `test_db2`;
|
|
CREATE TABLE `table1` (
|
`id` bigint(20) unsigned NOT NULL,
|
`source` int(10) unsigned NOT NULL,
|
`data` int(10) unsigned DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=SPIDER REMOTE_SERVER="test_srv1" REMOTE_DATABASE="test_db1" REMOTE_TABLE="table1";
|
Test:
USE `test_db2`;
|
|
#first try, success
|
INSERT INTO `table1`
|
(id, source)
|
VALUES
|
(1,2),
|
(2,2)
|
ON DUPLICATE KEY UPDATE
|
`source`=IFNULL(VALUES(`source`), `source`)
|
;
|
 |
#second try, same SQL, failed
|
#ERROR: SQL Error [1136] [21S01]: (conn=121265) Column count doesn't match value count at row 1
|
INSERT INTO `table1`
|
(id, source)
|
VALUES
|
(1,2),
|
(2,2)
|
ON DUPLICATE KEY UPDATE
|
`source`=IFNULL(VALUES(`source`), `source`)
|
;
|
Expected results:
Both INSERTs are successful.
First INSERT inserts 2 rows.
Second INSERT updates 2 rows.
Actual results:
First INSERT inserts 2 rows.
Second INSERT - failed:
SQL Error [1136] [21S01]: (conn=121265) Column count doesn't match value count at row 1