 | Coincidentally, today was published a review of an excellent book about MySQL Stored Procedures programming by Guy Harrison and Steven Feuerstein. I warmly recommend this book to anyone using stored routines with MySQL. |
CREATE PROCEDURE p1(x INT, y INT)
DETERMINISTIC
BEGIN
SELECT
x ;
SELECT
x AS first_param,
y AS second_param;
SELECT
x,
y,
x + y AS sum_xy,
x * y AS prod_xy;
SELECT * FROM t1;
END
$sth->more_results after retrieving each dataset. | Several months ago I wrote about logs on demand in MySQL 5.1. Now that 5.1 is approaching its maturity stage, I am happy to report that this feature has proven itself very handy and useful. Petr Chardin will talk about this very feature at the MySQL Conference and Expo. |
bkf clone bk://mysql.bkbits.net/mysql-5.0-community mysql-5.0Cool. Now what?
select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
set profiling = 1;
select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
Here is the first glimpse. Basically, it tells you the duration of each query. Not very exciting so far, but let's keep going. The Query_ID field is a reference for us, so that we can ask more about a specific query. In this list, the table creation is query no. 3, so let's ask some specifics about this one:
drop table if exists t1;
create table t1 (id int not null);
insert into t1 values (1), (2), (3);
select * from t1;
#
# AND FINALLY!
#
show profiles;
+----------+----------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+----------+-------------------------------------+
| 0 | 0.000045 | set profiling = 1 |
| 1 | 0.000071 | select @@profiling |
| 2 | 0.000087 | drop table if exists t1 |
| 3 | 0.031741 | create table t1 (id int not null) |
| 4 | 0.000615 | insert into t1 values (1), (2), (3) |
| 5 | 0.000198 | select * from t1 |
+----------+----------+-------------------------------------+
That's wonderful! Now we know more about the single tasks this query was performing. But it gets even better. Look at this:
SHOW PROFILE FOR QUERY 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000035 |
| After create | 0.031432 |
| query end | 0.000221 |
| freeing items | 0.000005 |
| logging slow query | 0.000006 |
| cleaning up | 0.000002 |
+----------------------+----------+
SHOW PROFILE CPU FOR QUERY 3;If you are debugging code, you may like the information provided by the following option:
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000000 | 0.000000 |
| creating table | 0.000035 | 0.000000 | 0.000000 |
| After create | 0.031432 | 0.001000 | 0.000000 |
| query end | 0.000221 | 0.000000 | 0.000000 |
| freeing items | 0.000005 | 0.000000 | 0.000000 |
| logging slow query | 0.000006 | 0.000000 | 0.000000 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
SHOW PROFILE source FOR QUERY 3;It tells you how much time was spent, and which lines of code you should look at if you want to improve it!
+----------------------+----------+-----------------------+--------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+----------------------+----------+-----------------------+--------------+-------------+
| checking permissions | 0.000040 | check_access | sql_parse.cc | 5256 |
| creating table | 0.000035 | mysql_create_table | sql_table.cc | 1737 |
| After create | 0.031432 | mysql_create_table | sql_table.cc | 1768 |
| query end | 0.000221 | mysql_execute_command | sql_parse.cc | 5085 |
| freeing items | 0.000005 | mysql_parse | sql_parse.cc | 5973 |
| logging slow query | 0.000006 | log_slow_statement | sql_parse.cc | 2178 |
| cleaning up | 0.000002 | dispatch_command | sql_parse.cc | 2143 |
+----------------------+----------+-----------------------+--------------+-------------+
connect;Now it's your turn to experiment and share your experiences!
set profiling=1;
use information_schema;
select count(*) from columns where table_schema='mysql';
+----------+
| count(*) |
+----------+
| 147 |
+----------+
show profiles;
+----------+----------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+----------+---------------------------------------------------------+
| 0 | 0.000194 | set profiling=1 |
| 1 | 0.000006 | SELECT DATABASE() |
| 2 | 0.005928 | select count(*) from columns where table_schema='mysql' |
+----------+----------+---------------------------------------------------------+
show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| Opening tables | 0.000051 |
| System lock | 0.000281 |
| Table lock | 0.000002 |
| init | 0.000006 |
| optimizing | 0.000020 |
| statistics | 0.000009 |
| preparing | 0.000012 |
| executing | 0.000011 |
| checking permissions | 0.000039 |
| Opening tables | 0.000136 |
| checking permissions | 0.000010 |
| Opening tables | 0.000185 |
| checking permissions | 0.000008 |
| Opening tables | 0.000478 |
| checking permissions | 0.000007 |
| Opening tables | 0.000107 |
| checking permissions | 0.000006 |
| Opening tables | 0.000096 |
| checking permissions | 0.000006 |
| Opening tables | 0.000052 |
| checking permissions | 0.000006 |
| Opening tables | 0.000051 |
| checking permissions | 0.000007 |
| Opening tables | 0.000145 |
| checking permissions | 0.000006 |
| Opening tables | 0.000445 |
| checking permissions | 0.000007 |
| Opening tables | 0.000395 |
| checking permissions | 0.000007 |
| Opening tables | 0.000189 |
| checking permissions | 0.000007 |
| Opening tables | 0.000195 |
| checking permissions | 0.000005 |
| Opening tables | 0.000053 |
| checking permissions | 0.000006 |
| Opening tables | 0.000055 |
| checking permissions | 0.000005 |
| Opening tables | 0.000058 |
| checking permissions | 0.000005 |
| Opening tables | 0.000064 |
| checking permissions | 0.000005 |
| Opening tables | 0.000123 |
| checking permissions | 0.000007 |
| Sending data | 0.000898 |
| end | 0.001215 |
| query end | 0.000007 |
| freeing items | 0.000003 |
| closing tables | 0.000010 |
| removing tmp table | 0.000002 |
| closing tables | 0.000420 |
| logging slow query | 0.000003 |
| cleaning up | 0.000002 |
+----------------------+----------+
52 rows in set (0.00 sec)
| After a long period of preparation, my pet project is out! The Quality Contribution Program is here! MySQL wants to reward its most active users who are contributing to the improvement of its products. This is not a lottery, where you submit some contributions, and if you are chosen you get the prize. In this program, you start contributing, and when you reach a given level, you (and everybody else in the same position) will get a free subscription to MySQL Enterprise. The project rules say how much you should contribute to get a Basic, Silver, Gold, or even Platinum subscription. |
export PATH=$HOME/usr/local/bin:$PATH./configure --prefix=$HOME/usr/local && make && make check && make install$ bk clone username@bk_repository/mysql-5.1 51
$ cd 51
$ BUILD/compile-dist
$ make test
$ make dist
(There is an online sample chapter, The realities of software testing containing the above definition)
- The software doesn’t do something that the product specification says it should do.
- The software does something that the product specification says it shouldn’t do.
- The software does something that the product specification doesn’t mention.
- The software doesn’t do something that the product specification doesn’t mention but should.
- The software is difficult to understand, hard to use, slow, or—in the software tester’s eyes—will be viewed by the end user as just plain not right.
What is this? It is a subtraction between two unsigned values. There is an overflow in the result, and then the result of 1-2 becomes the highest BIGINT value minus one.
SET SQL_MODE='';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+------------+-------------------------------------------+
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
+------------+-------------------------------------------+
| | 18446744073709551615 |
+------------+-------------------------------------------+
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';Fine. Now we know that we can take control of subtractions. But there is something more to know. The SQL MODE sticks to each procedure, function, or trigger, meaning that each routine is executed using the SQL mode that was active at creation time. This could lead to surprising results.
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+-------------------------------------------+
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION | -1 |
+-------------------------+-------------------------------------------+
set sql_mode='';Look here. We set the SQL_MODE to NO_UNSIGNED_SUBTRACTION, because we want to avoid that unpleasant effect, but the subtraction function was created with a different SQL_MODE.
drop function if exists subtraction;
create function subtraction(x int unsigned, y int unsigned)
returns int
deterministic
return x - y;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
select @@sql_mode, subtraction(1,2), cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+------------------+-------------------------------------------+
| @@sql_mode | subtraction(1,2) | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION | 2147483647 | -1 |
+-------------------------+------------------+-------------------------------------------+
SELECT
routine_name,. sql_mode
FROM
information_schema.routines
WHERE routine_schema='test'
AND routine_name='subtraction'
AND routine_type='function';
+--------------+----------+
| ROUTINE_NAME | SQL_MODE |
+--------------+----------+
| subtraction | |
+--------------+----------+
The first function returns a safe list of column names.
delimiter //
drop function if exists get_safe_column_list //
create function get_safe_column_list
(
p_db_name varchar(50),
p_table_name varchar(50),
p_null_text varchar(20)
)
returns varchar(10000)
reads sql data
begin
if ( @@group_concat_max_len < 10000 ) then
set group_concat_max_len = 10000;
end if;
return (
select
group_concat( if(is_nullable = 'no', column_name,
concat("COALESCE(",column_name, ", '", p_null_text,"')") ))
from
information_schema.columns
where
table_schema= p_db_name
and
table_name = p_table_name
);
end //
drop function if exists get_primary_key //The second routine returns a table primary key, as a list of column.
create function get_primary_key (
p_db_name varchar(50),
p_table_name varchar(50)
)
returns varchar(10000)
begin
if ( @@group_concat_max_len < 10000 ) then
set group_concat_max_len = 10000;
end if;
return (
select
group_concat(column_name order by ORDINAL_POSITION)
from information_schema.KEY_COLUMN_USAGE
where
table_schema=p_db_name
and table_name = p_table_name
and constraint_name = 'PRIMARY' );
end //
drop procedure if exists table_crc //The third procedure returns the global CRC of a given table.
create procedure table_crc (
IN p_db_name varchar(50),
IN p_table_name varchar(50),
OUT p_table_crc varchar(100)
)
reads sql data
main_table_crc:
begin
declare pk varchar(1000);
declare column_list varchar(10000);
set pk = get_primary_key(p_db_name, p_table_name);
set column_list = get_safe_column_list(p_db_name, p_table_name, 'NULL');
if (column_list is null) then
set p_table_crc = null;
leave main_table_crc;
end if;
set @q = concat(
'INSERT INTO bh SELECT @tcnt := @tcnt + 1, ',
'@tcrc := SHA1(CONCAT(@tcrc, CONCAT_WS("#",', column_list, ')))',
' FROM ', p_db_name, '.', p_table_name,
if (pk is null, '', concat(' ORDER BY ', pk))
);
drop table if exists bh;
create table bh (counter int, tcrc varchar(50)) engine = blackhole;
set @tcrc= '';
set @tcnt= 0;
prepare q from @q;
execute q;
set p_table_crc = concat(@tcnt,'-',@tcrc);
deallocate prepare q;
end //
drop procedure if exists table_compare //The final routine puts all pieces together, returning a boolean value telling if the two tables have the same contents.
create procedure table_compare (
IN p_db_name1 varchar(50),
IN p_table_name1 varchar(50),
IN p_db_name2 varchar(50),
IN p_table_name2 varchar(50),
OUT same_contents boolean
)
begin
declare crc1 varchar(100);
declare crc2 varchar(100);
call table_crc(p_db_name1,p_table_name1, crc1);
call table_crc(p_db_name2,p_table_name2, crc2);
select concat(p_db_name1, '.', p_table_name1) as table_name, crc1 as crc
union
select concat(p_db_name2, '.', p_table_name2) as table_name, crc2 as crc ;
set same_contents = (crc1 = crc2);
select crc1=crc2 as 'same contents';
end //
delimiter ;
mysql> use world;
Database changed
mysql> create table City2 like City;
Query OK, 0 rows affected (0.03 sec)
mysql> alter table City2 ENGINE = InnoDB;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into City2 select * from City order by District, population;
Query OK, 4079 rows affected (0.33 sec)
Records: 4079 Duplicates: 0 Warnings: 0
mysql> call table_crc(schema(), 'City', @city_crc);
Query OK, 0 rows affected, 1 warning (0.16 sec)
mysql> select @city_crc;
+-----------------------------------------------+
| @city_crc |
+-----------------------------------------------+
| 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> call table_crc(schema(), 'City2', @city2_crc);
Query OK, 0 rows affected (0.13 sec)
mysql> select @city2_crc;
+-----------------------------------------------+
| @city2_crc |
+-----------------------------------------------+
| 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
+-----------------------------------------------+
1 row in set (0.01 sec)
Le'ts make it fail, to see if it is true:
mysql> call table_compare(schema(), 'City',schema(), 'City2', @same);
+-------------+-----------------------------------------------+
| table_name | crc |
+-------------+-----------------------------------------------+
| world.City | 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
| world.City2 | 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
+-------------+-----------------------------------------------+
2 rows in set (0.24 sec)
+---------------+
| same contents |
+---------------+
| 1 |
+---------------+
1 row in set (0.24 sec)
mysql> update City2 set population = population + 1 where id = 1;Now the two tables will have at least one difference:
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> call table_compare(schema(), 'City',schema(), 'City2', @same);And our routine finds it. QED.
+-------------+-----------------------------------------------+
| table_name | crc |
+-------------+-----------------------------------------------+
| world.City | 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
| world.City2 | 4079-b3b613b20570024be727ef0454053a96cfc36633 |
+-------------+-----------------------------------------------+
2 rows in set (0.23 sec)
+---------------+
| same contents |
+---------------+
| 0 |
+---------------+
1 row in set (0.23 sec)
