[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
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
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
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ó:
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 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ó:
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
_______________________________________________ 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