4

I am still new to using databases, but currently I am using a mysql database and attempting to make my bash script unbreakable because it might be used in several different environments (dev, qa, etc). I know it will run correctly without this one line.

CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);

I also know that if I put that exact line into my database when that table does not exist then it runs perfectly and if it does exist already it gives me a warning, but does not break.

+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Note  | 1050 | Table 'backupfiles' already exists |
+-------+------+------------------------------------+

When I run the line above and there is no table named backupfiles it works perfectly, but when there is it hangs(when I say hangs it sits there and does nothing) 95% of the time and 5% of the time works. Has anyone run into a problem like this? I am using AWS RDS(Amazon Web serices Relational Database Service) and the mysql server is 5.5.27

Here is all of my code that I relates to the mysql database

mysql -h portal-rds -u $user --password=$mysqlpw <<QUERY_INPUT
CREATE DATABASE IF NOT EXISTS $DATABASE;
use $DATABASE;
CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);
INSERT INTO backupfiles VALUES ('$archive_file_name', '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());
COMMIT;
QUERY_INPUT
5
  • What version of MySQL are you running ? Is the table InnoDB ? Jul 11, 2013 at 17:27
  • @RolandoMySQLDBA this is in Amazon's Web Service's (AWS) Relational Database Service (RDS) and it is running mysql 5.5.27
    – Tall Paul
    Jul 11, 2013 at 17:35
  • Don't you mean use $DATABASE; instead of use glacier; ??? Jul 11, 2013 at 18:23
  • Thanks @RolandoMySQLDBA I missed that! I have been editing this section a lot today trying to fix this issue and earlier I hardcoded a few things, but the $DATABASE = 'glacier'
    – Tall Paul
    Jul 11, 2013 at 18:26
  • The answer to why this works inconsistently is probably just waiting for you to find it by running SHOW PROCESSLIST from another session while the connection is allegedly not reaponding. Jul 12, 2013 at 11:05

1 Answer 1

2

I find this troubling for two reasons

REASON #1

This could be a bug that never got squashed out of MySQL 5.5.17

REASON #2

You are trying to acquire a metadata lock on an InnoDB table in an RDS environment? Ouch !!!

Possible Workaround

Instead of trusting the metadata locking in RDS, ask the information_schema if the table exists already. Suppose the table is mydb.backupfiles. Run this query:

SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='backupfiles';

If the answer comes back 0, run CREATE TABLE backupfiles .... Otherwise, don't run it.

Possible Solution

Upgrade to the Latest MySQL (5.6.12) or the Latest MySQL5.5 (5.5.31). Hopefully, this issue was solved in a more recent version

Give it a Try !!!

UPDATE 2013-07-11 15:16 EDT

Here is a rewrite of your bash code to check if the table already exists

    echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
    echo "use $DATABASE;" >> /tmp/runthis.sql
    SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
    SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
    TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
    if [[ ${TABLE_EXISTS} -eq 0 ]]; then
      SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
      SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
      SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
      echo "${SQLSTMT}" >> /tmp/runthis.sql
    fi
    SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
    SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
    echo "${SQLSTMT}" >> /tmp/runthis.sql
    echo "COMMIT;" >> /tmp/runthis.sql
    mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql
6
  • So I can run this and it will return a value, I am not sure if I am searching with the wrong terms, but to simply put it where is the value it is returning being saved, or is there a way to save it so that I could put it in an if else statement?
    – Tall Paul
    Jul 11, 2013 at 18:12
  • Is the CREATE TABLE being launched in PHP or at the MySQL prompt ? Jul 11, 2013 at 18:14
  • It is all via a bash script and I am in the MySQL prompt when it is called, I just updated the initial question to try and add clarifying details
    – Tall Paul
    Jul 11, 2013 at 18:20
  • I am sorry I have to go to a meeting right now, I'll back in about 40 minutes to answer this a little more. Jul 11, 2013 at 18:28
  • That is fine, thank you so much for the help! I thought I fixed it but took out the create table if not exists line
    – Tall Paul
    Jul 11, 2013 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.