Tag Archives: MySQL

23 Apr

MySQL – Alter table-change datatype of column with foreign key

Schema alteration is a big headache especially when it has already reached to production and things get worse when the relations are involved.
Below is a short example for altering tables having foreign key constraints.

SQL for tables used in example:
create database temp;use temp;
drop table if exists city; drop table if exists country;
create table country ( c_id mediumint not null auto_increment primary key, c_name varchar(10)) engine=innodb;
insert into country (c_name) values ('india'),('uk'),('us');
create table city (city_id mediumint not null auto_increment primary key, country_id mediumint, city_name varchar(20), constraint fk_country_id foreign key (country_id) references country(c_id)) engine=innodb;
insert into city (country_id, city_name) values (1,'mumbai'), (2,'ahmedabad'), (2,'new york'), (2, 'la'), (3, 'london'), (3,'Birmingham');

We want to change the datatype of primary key c_id from mediumint to int.
Now, while you proceed to change the datatype of column in relation, you observe a typical mysql error.

mysql> alter table country modify column c_id int not null auto_increment;
ERROR 1025 (HY000): Error on rename of '.\temp\#sql-248_4' to '.\temp\country' (errno: 150)

“Error on the rename” doesn’t suggest you anything though you have a way out to know the actual error:

1) On your shell prompt fire:
$] perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

For more details you may look into last foreign key error section of innodb status:

2) In this case specifically you should check innodb status:
mysql> show engine innodb status;
...
-------------------------
LATEST FOREIGN KEY ERROR
-------------------------
120422 13:11:47 Error in foreign key constraint of table temp/city:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT "fk_country_id" FOREIGN KEY ("country_id") REFERENCES "country" ("c_id")
The index in the foreign key in table is "fk_country_id"
...

This clearly explains you the reason for error in table alteration. InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type. This restriction applies while you alter the parent or child table with Foreign Key Constraint.
So how ‘d you proceed? Here is one way to do it.

The steps to follow are:
– Alter all the referencing tables and remove Foreign Key relations.
– Alter all the referencing and referenced tables and modify column to new datatype.
– Alter all referencing tables create Foreign Key relations.

To ease out the task execute following SQL and you will get your queries that will help you changing the datatype step by step. You have to provide the parent table name and column that’s being referenced along with the datatype you want to modify to.

#COMMAND FOR DROP FOREIGNKEY RELATION
select
concat('ALTER TABLE ', table_name,' DROP FOREIGN KEY ',constraint_name,';') drop_fk_syntax
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';

+--------------------------------------------------+
| drop_fk_syntax |
+--------------------------------------------------+
| ALTER TABLE city DROP FOREIGN KEY fk_country_id; |
+--------------------------------------------------+

#COMMAND FOR APPLY COLUMN DEFINITION CHANGE TO PARENT TABLE
SELECT CONCAT('ALTER TABLE ', referenced_table_name,' MODIFY COLUMN ', referenced_column_name, ' bigint not null auto_increment;') modify_column
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';
+---------------------------------------------------------------------------+
| modify_column |
+---------------------------------------------------------------------------+
| ALTER TABLE country MODIFY COLUMN c_id bigint not null auto_increment; |
+---------------------------------------------------------------------------+

#COMMAND FOR APPLY COLUMN DEFINITION CHANGE TO CHILD TABLES
select concat('ALTER TABLE ', table_name,' MODIFY COLUMN ', column_name, ' bigint not null;') modify_column
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';

+------------------------------------------------------------+
| modify_column |
+------------------------------------------------------------+
| ALTER TABLE city MODIFY COLUMN country_id bigint not null; |
+------------------------------------------------------------+

#COMMAND FOR RESTORING FOREIGNKEY RELATION
select
concat('ALTER TABLE ',table_name,' ADD CONSTRAINT ',constraint_name ,' FOREIGN KEY (', column_name, ') REFERENCES ', referenced_table_name , '(', referenced_column_name,');') create_fk_syntax
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';

+--------------------------------------------------------------------------------------------------+
| create_fk_syntax |
+--------------------------------------------------------------------------------------------------+
| ALTER TABLE city ADD CONSTRAINT fk_country_id FOREIGN KEY (country_id) REFERENCES country(c_id); |
+--------------------------------------------------------------------------------------------------+

Execute above queries in sequence to modify your schema.

Always remember: “backup saves your life like no one else”.
Share your better ways.

30 Jan

Access Control in MySQL Stored Routines: DEFINER, INVOKER & SQL SECURITY

MySQL Stored Routines (functions and procedures) are not only used for improving performance but also they’re handy when it comes to enhancing security and restricting user access. This post briefs you about security aspects of stored routines by means of example.

By the definition of a Stored Routine,
– DEFINER clause specifies the creator of the stored routine.
– SQL SECURITY clause specifies the execution context of the stored routine.

Now let’s start with an example:

Create a very basic stored routine in test database:

DELIMITER //
CREATE PROCEDURE user_cnt()
BEGIN
SELECT COUNT(*) as total_user FROM mysql.user;
END;//
DELIMITER ;

Our current user is: root@localhost
[ You can see your current user by issuing select CURRENT_USER(); ]

Call the procedure:
CALL user_cnt()\G
total_user: 10

Here, We created a stored routine using root@localhost user.
– DEFINER: The user who creates this procedure. i.e. root@localhost
– SQL SECURITY: Defines under whose privileges the routine will be executed; defaults to DEFINER. i.e. root@localhost

As SQL SECURITY is set to DEFINER, a user even with only EXECUTE permission for routine can call and get the output of the stored routine regardless of whether that user has permission on mysql database or not.

Lets see how this works. Create a “execuser” with limited privileges:
GRANT EXECUTE ON test.* TO 'execuser'@'localhost' IDENTIFIED BY 'execuser' ;
FLUSH PRIVILEGEES;

Login using execuser to mysql prompt and call the procedure created under test database:
mysql> CALL user_cnt()\G
total_user: 10

Now check that below command fired by execuser fails due to lack of privileges:
mysql> select count(*) from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'

– Thus stored procedure allows us to restrict users to access tables directly but still getting access to certain data.
– User execuser@localhost is not having permission on mysql database and still he could get the data.
– As the SQL SECURITY was set to DEFINER (default), the execution of the routine happened under the security context of root@localhost user and returned the result.

Is this behaviour something that you don’t want to happen? Hmmm we have a way out here.
Lets rewrite the stored procedure as below:

DELIMITER //
DROP PROCEDURE IF EXISTS user_cnt;
CREATE DEFINER='root'@'localhost' PROCEDURE user_cnt()
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(*) as total_user FROM mysql.user;
END;//
DELIMITER ;

– Did you note “SQL SECURITY INVOKER”!! That’s our saviour.
– We’ve specified the DEFINER attribute explicitly.
– SQL SECURITY decides under whose privileges the stored routine gets executed! Here it is INVOKER, the user that CALLs the routine!

Let’s check through execuser@localhost user:
mysql> call user_cnt();
ERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'

So, this is now pretty clear that the stored routine tried to execute the SELECT query under INVOKER’s (execuser@localhost) privileges; and the routine failed as the INVOKER has no privileges on user table.

Finally, a few points:
Together DEFINER & SQL SECURITY clauses define the security context to be used during routine execution time.
The DEFINER attribute defaults to the current user & defines the creator of the Stored Routine.
SQL SECURITY defines the execution permission of the stored routine and defaults to DEFINER.

I hope this cleared the basics; thanks for reading.

06 Dec

Escaping “Lesson Learned” dealing with MySQL Databases & Case Sensitiviy

Do you ever need to transfer databases between servers? Different OSes?

Two common ways of data migration:
1. Create mysqldump and load it.
2. File transfer using SCP.

Case Sensitivity and Issue:
Yes, that can create big issues when you have to deal with systems having different case sensitivity.
E.g. on linux you can create directories with names “Kedar” or “kedar” which is not possible on windows!
It will be harmful when file-system itself restricting the names to lower case.
MySQL has a variable for that: lower_case_file_system.

