Tag Archives: workbench

MySQL Utilities meets the world’s ugliest table

In case you missed it, MySQL Utilities 1.3.0 (alpha) was released last week.  MySQL Utilities is a component of MySQL Workbench, but it’s been broken out into a separate download allowing command-line users access unencumbered by a pretty GUI interface.  Plus, it has some new features – most importantly to me, a utility (mysqlfrm) which can read .FRM files and produce CREATE TABLE statements as a result.  This will be very useful for recovery processes, in conjunction with InnoDB transportable tablespaces in situations where the InnoDB system tablespace is corrupted, and you don’t know the exact table definition.

When we first started testing the FRM reader, it didn’t bootstrap mysqld to decipher .FRM file contents – Chuck Bell spent hours staring into the abyss that is FRM file formats and trying to shed light in the many dark corners that exist.  We started with simple tests, like:  can it read all the .FRM files in the data/mysql directory?  Then I hacked a quick program that created tables with many different data types, modifiers and variants.  But these tests were all dwarfed by the contribution of my colleague and bug-finder extraordinaire, Shane Bester, who gave us what may well be the world’s ugliest table.  Behold:

Continue reading MySQL Utilities meets the world’s ugliest table

Using MySQL for Excel

I recently had a need to import a bunch of data into MySQL, and for reasons I won’t get into here, LOAD DATA INFILE wasn’t working for me.  I was in a hurry to get the data into MySQL so that I could filter it, and didn’t have time to work through my LOAD DATA INFILE issues.  Looking around for another way to quickly get my tab-delimited data into MySQL quickly, I decided to use the MySQL for Excel plugin.  If you haven’t seen this yet, and you have Excel, it might be worth your while. Continue reading Using MySQL for Excel

Workbench called me a dummy!

Seriously, it did.  Sorta.

I use Workbench for my daily work, and it’s a great tool.  If you haven’t tried the 5.2 release yet, you should.  While performing some maintenance, I happened to issue a DELETE statement against a table which had no indexes (it was 10 rows), and Workbench complained:

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

It turns out this is a new feature in 5.2.26 (and is still there in 5.2.27) – Workbench now uses the equivalent of –safe-updates mode for the mysql command-line client (also known as the –i-am-a-dummy option – seriously).  This wasn’t exactly convenient for me, especially since the DELETE was part of a larger script which I then had to revise and step through manually after it failed, but there’s an easy way to change this behavior.  If you’re like me, you might consider disabling this:

  • Go to Edit -> Preferences
  • Select the SQL Editor tab
  • Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”

Despite the text, the –safe-updates mode affects more than UPDATE and DELETE statements without WHERE clauses – it requires such statements to explicitly use indexes.

I’m changing this behavior on my installation before I run into other problems.

I’m no dummy.  🙂