[Maria-discuss] EXPLAIN and CONNECT
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? Regards Federico
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
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 <serg@mariadb.org> 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
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 <serg@mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] EXPLAIN and CONNECT A: "Federico Razzoli" <federico_raz@yahoo.it> 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
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
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 <serg@mariadb.org> escribió: Asunto: Re: [Maria-discuss] EXPLAIN and CONNECT Para: "Federico Razzoli" <federico_raz@yahoo.it> 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.
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
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
maybe include at extra "estimative rows count" just to understand what happened 2014-05-26 17:07 GMT-03:00 Sergei Golubchik <serg@mariadb.org>:
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
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
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 <serg@mariadb.org>:
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
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 <roberto@spadim.com.br>:
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 <serg@mariadb.org>:
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
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
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
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 <ian@mariadb.org> 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
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 <ian@mariadb.org> 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
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 <federico_raz@yahoo.it>:
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 <ian@mariadb.org> 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
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 <kentokushiba@gmail.com> escribió: Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: "Federico Razzoli" <federico_raz@yahoo.it> CC: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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 <federico_raz@yahoo.it>:
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 <ian@mariadb.org> 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
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 <federico_raz@yahoo.it>:
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 <kentokushiba@gmail.com> escribió:
Asunto: Re: [Maria-discuss] Repairing a SPIDER table Para: "Federico Razzoli" <federico_raz@yahoo.it> CC: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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 <federico_raz@yahoo.it>:
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 <ian@mariadb.org> 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
participants (6)
-
Federico Razzoli -
Ian Gilfillan -
kentoku -
Peter Laursen -
Roberto Spadim -
Sergei Golubchik