|
I tried to reproduce this bug, but did not get crash.
On 10.1.24
OpenTable: Column DATE_FORMAT("2017-06-15", '%H:-812209645:') not in result set
|
On 10.1.25-debug
Error (Code 1105): (1064) 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 ':%i:%s') FROM seq_1_to_1') FROM seq_1_to_1 LIMIT 0' at line 1 [SELECT DATE_FORMAT("2017-06-15", '%H:446379243:SELECT DATE_FORMAT("2017-(1064) 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 ':%i:%s') FROM seq_1_to_1') FROM seq_1_to_1 LIMIT 0' at line 1 []
|
Error (Code 1030): Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT
|
Could you please provide error log and .cnf file.
Query works as expected with other DATE_FORMAT, besides '%s'.
'%s' is expected to work similar to '%S', but it looks like it is substituded by string '1=1'
10.1.25-MariaDB-debug
|
|
CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL
|
DBNAME='test' OPTION_LIST='host=127.0.0.1,port=3306;user=root'
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%s\') FROM seq_1_to_1';
|
SELECT * FROM t1;
|
+----------------------------------+
|
| DATE_FORMAT("2017-06-15", '1=1') |
|
+----------------------------------+
|
| 1=1 |
|
+----------------------------------+
|
|
CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL
|
DBNAME='test' OPTION_LIST='host=127.0.0.1,port=3306;user=root'
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%S\') FROM seq_1_to_1';
|
SELECT * FROM t1;
|
+---------------------------------+
|
| DATE_FORMAT("2017-06-15", '%S') |
|
+---------------------------------+
|
| 00 |
|
+---------------------------------+
|
|
|
|
alice, I've attached my.cnf.
The issue doesn't reproduce for 10.1.16.
We've debugged the issue for 10.1.24, please see the stack trace below. The SQL below is more complicated but in essence to reproduce using '%H:%i:%s' in DATE_FORMAT is enough.
(gdb) bt
|
#0 0x00007f0a0e8d88f2 in _IO_vfprintf_internal (s=0x7f09147ec270,
|
format=0x7f08634009f8 "SELECT \r\n\t\t\t\t\t\tCAST(CONCAT(m.name, ': ') as CHAR(100)) as c_header_pref,\r\n\t\t\t\t\t\tCAST(DATE_FORMAT(c.ts, '%d.%m.%Y %H:%i:%s') as CHAR(100)) as c_header_time,\r\n\t\t\t\t\t\tc.m_comment_id, c.ts,"..., ap=0x7f09147ec3a0) at vfprintf.c:2026
|
#1 0x00007f0a0e981e4d in ___vsprintf_chk (
|
s=0x7f08634111f0 "SELECT \r\n\t\t\t\t\t\tCAST(CONCAT(m.name, ': ') as CHAR(100)) as c_header_pref,\r\n\t\t\t\t\t\tCAST(DATE_FORMAT(c.ts, '1665208760.Inappropriate ioctl for device.%Y %H:-256:", flags=1,
|
slen=18446744073709551615,
|
format=0x7f08634009f8 "SELECT \r\n\t\t\t\t\t\tCAST(CONCAT(m.name, ': ') as CHAR(100)) as c_header_pref,\r\n\t\t\t\t\t\tCAST(DATE_FORMAT(c.ts, '%d.%m.%Y %H:%i:%s') as CHAR(100)) as c_header_time,\r\n\t\t\t\t\t\tc.m_comment_id, c.ts,"..., args=0x7f09147ec3a0) at vsprintf_chk.c:87
|
#2 0x00007f0a0e981d8f in ___sprintf_chk (s=0x13 <Address 0x13 out of bounds>, flags=343847568, slen=4294965616, format=0xffffffffffffffff <Address 0xffffffffffffffff out of bounds>) at sprintf_chk.c:33
|
#3 0x00007f0a08954763 in TDBMYSQL::MakeSelect(_global*, bool) () from /usr/lib/mysql/plugin/ha_connect.so
|
#4 0x00007f0a089557f3 in TDBMYSQL::OpenDB(_global*) () from /usr/lib/mysql/plugin/ha_connect.so
|
#5 0x00007f0a08906507 in CntOpenTable(_global, TDB, MODE, char, char, bool, ha_connect*) () from /usr/lib/mysql/plugin/ha_connect.so
|
#6 0x00007f0a088f924c in ha_connect::OpenTable(_global*, bool) () from /usr/lib/mysql/plugin/ha_connect.so
|
#7 0x00007f0a088f99c2 in ha_connect::rnd_init(bool) () from /usr/lib/mysql/plugin/ha_connect.so
|
#8 0x00007f0a10eafc09 in handler::ha_rnd_init_with_error(bool) ()
|
#9 0x00007f0a10fa2146 in init_read_record(READ_RECORD, THD, TABLE, SQL_SELECT, int, bool, bool) ()
|
#10 0x00007f0a10d69530 in join_init_read_record(st_join_table*) ()
|
#11 0x00007f0a10d69799 in sub_select(JOIN, st_join_table, bool) ()
|
#12 0x00007f0a10d774ed in ?? ()
|
#13 0x00007f0a10d8a570 in JOIN::exec_inner() ()
|
#14 0x00007f0a10d8c4fd in JOIN::exec() ()
|
#15 0x00007f0a10d88e9a in mysql_select(THD, Item*, TABLE_LIST*, unsigned int, List<Item>&, Item, unsigned int, st_order, st_order, Item, st_order, unsigned long long, select_result, st_select_lex_unit, st_select_lex*) ()
|
#16 0x00007f0a10d8c7dd in handle_select(THD, LEX, select_result*, unsigned long) ()
|
#17 0x00007f0a10d3b132 in mysql_execute_command(THD*) ()
|
#18 0x00007f0a10d3bfd4 in mysql_parse(THD, char, unsigned int, Parser_state*) ()
|
#19 0x00007f0a10d3ea53 in dispatch_command(enum_server_command, THD, char, unsigned int) ()
|
#20 0x00007f0a10d3efd1 in do_command(THD*) ()
|
#21 0x00007f0a10dfe24f in do_handle_one_connection(THD*) ()
|
#22 0x00007f0a10dfe387 in handle_one_connection ()
|
#23 0x00007f0a1048db50 in start_thread (arg=<optimized out>) at pthread_create.c:304
|
|
|
About replacement of "%s": Indeed they are replaced by a query WHERE clause or by 1=1 when no where clause is specified in a query.
This is a feature of SRCDEF's described in https://mariadb.com/kb/en/the-mariadb-library/connect-external-table-types/
I don't know whether this can explain the crash but it is an issue anyway. If the %s can be allways replaced in date format by %S, this is OK and just should be strongly stated in the documentation.
Otherwise, I should find a way to make the difference. There was a discussion about this in MDEV-11832 and we agreed that %s was Ok.
Trying the above example, it did not crash but reported an error:
1296: Got error 174 'Column DATE_FORMAT("2017-06-15", 'H:-1793358732:[]')
|
not in result set' from CONNECT
|
When changing %s by %S in the create table statement, it worked and replied:
| DATE_FORMAT("2017-06-15", '%H:%i:%S') |
| 00:00:00 |
|
|
Hi bertrandop, I have played a bit with this.
Here are the test cases with automatic table discovery and user-assisted discovery test cases
# automatic discover '%S'
|
CREATE TABLE t
|
ENGINE=CONNECT
|
TABLE_TYPE=MYSQL
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%H:%i:%S\') as d';
|
SHOW CREATE TABLE t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`d` varchar(13) DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `SRCDEF`='SELECT DATE_FORMAT("2017-06-15", ''%H:%i:%S'') as d'
|
SELECT * FROM t;
|
d
|
00:00:00
|
DROP TABLE t;
|
|
# user-assisted discover '%S'
|
CREATE TABLE t (d datetime)
|
ENGINE=CONNECT
|
TABLE_TYPE=MYSQL
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%H:%i:%S\') as d';
|
SHOW CREATE TABLE t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`d` datetime DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `SRCDEF`='SELECT DATE_FORMAT("2017-06-15", ''%H:%i:%S'') as d'
|
SELECT * FROM t;
|
d
|
1970-01-01 00:00:00 # ERROR 0 : date not good!
|
DROP TABLE t;
|
|
### ERROR 1
|
# automatic discover '%s'
|
CREATE TABLE t
|
ENGINE=CONNECT
|
TABLE_TYPE=MYSQL
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%H:%i:%s\') as d';
|
connect.anel-connect [ fail ]
|
Test ended at 2020-09-09 18:14:51
|
|
CURRENT_TEST: connect.anel-connect
|
mysqltest: At line 23: query 'CREATE TABLE t
|
ENGINE=CONNECT
|
TABLE_TYPE=MYSQL
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%H:%i:%s\') as d'' failed: 1105: (1064) 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 ':%i:%s') as d') as d LIMIT 0' at line 1 [SELECT DATE_FORMAT("2017-06-15", '%H:-956718997:SELECT DATE_FO(1064) 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 ':%i:%s') as d') as d LIMIT 0' at line 1 []
|
|
### ERROR 2
|
# user-assisted discover '%S'
|
CREATE TABLE t (d datetime)
|
ENGINE=CONNECT
|
TABLE_TYPE=MYSQL
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%H:%i:%s\') as d';
|
SHOW CREATE TABLE t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`d` datetime DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `SRCDEF`='SELECT DATE_FORMAT("2017-06-15", ''%H:%i:%s'') as d'
|
SELECT * FROM t;
|
connect.anel-connect [ fail ]
|
Test ended at 2020-09-09 18:16:23
|
|
CURRENT_TEST: connect.anel-connect
|
mysqltest: At line 29: query 'SELECT * FROM t' failed: 1296: Got error 174 '(1064) 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 ':-1845492160:') as d' at line 1 [SELECT DATE_FORMAT("20' from CONNECT
|
|
As we can see, we have different kinds of failures.
*Error 0*: table created but date is not the same as from srcdef
Checked with different date and time and is returning valid time format
SELECT * FROM t;
|
d
|
1970-01-01 23:12:11
|
*Error 1:*": For automatic discovery one kind of failure
mysqltest: At line 23: query 'CREATE TABLE t
|
ENGINE=CONNECT
|
TABLE_TYPE=MYSQL
|
SRCDEF='SELECT DATE_FORMAT("2017-06-15", \'%H:%i:%s\') as d'' failed: 1105: (1064) 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 ':%i:%s') as d') as d LIMIT 0' at line 1 [SELECT DATE_FORMAT("2017-06-15", '%H:-956718997:SELECT DATE_FO(1064) 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 ':%i:%s') as d') as d LIMIT 0' at line 1 []
|
*Error 2:*": For user-assisted table discovery different kind of failure
mysqltest: At line 29: query 'SELECT * FROM t' failed: 1296: Got error 174 '(1064) 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 ':-1845492160:') as d' at line 1 [SELECT DATE_FORMAT("20' from CONNECT
|
I would like to try to create a patch, but just would like to know directions about.
The logic is following:
1. Catch (somehow?) date_format in srcdef and test if there is `%s`. If exists, change it to `%S`.
2. Investigate(not sure which part of code?) why date column is returning 1970-01-01 lower bound of timestamp
|
my.cnf