MySQL, the strange case of a timestamp field

Posted on January 9, 2013 Comments

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t;
+----+------+
| id | val  |
+----+------+
|  1 | foo  |
|  2 | var  |
+----+------+
2 ROWS IN SET (0.00 sec)
 
 
-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA
mysql> ALTER TABLE t ADD ts_field TIMESTAMP;
Query OK, 2 ROWS affected (0.35 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t (val) VALUES ("foo 2") ,("var 2");
Query OK, 2 ROWS affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
-- THERE IS THE MAGIC:
mysql> SELECT * FROM t;
+----+-------+---------------------+
| id | val   | ts_field            |
+----+-------+---------------------+
|  1 | foo   | 0000-00-00 00:00:00 |
|  2 | var   | 0000-00-00 00:00:00 |
|  3 | foo 2 | 2013-01-09 23:20:01 |    <---
|  4 | var 2 | 2013-01-09 23:20:01 |    <---
+----+-------+---------------------+
4 ROWS IN SET (0.00 sec)

What happened? I dont know.

The new table structure is:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(50) DEFAULT NULL,
  `ts_field` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

It only happens if the table doesn’t have another timestamp field yet.

Now, this is useful? maybe. This is a bug? maybe.

Update: This is a feature documented in https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html, sorry about my lapsus-post.

MySQL get disk usage of all databases

Posted on November 30, 2012 Comments

I was tired to get manually disk space used for all MySQL databases, I just created a stored procedure to get an overview of the database sizes in our MySQL server.

MySQL don’t have a command that allows us an overall summary of the databases, something like SHOW TABLE STATUS for databases. The “SHOW DATABASES” command lists only the current databases without any other information like how many space are using or how many tables there are, etc.

The procedure that I wrote this based on INFORMATION_SCHEMA database, which contains rich information of all existing databases.

I suggest, in a previous post, place all these procedures in a database called tools, but you can still choose different location.

So when call the procedure will have a global view of information that will be useful for maintenance tasks.

mysql> call tools.sp_overview();
+------------------------------+---------+--------+----------+---------+
| Database                     | Charset | Tables | Routines | Size Mb |
+------------------------------+---------+--------+----------+---------+
| database1                    | utf8    |     43 |       28 |     7.0 |
| database2                    | latin1  |     43 |       28 |   205.0 |
| database3                    | utf8    |    116 |        0 |   126.2 |
| database4                    | utf8    |     99 |        0 |     0.3 |
| database5                    | utf8    |    165 |        0 |    77.4 |
| database6                    | utf8    |    121 |        2 |   719.4 |
| database7                    | utf8    |    122 |        0 |    91.3 |
| database8                    | utf8    |    116 |        0 |    89.7 |
| database9                    | utf8    |    124 |        0 |     4.5 |
| database10                   | utf8    |    113 |        0 |   147.7 |
| database11                   | latin1  |    119 |        3 |   436.4 |
| database12                   | latin1  |    122 |        0 |   439.1 |
| database13                   | latin1  |    122 |        4 |   452.7 |
| database14                   | utf8    |    115 |        0 |   273.0 |
| database15                   | utf8    |    122 |        0 |   265.5 |
| database16                   | utf8    |      0 |        0 |    NULL |
| database17                   | latin1  |     26 |        0 |     0.9 |
| database18                   | latin1  |      9 |        0 |     0.0 |
| database19                   | latin1  |     22 |        0 |     0.3 |
+------------------------------+---------+--------+----------+---------+
19 rows in set (3.01 sec)

The first time execution maybe you will get results in a few seconds.

There is the source code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS tools.sp_overview$$
CREATE PROCEDURE tools.sp_overview()
BEGIN
 
    SELECT s.SCHEMA_NAME AS `Database`, s.DEFAULT_CHARACTER_SET_NAME AS `Charset`,
        COUNT(t.TABLE_NAME) AS `Tables`,
 
        (SELECT COUNT(*) FROM information_schema.ROUTINES AS r
            WHERE r.routine_schema = s.SCHEMA_NAME) AS `Routines`,
 
         round(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576 ,1) AS `Size Mb`
 
        FROM information_schema.SCHEMATA AS s
            LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema
        WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema')
 
    GROUP BY s.SCHEMA_NAME;
END$$
DELIMITER ;

Converting MS Access to MySQL with relationships

Posted on March 16, 2012 Comments

I have used tools like MySQL Migration Toolkit (*) and Bullzip’s Access To MySQL utility, both do a excellent job but without relationships. We can spend a lot of hours to identifying and creating relationships until now:

I have write a VBA script to identify MS-Access relationships and create MySQL code with SQL-CREATE sentences, it will be useful after migration process using any free tool:

Option Explicit
'Put this function in new/existing MS-Access module.
Public Sub printRelations()
    Dim sql, fk As String
    Dim i, j As Integer
    For i = 0 To CurrentDb.Relations.Count - 1
        sql = "ALTER TABLE `" & CurrentDb.Relations(i).ForeignTable & _
            "` ADD CONSTRAINT `" & CurrentDb.Relations(i).Name & "` FOREIGN KEY ("
        fk = "("
        For j = 0 To CurrentDb.Relations(i).Fields.Count - 1
            sql = sql & "`" & CurrentDb.Relations(i).Fields(j).ForeignName & "` ,"
            fk = fk & "`" & CurrentDb.Relations(i).Fields(j).Name & "` ,"
        Next j
 
        sql = Left(sql, Len(sql) - 1)
        fk = Left(fk, Len(fk) - 1)
        fk = fk & ")"
        sql = sql & ") REFERENCES `" & CurrentDb.Relations(i).Table & "`" & fk & ";"
 
        Debug.Print sql
    Next i
End Sub

To run the adobe code go to intermediate windows (Ctrl+G) and execute: printRelations and copy the generated SQL code.

Enjoy!

(*)MySQL Migration Toolkit is discontinued but is still available from mirrors like:

http://mirrors.dotsrc.org/mysql/Downloads/MySQLGUITools/

MySQL must improve error messages

Posted on March 12, 2012 Comments

I just finished a database modification, a new foreign key creation shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
	id INTEGER NOT NULL PRIMARY KEY,
	bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
	id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
	ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens, but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would not be awake until 2 am.

I’m using MySQL 5.5.21 community edition.

if I had noticed that wrong field name

Twitter bug found!

Posted on March 2, 2012 Comments

Some days ago while I’m looking for what are saying about a mysql.com server down I found a twitter bug:

Is not a big deal, to repeat this bug you must follow these steps:

1. Find any term, in this case “mysql.com” then in results looking for a word that have the search term as a part of them (ex dev.mysql.com) and select the a part or entire word:

2. Press Ctrl + C, some HTML codes appear from nowhere:

3. Do it again and again, you will see an strange twit like this:

I’m using Firefox 8.0 under Ubuntu, but you will get the same result using Chrome and (maybe) other web clients… you can try with other searches like twitter.com and select pic.twitter.com results.

UPDATE:

@jeremycole says: “Your URL-selection Twitter bug was fixed and shipped yesterday afternoon! Thanks!” on Mar 14 2012

Alias shortcuts to MySQL CLI

Posted on March 1, 2012 Comments

Do you get write laziness in the command line everything what you need to connect to a MySQL server every time?

It may take less than minute, but sometimes one minute is vital (especially if we’re near the end of the world):

ivancp@ubuntu$ mysql -u root -p -h mysqlhost database

When we are hurry, these commands often fail several times per minute.

The solution: we can create shortcuts with bash alias commands in file ~/.bashrc :

# File ~ /. Bashrc
 
# Command "my" to connect to a local server
alias my='mysql -u root -p'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root -h 192.168.1.56 -p'

Next time if you want to access the local server just type the command my [database name] , there only ask for database password. You can use any command aliases, I prefer “my” and “my2″ they are short and useful.

But if you have several servers comes another problem, how to know in which server I’m?

Open a mysql-cli can be super fast with alias shortcuts, but all terminals have the same default prompt: mysql> To avoid disasters (ex. run DROP in wrong place) you can change mysql-cli prompt with option --prompt , then finally our .bashrc look like this:

#improved ~/.bashrc 
 
# Command "my" to connect to a local server
alias my='mysql -u root --password=secret --prompt="local> "'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root --password=secret  -h 192.168.1.56 --prompt="server 1> "'

Note I added --password parameter, it can be dangerous, use it under your own risk.

Enjoy!

Custom auto increment values

Posted on February 28, 2012 Comments

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

To make it possible we have an option based on past article MySQL Sequences:

Create the table to store the current sequence values:

CREATE TABLE _sequence
(
    seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
    seq_group VARCHAR(10) NOT NULL,
    seq_val INT UNSIGNED NOT NULL
);

Create a function to get and increment the current value:

delimiter //
DROP FUNCTION IF EXISTS getNextCustomSeq//
CREATE FUNCTION getNextCustomSeq
(
    sSeqName VARCHAR(50),
    sSeqGroup VARCHAR(10)
) RETURNS VARCHAR(20)
BEGIN
    DECLARE nLast_val INT; 
 
    SET nLast_val =  (SELECT seq_val
                          FROM _sequence
                          WHERE seq_name = sSeqName
                                AND seq_group = sSeqGroup);
    IF nLast_val IS NULL THEN
        SET nLast_val = 1;
        INSERT INTO _sequence (seq_name,seq_group,seq_val)
        VALUES (sSeqName,sSeqGroup,nLast_Val);
    ELSE
        SET nLast_val = nLast_val + 1;
        UPDATE _sequence SET seq_val = nLast_val
        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
    END IF; 
 
    SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
    RETURN @ret;
END// 
 
delimiter ;

Create a stored procedure to modify a current sequence value:

delimiter //
DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
              sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
BEGIN
    IF (SELECT COUNT(*) FROM _sequence  
            WHERE seq_name = sSeqName  
                AND seq_group = sSeqGroup) = 0 THEN
        INSERT INTO _sequence (seq_name,seq_group,seq_val)
        VALUES (sSeqName,sSeqGroup,nVal);
    ELSE
        UPDATE _sequence SET seq_val = nVal
        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
    END IF;
END//
delimiter ;

Testing the new functions:

Create a table:

CREATE TABLE custom_autonums
(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   seq_1 VARCHAR(20), -- custom sequence 1
   seq_2 VARCHAR(20), -- custom sequence 2
   UNIQUE(seq_1),
   UNIQUE(seq_2)
);

Create trigger:

delimiter //
DROP TRIGGER IF EXISTS custom_autonums_bi//
 
CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
FOR each ROW
BEGIN
   SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
   SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
END//
 
delimiter ;

Insert some values:

INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
SELECT * FROM custom_autonums;
+----+------------+------------+
| id | seq_1      | seq_2      |
+----+------------+------------+
|  4 | 001-000001 | DBA-000001 |
|  5 | 001-000002 | DBA-000002 |
|  6 | 001-000003 | DBA-000003 |
+----+------------+------------+
3 ROWS IN SET (0.00 sec)

Altering current values:

CALL sp_setCustomVal('seq_1','001',675);
 
INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
SELECT * FROM custom_autonums;
+----+------------+------------+
| id | seq_1      | seq_2      |
+----+------------+------------+
|  4 | 001-000001 | DBA-000001 |
|  5 | 001-000002 | DBA-000002 |
|  6 | 001-000003 | DBA-000003 |
|  7 | 001-000676 | DBA-000004 |
|  8 | 001-000677 | DBA-000005 |
|  9 | 001-000678 | DBA-000006 |
+----+------------+------------+
6 ROWS IN SET (0.00 sec)

Enjoy!

CMS based on phpBB posts

Posted on February 27, 2012 Comments

If you are using phpBB maybe you need to show a individual phpBB post on an external page (outside of our phpBB forum) but it can be frustrating because phpBB uses a key in each bbcode (bitfield), but when I explore the source code I found the solution to extract parsed HTML text from phpBB posts.

Also I try to use a phpBB CMS Mod but I leave it because is still in development and now I’m trying to make my own CMS from phpBB posts, I use some code from phpBB tutorials and now I have a very basic CMS using phpBB Posts.

This is an overview:

I use apache mod_rewrite to make search engine friendly URL’s. (like this post)
The phpBB posts are located in a hide moderated forum, it also can use as a section to allow phpBB user post articles.
The media content (images, attachments, etc) is stored in phpBB files section.
I make custom .tpl files to sections: frontpage, article listings, articles, etc.
Also I defined custom bbcodes to show: head sections (h1,h2,h3…), highlighted code, HTML tables, notes, comments, etc.

But, is still in development I need to finish the resources section and multi-language content, from now all content will be based on phpBB.

You can view the CMS running on Latindevelopers.com (spanish only) when I finish this custom phpBB CMS I will test the security, and maybe publish under GPL.

Install bmon as service

Posted on February 27, 2012 Comments

bmon is a useful network monitoring tool, the most useful feature is that bmon store historical data from las 60 seconds, minutes, hours and days, the “problem” is that bmon needs to be running all time.

If you have a network server with Linux as proxy, you will probably need to see the current bandwidth usage or see what happens 60 minutes ago.

We can use screen with a running bmon process to generate HTML result of current bandwidth usage, and we will see generated HTML pages from Apache.

Steps to install and configure

Pasos para instalar y ejecutar:

Install bmon and screen:
sudo apt-get install screen bmon
Create the directory: (you can change this option)/var/www/bmon-html

Create the file /etc/init/bmon.conf with follow content:

description "bmon bandwidth monitoring process"

start on (local-filesystems and net-device-up and runlevel [2345])
stop on runlevel [016]

exec screen -d -m -S bmon bmon -O html:path=/var/www/bmon-html

Start the new service:sudo service bmon start
Then the web page http://localhost/bmon-html/ will be availbale and updated constantly.

Enjoy!

A better SHOW TABLE STATUS

Posted on February 25, 2012 Comments

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       |
+----------------------------+--------+-------+---------+-----------------+
| actor                      | InnoDB | 200   | 0.03 Mb | utf8_general_ci |
| actor_info                 | [VIEW] | -     | -       | -               |
| address                    | InnoDB | 589   | 0.09 Mb | utf8_general_ci |
| category                   | InnoDB | 16    | 0.02 Mb | utf8_general_ci |
| city                       | InnoDB | 427   | 0.06 Mb | utf8_general_ci |
| country                    | InnoDB | 109   | 0.02 Mb | utf8_general_ci |
| customer                   | InnoDB | 541   | 0.12 Mb | utf8_general_ci |
| customer_list              | [VIEW] | -     | -       | -               |
| film                       | InnoDB | 1131  | 0.27 Mb | utf8_general_ci |
| film_actor                 | InnoDB | 5143  | 0.27 Mb | utf8_general_ci |
| film_category              | InnoDB | 316   | 0.08 Mb | utf8_general_ci |
| film_list                  | [VIEW] | -     | -       | -               |
| film_text                  | MyISAM | 1000  | 0.31 Mb | utf8_general_ci |
| inventory                  | InnoDB | 4673  | 0.36 Mb | utf8_general_ci |
| language                   | InnoDB | 6     | 0.02 Mb | utf8_general_ci |
| nicer_but_slower_film_list | [VIEW] | -     | -       | -               |
| payment                    | InnoDB | 15422 | 2.12 Mb | utf8_general_ci |
| rental                     | InnoDB | 15609 | 2.72 Mb | utf8_general_ci |
| sales_by_film_category     | [VIEW] | -     | -       | -               |
| sales_by_store             | [VIEW] | -     | -       | -               |
| staff                      | InnoDB | 1     | 0.09 Mb | utf8_general_ci |
| staff_list                 | [VIEW] | -     | -       | -               |
| store                      | InnoDB | 2     | 0.05 Mb | utf8_general_ci |
+----------------------------+--------+-------+---------+-----------------+
23 rows in set (0.04 sec)
 
+----------------------------+-----------+---------------------+
| Routine Name               | Type      | Comment             |
+----------------------------+-----------+---------------------+
| get_customer_balance       | FUNCTION  |                     |
| inventory_held_by_customer | FUNCTION  |                     |
| inventory_in_stock         | FUNCTION  |                     |
| film_in_stock              | PROCEDURE |                     |
| film_not_in_stock          | PROCEDURE |                     |
| rewards_report             | PROCEDURE |                     |
| customer_create_date       | TRIGGER   | On INSERT: customer |
| del_film                   | TRIGGER   | On DELETE: film     |
| ins_film                   | TRIGGER   | On INSERT: film     |
| payment_date               | TRIGGER   | On INSERT: payment  |
| rental_date                | TRIGGER   | On INSERT: rental   |
| upd_film                   | TRIGGER   | On UPDATE: film     |
+----------------------------+-----------+---------------------+
12 rows in set (0.04 sec)
 
Query OK, 0 rows affected (0.04 sec)

There is the procedure source code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
BEGIN 
-- Obtaining tables and views
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     ENGINE AS `Engine`,
     TABLE_ROWS AS `Rows`,
     CONCAT(
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
        , ' Mb')
       AS `Size`,
     TABLE_COLLATION AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     '[VIEW]' AS `Engine`,
     '-' AS `Rows`,
     '-' `Size`,
     '-' AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname 
          AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
    SELECT ROUTINE_NAME AS `Routine Name`, 
     ROUTINE_TYPE AS `Type`,
     '' AS `Comment`
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = dbname
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
    FROM information_schema.TRIGGERS
    WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;

To use in your place you must call as:

mysql> call tools.sp_status(database());

Note the stored procedure has created in tools database (you can use another db), the goal of this is to call that useful procedure from any database, and it receives the name of database as parameter because is not possible obtain the current database from inside of stored procedure.

I published that code on MySQL custom show table status entry at forge.mysql.com.

Any ideas to improving the source code to make it more useful?

Fatal Exception

Ivan Cachicatari's Blog @latindev

MySQL, the strange case of a timestamp field

MySQL get disk usage of all databases

Converting MS Access to MySQL with relationships

MySQL must improve error messages

Twitter bug found!

Alias shortcuts to MySQL CLI

Custom auto increment values

CMS based on phpBB posts

Install bmon as service

A better SHOW TABLE STATUS

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: