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:
14528056775dd802a598f6e_000006

What happened? I dont know.

The new table structure is:
14528056775dd802a598f6e_000007

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:
14528056775dd802a598f6e_000011

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 …

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

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,
001-000034
001-000035
001-000036

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

  • Create the table to store the current sequence values:
    14528056775dd802a598f6e_000015

  • Create a function to get and increment the current value:
    14528056775dd802a598f6e_000016

  • Create a stored procedure to modify …
  • A better SHOW TABLE STATUS

    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:

    Creating sequences in MySQL

    Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.

    As we know, MySQL have auto-increment fields, that are very useful too, but we miss that feature when we are in a PostgreSQL …

    Go back to top