Documentation says: This variable describes the case sensitivity of file names on the file system where the data directory is located. OFF means file names are case sensitive, ON means they are not case sensitive. This variable is read only because it reflects a file system attribute and setting it would have no effect on the file system.

Try loading data having different values for lower_case_file_system and you will get it!

How MySQL tries to solves this problem:
It asks user to handle the issue by changing system variable lower_case_table_names accordingly.
Check the options available:
0 : Table names are stored as specified and comparisons are case sensitive.
1 : Table names are stored in lowercase on disk and comparisons are not case sensitive.
2 : Table names are stored as given but compared in lowercase.

So where do I see the problem?
Here control is given to user with the variable lower_case_table_names and ignoring which can definitely lead to issues. These issues can be avoided by putting restrictions.
Also queries working on MAC / Windows may not work well on Linux / Other Flavours; cause problems on application side.

Documentation Says: You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result.

Now, I recently read the change: PERFORMANCE_SCHEMA is renamed to lowercase in MySQL 5.5 to answer a bug [http://bugs.mysql.com/bug.php?id=57609] pointed by Marc Alff’s post [http://marcalff.blogspot.com/].

In one line I wish: lower_case_table_names, RIP!
I wish MySQL does the same for all tables in order to avoid case sensitivity problems :).

I feel MySQL should not allow upper cases. What do you think?

30 Nov

Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL

Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround.

Download MySQL UDF:

