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

Question

I need to write setup scripts for MySQL (usually run using 'source [file]' from mysql console) that depend partly on existing data, and there are differences in environments, meaning that sometimes script does fail. A common case is that a 'SET' statement with a select (to locate an id) fails to find anything; console sets value to NULL. In this case I would like the script to fail; I have heard this would be done by raising an error (with other DBs). However it appears like MySQL didn't have a way to do this.

Are there good ways to force failure under such conditions? As things are now updates will fail when insert tries to use null, but even that does not terminate script itself. Ideally it would just fail and terminate as soon as a problem is encountered.

Answer 1

Looks like KILL QUERY should do what you need.

Answer 2

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.

Answer 3

I had the same problem and the GUI isn't an option for me. Here's how I solved it:

    DELIMITER $$

    DROP FUNCTION IF EXISTS sfKillConnection $$

    CREATE FUNCTION sfKillConnection() RETURNS INT
    BEGIN

        SELECT connection_id() into @connectionId;
        KILL @connectionId;
        RETURN @connectionId;
    END $$


    DELIMITER ;

It's a function instead of a procedure so it can be called in a script like this:

select if(@error, sfKillConnection(), 0);

You'll probably need to start the mysql client with the --disable-reconnect option.

Answer 4

Here's a way to execute your upgrade commands only if a certain version is in place.

It's ugly but it's all I can suggest for mysql 5.

DELIMITER $$  
DROP FUNCTION IF EXISTS `Upgrade_Build`$$    
CREATE FUNCTION `Upgrade_Build`(bversion INT) RETURNS varchar(30)
BEGIN 
  IF bversion = (SELECT MIN(`db_version`) FROM `system`) THEN 
    UPDATE `pages`
      SET `component_parameters` =
        REPLACE(`component_parameters`,'folderviewer_','folder_viewer.'); 
    UPDATE `system`
      SET `db_version` = bversion+1;
    return CONCAT('Success: ',bversion,' > ',bversion+1); 
  ELSE 
    return CONCAT('Failed - Version is ',(SELECT MIN(`db_version`) FROM `system`)); 
  END IF; 
END$$
DELIMITER ;

To use it do this:

SELECT Upgrade_Build(1327);

You'll see something like this if the current version WAS 1327:

Success: 1327 > 1328

Run it a second time and you'll see:

Failed - Version is 1328

Hope this is of use to someone.

Blessings,

Martin Francis http://www.ecclesiact.com <><

Answer 5

Here's a low-forehead approach:

I needed to make sure that @search_admin_realname corresponded to a record in the profiles table; if not, I wanted to abort my script.

So I did this:

select @search_admin_id:= userid
from profiles
where realname=@search_admin_realname
;
# test that @search_admin_id is not null; 
# if it is, then the insert will fail and script will abort.
create temporary table t_search_admin_id ( search_admin_id mediumint( 9 ) not null );
insert into t_search_admin_id ( search_admin_id ) values ( @search_admin_id );

The only drawback is that I can't really customize the error message :(