ERROR 1146 (42S02) at line xxxx Table dbname.tablename' does

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

ERROR 1146 (42S02) at line xxxx Table dbname.tablename' does

Postby sjb99 » 20. September 2019 02:15

Hello, Can I request help from any mariadb/mysql Guru's pls.
I am new to mariadb administration but have been doing db2 and oracle for quite some time.

I am trying to import a database from a zippedbackup done by mysqldump and is getting the below error.

ERROR 1146 (42S02) at line 4160: Table 'xxx.yyy' doesn't exist
Command I am using is: nohup gunzip < /data/bkups/mysqlbkp-20190916.sql.gz | mysql -u root -p

Mariadb version is: 10.4.7 (latest download)
Backup is made on mariadb version: 10.1.17

The import fails after creating around 15 tables that has over 10k rows in each.

So far - 1. I have run mysqlcheck on the source table xxx.yyy and it came back ok indicating no corruption. 2. I dropped and recreated the table and then took the mysqldump assuming there is some char that is causing the problem. That didnt help either. I also dont see the anything related/usefull to this error in the mariadb documentation.

Please help. Tx
sjb99
 
Posts: 5
Joined: 20. September 2019 01:41
XAMPP version: 10.4.7
Operating System: Centos

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby Nobbie » 20. September 2019 11:26

What is line 4160 of your backup? That is the critical line, a have no clue what is written there.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby sjb99 » 20. September 2019 16:05

# zcat 20190919.sql.gz | sed -n '4160p'
/*!50001 CREATE ALGORITHM=UNDEFINED */

Looks like a commented out line. Why would it create the error 1146 and how to recover ?
sjb99
 
Posts: 5
Joined: 20. September 2019 01:41
XAMPP version: 10.4.7
Operating System: Centos

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby sjb99 » 20. September 2019 16:12

[root@gumdb-po-c1p backups]# zcat 20190919.sql.gz | sed -n '4160p'
/*!50001 CREATE ALGORITHM=UNDEFINED */
^C

# zcat 20190919.sql.gz | sed -n '4155p;4156p;4157p;4158p;4159p;4160p;4161p'
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = latin1 */;
/*!50001 SET character_set_results = latin1 */;
/*!50001 SET collation_connection = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`natcmts`@`%` SQL SECURITY INVOKER */
sjb99
 
Posts: 5
Joined: 20. September 2019 01:41
XAMPP version: 10.4.7
Operating System: Centos

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby sjb99 » 21. September 2019 04:12

I did unzip the backup and then I used sed to modify the definer

gunzip 20190919.sql.gz
sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' 20190919.sql > modifd_20190919.sql

Importing data failed again at the same line number and same error message. Basically the suggestion in this link didnt help http://xplus3.net/2013/10/10/remove-definer-clause-mysqldump/

@Nobbie, can you help pls.
sjb99
 
Posts: 5
Joined: 20. September 2019 01:41
XAMPP version: 10.4.7
Operating System: Centos

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby Nobbie » 21. September 2019 09:32

Upload the backup somewhere and send me a link.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby sjb99 » 22. September 2019 00:43

I cannot upload the dump due to firewall issues. I can do a webex if you can. Thankyou
sjb99
 
Posts: 5
Joined: 20. September 2019 01:41
XAMPP version: 10.4.7
Operating System: Centos

Re: ERROR 1146 (42S02) at line xxxx Table dbname.tablename'

Postby Nobbie » 22. September 2019 10:04

I have no clue what webex means.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 8 guests