0

Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.

The important part of the schema shows

/*Database: mamlocal*/
----------------------

/*Table Information*/
---------------------

Name                                  Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time          Check_time  Collation          Checksum  Create_options  Comment                                                        Max_index_length  Temporary  
------------------------------------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -------------------  ----------  -----------------  --------  --------------  -----------------------------------------------------------  ------------------  -----------
...
member                                InnoDB       10  Dynamic        706             162       114688                0         49152          0            4528  2020-08-17 14:48:55  2021-04-06 10:27:53  (NULL)      utf8_general_ci      (NULL)                                                                                                0  N          
...

and the table member is created using...

CREATE TABLE member (
  member_id int(11) NOT NULL AUTO_INCREMENT, 
  member_title varchar(10) DEFAULT NULL,
  member_fn_1 varchar(50) DEFAULT NULL,
  member_sn varchar(50) DEFAULT NULL,

  ... other fields
 
PRIMARY KEY (member_id)
)  

These statements each produce the expected results.

SELECT * FROM information_schema.tables WHERE table_schema = 'mamlocal' AND table_name = 'member';
SELECT * FROM information_schema.tables WHERE  table_name = 'member';
SELECT member_id FROM member
SELECT member_id FROM mamlocal.member

but these produce Error Code: 1109 Unknown table 'member' in field list

UPDATE member SET  member_id = member_id ;
UPDATE mamlocal.member SET  member_id = member_id 

(The real update does something useful but I simplified it to try to find out where the error was)

Can anyone help me to identify why?

I have looked at these other questions but still cannot see what is wrong Qn1 Qn2 Qn3

Here is an SQLfiddle showing the same code working correctly in MySQL, as I would expect and I have used similar code in many other places without problems to update table member. Using the same code to update another table works ok.

More experiments showing that table member is there.

This code...

INSERT INTO member () VALUES  ();
UPDATE member SET  member_id = member_id ;

... gives me the message

2 queries executed, 1 success, 1 errors, 0 warnings

Query: insert into member () values ()

1 row(s) affected

Execution Time : 0.002 sec
Transfer Time  : 0.001 sec
Total Time     : 0.004 sec
-----------------------------------------------------------

Query: UPDATE member SET member_id = member_id

Error Code: 1109
Unknown table 'member' in field list

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec
0

I think this might be the answer and might help others with this problem.

I had a test trigger on the table member

 CREATE  TRIGGER `test` BEFORE UPDATE ON `member` FOR EACH ROW 
    BEGIN
     DECLARE addr VARCHAR(100);
     DECLARE        joint INT;
        SET @joint := is_joint_member(member.member_id);
        SET @addr = 'jgkjgkjg' ;
     CALL dummy2;   
    END $$

where

is_joint_member()

is a working UDF that I've used for ages and

dummy2()

is an empty, but working stored procedure just to see if I could call a proc inside a trigger. However the trigger might not be working correctly as if I alter it and comment out

SET @joint := is_joint_member(member.member_id);

then my UPDATE works OK, as it does if I drop the trigger all together.

In other words the error

Error Code: 1109 Unknown table 'member' in field list

is misleading as it does not mean that the table doesn't exist, it means that something has gone wrong with a trigger attached to the update event on that table.

Something to watch out for in future!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.