• A Django site. •
• •
March 2, 2013

Saptarshi Purkayastha
SaptarshiPurkayastha
Sunny Talks Tech is about »
» Alter Table for column with Foreign key in MySQL 5.6 Fails

Oracle released the much awaited MySQL 5.6 GA on 5th Feb, 2013. Much to everyone’s surprise and mysqlchanging direction in some sense, lots of improvements were made available in the Community release of MySQL, which were expected to be only part of the Enterprise Edition only.

Eager to try out the new NoSQL and performance improvements in 5.6, I downloaded the new installer. It is a packaged installer than unpacks and installs connectors, workbench and few other things along with the MySQL 5.6 Server. A surprising place where I got stuck was trying to install OpenMRS. The liquibase changeset uses <modifyType> tag and attempts to change the varchar column size. This works well under MySQL 5.5, but fails in 5.6.

While I’ve tried searching for this change in the release notes, what’s new and few other places, I haven’t found this mentioned clearly for the MySQL 5.6 release. The problem is that earlier you could disable the foreign key constraints check, modify the columns that have the constraints and re-enable the foreign key checks. If you changed the columns on both ends fine, things would just work well. But in 5.6 it seems there has been a change to this and the only mention I’ve found is new error messages that the server can throw. There is probably some tighten of things around the constraints management, but I couldn’t find much.

Here are the server error messages from MySQL 5.6 and MySQL 5.5:

http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html#error_er_fk_column_cannot_change
which wasn't there in:
http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

by Saptarshi Purkayastha at 4:15 PM under blog , database , developer , intern , liquibase , mysql , openmrs , soc , sql
December 17, 2009

Justin Miranda
OpenMRS Blog
in the midst of modern is about »
» On backing up and restoring large mysql databases

In order to improve the performance of mysql when dealing with large databases, there's a couple of settings that can be tweaked in your my.cnf file (or through a MySQL administration tool). The following line has proven to be the most useful.

innodb_buffer_pool_size = 1G

I've been meaning to write about this improvement for the last few weeks, but I wanted to test it myself first. So far I'm very pleased with the performance improvement (database restore time is down from about 3 hours to about 45 minutes on a ~1.2G database). I haven't tested how this setting affects MySQL during normal execution, but it certainly helps improve performance during backups and restores. So if you find yourself backing up and restoring large MySQL databases, you should test this out. You should (obviously) not set innodb_buffer_pool_size to a value greater than the available memory on your machine.

Thanks to Marc Harrison for the tip.

If you want to read some more about this configuration, I found a couple of blog entries from a few years ago Enjoy!

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

by Justin Miranda at 4:22 PM under blog , developer , mentor , mysql , openmrs
June 1, 2008

Upul Godage
UpulGodage
Summer of Code Notes from Upul is about »
» MySQL stored procedure to upgrade tribe


I wrote a MySQL stored procedure to upgrade the existing patient tribe values to a person attribute type.  This method is not going to be used, so it will not be checked in. So I am putting it here so that somebody may get some use out of it.

– define the procedure
DELIMITER $$

DROP PROCEDURE IF EXISTS `openmrs`.`convert_tribe`$$
CREATE PROCEDURE `openmrs`.`convert_tribe` ()
BEGIN
DECLARE currentAutoCommit BOOLEAN;

DECLARE tribeTypeId INT(11); — tribe person attribute type id

– fetched values of patients with tribe are stored in the following variables
DECLARE patientId INT(11);
DECLARE tribeId INT(11);

DECLARE noMoreRows INT(11) DEFAULT 0;

DECLARE patientsWithTribe CURSOR FOR
SELECT patient_id, tribe FROM patient WHERE tribe IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET noMoreRows = 1;
END;

– disable auto commit
SELECT @@autocommit INTO currentAutoCommit;
SET autocommit = 0;

START TRANSACTION;

– create the tribe person attribute type
INSERT INTO person_attribute_type
(name, description, format, creator, date_created)
VALUES (’Tribe’, ‘Tribe of the person’, ‘org.openmrs.module.tribe.Tribe’, 1, NOW());
– get the created tribe person attribute type id
SELECT person_attribute_type_id into tribeTypeId FROM person_attribute_type WHERE name = ‘Tribe’;

– read patient tribe values and add new person attributes
OPEN patientsWithTribe;
cursorLoop : LOOP
FETCH patientsWithTribe INTO patientId, tribeId;
IF noMoreRows = 1 THEN
LEAVE cursorLoop;
END IF;

INSERT INTO person_attribute (person_id, value, person_attribute_type_id, creator, date_created)
VALUES (patientId, tribeId, tribeTypeId, 1, now());
END LOOP cursorLoop;

CLOSE patientsWithTribe;

– remove tribe column
ALTER TABLE patient DROP FOREIGN KEY belongs_to_tribe;
ALTER TABLE patient DROP COLUMN tribe;

– commit everything and restore auto commit value
COMMIT;
SET autocommit = currentAutoCommit;
END$$

DELIMITER ;

– run the procedure
CALL convert_tribe;

– drop the procedure
DROP PROCEDURE convert_tribe;

by upulgodage at 5:04 PM under blog , developer , intern , mysql , openmrs , soc , sql , stored procedure (Comments)