[MDEV-7415] Query timeout expired' from CONNECT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
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

Attachments

Activity

Ascending order - Click to sort in descending order

View 7 older comments

Takuya Aoki (Inactive) added a comment - 2015-01-28 14:39 - edited

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

Takuya Aoki (Inactive) added a comment - 2015-01-28 14:39 - edited 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

Olivier Bertrand added a comment - 2015-01-29 11:42

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.

Olivier Bertrand added a comment - 2015-01-29 11:42 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.

Takuya Aoki (Inactive) added a comment - 2015-01-29 12:10

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.

Takuya Aoki (Inactive) added a comment - 2015-01-29 12:10 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.

Takuya Aoki (Inactive) added a comment - 2015-01-30 11:52

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

Takuya Aoki (Inactive) added a comment - 2015-01-30 11:52 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

Takuya Aoki (Inactive) added a comment - 2015-03-07 15:08

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.

Takuya Aoki (Inactive) added a comment - 2015-03-07 15:08 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.

People

Assignee:: Olivier Bertrand

Reporter:: Takuya Aoki (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Due:: 2015-01-29

Created:: 2015-01-07 11:36

Updated:: 2015-03-07 15:08

Resolved:: 2015-01-13 20:45

Git Integration

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