Way to Abort Execution of MySQL Scripts (Raising Error Perhaps)

Way to abort execution of MySQL scripts (raising error perhaps)?

I think what you're encountering is a limitation of the MySQL console. Given a list of statements, the MySQL console executes each one regardless of any errors generated. Even if you implemented some of the error-raising suggestions that previous comments have mentioned, the MySQL console won't stop executing when such an error is encountered.

I'll assume that you don't have the resources to apply a scripting language to the problem that could execute your SQL for you and handle the errors. I think in this case, you just need a more robust tool than the MySQL console.

MySQL Administrator does what you need, if I understand your problem correctly. If you set up your MySQL connection and connect to the database, you have two tools available from the Tools menu. The normal MySQL console is there, but you also have the MySQL Query Browser.

If you open the Query Browser, you get a decent GUI view of your MySQL databases. File -> Open Script to open your SQL script, then use the Execute button.

You get a nice progress bar, and more importantly from the sounds of it, if a query fails, the script execution halts and highlights the failed query. You can choose to skip it and keep going, or even manually modify your data and start up from someplace else farther down the script.

I abandoned the MySQL console almost immediately once I found out about and tried Administrator.

Abort MySQL script conditionally

I think this would best be solved in a shell script, or installer app. You cannot really have any control constructs in SQL script.

A very simple solution would be to create a script that generates the command line for the actual script to execute based on a select on the meta table.

So lets say you have this script, gen_upgrade_command.sql:

select concat(
       'mysql -u<user> -p<password> -h<host> -e"SOURCE upgrade'
   ,    val + 1
   ,   '.sql"'
   )
from meta;

Which you run like this

mysql -u<user> -p<password> -h<host> -Nrs < gen_upgrade_command.sql > do_upgrade.bat

Now, do_upgrade.bat contains this generated command line:

mysql -u<usere> -p<password> -h<host> -e"SOURCE upgrade1.sql"

and running do_upgrade.bat will run upgrade1.sql

Of course you can modify the original script to not select any row at all too, that's just up to you.

Return error from query before finishing it

Maybe you can use SIGNAL to raise an error.

SQL Server - stop or break execution of a SQL script

The raiserror method

raiserror('Oh no a fatal error', 20, -1) with log

This will terminate the connection, thereby stopping the rest of the script from running.

Note that both severity level 20 or higher and the WITH LOG option are necessary for it to work this way.

This even works with GO statements, eg.

print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'

Will give you the output:

hi
Msg 2745, Level 16, State 2, Line 1
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Oh no a fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Notice that 'ho' is not printed.

CAVEATS:

• This only works if you are logged in as admin ('sysadmin' role), and also leaves you with no database connection.
• If you are NOT logged in as admin, the RAISEERROR() call itself will fail and the script will continue executing.
• When invoked with sqlcmd.exe, exit code 2745 will be reported.

Reference: http://www.mydatabasesupport.com/forums/ms-sqlserver/174037-sql-server-2000-abort-whole-script.html#post761334

The noexec method

Another method that works with GO statements is set noexec on (docs). This causes the rest of the script to be skipped over. It does not terminate the connection, but you need to turn noexec off again before any commands will execute.

Example:

print 'hi'
go

print 'Fatal error, script will not continue!'
set noexec on

print 'ho'
go

-- last line of the script
set noexec off -- Turn execution back on; only needed in SSMS, so as to be able 
               -- to run this script again in the same session.

Handling errors and continuing execution in PHP script

$result = mysql_query($sql);
if(mysql_error()) {
  echo "Error: " . mysql_error();
}

PHP override error in loop and continue with next statement

How do I make the script skip any queries that throw an error

Doing that would be a really bad idea. If the queries are throwing an error then either there's bad data or your code is wrong.

Looking at the code you've provided (it doesn't batch the inserts) it will throw an error for a duplicate key - which may not be a valid fault in the processing - to deal with this, you should amend your code:

$sql = sprintf("INSERT IGNORE INTO "...

While there may be legitimate reasons for ignoring other errors, you should only explicitly ignore errors that you know are valid cases, e.g.

    if (!$result = mysql_query($sql)) {
        $err=mysql_errno();
        $msg="error " . $err . "\nin $sql\n\n" . mysql_error();
        if (!array_key_exists($err, $aceptable_error) {
           die("Failed: $msg");
        } else {
           print "warning: $msg";
        }
    }

Dynamic SQL returns 'syntax error' on Google Apps Script using JDBC and MySQL

The solution was indeed like Marc B described, and what my edit shows. That is, creating a single connection, and executing multiple SQL selects instead of running two selects on the same execution.

---

Related Topics