Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.13
-
Fix Version/s: 10.0.16
-
Component/s: Storage Engine - Connect
-
Labels:None
-
Environment:CentOS release 6.5 (X86_64)
Description
My connect table is not working due to timeout reasons.
Is there a way I can configure to change the query timeout.
/etc/odbc.ini
[test] DSN = test Driver = ODBC Driver 11 for SQL Server Trace = Yes Server = 10.0.1.102 Port = 1433 Database = testdb Language = us_english
/etc/odbcinst.ini
[ODBC Driver 11 for SQL Server] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1
test on isql
isql test uid PASSWORD select TOP(1) * FROM dmi_vCustomer2; (Returns 1 row 66 seconds later.)
error on MariaDB
CREATE OR REPLACE TABLE ms_dmi_vCustomer2 ( customer_no int DEFAULT NULL ,post_code varchar(7) DEFAULT NULL ,customer_category smallint DEFAULT NULL ,mail_address varchar(100) DEFAULT NULL ,sex int DEFAULT NULL ,birthday char(23) DEFAULT NULL ,update_date char(23) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=test;UID=uid;pwd=PASSWORD' `table_type`=ODBC `block_size`=500 `tabname`='dmi_vCustomer2'; select * from ms_dmi_vCustomer2 limit 10; (Returns the following error 27 seconds later.) ERROR 1296 (HY000): Got error 122 '[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired' from CONNECT
*.err file
S1T00: [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired, Native=0 [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expiredrnd_next CONNECT: [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired GEM(73): [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired external_lock: this=0x7fe8e6894a20 thd=0x7fe8f96fc008 xp=0x7fe8e69141a0 g=0x7fe8e68e7000 lock_type=2 ODBC CloseDB: closing ms_dmi_vCustomer2
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I don't have the original environment at the moment so I did a reverse test to see how the option list works in 10.0.16 release.
Shouldn't the query fail in the below case since the query takes longer than the ConnectTimeout or QueryTimeout whcih is 1 second?
[root@kc1060 ~]# mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.0.16-MariaDB MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use test2; Database changed MariaDB [test2]> CREATE OR REPLACE TABLE maria_table_user ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_user' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36,QueryTimeout=1,ConnectTimeout=1'; Query OK, 0 rows affected (0.05 sec) MariaDB [test2]> SELECT family_no, count(*) FROM maria_table_user WHERE export_date<'2015-01-01' AND init_date>'2001-01-01' GROUP BY family_no; +-----------+----------+ | family_no | count(*) | +-----------+----------+ | 0 | 104290 | | 1 | 34576 | | 2 | 54312 | | 3 | 49747 | | 4 | 2640 | | 5 | 54887 | | 6 | 324 | | 7 | 203 | | 8 | 129 | | 9 | 15 | | 10 | 830 | | 11 | 13 | | 12 | 11 | | 13 | 8 | | 14 | 3 | | 15 | 52 | | 16 | 3 | | 17 | 4 | | 20 | 18 | | 23 | 1 | | 25 | 3 | | 30 | 6 | | 36 | 1 | | 48 | 1 | +-----------+----------+ 24 rows in set (7.00 sec)
record in log file(/var/lib/mysql/servername.err)
MyColumns: cmd='SHOW FULL COLUMNS FROM table_user FROM test' AVB: mp=(nil) type=1 nval=6 len=12 check=1 blank=0 AVB: mp=(nil) type=3 nval=6 len=4 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=16 check=1 blank=0 AVB: mp=(nil) type=7 nval=6 len=4 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=4 check=1 blank=0 AVB: mp=(nil) type=3 nval=6 len=4 check=1 blank=0 AVB: mp=(nil) type=3 nval=6 len=4 check=1 blank=0 AVB: mp=(nil) type=3 nval=6 len=4 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=0 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=0 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=0 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=0 check=1 blank=0 AVB: mp=(nil) type=1 nval=6 len=0 check=1 blank=0 s_init: CREATE TABLE whatever (`company_id` CHAR(4) NOT NULL DEFAULT 'DMI',`user_id` CHAR(16) NOT NULL DEFAULT 'no_data',`init_date` DATE,`init_channel` CHAR(2),`family_no` TINYINT(4),`export_date` DATETIME) TABLE_TYPE='MYSQL' TABNAME='table_user' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36,QueryTimeout=1,ConnectTimeout=1' CHARSET=utf8 New CONNECT 0x7f938b0b2020, table: <null> Delete CONNECT 0x7f938b0b2020, table: <null>, xp=(nil) count=0 New CONNECT 0x7f938b06b220, table: maria_table_user Delete CONNECT 0x7f938b06b220, table: <null>, xp=(nil) count=0 New CONNECT 0x7f938b06b220, table: maria_table_user Delete CONNECT 0x7f938b06b220, table: <null>, xp=(nil) count=0 New CONNECT 0x7f938b06c620, table: maria_table_user open: name=./test2/maria_table_user mode=2 test=18 external_lock: this=0x7f938b06c620 thd=0x7f939cab2008 xp=0x7f938b0db080 g=0x7f938b0d9000 lock_type=0 0x7f938b06c620 check_mode: cmdtype=0 Cmd=SELECT family_no, count(*) FROM maria_table_user WHERE export_date<'2015-01-01' AND init_date>'2001-01-01' GROUP BY family_no New mode=10 Calling CntCheckDB db=test2 cras=0 external_lock: rc=0 0x7f938b06c620 In info: flag=18 valid_info=0 XTAB: making new TABLE maria_table_user (null) Cond type=12 Cond: Ftype=12 name=and Cond type=1 Func type=4 argnum=2 Argtype(0)=0 Field index=5 Field name=export_date Argtype(1)=3 Value=2015-01-01 Cond type=1 Func type=7 argnum=2 Argtype(0)=0 Field index=2 Field name=init_date Argtype(1)=3 Value=2001-01-01 cond_push: (export_date < '2015-01-01' AND init_date > '2001-01-01') rnd_init: this=0x7f938b06c620 scan=1 xmod=10 alter=0 ColDB: am=192 colname=init_date tabname=maria_table_user num=0 cdp(3).Name=init_date cp=(nil) making new MYSQLCOL C3 init_date at 0x7f9382c00878 colp=0x7f9382c00878 ColDB: am=192 colname=family_no tabname=maria_table_user num=0 cdp(5).Name=family_no cp=(nil) making new MYSQLCOL C5 family_no at 0x7f9382c00900 colp=0x7f9382c00900 ColDB: am=192 colname=export_date tabname=maria_table_user num=0 cdp(6).Name=export_date cp=(nil) making new MYSQLCOL C6 export_date at 0x7f9382c00988 colp=0x7f9382c00988 MakeDateFormat: dfmt=YYYY-MM-DD hh:mm:ss Done: in=%4d-%2d-%2d %2d:%2d:%2d out=%Y-%m-%d %H:%M:%S MakeDateFormat: dfmt=YYYY-MM-DD hh:mm:ss Done: in=%4d-%2d-%2d %2d:%2d:%2d out=%Y-%m-%d %H:%M:%S Query=SELECT `init_date`, `family_no`, `export_date` FROM `table_user` WHERE (export_date < '2015-01-01' AND init_date > '2001-01-01') Cond type=12 Cond: Ftype=12 name=and Cond type=1 Func type=4 argnum=2 Argtype(0)=0 Field index=5 Field name=export_date Argtype(1)=3 Value=2015-01-01 Cond type=1 Func type=7 argnum=2 Argtype(0)=0 Field index=2 Field name=init_date Argtype(1)=3 Value=2001-01-01 cond_push: (export_date < '2015-01-01' AND init_date > '2001-01-01') external_lock: this=0x7f938b06c620 thd=0x7f939cab2008 xp=0x7f938b0db080 g=0x7f938b0d9000 lock_type=2 MySQL CloseDB: closing maria_table_user rc=0
Yes it is bizarre. It is what I also noted in my last comment.
By the way, does this make your original table to work? This was the first subject of this case.
I can't check right now but I will be able to soon.
It is a special case where I connected to a slow responding VPN server.
Not the query time but the time until a DSN replys matters, I guess.
I found the original error occurs when I set QueryTimeout=1 with CONNECT table to a view on SQL server.
It didn't occur with QueryTimeout=20.
I assume this solves the problem,I will also check in the original environment.
Thank you for the fix.
On SQL server
CREATE VIEW heavy_view AS SELECT CASE WHEN MAX(t3.charge) <> 0 THEN MIN(t2.order_no) / 3.14 ELSE 0 END AS ROW1 ,CASE WHEN MAX(t3.charge) <> 0 THEN MIN(t2.customer_no) / 3.14 ELSE 0 END AS ROW2 ,CASE WHEN MAX(t3.charge) <> 0 THEN MIN(t2.order_method) / 3.14 ELSE 0 END AS ROW3 ,CASE WHEN MAX(t3.charge) <> 0 THEN MIN(t2.pay_method) / 3.14 ELSE 0 END AS ROW4 ,CASE WHEN MAX(t3.charge) <> 0 THEN MAX(t2.order_no) / 3.14 ELSE 0 END AS ROW5 ,CASE WHEN MAX(t3.charge) <> 0 THEN MAX(t2.customer_no) / 3.14 ELSE 0 END AS ROW6 ,CASE WHEN MAX(t3.charge) <> 0 THEN MAX(t2.order_method) / 3.14 ELSE 0 END AS ROW7 ,CASE WHEN MAX(t3.charge) <> 0 THEN MAX(t2.pay_method) / 3.14 ELSE 0 END AS ROW8 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MIN(t2.order_no) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW9 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MIN(t2.customer_no) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW10 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MIN(t2.order_method) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW11 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MIN(t2.pay_method) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW12 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MAX(t2.order_no) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW13 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MAX(t2.customer_no) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW14 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MAX(t2.order_method) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW15 ,CASE WHEN MAX(t3.charge) <> 0 THEN 2 * MAX(t2.pay_method) + 2 * MIN(t2.order_no) ELSE 0 END AS ROW16 FROM wrk_dmi_vCustomer t1 LEFT JOIN dmi_vHeader t2 ON t1.customer_no = t2.customer_no LEFT JOIN dmi_vDelivery t3 ON t2.order_no = t3.order_no GROUP BY t1.customer_no ,t1.post_code,t2.order_madia,t2.pay_method ,t2.coupon_no,t3.charge;
On MariaDB 10.0.16
[root@kc1060 ~]# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.0.16-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use test;
No connection. Trying to reconnect...
Connection id: 57
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> CREATE OR REPLACE TABLE `ms_heavy_view`
-> ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=nlj_original;UID=SYSTEMDMI;pwd=H0geFuga' `table_type`=ODBC `block_size`=1 `tabname`='heavy_view';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 58
Current database: test
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> SELECT * FROM ms_heavy_view LIMIT 0,1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 59
Current database: test
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
| ROW1 | ROW2 | ROW3 | ROW4 | ROW5 | ROW6 | ROW7 | ROW8 | ROW9 | ROW10 | ROW11 | ROW12 | ROW13 | ROW14 | ROW15 | ROW16 |
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
| 4975.159235 | 318.152866 | 0.318471 | 0.636942 | 11642.356687 | 318.152866 | 0.318471 | 0.636942 | 62488 | 33242 | 31246 | 31248 | 104358 | 33242 | 31246 | 31248 |
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
1 row in set (3.40 sec)
MariaDB [test]> CREATE OR REPLACE TABLE `ms_heavy_view_1`
-> ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=nlj_original;UID=SYSTEMDMI;pwd=H0geFuga' `table_type`=ODBC `block_size`=1 `tabname`='heavy_view' OPTION_LIST='QueryTimeout=1,ConnectTimeout=1';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 60
Current database: test
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> SELECT * FROM ms_heavy_view_1 LIMIT 0,1;
ERROR 1296 (HY000): Got error 174 '[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired' from CONNECT
MariaDB [test]> CREATE OR REPLACE TABLE `ms_heavy_view_20`
-> ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=nlj_original;UID=SYSTEMDMI;pwd=H0geFuga' `table_type`=ODBC `block_size`=1 `tabname`='heavy_view' OPTION_LIST='QueryTimeout=20,ConnectTimeout=20';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 61
Current database: test
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> SELECT * FROM ms_heavy_view_20 LIMIT 0,1;
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
| ROW1 | ROW2 | ROW3 | ROW4 | ROW5 | ROW6 | ROW7 | ROW8 | ROW9 | ROW10 | ROW11 | ROW12 | ROW13 | ROW14 | ROW15 | ROW16 |
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
| 4975.159235 | 318.152866 | 0.318471 | 0.636942 | 11642.356687 | 318.152866 | 0.318471 | 0.636942 | 62488 | 33242 | 31246 | 31248 | 104358 | 33242 | 31246 | 31248 |
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
1 row in set (3.39 sec)
MariaDB [test]> CREATE OR REPLACE TABLE `ms_heavy_view_20v1`
-> ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=nlj_original;UID=SYSTEMDMI;pwd=H0geFuga' `table_type`=ODBC `block_size`=1 `tabname`='heavy_view' OPTION_LIST='QueryTimeout=20,ConnectTimeout=1';
Query OK, 0 rows affected (0.07 sec)
MariaDB [test]> SELECT * FROM ms_heavy_view_20v1 LIMIT 0,1;
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
| ROW1 | ROW2 | ROW3 | ROW4 | ROW5 | ROW6 | ROW7 | ROW8 | ROW9 | ROW10 | ROW11 | ROW12 | ROW13 | ROW14 | ROW15 | ROW16 |
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
| 4975.159235 | 318.152866 | 0.318471 | 0.636942 | 11642.356687 | 318.152866 | 0.318471 | 0.636942 | 62488 | 33242 | 31246 | 31248 | 104358 | 33242 | 31246 | 31248 |
+-------------+------------+----------+----------+--------------+------------+----------+----------+-------+-------+-------+-------+--------+-------+-------+-------+
1 row in set (3.40 sec)
MariaDB [test]> CREATE OR REPLACE TABLE `ms_heavy_view_20v2`
-> ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=nlj_original;UID=SYSTEMDMI;pwd=H0geFuga' `table_type`=ODBC `block_size`=1 `tabname`='heavy_view' OPTION_LIST='QueryTimeout=1,ConnectTimeout=20';
Query OK, 0 rows affected (0.07 sec)
MariaDB [test]> SELECT * FROM ms_heavy_view_20v2 LIMIT 0,1;
ERROR 1296 (HY000): Got error 174 '[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired' from CONNECT
I tested in the original environment where the below error occurred.
ERROR 1296 (HY000): Got error 122 '[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired' from CONNECT
Changing the QueryTimeout value (I set it to 3600) solved the problem.
Did you try to use QueryTimeout option in odbc.ini?
I am not an expert at ODBC, but the quick search suggests that there is at least an option with this name, so possibly it will work.