Skip to content

Instantly share code, notes, and snippets.

@rogeriolino

rogeriolino/fix_mysql.sql

Last active September 9, 2024 20:42
Show Gist options
  • Save rogeriolino/7c211fb83ea2325f4fcd45346da91401 to your computer and use it in GitHub Desktop.
Save rogeriolino/7c211fb83ea2325f4fcd45346da91401 to your computer and use it in GitHub Desktop.
Download ZIP
Fix Novo SGA MySQL AUTO_INCREMENT
Raw
fix_mysql.sql
DROP PROCEDURE IF EXISTS update_atendimentos_auto_increment;
DROP PROCEDURE IF EXISTS fix_atendimentos_id;
DELIMITER $$
CREATE PROCEDURE update_atendimentos_auto_increment()
BEGIN
SET @next_historico_id = (SELECT COALESCE(MAX(id), 0) + 1 FROM historico_atendimentos);
SET @next_atendimentos_id = (SELECT COALESCE(MAX(id), 0) + 1 FROM atendimentos);
SET @sql = CONCAT('ALTER TABLE atendimentos AUTO_INCREMENT = ', GREATEST(@next_historico_id, @next_atendimentos_id));
PREPARE stmt FROM @sql;
EXECUTE stmt;
END$$
CREATE PROCEDURE fix_atendimentos_id()
BEGIN
SET @last_id = (SELECT COALESCE(MAX(id), 0) FROM historico_atendimentos);
SET FOREIGN_KEY_CHECKS=0;
UPDATE atendimentos SET id = id + @last_id WHERE id < @last_id;
UPDATE atendimentos_codificados SET atendimento_id = atendimento_id + @last_id WHERE atendimento_id < @last_id;
SET FOREIGN_KEY_CHECKS=1;
END$$
DELIMITER ;
@rogeriolino
Copy link
Author

start.sh

#!/bin/sh
echo "Starting pre-flight check..."

echo -n "Database url: "
if [ -z "$DATABASE_URL" ]; then
    echo "\nYou need to tell me where the database is and how to connect to it by setting DATABASE_URL environment variable"
    echo "e.g.: Using the flag -e DATABASE_URL='mysql://user:pass@127.0.0.1:3306/dbname?charset=utf8mb4&serverVersion=5.7' at docker container run"
    exit 1
fi
echo "Ok"

bin/console cache:clear --no-debug --no-warmup
bin/console cache:warmup

# we need to wait until the database is up and accepting connections
until bin/console -q doctrine:query:sql "select version()" > /dev/null 2>&1; do
    echo "Waiting for database...";
    sleep 5;
done

echo "Database is up, configuring schema"

set -xe

# Copy env vars to environment file (for cron usage)
printenv >> /etc/environment

# Install/Updates the database schema
/var/www/html/bin/console novosga:install
/var/www/html/bin/console dbal:run-sql 'CALL update_atendimentos_auto_increment'

echo "Setup done! Starting apache"
exec /usr/bin/supervisord -n

@rogeriolino
Copy link
Author

Dockerfile

FROM novosga/novosga:2.1

COPY start.sh /usr/local/bin

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment