CODEWITHSUNDEEP
mysqlsqlbashcommand-line
Eljuan
Eljuan

Reputation: 104

How can I check for errors from the preceding statement in a MySQL script?

I am running some MySQL scripts (for batches) from shell scripts, just using the mysql command line utility. In one of these scripts, I want to create a new table, populate it, and when I am positive that the population succeeded, rename it to take the place of an old table, and drop the old table, so the sequence of events looks like this:

create table if not exists mystuff_tmp (
  id int(10) unsigned not null auto_increment,
  average_value decimal(10,4) default null,
  day date not null,
  primary key (id)
) engine=InnoDB default charset=utf8;

insert into mystuff_tmp (
    average_value,
    day
) select
    avg(value),
    date(input)
from
    details
where
    input between date(now()) - interval 7 day and date(now)
group by
    date(input);

***if there were no errors***
rename table mystuff to mystuff_old, mystuff_tmp to mystuff;
drop table mystuff_old;

In Sybase (and its children, like MS SQL), there's @@error, but the only similar system variable I find in MySQL is error_count, and that does not get updated by session errors...

Upvotes: 1

Views: 488

Answers (3)

joem
joem

Reputation: 331

I think

SELECT @@session.warning_count;

or 

SELECT @@session.error_count;

will get you what you want

Upvotes: 1

Shadow
Shadow

Reputation: 34232

Mysql has error handlers to handle various errors, and warnings. In these error handlers you can specify value to a variable. Before the rename operation you can check the value of the variable.

Upvotes: 0

Sasha Pachev
Sasha Pachev

Reputation: 5326

What I do is just break up the shell script into parts. For example:

mysql_cmd="mysql -u$mysql_user -p$mysql_pw $db"
$mysql_cmd <<eot
create table if not exists mystuff_tmp (
  id int(10) unsigned not null auto_increment,
  average_value decimal(10,4) default null,
  day date not null,
  primary key (id)
) engine=InnoDB default charset=utf8;

insert into mystuff_tmp (
    average_value,
    day
) select
    avg(value),
    date(input)
from
    details
where
    input between date(now()) - interval 7 day and date(now)
group by
    date(input);
eot

if [ $? != 0]
then
  $mysql_cmd <<eot
rename table mystuff to mystuff_old, mystuff_tmp to mystuff;
drop table mystuff_old;
eot
fi

Upvotes: 0

Related Questions