[Maria-discuss] EXPLAIN and CONNECT

newer
[Maria-discuss] MariaDB encryption

Federico Razzoli

23 Mar 2014 23 Mar '14

2:23 p.m.

Show replies by date

Sergei Golubchik

26 Mar 26 Mar

4:44 p.m.

Hi, Federico! On Mar 23, Federico Razzoli wrote:

...

Hello list

I found out that EXPLAIN SELECT aggregate_func() FROM connect_table returns a "rows" value which is much greater than the rows in the table. For example:

MariaDB [test]> SELECT COUNT(*) FROM t; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.49 sec)

MariaDB [test]> EXPLAIN SELECT COUNT(*) FROM t; +------+-------------+-------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 7783705 | | +------+-------------+-------+------+---------------+------+---------+------+---------+-------+

So, my question is: is there any difference in the output of EXPLAIN with CONNECT? Is CONNECT really reading 7.8 millions rows in a table with only 1 million rows?

No, most probably it means that CONNECT estimates in this case are way off. Could you show a complete test case for this? Regards, Sergei

Peter Laursen

5:41 p.m.

I doubt that EXPLAIN will ever make any sense/would work reliably with CONNECT tables at al (though it may depend on the type of data source)l. Consequently I think that it would be better to return a(n generic) error message like "EXPLAIN is not available for the actual storage engine" (or a warning at least like "EXPLAIN may return misleading information with storage engines mapping external data sources due to non-availability of internals of the data source"). (I use the term *generic* because it may apply to other storage engines as well (I am having Cassandra and Federated/Federated_x engines in my thoughts too).) -- Peter On Wed, Mar 26, 2014 at 8:44 PM, Sergei Golubchik wrote:

...

Hi, Federico!

On Mar 23, Federico Razzoli wrote:

...
Hello list

I found out that EXPLAIN SELECT aggregate_func() FROM connect_table returns a "rows" value which is much greater than the rows in the table. For example:

MariaDB [test]> SELECT COUNT(*) FROM t; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.49 sec)

MariaDB [test]> EXPLAIN SELECT COUNT(*) FROM t;

+------+-------------+-------+------+---------------+------+---------+------+---------+-------+

...
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-------+------+---------------+------+---------+------+---------+-------+

...
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 7783705 | |

+------+-------------+-------+------+---------------+------+---------+------+---------+-------+

...
So, my question is: is there any difference in the output of EXPLAIN with CONNECT? Is CONNECT really reading 7.8 millions rows in a table with only 1 million rows?

No, most probably it means that CONNECT estimates in this case are way off. Could you show a complete test case for this?

Regards, Sergei

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Federico Razzoli

9:14 p.m.

Hi Sergei You can repeat this very easily: MariaDB [test]> CREATE TABLE t_connect (c INT PRIMARY KEY) ENGINE=CONNECT TABLE_TYPE=CSV; Query OK, 0 rows affected, 1 warning (0.11 sec) Warning (Code 1105): No file name. Table will use t_connect.csv MariaDB [test]> INSERT INTO t_connect VALUES (1),(2);Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> EXPLAIN SELECT MAX(c) FROM t_connect;+------+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_connect | ALL | NULL | NULL | NULL | NULL | 4 | | +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) But adding more rows, the difference between the rows value and the reality becomes big. Regards Federico -------------------------------------------- Mer 26/3/14, Sergei Golubchik ha scritto: Oggetto: Re: [Maria-discuss] EXPLAIN and CONNECT A: "Federico Razzoli" Cc: maria-discuss@lists.launchpad.net Data: Mercoledì 26 marzo 2014, 20:44 Hi, Federico! On Mar 23, Federico Razzoli wrote:

...

Hello list

...

I found out that EXPLAIN SELECT aggregate_func() FROM connect_table returns a "rows" value which is much greater than the rows in the table. For example:

MariaDB [test]> SELECT COUNT(*) FROM t; +----------+ | COUNT(*) | +----------+ | 1000000 |

+----------+

...

1 row in set (0.49 sec)

