|
Hi Jocelyn,
I assume the CONNECT table is the same as in MDEV-6142.
Could you please paste the structure of the underlying table, and also try the same on it?
Is it reliably reproducible on the CONNECT table?
|
|
Hi Elena,
Yes it's the same than MDEV-6142, and the underlying table is a tokudb table :
CREATE TABLE `url_list` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`url` varchar(2048) NOT NULL DEFAULT '',
|
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`id_license_domain` int(10) unsigned NOT NULL DEFAULT '0',
|
`id_license` int(10) unsigned NOT NULL DEFAULT '0',
|
`dom` mediumint(8) unsigned NOT NULL DEFAULT '0',
|
`loadtime` mediumint(8) unsigned NOT NULL DEFAULT '0',
|
`gen` mediumint(8) unsigned NOT NULL DEFAULT '0',
|
`script` varchar(2048) NOT NULL DEFAULT '',
|
`database_time` mediumint(8) unsigned NOT NULL DEFAULT '0',
|
PRIMARY KEY (`id`),
|
KEY `date` (`date`)
|
) ENGINE=TokuDB AUTO_INCREMENT=116935390 DEFAULT CHARSET=latin1 `compression`='tokudb_small'
|
No problem with CTRL+C on the underlying table :
MariaDB [test]> SELECT SQL_NO_CACHE * FROM url_list;
|
^CCtrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
MariaDB [test]> SELECT SQL_NO_CACHE * FROM url_list;
|
^CCtrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
MariaDB [test]> SELECT * FROM url_list LIMIT 10;
|
SELECT `id`, `url`, `date`, `id_license_domain`, `id_license`, `dom`, `loadtime`, `gen`, `script`, `database_time` FROM `url_list`;
|
^CCtrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
MariaDB [test]> SELECT `id`, `url`, `date`, `id_license_domain`, `id_license`, `dom`, `loadtime`, `gen`, `script`, `database_time` FROM `url_list`;
|
^CCtrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
However the query with the CONNECT table always fails to CTRL+C.
|
|
I could not reproduce this behaviour. Example:
D:\CommonSource\mariadb-10.0.5\10.0-connect\client>mysql -u root
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 2
|
Server version: 5.5.5-10.0.11-MariaDB-debug Source distribution
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
mysql> use oshpd
|
Database changed
|
mysql> select d.diag, count(*) cnt from diag d, patients p where d.recordnb = p.recordnb
|
-> and ageyears < 17 and county = 30 and drg <> 11 and d.diag between 4296 and 9434
|
-> group by d.diag order by cnt desc;
|
Ctrl-C -- sending "KILL QUERY 2" to server ...
|
Ctrl-C -- query aborted.
|
ERROR 1317 (70100): Query execution was interrupted
|
mysql>
|
Are you sure it is only related to CONNECT? Did you try with other tables?
FYI: The diag and patients tables are CONNECT FIX tables based on plain text files. For instance:
CREATE TABLE `diag` (
|
`Recordnb` int(8) NOT NULL COMMENT 'Patient Discharge Record Number',
|
`Diagnb` smallint(2) NOT NULL COMMENT 'Diagnosis number' `flag`=8,
|
`RLN` varchar(9) NOT NULL COMMENT 'linkage number SSN' `flag`=10,
|
`Diag` int(5) NOT NULL COMMENT 'diagnosis' `flag`=19,
|
`DiagPresent` char(1) NOT NULL COMMENT 'diagnosis present at admission' `flag`=24,
|
KEY `XDIAG` (`Diag`),
|
KEY `XRCN` (`Recordnb`),
|
KEY `XDGN` (`Diagnb`),
|
KEY `XRDG` (`RLN`,`Diagnb`)
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=FIX `file_name`='E:/OSHPD/Hospital_Data/diag.txt' `readonly`=YES `block_size`=4096 `lrecl`=27;
|
|
|
Hi Jocelyn,
This might be explained by what you experimented in MDEV-6142. When you execute this query, all memory could have been eaten and the server crashed before you sent the Ctrl-C. Therefore, we are back to memory issue and this could not be related with Ctrl-C at all.
By the way, did you have a chance to test the fix I made to MYSQL table type (huge=1)?
Regards,
OLivier
|
|
Hi Olivier,
The Ctrl-C issue occurs even at the very begin of the query, when there is still plenty of RAM available, so I don't think it's related to a memory issue.
Unfortunately, I've not yet taken a look at your fix so far (perhaps you could give me an access to the already compiled .so to speed up things ? - I'm running on a 64 bits Debian platform).
Regards,
Jocelyn
|
|
Hi Jocelyn,
I don't see what I can do because I have been unable to reproduce this issue so far. Now even the query can last a long time it is quite possible that MySQL allocates the memory as soon as it nows how many rows can be in the result set and then fetch the data to fill it. This should be discussed with someone knowing how it proceeds.
About a compiled .so, this will not be possible immediately. I am currently traveling and be back home only for may 25. The computer I have here is a 32-bits machine. Home I have a 64-bits ubuntu machine, will it be compatible?
However, this may not be useful as this fix is included in the newly released MariaDB version 10.0.11.
Regards,
Olivier
|
|
Hi Olivier,
I didn't noticed it was bundled in 10.0.11. So I confirm with huge=1, the memory problem is now solved. However the CTRL+C issue is still there.
Basically, CTRL+C only occurs after all the rows from the underlying table are fetched.
MariaDB [(none)]> show full processlist;
|
+-----+------+-----------------+--------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+------+-----------------+--------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------+
|
| 316 | jira | 127.0.0.1:58794 | jiradb | Sleep | 57 | | NULL | 0.000 |
|
| 352 | root | localhost | NULL | Query | 0 | init | show full processlist | 0.000 |
|
| 355 | root | localhost | test | Killed | 6 | Sending data | SELECT * FROM url_list_connect LIMIT 1 | 0.000 |
|
| 356 | root | localhost | test | Query | 6 | Queried about 3250000 rows | SELECT `id`, `url`, `date`, `id_license_domain`, `id_license`, `dom`, `loadtime`, `gen`, `script`, `database_time` FROM `url_list` | 0.000 |
|
+-----+------+-----------------+--------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------+
|
|
MariaDB [(none)]> show full processlist;
|
+-----+------+-----------------+--------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+------+-----------------+--------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------+
|
| 316 | jira | 127.0.0.1:58794 | jiradb | Sleep | 11 | | NULL | 0.000 |
|
| 352 | root | localhost | NULL | Query | 0 | init | show full processlist | 0.000 |
|
| 355 | root | localhost | test | Killed | 80 | Sending data | SELECT * FROM url_list_connect LIMIT 1 | 0.000 |
|
| 356 | root | localhost | test | Query | 80 | Queried about 40130000 rows | SELECT `id`, `url`, `date`, `id_license_domain`, `id_license`, `dom`, `loadtime`, `gen`, `script`, `database_time` FROM `url_list` | 0.000 |
|
+-----+------+-----------------+--------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------+
|
4 rows in set (0.00 sec)
|
Thanks,
Jocelyn
|
|
Hi Jocelyn,
I've built ha_connect.so from the top of 10.0-connect tree, if you want to try it, it's here: ftp://ftp.askmonty.org/public/ha_connect.so
Hope it will work for you. Built in a Wheezy 64-bit machine.
|
|
Hi Elena,
Thanks for the build, I'll try to find some time to test it and if it still fails I'll try to make a reproducable testcase.
|
|
Hi Jocelyn,
By the way, your query is using a MYSQL table. This means that there are two queries executing, the one you entered and the one CONNECT sent to the other server. This means that perhaps, when the first query is interrupted, an interrupt signal should also be sent to the other server (which can be the same one with CONNECT)
Did you try this with the FEDERATED engine? If it works I could try to see what it does and try to do the same thing.
Olivier
|
|
Hi Olivier,
FYI, the test I've done is with a table on the same server (and actually in the same database).
Jocelyn
|
|
Hi Jocelyn,
Are you still experiencing the problem with Ctrl+C? Should we keep the bug open?
|
|
Hi Elena,
I've just made the test with 10.0.13, and the issue is still there.
Jocelyn
|
|
This seems to be fairly complex. Testing with the FEDERATED engine, I got these results:
MariaDB [test]> select * from fediag limit 10;
|
Ctrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
It seems OK, however after canceling, the remote query seemed to continue and the second message came only after 2 minutes, the same time the query was taking when not interrupted.
Pressing twice Ctrl-C had the following effect:
MariaDB [test]> select * from fediag limit 10;
|
Ctrl-C -- query killed. Continuing normally.
|
^C
|
D:\MariaDB 10.0\bin>mysql
|
The second one interrupted the whole session.
|
|
Now I have done the same tests with a CONNEC table of MYSQL type:
MariaDB [test]> select * from remdiag limit 10;
|
Ctrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
Same waiting for two minutes before the "Query execution was interrupted" was printed.
And with two Ctrl-C:
MariaDB [test]> select * from remdiag limit 10;
|
Ctrl-C -- query killed. Continuing normally.
|
^C
|
D:\MariaDB 10.0\bin>
|
See? Exactly the same behaviour. Therefore this is not a CONNECT issue but an issue about how to interrupt remote queries.
|
|
So, the problem here is that upon Ctrl+C, the "local" query gets killed, but the "remote" query keeps running.
I can reproduce it quite easily with both Connect and Federated as the local table, TokuDB and InnoDB as the remote table with a manual test – 1,000,000 rows in the underlying table give me enough time to send Ctrl+C, after that I can see the "local" query in the Killed state and the "remote" query in the working state for about the same time as it normally takes to execute the whole thing.
In MySQL, the whole Ctrl+C thing seems to work a bit differently, but the result is basically the same – not everything gets interrupted.
I'll leave it to serg to decide whether it's a bug and needs to be fixed. After all, Ctrl+C apparently just sends KILL QUERY to the connection where it's triggered; and the "remote" query is running in a separate connection, so why would it be interrupted?.. Expectedly, the same happens if I run KILL QUERY <local connection> from another thread.
I suppose the semantics of Ctrl+C or KILL QUERY can be changed so that it also kills queries in "child" connections, but I don't know whether it's desirable.
|
|
There is nothing a server can do here. It must be done inside the engine, each engine separately, and basically would need a complete refactoring of how it communicates with the remote server.
It's easy make the engine to interrupt remote query while reading results. But interrupting the wait would, basically, need switching to the async API. And I don't even think it's possible in the server. Alternatively it can be emulated with a separate io thread. It's doable, but a rather big change.
|