Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.4.7, 10.4.15, 10.4.18
Description
Starting from 10.4.7+ (probably due to the fix for MDEV-16248) Spider seems to generate weird and unnecessary syntax for INSERTs in case the table has FLOAT column. Consider the following example (with proper spider user already added):
[openxs@fc31 maria10.4]$ bin/mysql -uopenxs --socket=/tmp/mariadb.sock test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 2029
|
Server version: 10.4.18-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [test]> drop table ti;
|
Query OK, 0 rows affected (0.070 sec)
|
|
MariaDB [test]> create table ti(id int primary key, i int, f float);
|
Query OK, 0 rows affected (0.115 sec)
|
|
MariaDB [test]> insert into ti values(1,1,1);
|
Query OK, 1 row affected (0.001 sec)
|
|
MariaDB [test]> select * from ti;
|
+----+------+------+
|
| id | i | f |
|
+----+------+------+
|
| 1 | 1 | 1 |
|
+----+------+------+
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> drop table ts;
|
Query OK, 0 rows affected (0.017 sec)
|
|
MariaDB [test]> create table ts(id int primary key, i int, f float) engine=spider comment 'host "127.0.0.1", user "spider", password "spider", table "ti", port
|
"3309"';
|
Query OK, 0 rows affected (0.047 sec)
|
|
MariaDB [test]> select @@port, @@spider_same_server_link;
|
+--------+---------------------------+
|
| @@port | @@spider_same_server_link |
|
+--------+---------------------------+
|
| 3309 | 1 |
|
+--------+---------------------------+
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> insert into ts values(2,2,2);
|
ERROR 1305 (42000): FUNCTION test.to_float does not exist
|
MariaDB [test]> exit
|
Bye
|
Looks like function creation on the fly fails. In the general query log we see what happens:
[openxs@fc31 maria10.4]$ tail data/fc31.log
|
201217 10:09:06 2029 Query select @@port, @@spider_same_server_link
|
201217 10:09:20 2029 Query insert into ts values(2,2,2)
|
2030 Connect spider@localhost as anonymous on
|
2030 Query set session transaction isolation level repeatable read;set session autocommit = 1;set session wait_timeout = 604800;set session sql_mode = 'strict_trans_tables,error_for_division_by_zero,no_auto_create_user,no_engine_substitution';set session time_zone = '+00:00';start transaction
|
2030 Query SET NAMES latin1
|
2030 Init DB test
|
2030 Query insert into `test`.`ts`(`id`,`i`,`f`)values(2,2,/* create function to_float(a decimal(20,6)) returns float return a */ to_float(2))
|
2030 Query rollback
|
2030 Quit
|
201217 10:09:24 2029 Quit
|
[openxs@fc31 maria10.4]$
|
INSERT works as expacted without FLOAT columns in the table.
Attachments
Issue Links
- causes
-
MDEV-33191 SIGSEGV in spider_db_delete_all_rows on TRUNCATE, UBSAN: member call on null pointer of type 'struct spider_db_handler' in spider_db_delete_all_rows
-
- Closed
-
It seems to help:
MariaDB [test]> show create table ti\G
*************************** 1. row ***************************
Table: ti
Create Table: CREATE TABLE `ti` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
`f` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0,000 sec)
MariaDB [test]> insert into ti values (1,2,3);
Query OK, 1 row affected (0,001 sec)
MariaDB [test]> select * from ti;
+----+------+------+
| id | i | f |
+----+------+------+
| 1 | 2 | 3 |
+----+------+------+
1 row in set (0,001 sec)
MariaDB [test]> show create table ts\G
*************************** 1. row ***************************
Table: ts
Create Table: CREATE TABLE `ts` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
`f` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='host "127.0.0.1", user "spider", password "spider", wrapper "mariadb", table "ti", port "3309"'
1 row in set (0,005 sec)
MariaDB [test]> insert into ts values (2,2,3);
Query OK, 1 row affected (0,003 sec)
MariaDB [test]> select * from ts;
+----+------+------+
| id | i | f |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 2 | 3 |
+----+------+------+
2 rows in set (0,005 sec)
but I see no reason for this NOT to work that way by default:
32 Connect spider@localhost as anonymous on
32 Query set session time_zone = '+00:00'
32 Query SET NAMES latin1
32 Init DB test
32 Query show table status from `test` like 'ti'
201217 18:18:04 31 Query insert into ts values (2,2,3)
32 Query set session transaction isolation level repeatable read;set session autocommit = 1;set session wait_timeout = 604800;set session sql_mode = 'strict_trans_tables,error_for_division_by_zero,no_auto_create_user,no_engine_substitution';start transaction
32 Query insert into `test`.`ti`(`id`,`i`,`f`)values(2,2,cast(3 as float))
32 Query commit
32 Quit
I am also not sure why cast is needed above.