MariaDB [test]> EXPLAIN SELECT COUNT(*) FROM t;

+------+-------------+-------+------+---------------+------+---------+------+---------+-------+

...

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-------+------+---------------+------+---------+------+---------+-------+

...

| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 7783705 | |

+------+-------------+-------+------+---------------+------+---------+------+---------+-------+

...

So, my question is:

is there any difference in the output of EXPLAIN

...

with CONNECT? Is CONNECT really reading 7.8 millions rows in a table with only 1 million rows?

No, most probably it means that CONNECT estimates in this case are way off. Could you show a complete test case for this? Regards, Sergei

Sergei Golubchik

12 May 12 May

10:40 a.m.

Hi, Federico! On Mar 27, Federico Razzoli wrote:

...

Hi Sergei

You can repeat this very easily:

MariaDB [test]> CREATE TABLE t_connect (c INT PRIMARY KEY) ENGINE=CONNECT TABLE_TYPE=CSV; Query OK, 0 rows affected, 1 warning (0.11 sec)

Warning (Code 1105): No file name. Table will use t_connect.csv MariaDB [test]> INSERT INTO t_connect VALUES (1),(2);Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> EXPLAIN SELECT MAX(c) FROM t_connect; +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_connect | ALL | NULL | NULL | NULL | NULL | 4 | | +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)

But adding more rows, the difference between the rows value and the reality becomes big.

Thanks, I've reported it as https://mariadb.atlassian.net/browse/MDEV-6232 I tried to repeat it earlier, but then it uncovered other bugs in the CONNECT indexes, they're fixed now. Your original query gets "Table was optimized away", so I have to modified it a bit for the bug report. Regards, Sergei

Federico Razzoli

11:33 a.m.

Hi Sergej, Thank you for this update. I'm still curious: does CONNECT read the rows several times? If so, is there a way to avoid this until the bug is solved? Regards, Federico -------------------------------------------- El lun, 12/5/14, Sergei Golubchik escribió: Asunto: Re: [Maria-discuss] EXPLAIN and CONNECT Para: "Federico Razzoli" CC: maria-discuss@lists.launchpad.net Fecha: lunes, 12 de mayo, 2014 15:40 Hi, Federico! On Mar 27, Federico Razzoli wrote:

...

Hi Sergei

...

You can repeat this very easily:

MariaDB [test]> CREATE TABLE t_connect (c INT PRIMARY KEY) ENGINE=CONNECT TABLE_TYPE=CSV; Query OK, 0 rows affected, 1 warning (0.11 sec)

Warning (Code 1105): No file name. Table will use t_connect.csv MariaDB [test]> INSERT INTO t_connect VALUES (1),(2);Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> EXPLAIN SELECT MAX(c) FROM t_connect;

+------+-------------+-----------+------+---------------+------+---------+------+------+-------+

...

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-----------+------+---------------+------+---------+------+------+-------+

...

| 1 | SIMPLE | t_connect | ALL | NULL | NULL | NULL | NULL | 4 | |

+------+-------------+-----------+------+---------------+------+---------+------+------+-------+

...

1 row in set (0.00 sec)

But adding more rows, the difference between the rows value and the reality becomes big.

Sergei Golubchik

26 May 26 May

5:07 p.m.

Hi, Federico! On May 12, Federico Razzoli wrote:

...

Hi Sergej,

Thank you for this update. I'm still curious: does CONNECT read the rows several times? If so, is there a way to avoid this until the bug is solved?

I suppose you've seen the answer in the bug report. But just for the sake of completeness I repeat it here. CONNECT does not read rows several times. But in the CSV file format, rows can have different lengths and CONNECT cannot directly know how many rows your table has. So, it *estimates* the number of rows by dividing the file length by the minimal row length (which it can calculate from the table definition). Regards, Sergei

Roberto Spadim

5:30 p.m.

maybe include at extra "estimative rows count" just to understand what happened 2014-05-26 17:07 GMT-03:00 Sergei Golubchik :

...

Hi, Federico!

