Unexpected behaviour when got error in the middle of mysqldump backup file import

Question

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" [email protected]

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.

Answer 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.

Answer 2

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.