Moving from MSSQL to MySQL.

Hi.

I'm trying to build a generic DB change script, one which will be used to alter the INTERNAL db and then copied to the TEST and LIVE servers for running to make sure the DB schemas match and then we can move the data.

The reason for this is that manually amending the DBs and then someone forgets and BOOM!!! Usual sort of thing. So, having a mechanism to make the DB changes in order needs to be implemented.

The ability to add new columns to existing tables, create new tables, indexes, alter data if required, etc. All the usual stuff.


What I'm not sure (I'm 3 days into active MySQL development - used MS SQL for the last 7 years) is can I do ....

begin transaction

statement1
if error then
  rollback transaction
  exit
endif

statement2
if error then
  rollback transaction
  exit
endif

statement3
if error then
  rollback transaction
  exit
endif

commit transaction

Open in new window


So, say I want to add a column called "publish_merchant" which is a bit type to the table sw_merchants if it doesn't exist with a default of 0, how would I do that?

I can get bits working ...

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant'

Open in new window


But I can't see how to "wrap" that up in a test firstly or how to wrap that in error handling.

AND then how to have multiple statements so that all or none of the changes work.

Over time, new features will be added to the DB and these changes need to be synched as part of the app.


Given the nature of my issue, have I chosen the right approach? Is there a better approach?

I can't just clone the DB. Only SOME of the data moves from INTERNAL (datafeeds). DataFeeds creates data for use in the TEST and LIVE sites. But some data is for the use of INTERNAL only. So, just cloning the entire DB is out.

Any ideas.

Regards,

Richard.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Stephan_SchrandtCommented:
You can use something like this for testing:

IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant') IS NULL

And set autocommit off at the beginning of your stored proc so you explicitely have to commit the whole transaction:

SET autocommit = 0

More on mysql errors can be found here:

http://dev.mysql.com/doc/refman/5.1/en/innodb-error-handling.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperAuthor Commented:
OK. I'm frustrated. I've done this a million times in MS SQL. What am I doing wrong with mySQL?

Current code below just tells me I've got a "Syntax Error" ...

/**
 * $Id$
 *
 * This is the SQL Script that must be run to update the following databases:
 * 1 - Integration : datafeeds - unswitchd
 * 2 - Integration : fshopdb - switched
 * 3 - Production  : fshopdb - switched
 *
 * There will always be two "versions" of the code as there are switched and unswitched tables.
 *
 * Watch out for the switch tables. If the switch is set to 1 then use 2 and vice versa.
 */
DROP PROCEDURE IF EXISTS `test`.`DataFeeds_DatabaseUpgrade`;

DELIMITER $$

CREATE PROCEDURE `test`.`DataFeeds_DatabaseUpgrade`()
BEGIN
/**
 * RQuadling : 2012/01/05 19:12
 *
 * SW_MERCH.publish_merchant column
 *
 * :: START ::
 */
-- datafeeds
IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant') IS NULL THEN
    BEGIN
    ALTER TABLE sw_merch ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
    END

-- fsshopdb -- Remember to use the OTHER table for the switching.
IF (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'table_switch') IS NOT NULL THEN
    BEGIN
    SELECT @i_Switch = switch FROM fshopdb.table_switch;

    IF 1 = @i_Switch THEN
        BEGIN
        IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_2' AND COLUMN_NAME = 'publish_merchant') IS NULL THEN
            BEGIN
            ALTER TABLE sw_merch_2 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
            END
        END

    IF 2 = @i_Switch THEN
        BEGIN
        IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_1' AND COLUMN_NAME = 'publish_merchant') IS NULL THEN
            BEGIN
            ALTER TABLE sw_merch_1 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
            END
        END
END$$

DELIMITER ;

CALL `test`.`DataFeeds_DatabaseUpgrade`();

Open in new window


Now, no matter where I put ; (after END's), all I do is move the error code around.

I'm very tired of this. This is not a complex query. Just test to see if the column exists, if not, add it. Simply.


ARGH!!!!


Any help - PLEASE

0
Stephan_SchrandtCommented:
Where are your End Ifs ? Too much tsql I think ;-)

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
Got it!!!!

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `DataFeeds_DatabaseUpgrade`()
BEGIN
/**
 * RQuadling : 2012/01/05 19:12
 *
 * SW_MERCH.publish_merchant column
 *
 * :: START ::
 */
-- datafeeds
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant') THEN
    ALTER TABLE sw_merch ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
    INSERT INTO DatabaseUpgrades.Upgrades(UpgradeNotes) VALUES('Added sw_merch.publish_merchant column');
END IF;

-- fsshopdb -- Remember to use the OTHER table for the switching.
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'table_switch') THEN
    SELECT @i_Switch = switch FROM fshopdb.table_switch;

    IF 1 = @i_Switch AND NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_2' AND COLUMN_NAME = 'publish_merchant') THEN
        ALTER TABLE sw_merch_2 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
        INSERT INTO DatabaseUpgrades.Upgrades(UpgradeNotes) VALUES('Added sw_merch_2.publish_merchant column');
    END IF;

    IF 2 = @i_Switch AND NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_1' AND COLUMN_NAME = 'publish_merchant') THEN
        ALTER TABLE sw_merch_1 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
        INSERT INTO DatabaseUpgrades.Upgrades(UpgradeNotes) VALUES('Added sw_merch_1.publish_merchant column');
    END IF;
END IF;
/**
 * :: START ::
 */

END

Open in new window


This is from the created stored procedure.

Looks good so far...
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
Yeah. WAY too much T-SQL!!! Thank you for your pointers.

The main issue was the lack of flow control code OUTSIDE of a stored procedure. Grrrrr.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.