On May 12, Federico Razzoli wrote:

...
Hi Sergej,

Thank you for this update. I'm still curious: does CONNECT read the rows several times? If so, is there a way to avoid this until the bug is solved?

I suppose you've seen the answer in the bug report. But just for the sake of completeness I repeat it here.

CONNECT does not read rows several times. But in the CSV file format, rows can have different lengths and CONNECT cannot directly know how many rows your table has. So, it *estimates* the number of rows by dividing the file length by the minimal row length (which it can calculate from the table definition).

Regards, Sergei

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

Sergei Golubchik

7:52 p.m.

Hi, Roberto! On May 26, Roberto Spadim wrote:

...

maybe include at extra "estimative rows count" just to understand what happened

Row count in the EXPLAIN output is *always* an estimation, there is no need to specify it explicitly because it is always the case.

...

...
I suppose you've seen the answer in the bug report. But just for the sake of completeness I repeat it here.

CONNECT does not read rows several times. But in the CSV file format, rows can have different lengths and CONNECT cannot directly know how many rows your table has. So, it *estimates* the number of rows by dividing the file length by the minimal row length (which it can calculate from the table definition).

Regards, Sergei

Roberto Spadim

9:05 p.m.

nice :) no problems in this case i wasn't considering this i will check if documentations have this, serious i don't remember if something simple like this is explained and i never checked before :) if i don't found this information, i will reply here 2014-05-26 19:52 GMT-03:00 Sergei Golubchik :

...

Hi, Roberto!

On May 26, Roberto Spadim wrote:

...
maybe include at extra "estimative rows count" just to understand what happened

Row count in the EXPLAIN output is *always* an estimation, there is no need to specify it explicitly because it is always the case.

...
...
I suppose you've seen the answer in the bug report. But just for the sake of completeness I repeat it here.

CONNECT does not read rows several times. But in the CSV file format, rows can have different lengths and CONNECT cannot directly know how many rows your table has. So, it *estimates* the number of rows by dividing the file length by the minimal row length (which it can calculate from the table definition).

Regards, Sergei

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

Roberto Spadim

9:10 p.m.

right sergei! rowsAn *estimate *of how many rows we will find in the table for each key lookup. 2014-05-26 21:05 GMT-03:00 Roberto Spadim :

...

nice :) no problems in this case i wasn't considering this i will check if documentations have this, serious i don't remember if something simple like this is explained and i never checked before :)

if i don't found this information, i will reply here

2014-05-26 19:52 GMT-03:00 Sergei Golubchik :

Hi, Roberto!

...
On May 26, Roberto Spadim wrote:

...
maybe include at extra "estimative rows count" just to understand what happened

Row count in the EXPLAIN output is *always* an estimation, there is no need to specify it explicitly because it is always the case.

...
...
I suppose you've seen the answer in the bug report. But just for the sake of completeness I repeat it here.

CONNECT does not read rows several times. But in the CSV file format, rows can have different lengths and CONNECT cannot directly know how many rows your table has. So, it *estimates* the number of rows by dividing the file length by the minimal row length (which it can calculate from the table definition).

Regards, Sergei

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

Federico Razzoli

27 May 27 May

7:29 a.m.

New subject: [Maria-discuss] Repairing a SPIDER table

It seems I've played too much with bug 6269: https://mariadb.atlassian.net/browse/MDEV-6269 As a result, a table has crashed: MariaDB [test]> DROP TABLE user; ERROR 1051 (42S02): Unknown table 'test.user' MariaDB [test]> REPAIR TABLE user; +-----------+--------+----------+---------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+--------+----------+---------------------------------+ | test.user | repair | Error | Table 'test.user' doesn't exist | | test.user | repair | status | Operation failed | +-----------+--------+----------+---------------------------------+ 2 rows in set (0.00 sec) Errlog agrees: 140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above. Is there another way? Should I report this as a bug? Regards Federico

Ian Gilfillan

7:51 a.m.

New subject: [Maria-discuss] Repairing a SPIDER table

I found Spider tables extremely easy to corrupt when I was looking at Spider (see e.g. https://mariadb.atlassian.net/browse/MDEV-5559), so eventually stopped exploring. Unhelpfully, this was in January and I can't remember what I used to do to recover, except that I used it often! Perhaps manual deletion? ian On 27/05/2014 12:29, Federico Razzoli wrote:

...

It seems I've played too much with bug 6269:

https://mariadb.atlassian.net/browse/MDEV-6269

As a result, a table has crashed:

MariaDB [test]> DROP TABLE user; ERROR 1051 (42S02): Unknown table 'test.user' MariaDB [test]> REPAIR TABLE user; +-----------+--------+----------+---------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+--------+----------+---------------------------------+ | test.user | repair | Error | Table 'test.user' doesn't exist | | test.user | repair | status | Operation failed | +-----------+--------+----------+---------------------------------+ 2 rows in set (0.00 sec)

Errlog agrees:

140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired

The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Is there another way? Should I report this as a bug?

Regards Federico

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Federico Razzoli

8:42 a.m.

New subject: [Maria-discuss] Repairing a SPIDER table

Ian, thanks for your reply. I can't find a way to manually delete it. The table is not in mysql.spider_tables, and I don't see any file... Federico -------------------------------------------- El mar, 27/5/14, Ian Gilfillan escribió: Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: maria-discuss@lists.launchpad.net Fecha: martes, 27 de mayo, 2014 12:51 I found Spider tables extremely easy to corrupt when I was looking at Spider (see e.g. https://mariadb.atlassian.net/browse/MDEV-5559), so eventually stopped exploring. Unhelpfully, this was in January and I can't remember what I used to do to recover, except that I used it often! Perhaps manual deletion? ian On 27/05/2014 12:29, Federico Razzoli wrote:

...

It seems I've played too much with bug 6269:

https://mariadb.atlassian.net/browse/MDEV-6269

As a result, a table has crashed:

MariaDB [test]> DROP TABLE user;

...

ERROR 1051 (42S02): Unknown table 'test.user'

...

MariaDB [test]> REPAIR TABLE user;

+-----------+--------+----------+---------------------------------+

...

| Table | Op | Msg_type | Msg_text |

+-----------+--------+----------+---------------------------------+

...

| test.user | repair | Error | Table 'test.user' doesn't exist |

...

+-----------+--------+----------+---------------------------------+

...

2 rows in set (0.00 sec)

Errlog agrees:

140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired

The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Is there another way?

Should I report this as a bug?

...

Regards

Federico

...

_______________________________________________

...

Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Federico Razzoli

9:09 a.m.

New subject: [Maria-discuss] Repairing a SPIDER table

Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem). Federico -------------------------------------------- El mar, 27/5/14, Ian Gilfillan escribió: Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: maria-discuss@lists.launchpad.net Fecha: martes, 27 de mayo, 2014 12:51 I found Spider tables extremely easy to corrupt when I was looking at Spider (see e.g. https://mariadb.atlassian.net/browse/MDEV-5559), so eventually stopped exploring. Unhelpfully, this was in January and I can't remember what I used to do to recover, except that I used it often! Perhaps manual deletion? ian On 27/05/2014 12:29, Federico Razzoli wrote:

...

It seems I've played too much with bug 6269:

https://mariadb.atlassian.net/browse/MDEV-6269

As a result, a table has crashed:

MariaDB [test]> DROP TABLE user;

...

ERROR 1051 (42S02): Unknown table 'test.user'

...

MariaDB [test]> REPAIR TABLE user;

+-----------+--------+----------+---------------------------------+

...

| Table | Op | Msg_type | Msg_text |

+-----------+--------+----------+---------------------------------+

...

| test.user | repair | Error | Table 'test.user' doesn't exist |

...

+-----------+--------+----------+---------------------------------+

...

2 rows in set (0.00 sec)

Errlog agrees:

140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired

The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Is there another way?

Should I report this as a bug?

...

Regards

Federico

...

_______________________________________________

...

Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

kentoku

3 Jun 3 Jun

4:46 p.m.

New subject: [Maria-discuss] Repairing a SPIDER table

Hi Federico and Ian,

...

Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem).

This is bug of Spider's table discovery feature. I'll fix this bug soon. Please use "create table" statement with column definitions for now.

...

[ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired

...
The question is: how

should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Did you try "repair table mysql.spider_tables"? Thanks, Kentoku 2014-05-27 21:09 GMT+09:00 Federico Razzoli :

...

Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem).

Federico

-------------------------------------------- El mar, 27/5/14, Ian Gilfillan escribió:

Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: maria-discuss@lists.launchpad.net Fecha: martes, 27 de mayo, 2014 12:51

I found Spider tables extremely easy to corrupt when I was looking at Spider (see e.g. https://mariadb.atlassian.net/browse/MDEV-5559), so eventually stopped exploring.

Unhelpfully, this was in January and I can't remember what I used to do to recover, except that I used it often! Perhaps manual deletion?

ian

On 27/05/2014 12:29, Federico Razzoli wrote:

...
It seems I've played too much with bug 6269:

https://mariadb.atlassian.net/browse/MDEV-6269

As a result, a table has crashed:

MariaDB [test]> DROP TABLE user;

...
ERROR 1051 (42S02): Unknown table 'test.user'

...
MariaDB [test]> REPAIR TABLE user;

+-----------+--------+----------+---------------------------------+

...
| Table | Op | Msg_type | Msg_text |

+-----------+--------+----------+---------------------------------+

...
| test.user | repair | Error | Table 'test.user' doesn't exist |

| test.user | repair | status | Operation failed |

...
+-----------+--------+----------+---------------------------------+

...
2 rows in set (0.00 sec)

Errlog agrees:

140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired

The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Is there another way?

Should I report this as a bug?

...
Regards

Federico

...
_______________________________________________

...
Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Federico Razzoli

5:04 p.m.

New subject: [Maria-discuss] Repairing a SPIDER table

Hi Kentoku Yes, I repaired mysql.spider_tables. But the problem with the `user` table remains. Since I could not find any solution, I reported this bug: https://mariadb.atlassian.net/browse/MDEV-6293 Regards Federico -------------------------------------------- El mar, 3/6/14, kentoku escribió: Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: "Federico Razzoli" CC: "Maria Discuss" Fecha: martes, 3 de junio, 2014 21:46 Hi Federico and Ian,

...

Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem).

This is bug of Spider's table discovery feature. I'll fix this bug soon. Please use "create table" statement with column definitions for now.

...

[ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired > The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Did you try "repair table mysql.spider_tables"? Thanks, Kentoku 2014-05-27 21:09 GMT+09:00 Federico Razzoli :

...

Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem).

Federico

--------------------------------------------

...

El mar, 27/5/14, Ian Gilfillan escribió:

...

Para: maria-discuss@lists.launchpad.net Fecha: martes, 27 de mayo, 2014 12:51

I found Spider

...

extremely easy to corrupt when I was looking at Spider (see e.g. https://mariadb.atlassian.net/browse/MDEV-5559), so eventually stopped exploring.

Unhelpfully, this was in January and I can't remember what I used to do to recover, except that I used it often! Perhaps manual deletion?

ian

On 27/05/2014 12:29, Federico Razzoli wrote: > It seems I've played too much with bug 6269: > > https://mariadb.atlassian.net/browse/MDEV-6269 > > As a result, a table has crashed: > > MariaDB [test]> DROP TABLE user; > ERROR 1051 (42S02): Unknown table 'test.user' > MariaDB [test]> REPAIR TABLE user; > +-----------+--------+----------+---------------------------------+ > | Table | Op | Msg_type | Msg_text |

+-----------+--------+----------+---------------------------------+ > | test.user | repair | Error | Table 'test.user' doesn't exist | > | test.user | repair | status | Operation failed | > +-----------+--------+----------+---------------------------------+ > 2 rows in set (0.00 sec) > > Errlog agrees: > 140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired > The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above. > > Is

Asunto: Re: [Maria-discuss] Repairing a SPIDER table tables there another way?

...

> Should I report this as a bug? > > Regards > Federico >

>

...

> Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >

...

Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________

...

Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

kentoku

4 Jun 4 Jun

1:23 p.m.

New subject: [Maria-discuss] Repairing a SPIDER table

Hi Federico,

...

Yes, I repaired mysql.spider_tables. But the problem with the `user` table remains. Since I could not find any solution, I reported this bug: https://mariadb.atlassian.net/browse/MDEV-6293

ok, I'll check it. Thanks, Kentoku 2014-06-04 5:04 GMT+09:00 Federico Razzoli :

...

Hi Kentoku

Yes, I repaired mysql.spider_tables. But the problem with the `user` table remains. Since I could not find any solution, I reported this bug: https://mariadb.atlassian.net/browse/MDEV-6293

Regards Federico

-------------------------------------------- El mar, 3/6/14, kentoku escribió:

Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: "Federico Razzoli" CC: "Maria Discuss" Fecha: martes, 3 de junio, 2014 21:46

Hi Federico and Ian,

...
Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem).

This is bug of Spider's table discovery feature. I'll fix this bug soon. Please use "create table" statement with column definitions for now.

...
[ERROR] mysqld: Table './mysql/spider_tables' is

...
marked as crashed and should be repaired

...
The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

Did you try "repair table mysql.spider_tables"?

Thanks, Kentoku

2014-05-27 21:09 GMT+09:00 Federico Razzoli :

...
Sorry for flooding. I've read your bug report, Ian. If I understand correctly, SPIDER ignores the CONNECTION option. So 6268 and 5559 could be the same bug (both with incorrect title: in 6268, Elena's comment explains the problem).

