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…

The post MySQL – Alter table-change datatype of column with foreign key first appeared on Change Is Inevitable.

As we know, MySQL workbench is excellent GUI tool for managing MySQL DB servers, creating ERDs (Data Modelling) and for sql development. But with this GUI tool, we are getting some command-line utilities too like mysqldiskusage, mysqlindexcheck, mysqlfailover, mysqldiff, mysqldbcompare etc., Here, I’m describing mysqldiskusage utility, which is not only displays mysql db usage but … Continue Reading …

The MySQL Connect Call for Papers opened last Monday. Thanks to all of you who already submitted conference sessions and BOFs.

The CFP is running until May 6; if you have not submitted session proposals yet, you have about 2 weeks left to do so.

Interested in sponsorship and exhibit opportunities? You will find more information here.

We look forward to hearing from you!

We have setup our database servers to log all slow queries > 2 seconds. This can be done by enabling below from your configuration file or via runtime using SET GLOBAL command.

Append to configuration (my.cnf) under [mysqld]

slow_query_log = on
long_query_time = 2

Using query

SET GLOBAL slow_query_log = on;
SET GLOBAL long_query_time = 2

With this current setup, we filtered all queries from our application running more than 2 seconds. This is a good way to identify slow queries and optimise them.

However, there are also queries from the MySQL Enterprise monitor that were also being logged. In this case, i found out a (2) graph metrics checking every minute for the total backup time (total_time) and total lock time (lock_time). It takes about 3-6 seconds with only about 70 records on table.

To …

The Debt of Dirty Data

--Hey, why can't our Insurance and Billing system just talk to our Admissions system?  We just get too many errors in patient names and numbers, and fixing all of this is becoming a nightmare.

--Oh, alright let's integrate them then.

Duplicate data, multiple databases, and the need to communicate new records and updated records, has some organizations in a frenzy.  The mantra "just integrate them" makes it seem like there's a technology that will make this happen like magic.

It's not just the technology, it's the data.

Even if you follow database normalization rules (arcane, mathematically-based rules for designing a database for the non-database types), that doesn't mean you have clean, tight data.  Even still, in all of the databases I've seen, over the years, I've still yet to see a perfectly designed database anyway.  Add to …

Experienced MySQL DBAs know that temp tables cause major problems for MySQL replication.  It turns out the converse is also true:  replication can cause major problems for temporary tables.

In a recent customer engagement we enabled Tungsten Replicator with a MySQL application that originally ran on a server that did not use replication.  QA promptly discovered reports that previously ran in 10 seconds were now running in as many minutes.  It turned out that the reports used temp tables to assemble data, and these were being written into the master binlog.  This created bloated binlogs and extremely slow reports.  We fixed the problem by enabling row replication (i.e., binlog-format=row in my.cnf).

A common DBA response to temp table problems is to try to eliminate them completely, as suggested in the excellent …

This is the second and final part of my notes from the MySQL conference. In this part I'll focus on the technical substance of talks I saw, and didn't see.

More than ever before I was a contributor rather than attendee at this conference. Looking back, this resulted in seeing less talks than I would have wanted to, since I was speaking or preparing to speak myself. Sometimes it was worse than speaking, for instance I spent half a day picking up pewter goblets from an egnravings shop... (congratulations to all the winners again :-) Luckily, I can make up for some of that by going back and browse their slides. This is especially important whenever 2 good talks are scheduled in the same slot, or in the same slot when I was to speak. So I have categorized topics here along various axes, but also along the "things I did see" versus "things I missed" axis.

My own talks

…

I have finally recovered from my trip to Santa Clara enough that I can scribble down some notes from this year's MySQL Conference. Writing a travel report is part of the deal where my employer covers the travel expense, so even if many people have written about the conference, I need to do it too. And judging from the many posts for instance from Pythian's direction, Nokia is perhaps not the only company with such a policy?

Baron's keynote

There has usually always been something that can be called a "soft keynote". Pirate Party founder Rick Falckvinge speaking at a database conference is a memorable example (I still keep in touch with him, having met him at the Hyatt Santa Clara). This year there was one less day, and therefore less keynotes. The soft keynote was therefore taken care of by Baron using some time out of Peter's opening keynote. Baron's talk was an ode to the conference itself, underscoring the meaning of the …

Last Friday the Dutch TV program Zembla aired part two of the "verzuimpolitie" series. The first part was mainly about how employers could access medical information about employees. There is a news article about the second part here (with google translate).

…

Two separate posts on gamedev.stackexchange.com drew my attention. It appears that some game programmers are getting it WRONG.

Superficially, it seems that in some games (think of scrolling games with enemies appearing of the edge of the screen), the enemies are all approaching at a constant rate.

But what REALLY happens, is that the viewport scrolls at a fixed rate, and each enemy starts at a fixed world-space position, and activates when the viewport reaches a particular place.

This is not a very important distinction for the player, but its a HUGE distinction for the programmer.

If the developer thinks it's good to have 1 million enemies, all of which are created at the beginning of the game, and then move each one of them, every single tick, to a slightly different position as the camera moves, then they are probably doing it wrong:

…