Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What version of MySQL are you running ? Is the table InnoDB ? –  RolandoMySQLDBA Jul 11 '13 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 '13 at 17:35
    
Don't you mean use $DATABASE; instead of use glacier; ??? –  RolandoMySQLDBA Jul 11 '13 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 '13 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. –  Michael - sqlbot Jul 12 '13 at 11:05

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
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 '13 at 18:12
    
Is the CREATE TABLE being launched in PHP or at the MySQL prompt ? –  RolandoMySQLDBA Jul 11 '13 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 '13 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. –  RolandoMySQLDBA Jul 11 '13 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 '13 at 18:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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