mysql script file (.sql) running problem

Question

My system requirement is to execute sequence of mysql commands from ubuntu terminal,
For testing I create a file query.sql on the desktop that have a content like:

create table aa (id int);  
create tab bb (id int);  
create table cc (id int);

I execute this with command like:

zero@zero-desktop:~/Desktop$ mysql --user=root --password=admin --database=zero <query.sql

My problem is if any query fail, script stop executing.
Can anybody tell me how I prevent it[ here only second have wrong syntax, first and third should be run] I want to execute whole script, if any fail avoid it and start again from next query and generate a file where it shows error list........

Make sure first it's really a good idea to run queries with syntax errors. — Álvaro González
– Álvaro González, Commented Mar 29, 2011 at 7:46
some queries fails while there are no syntactical mistake,[ table have restriction to insert only 5 row and when we go for 6 then what? it is just an example i think we should think if one fail how we overcome] — Arjun
– Arjun, Commented Mar 29, 2011 at 8:00

Arjun · Accepted Answer · 2011-03-29 11:09:57Z

1

if you want to excute whole script file , no matter query is syntactically correct or not use this command

zero@zero-desktop:~/Desktop$ mysql --force  --user=root --password=admin   --database=zero  <query.sql

it will skip query if have problem and move to execute next query...
and finally you will get error list if there had any syntatically wrong query...... error will display on terminal like:- ERROR 1064 (42000) at line 2: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'tab bb (id int)' at line 1 ERROR 1050 (42S01) at line 3: Table 'cc' already exists
-----------IS THERE ANY WAY TO KEEP THIS ERROR ON FILE RATHER THAN TERMINAL?----------

answered Mar 29, 2011 at 11:09

Arjun

3,6696 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arjun Over a year ago

yes complete query is below use it....zero@zero-desktop:~/Desktop$ mysql --force --user=root --password=admin --database=zero <query.sql 2>error.log

jdog · Accepted Answer · 2011-03-29 07:37:39Z

0

I've never used it, but the --force key should ignore all errors

Again, haven't tested this as well, but appending the mysql line with "2> error.log" should redirect all error output of mysql to a file error.log

answered Mar 29, 2011 at 7:37

jdog

2,5347 gold badges44 silver badges80 bronze badges

Comments

Ocaso Protal · Accepted Answer · 2011-03-29 07:43:21Z

0

If you only have CREATE TABLEStatements in your file you can add a IF NOT EXISTS to avoid problems when the table already exists. That way you can rerun your script without any problems after you received an error.

create table if not exists aa (id int);  
create tab if not exists bb (id int);  
create table if not exists cc (id int);

See: CREATE TABLE Syntax

answered Mar 29, 2011 at 7:43

Ocaso Protal

20.5k8 gold badges80 silver badges92 bronze badges

1 Comment

Arjun Over a year ago

I am just trying to know the concept that's why i made syntactical mistake, i will try to write the query which will never fail but if it happened then what ? I only want -execute those who can , and show error report if any query fail.

Collectives™ on Stack Overflow

mysql script file (.sql) running problem

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related