0

i am using mysql 5.5.40 version. i am importing a large .sql file having around 36000 lines using a script. The script contains below statements.

mysql -uxx -p < dumpfile.sql 2 > error.log
cat error.log|mail -s "import error" a@something.com

Sometime after executing the script, I got the mail saying "ERROR 1146 (42S02) at line 35687:Table 'dbname.tablename' doesn't exist. As i am not using force option while importing , it should not execute lines from 35688 to 36000 lines. it should abort when it hit with error. But when i have checked manually, it seems the lines are executed. Is it the expected behavior?

We know its expected behaviour when using source command in mysql command line client.

  • Not a correct behaviour. Can you make sure it is really proceeding further? How are you making sure it is progressing on next lines? Place select "debugging" right after line 35687 and reload! mysql -uxx -p < dumpfile.sql 1>error.log 2>&1 – mysql_user Nov 25 '15 at 12:04
  • There are few views left for multiple databases after this line. i have checked the databases and views exist. By this, i can say its proceeded further. – kasi Nov 25 '15 at 13:52
  • can you please try above and confirm that you see "select" output logged? – mysql_user Nov 25 '15 at 15:58
  • It's a 20GB file. So unable to edit the file.So what i have done is using less command taken the lines from 35687 manually and paste it into another file and done what you have said. Select output not logged. that means its not proceeding further. – kasi Nov 26 '15 at 7:06
  • That's right... so the views that you claim loaded from dump were actually present already – mysql_user Nov 26 '15 at 7:21
1

The behaviour is correct and it is incorrectly observed as per update from the questioner. This got verified by further debugging the dumpload adding a debugging select syntax after the erroneous SQL in the dump.

0

I got the answer finally. When taking mysqldump for a database, Its dumping temporary table structure for a view first (creating a table with same name as view having just plain columns with no where conditions and joins under myisam storage engine even though innodb is my default storage engine). Later at the end of the dump i.e after dumping all tables structures and procedures for the database its dumping final view structure.

Example:
-- Temporary table structure for view sample_view
DROP TABLE IF EXISTS sample_view;
/!50001 DROP VIEW IF EXISTS sample_view/;
/*!50001 CREATE TABLE sample_view (
colA tinyint NOT NULL,
colB tinyint NOT NULL
) ENGINE=MyISAM */;

-- Final view structure for view sample_view
/!50001 DROP TABLE IF EXISTS sample_view/;
/!50001 DROP VIEW IF EXISTS sample_view/;
/*!50001 CREATE ALGORITHM=UNDEFINED /
/
!50013 DEFINER=root@localhost SQL SECURITY DEFINER /
/
!50001 VIEW sample_view AS select sp.colA AS colA,sp.colB AS colB from tableC sp where (sp.START_DATE = (select
max(b.START_DATE) from tableC b where ((b.colA = sp.colA) and
(b.START_DATE <= curdate())))) */;

So while importing ,I got error at final view structure for the specified view.Import did not proceeded further and aborted at this point. Even though i found the remaining views in database after this particular view in dump file, they are not correct ones.I misled by these temporary table structures. So mysql import is behaving as expected.

  • So answer is a wrong assumption, which I hinted you to check via debug "Can you make sure it is really proceeding further? " and "can you please try above and confirm that you see "select" output logged?" What you saw and concluded later using that was upto you as we donot have dump contents to conclude... ;) – mysql_user Nov 26 '15 at 8:54
  • i totally agree with you..its my bad..i just checked the views but not their structures. – kasi Nov 26 '15 at 9:46
  • i voted my answer as it have more info regarding dumping views and its going to help mostly for viewers.. – kasi Nov 27 '15 at 14:00

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.