Long query for testing

Question

I'm trying some fault-tolerance and in aplication and does anyone know of a long executing query using the default mysql tables?

The idea is to run that query, crash the mysqld to see if my app detects the error and tries to connect to another mysqld.

Thanks

score 0 · Answer 1 · answered Jun 23 '13 at 18:04

0

Even the longest query will not crash the mysqld! you might lose the connection, depending on the driver you are using, but I believe the server will run and run and run until it is finished. And long running queries will be hard to do with the standard tables. In my experience, altering indexes on very big (10's ir better 100's of millions of rows, depending on the spedd/ram of your server) will keep it busy for quite a while

answered Jun 23 '13 at 18:04

Steffen Nieuwenhoven

505
8
21

I don't think the OP wants the query to crash mysqld. I think s/he wants to do that separately. The OP just wants to be sure that a query is running. – Gordon Linoff Jun 23 '13 at 18:12
in that case, the sentence "The idea is to run that query, crash the mysqld to see if my app detects the error and tries to connect to another mysqld." is a bit confusing :). Anyway, the other answers probably got it better than I did anyway – Steffen Nieuwenhoven Jun 24 '13 at 08:14

score 0 · Answer 2 · answered Jun 23 '13 at 18:09

Just do multiple cross joins and save into a table:

create table dum as
    select *
    from information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t;

Or if you don't want to create a table, maybe something like this:

    select count(*)
    from information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t cross join
         information_schema.tables t;

I just realized -- I don't know if information_schema.tables actually has any rows in a new installation. But you get the idea. The worst case would be putting together something like:

select count(*)
from (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n1 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n2 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n3 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n4 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n5 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n6 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n7 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n8 cross join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n9;

By the way, this is a fun question. So much of my life is devoted to getting queries to run faster, that it is amusing to think about the opposite problem.

score 0 · Answer 3 · answered Jun 24 '13 at 01:30

0

The SLEEP() function might help you. SELECT SLEEP(10); returns 0 after 10 seconds.

answered Jun 24 '13 at 01:30

RandomSeed

29,301
6
52
87

Long query for testing

3 Answers3