[MDEV-29663] Incorrect, confusing error message: ERROR 1054 (42S22): Unknown column 't0.c1' in 'field list' - Jira

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.5, 10.6
Component/s: Storage Engine - Spider
Labels:

Description

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'PWD1';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'PWD1');

CREATE TABLE t (c INT) ENGINE=InnoDB;

CREATE TABLE ts (c1 INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

SELECT c1 FROM ts;

Leads to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)
10.11.0-dbg>SELECT c1 FROM ts;
ERROR 1054 (42S22): Unknown column 't0.c1' in 'field list'

1. The t0 is incorrect, there is no t0 defined. This is likely a Spider-internal reference.
2. The error is confusing. We could replace it with something like:

ERROR 1054 (42S22): Unknown column 'c1' in parent spider table 't'.

Or similar (assuming that this is the underlaying issue).

Changing the definition of table t to column name c1 (instead of c) fixes the issue.

Attachments

Issue Links

duplicates

MDEV-29430 'Got error 1054 when reading table' on SELECT (Spider)

Closed

relates to

MDEV-29664 Assertion `!n_mysql_tables_in_use' failed in innobase_close_connection

Closed

Activity

Ascending order - Click to sort in descending order

Roel Van de Paar added a comment - 2022-10-20 03:39

Additional testcase

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'P';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'P');

CREATE TABLE t (c1 INT) ENGINE=InnoDB;

CREATE TABLE t2 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

SELECT c FROM t2 WHERE c>ALL (SELECT 1 FROM t);

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
2022-10-20 14:39:11 4 [ERROR] Got error 1054 when reading table './test/t2'

Roel Van de Paar added a comment - 2022-10-20 03:39 Additional testcase INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER Spider@localhost IDENTIFIED BY 'P' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , user 'Spider' , PASSWORD 'P' ); CREATE TABLE t (c1 INT ) ENGINE=InnoDB; CREATE TABLE t2 (c INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; SELECT c FROM t2 WHERE c> ALL ( SELECT 1 FROM t); 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized) 2022-10-20 14:39:11 4 [ERROR] Got error 1054 when reading table './test/t2'

Roel Van de Paar added a comment - 2022-10-20 06:06

In certain cases, a correct error makes it to the error log. For example, when executing:

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'P';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'P');

CREATE TABLE t (c INT) ENGINE=InnoDB;

CREATE TABLE t1 (a INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

INSERT INTO t1 VALUES(0);

DROP TABLE t;

SELECT COUNT(*)=1 FROM t1;

We see ERROR 1146 in both the client and the error log:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
10.11.0-opt>SELECT COUNT(*)=1 FROM t1;
ERROR 1146 (42S02): Table 'test.t' doesn't exist

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
2022-10-20 17:03:03 4 [ERROR] Got error 1146 when reading table './test/t1'

However, when only the last line is changed to:

SELECT COUNT(*) FROM t1;

Then only the CLI will show the issue, not the error log. This seems closely related to the testcase in the previous comment.

Roel Van de Paar added a comment - 2022-10-20 06:06 In certain cases, a correct error makes it to the error log. For example, when executing: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER Spider@localhost IDENTIFIED BY 'P' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , user 'Spider' , PASSWORD 'P' ); CREATE TABLE t (c INT ) ENGINE=InnoDB; CREATE TABLE t1 (a INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; INSERT INTO t1 VALUES (0); DROP TABLE t; SELECT COUNT (*)=1 FROM t1; We see ERROR 1146 in both the client and the error log: 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized) 10.11.0-opt>SELECT COUNT(*)=1 FROM t1; ERROR 1146 (42S02): Table 'test.t' doesn't exist 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized) 2022-10-20 17:03:03 4 [ERROR] Got error 1146 when reading table './test/t1' However, when only the last line is changed to: SELECT COUNT (*) FROM t1; Then only the CLI will show the issue, not the error log. This seems closely related to the testcase in the previous comment.

Roel Van de Paar added a comment - 2022-10-20 06:08

There is an additional testcase/information in ~~MDEV-29430~~, which was marked as a duplicate of this bug.

Roel Van de Paar added a comment - 2022-10-20 06:08 There is an additional testcase/information in MDEV-29430 , which was marked as a duplicate of this bug.

People

Assignee:: Yuchen Pei

Reporter:: Roel Van de Paar

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2022-09-29 02:25

Updated:: 2024-09-10 15:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server