Federico

--------------------------------------------

...
El mar, 27/5/14, Ian Gilfillan escribió:

...
Para: maria-discuss@lists.launchpad.net Fecha: martes, 27 de mayo, 2014 12:51

I found Spider

Asunto: Re: [Maria-discuss] Repairing a SPIDER table tables

...
extremely easy to corrupt when I was looking at Spider (see e.g. https://mariadb.atlassian.net/browse/MDEV-5559), so eventually stopped exploring.

Unhelpfully, this was in January and I can't remember what I used to do to recover, except that I used it often! Perhaps manual deletion?

ian

On 27/05/2014 12:29, Federico Razzoli wrote:

...
It seems I've played too much with bug 6269:

...
https://mariadb.atlassian.net/browse/MDEV-6269

As a

result, a table has crashed:

...
MariaDB [test]> DROP TABLE user;

...
ERROR 1051 (42S02): Unknown table 'test.user'

...
MariaDB [test]> REPAIR TABLE user;

+-----------+--------+----------+---------------------------------+

...
...
| Table | Op | Msg_type | Msg_text |

+-----------+--------+----------+---------------------------------+

...
...
| test.user | repair | Error | Table 'test.user' doesn't exist |

| test.user | repair | status | Operation failed |

...
...
...
2 rows in set (0.00 sec)

Errlog agrees:

140527 12:23:56 [ERROR] mysqld: Table './mysql/spider_tables' is marked as crashed and should be repaired

...
The question is: how should I repair it? mysql.spider_tables is already empty. REPAIR TABLE does not work. Can't find a variable to force recovery on restart. DROP and CREATE OR REPLACE are impossible, as shown above.

...
Is

+-----------+--------+----------+---------------------------------+ there another way?

...
...
Should I report this as a bug?

...
Regards

...
Federico

...
...
...
_______________________________________________

...
...
Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________

...
Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________

...
Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

3706

Age (days ago)

3779

Last active (days ago)

List overview

Download

17 comments

6 participants

participants (6)

Federico Razzoli
Ian Gilfillan
kentoku
Peter Laursen
Roberto Spadim
Sergei Golubchik

[Maria-discuss] EXPLAIN and CONNECT

tags

participants (6)