MySQL dynamic pivot table

I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:

1. Write the main query with rows: project identifier, task subject and …

MySQL, the strange case of a timestamp field

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:

What happened? I dont know.

The new table structure is:

It only …

MySQL get disk usage of all databases

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 …

Converting MS Access to MySQL with relationships

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 …

MySQL must improve error messages

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:

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 …

Twitter bug found!

Some days ago while I’m looking for what are saying about a 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 “” then in results looking for a word that have the search term as a part of them (ex and …

Alias shortcuts to MySQL CLI

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):

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

The solution: we can create shortcuts with bash alias …

Custom auto increment values

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 a function to get and increment the current value:

  • Create a stored procedure to modify …
  • CMS based on phpBB posts

    phpBBIf 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 …

    Install bmon as service

    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 …

    Go back to top