Comma Seperated ALTER TABLE comamnds causes Error Code 1054

[LostInTheWoods]

The .patch script generated produces comma separated statements like this:

ALTER TABLE a ADD COLUMN b tinyint(1) NOT NULL DEFAULT '0' AFTER c , ADD COLUMN d tinyint(1) NULL DEFAULT '1' AFTER f;

But this is a known problem and produces Error Code: 1054 http://bugs.mysql.com/bug.php?id=60650

A suggested fix is to use separate ALTER TABLE statements: ALTER TABLE a ADD COLUMN b tinyint(1) NOT NULL DEFAULT '0' AFTER c ALTER TABLE a ADD COLUMN d tinyint(1) NULL DEFAULT '1' AFTER f;

Can this be implemented?

[mmatuson]

Yes, this can be implemented. I had plans to fix this but have not had the time to do it. Pull requests welcome.

[LostInTheWoods]

Unfortunately, I don't know Python, so I don't feel able to contribute. But this tool is exactly what I need to nightly schema comparisons with CRON. If you manage to find time, that would be great. Thanks.

[vingc]

I have coded one AWK script to improve the .patch script, content like this:

!/bin/bash

################################## VARIABLES & FUNCTIONS ###################################### SRC_PATCH_SCRIPT="$1" DST_PATCH_SCRIPT="$2"

################################## MAIN BODY ############################################## awk ' BEGIN{ FS = ","

% print "FS:" FS

}

($1 ~ /^ALTER TABLE [a-zA-Z0-9_]*/) && (NF >= 2) { OFS="\n" ret = match($1,/^ALTER TABLE [a-zA-Z0-9_-]*/) if(ret == 0) { print $0 next } prefix = substr($1,RSTART,RLENGTH)

print "prefix:" prefix

tmpRowStr = substr($1,RSTART+RLENGTH,length($1)-(RLENGTH+RSTART)+1)

% print "tmpRowStr:" tmpRowStr

for(i=2; i<=NF; i++)
{
   ret = match($i,/^ ADD|DROP|MODIFY COLUMN `[a-zA-Z0-9_-]*`/)
   if(ret == 0)
   {

% this field belongs to current Row

      tmpRowStr = tmpRowStr "," $i  
   }
   else
   {

% output old Row & create new Row

      print prefix tmpRowStr ";"
      tmpRowStr = $i
   }
}

% output the last field ,it belongs to new Row and has a ";" already.

print  prefix tmpRowStr
next

}

1 {print $0} ' "$SRC_PATCH_SCRIPT" >"$DST_PATCH_SCRIPT"