Bug #533 --force ignored when also -e "source myscript.sql" is present
Submitted: 29 May 2003 3:23 Modified: 29 May 2003 12:24
Reporter: Ralf Hauser Email Updates:
Status: won't fix Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:c:\mysql\bin\mysql.exe Ver 12.18 Distri OS:Microsoft Windows (win2k)
Assigned to: CPU Architecture:Any

[29 May 2003 3:23] Ralf Hauser
Description:
To rebuild my database, I wrote a script drop.sql that drops a lot of tables and indexes.
I want to call it out of a build.xml of http://ant.apache.org with an exec task, in short: with a shell or batch script equivalent

As I am manipulating the database also with mysql-cc via the GUI, it may happen that not all tables and indexes referenced in the drop.sql script are present.

How to repeat:
If I call my script like
proj/MySQL> mysql -u root --force -e "source drop.sql"
it stops at the first ERROR 1064 

This happens DESPITE the "force" attribute!!

if I call it
<<proj/MySQL> mysql -u root --force 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4006 to server version: 4.0.12-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \. dropprivalope_ER_Model.sql
Database changed
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '' at line 1
Query OK, 0 rows affected (0.05 sec)

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '' at line 1
...
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '' at line 1
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.02 sec)

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '' at line 1
Query OK, 0 rows affected (0.02 sec)

mysql>
>> it works as expected.

Suggested fix:
make "force" also work in combination with '-e "source ..."'
[29 May 2003 4:38] Ralf Hauser
relevant page;s http://www.mysql.com/doc/en/mysql.html and http://www.mysql.com/doc/en/Batch_Commands.html
[29 May 2003 11:52] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

--force is intended to be used in Batch Mode only. I.e. like:

mysql --force < myscript.sql

# Unix style
cat myscript.sql | mysql --force

# Windows style
type myscript.sql | mysql --force

-e "" is intened to send Single query or command that's why --force does not make much sence for it. Single query can either succeed or fail. Maning of --force is to continue executing batch of queries even if some of them fail. Without it mysql will stop after first error.
[29 May 2003 12:24] Ralf Hauser
Alexander,

Thanks for your quick reply.
Unfortunately, the Ant exec task happily takes multiple arguments, but doesn't allow for pipes (|) nor I/O redirects (<,>) - at least not in a way portable between Unix and Win.
Therefore, please take this as an enhancement request with the following variants:
1) let the scripts be added as arguments to mysql.exe with option "-m" such as "multiple commands" (one of the few letters or the alphabet left)  or
2) change the implementation of "--force" such that it also is effective under '"-e source script.sql"' since this appears to be "valid" under the title of "batch mode" at least according to the part of your website's documentation I came across, namely http://www.mysql.com/doc/en/Batch_mode.html (therefore, I disagree with you - some minor "bug" might be here all the same...  ;) )
3) provide another way to get it working with ant - for example by adding a mysql ant-task (--> I guess this would require an additonal ant-mysql.jar or could become part of the jar of http://ant-contrib.sourceforge.net).

Rgds r.