bradfordpythian · February 22, 2017 18:51 · Feb 22, 2017 · Feb 21, 2017 · Feb 21, 2017 · Feb 21, 2017
diff --git a/alter_to_tokudb.sh b/alter_to_tokudb.sh
@@ -9,6 +9,7 @@ TMP_FILE="${TMP_DIR}/${SCRIPT_NAME}.tmp.$$"
 
 [ -z "${SCHEMA}" ] && SCHEMA="test"
 [ -z "${SOURCE_ENGINE}" ] && SOURCE_ENGINE="InnoDB"
+[ -z "${ROW_FORMAT}" ] && ROW_FORMAT="TOKUDB_LZMA"
 
 generate_foreign_keys() {
   local FILE=$1
@@ -42,7 +43,7 @@ alter_tables() {
 
   [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1
 
-  mysql -N -u${MYSQL_USER} -p${MYSQL_PWD} -e "SELECT CONCAT('SELECT NOW();ALTER TABLE ',TABLE_NAME,' ENGINE=TokuDB;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND engine = '${SOURCE_ENGINE}'"  ${SCHEMA} > ${FILE}
+  mysql -N -u${MYSQL_USER} -p${MYSQL_PWD} -e "SELECT CONCAT('SELECT NOW();ALTER TABLE ',TABLE_NAME,' ENGINE=TokuDB, ROW_FORMAT=${ROW_FORMAT};') FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND engine = '${SOURCE_ENGINE}'"  ${SCHEMA} > ${FILE}
   RC=$?
   [ ${RC} -ne 0 ] && echo "ERROR: Unable to generate alter file for '${SOURCE_ENGINE}' tables" && exit ${RC}
 

diff --git a/alter_to_tokudb.sh b/alter_to_tokudb.sh
@@ -57,11 +57,12 @@ alter_tables() {
 [ -z "${MYSQL_USER}" -o -z "${MYSQL_PWD}" ] && echo "ERROR: MySQL credentials are not defined" && exit 2
 
 # Generate the foreign keys that are to be dropped
-FK_FILE="${LOG_DIR}/drop_foreign_keys.sql"
+FK_FILE="${LOG_DIR}/drop_foreign_keys.pre.sql"
 generate_foreign_keys ${FK_FILE}
 [ -s "${FK_FILE}" ]  && drop_foreign_keys ${FK_FILE}
 
 # Verify there are no foreign keys left
+FK_FILE="${LOG_DIR}/drop_foreign_keys.post.sql"
 generate_foreign_keys ${FK_FILE}
 [ -s "${FK_FILE}" ] && echo "ERROR: foreign keys remain in '${FK_FILE}'" && exit 1
 echo "All foreign keys for Schema ${SCHEMA} removed"

diff --git a/alter_to_tokudb.sh b/alter_to_tokudb.sh
@@ -59,7 +59,7 @@ alter_tables() {
 # Generate the foreign keys that are to be dropped
 FK_FILE="${LOG_DIR}/drop_foreign_keys.sql"
 generate_foreign_keys ${FK_FILE}
-[ ! -s "${FK_FILE}" ]  && drop_foreign_keys ${FK_FILE}
+[ -s "${FK_FILE}" ]  && drop_foreign_keys ${FK_FILE}
 
 # Verify there are no foreign keys left
 generate_foreign_keys ${FK_FILE}

diff --git a/alter_to_tokudb.sh b/alter_to_tokudb.sh
@@ -0,0 +1,72 @@
+#!/bin/sh
+
+SCRIPT_NAME=`basename $0 | sed -e "s/\.sh$//"`
+[ -z "${TMP_DIR}" ] && TMP_DIR="/tmp"
+TMP_FILE="${TMP_DIR}/${SCRIPT_NAME}.tmp.$$"
+
+[ -z "${LOG_DIR}" ] && LOG_DIR="/opt/pythian/log"
+[ ! -d "${LOG_DIR}" ] && echo "ERROR: Log directory '${LOG_DIR}' is not accessible." && exit 1
+
+[ -z "${SCHEMA}" ] && SCHEMA="test"
+[ -z "${SOURCE_ENGINE}" ] && SOURCE_ENGINE="InnoDB"
+
+generate_foreign_keys() {
+  local FILE=$1
+
+  [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1
+
+  echo "Generating foreign keys in '${SCHEMA}'"
+  mysql -N -u${MYSQL_USER} -p${MYSQL_PWD} -e "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_SCHEMA=DATABASE();" ${SCHEMA} > ${FILE}
+  RC=$?
+  [ ${RC} -ne 0 ] && echo "ERROR: select of foreign keys failed" && exit ${RC}
+
+  return 0
+}
+
+drop_foreign_keys() {
+  local FILE=$1
+
+  [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1
+
+  echo "Dropping foreign keys in '${SCHEMA}' with '${FILE}'"
+  mysql -vvv -u${MYSQL_USER} -p${MYSQL_PWD} ${SCHEMA} < ${FILE}
+  RC=$?
+  [ ${RC} -ne 0 ] && echo "ERROR: drop of foreign keys failed" && exit ${RC}
+
+  return 0
+
+}
+
+alter_tables() {
+  local FILE=$1
+
+  [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1
+
+  mysql -N -u${MYSQL_USER} -p${MYSQL_PWD} -e "SELECT CONCAT('SELECT NOW();ALTER TABLE ',TABLE_NAME,' ENGINE=TokuDB;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND engine = '${SOURCE_ENGINE}'"  ${SCHEMA} > ${FILE}
+  RC=$?
+  [ ${RC} -ne 0 ] && echo "ERROR: Unable to generate alter file for '${SOURCE_ENGINE}' tables" && exit ${RC}
+
+  time mysql -vvv -u$MYSQL_USER -p$MYSQL_PWD ${SCHEMA} < ${FILE}
+  RC=$?
+  [ ${RC} -ne 0 ] && echo "ERROR: Alter of tables failed" && exit ${RC}
+
+  return 0
+}
+
+# Ensure environment variables to access MySQL environment via script are defined
+[ -z "${MYSQL_USER}" -o -z "${MYSQL_PWD}" ] && echo "ERROR: MySQL credentials are not defined" && exit 2
+
+# Generate the foreign keys that are to be dropped
+FK_FILE="${LOG_DIR}/drop_foreign_keys.sql"
+generate_foreign_keys ${FK_FILE}
+[ ! -s "${FK_FILE}" ]  && drop_foreign_keys ${FK_FILE}
+
+# Verify there are no foreign keys left
+generate_foreign_keys ${FK_FILE}
+[ -s "${FK_FILE}" ] && echo "ERROR: foreign keys remain in '${FK_FILE}'" && exit 1
+echo "All foreign keys for Schema ${SCHEMA} removed"
+
+ALTER_FILE="${LOG_DIR}/alter.sql"
+alter_tables ${ALTER_FILE}
+
+exit 0
No results found