[root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz

[refer: http://www.mysqludf.org/]

Extract and Install:

[root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz
install.sh
lib_mysqludf_sys.c
lib_mysqludf_sys.html
lib_mysqludf_sys.so
lib_mysqludf_sys.sql
Makefile

[root@localhost kedar]# sh install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
MySQL UDF compiled successfully

Please provide your MySQL root password
Enter password:
MySQL UDF installed successfully

Create function sys_exec as follows:

CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so';

sys_exec – executes an arbitrary command, and returns it’s exit code.

You also can similarly create functions:
sys_eval – executes an arbitrary command, and returns it’s output.
sys_get – gets the value of an environment variable.
sys_set – create an environment variable, or update the value of an existing environment variable.

Example – How to load txt file to MySQL using Stored Procedure & Load Data syntax:

Step-1. Creating table:

CREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM

Step-2. Create a sample file to load:

vi loadtest.txt
1
2
3

Step-3. Create a shell script:

vi /tmp/load.sh
mysql -u mysql_user -p mysql_password -e “load data local infile \”$1\” into table $2;”

Step-4. Create a Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `load_data_SP` $$
CREATE PROCEDURE `load_data_SP` (in_filepath varchar(100),in_db_table varchar(100))
BEGIN
declare exec_str varchar(500);
declare ret_val int;
set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
set ret_val=sys_exec(exec_str);
if ret_val=0 then
select “Success” as Result;
else
select “Please check file permissions and paths” as Result;
end if;
END $$
DELIMITER ;

Step 5. Execute:

CALL `load_data_SP`(‘/tmp/loadtest.txt’ , ‘test.t’);

…and that’s it Stored Procedure will return Success or Failure accordingly.

Make sure you’re having file permissions well set and MySQL can access the files.
Here I’ve kept the files under /tmp directory with chmod 777 & chown mysql:mysql to remove the permission-issue possibility.

Hope this helps.

18 Nov

10 Steps: MySQL Monitoring through Nagios: Install & Configure

Nagios is a powerful monitoring system and here we will learn how to monitor MySQL through Nagios. We will be installing Nagios, required plugins and configuring it to monitor MySQL Database Server.

Let’s unleash the power step by step:

Installing and configuring Nagios

Step-1 : Install required stuff:
yum install httpd
yum install gcc
yum install glibc*
yum install gd*

Step-2 :Create Nagios user account and group
useradd nagios
passwd nagios
groupadd nagcmd
usermod -G nagcmd nagios
usermod -G nagcmd apache

Step-3: Downloads:
Create directory:
mkdir NagiosSetup
cd NagiosSetup

Download nagios
wget -X Get "http://sourceforge.net/projects/nagios/files/nagios-3.x/nagios-3.2.1/nagios-3.2.1.tar.gz/download"

Download Nagios Plugins:
wget -X Get "http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download"

Step-4: Install Nagios

tar -xzvf nagios-3.2.1.tar.gz
cd nagios-3.2.1
./configure --with-command-group=nagcmd
make all
make install
make install-config
make install-commandmode
make install-init
chkconfig --add nagios

[If you miss step “make install-init” you may get:: error reading information on service nagios: No such file or directory ]

Configure Nagios Web Interface:
make install-webconf
htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin

[specify password for nagios admin]

Step-5: Install plugins
tar xvf nagios-plugins-1.4.11.tar.gz
cd nagios-plugins-1.4.11
./configure --with-nagios-user=nagios --with-nagios-group=nagios
make
make install

Step-6: Verify Installation, Starting nagios for the first time

service nagios start
Browse: http://localhost/nagios

Here if you get Error:
“You don’t have permission to access /nagios/ on this server.”

Check /etc/httpd/conf/httpd.conf for DirectoryIndex.
If it’s not having index.php add it as follows:

vi /etc/httpd/conf/httpd.conf
DirectoryIndex index.php index.html index.html.var

Make sure you do restart apache(httpd) and nagios every time you change the config file. You must have php installed.

Monitoring MySQL:

Step-7: Download, Extract and install the MySQL Plugin:

wget http://labs.consol.de/wp-content/uploads/2010/10/check_mysql_health-2.1.3.tar.gz

tar -zxvf check_mysql_health-2.1.3.tar.gz
cd check_mysql_health-2.1.3
./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl
make
make install

Step-8: Create database user:
grant usage, replication client on *.* to 'nagios'@'localhost' identified by 'nagios';

Step-9: Provide email address for nagiosadmin:
[Change contacts.cfg file accordingly.]
vi /usr/local/nagios/etc/objects/contacts.cfg

define contact{
contact_name nagiosadmin ; Short name of user
use generic-contact ; Inherit default values from generic-contact template (defined above)
alias Kedar ; Full name of user
email kedar@nitty-witty.com ; <<***** CHANGE THIS TO YOUR EMAIL ADDRESS ******
}

Step-10: Configuring Nagios to Monitor MySQL Server

vi /usr/local/nagios/etc/nagios.cfg
add following line:
cfg_file=/usr/local/nagios/etc/objects/mysqlmonitoring.cfg

Define check_mysql_health command as follows:

vi /usr/local/nagios/etc/objects/commands.cfg

define command{
command_name check_mysql_health
command_line $USER1$/check_mysql_health -H $ARG4$ --username $ARG1$ --password $ARG2$ --port $ARG5$ --mode $ARG3$
}

Enter services to be monitored in mysqlmonitoring.cfg:

vi /usr/local/nagios/etc/objects/mysqlmonitoring.cfg
Add:

define service{
use local-service
host_name localhost
service_description MySQL connection-time
check_command check_mysql_health!nagios!nagios!connection-time!127.0.0.1!3306!
}


define service{
use local-service
host_name localhost
service_description MySQL slave-io-running
check_command check_mysql_health!nagios!nagios!slave-io-running!127.0.0.1!3306!
}


define service{
use local-service
host_name localhost
service_description MySQL slave-sql-running
check_command check_mysql_health!nagios!nagios!slave-sql-running!127.0.0.1!3306!
}

Here we’ve monitored 3 services: Connection-time, io thread and sql thread (replication) status. You can monitor more parameters described here: http://labs.consol.de/nagios/check_mysql_health/

Note: Every time you change configuration file, verify before starting nagios using command:
/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg

Finally start nagios service and you’re done with nagios installation and configuration for monitoring MySQL.

Download PDF

I hope you’ve found this useful.

-- Kedar Vaijanapurkar --