Microsoftism's on my MacBook

Seems Mac OS/X has not escaped the unnecessary annoyances of Windows. Below are details to unstall a product on my MacBook, following it crashing my machine a few times with the “blue screen on death” below.

Eye TV Lite is the software that comes with the Pinnacle TV line of products for the Apple Mac.

 

To completely uninstall the EyeTV Lite software and it associated files, please follow the steps below.

 

1.      Drag the EyeTV Lite application from the Application folder to the trash.

a.       Open Finder

b.      Select Applications

c.       Select EyeTV Lite and drab it to the trash

 

2.      Using Finder, move the following files to the trash.

 

Below is a list of the files that need to be removed and their function.  Below that is the location of the files. 

 

com.elgato.eyetv.plist – stores the preferences and there are 2 copies on the hard drive

EyeTV Helper – assits with USB/FireWire issues

com.elgato.eyetv.devices.plist – preference file related to the available devices

com.elgato.eyetv.world.plist – preference file related to the channel lists

Wakein – assists EyeTV Lite with waking/booting to record

EyeTVEPG.db – EPG data is stored in this file

EyeTV Archive – contains all of your recordings and scheduled programs

EyeTVClassicDontSeize.kext – All of the kernel extension files .kext are to prevent the Mac OS X from attempting to talk to TV devices that can appear as generic USB audio class or HID class devices before the actual firmware has been uploaded to them.

EyeTVAfaTechHidBlock.kext – same as above

            EyeTVEmpiaAudioBlock.kext – same as above

 

Drag these files from the location listed to the trash using Finder

 

/Users/(username)/Library/Preferences/com.elgato.eyetv.plist

 

/Library/Preferences/com.elgato.eyetv.plist

/Library/Preferences/com.elgato.eyetv.devices.plist

/Library/Preferences/com.elgato.eyetv.world.plist

 

/Library/Application Support/EyeTV/EyeTV Helper

/Library/Application Support/EyeTV/Wakein

/Library/Application Support/EyeTV/EyeTVEPG.db

 

/Users/(username)/Library/EyeTV Archive/

 

/System/Library/Extensions/EyeTVClassicDontSeize.kext

/System/Library/Extensions/EyeTVAfaTechHidBlock.kext

/System/Library/Extensions/EyeTVEmpiaAudioBlock.kext

 

NOTE 1:  If you erase the EyeTV preference files, then any activation key you may have will need to be re-entered.

 

NOTE 2:  The files with the extention .kext can only be erased with root/superuser access. Also, to erase EyeTV Helper, you first have to quit it by using the Process Viewer or Activity Monitor.

Reference

Optimal OS Partitions

What is the optimal OS partition layout for a database server?

I’ve seen so many different configurations for OS partitions of recent time, none to my satisfaction.
Historically, in Unix days 20 years agao, long before RAID and SAN’s all my experiences were for strongly defined partitions.
That is separate partitions for the OS /, /boot, /tmp, /usr, /var and then seperate partitions for effectively application and data with /home and /opt

Today what is optimal for an OS configuration on a database server.
I’m seeking the input and experiences of the community. I’m making the assumption of at least RAID 1 or better for all disks.

It’s obvious that the database partition must be separate, and given snapshot capabilities both the data and binary logs should be specified on the same partition for consistency.
It’s also obvious the /tmp filesystem should not be with the / file system. You never want anything stupid that is using the /tmp filesystem to affect your operational system.

I’ll make the following assumptions.

  • Is a production database server
  • You are not installing new software often, therefore /usr should remain relatively static.
  • You have correctly configured MySQL not to place an data in /var.

This leaves /boot, /usr and /var for the OS. Do these require separate partitions? I would like to see it but do people care. With the amount of disk space available does a large amount bypass the need?

Any comments would be appreciated.

Mac Printscreen

There are many things I’m coming to grip with on my new MacBook. One is missing buttons on the screen, like Page Up, Page Down and PrntSc.

Well I at least found out from Eric that I can get Print Screen with the following keyboard syntax.

Command-shift-4 then space then mouse click

Procedure privileges

I came across a problem on site yesterday. In moving the development environment to a new server and creating more appropriate permissions for users (they were using ALL on *.*) I found that the Java application would crash with a NullPointerException. The permissions were standard, and calling the Stored Procedure worked via the mysql prompt.

CREATE USER devuser@99.99.99.99;
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON devdb.* to devuser@99.99.99.99;
CALL sp_test()

You can spend a lot of time looking into problems, luckily this development configuration had taken my advice to enabled the General Query Log. (Something everybody should do to know your SQL).

In closer inspection the following command was being sent to the MySQL Server. SHOW CREATE PROCEDURE sp_test; Attempting to run this command via the mysql prompt works.

SHOW CREATE PROCEDURE sp_test;
+-----------+----------+------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+------------------+
| sp_test   |          | NULL             |
+-----------+----------+------------------+

On closer inspection, the Create Procedure content contains NULL, seems suspicious. Running this command as the ‘root’ MySQL user I get the content of the procedure.

RFTM sheds light that this command requires privileges to mysql.proc. Running the following was necessary to get operation.

 GRANT SELECT ON mysql.proc to devuser@99.99.99.99;

This requirement is rather stupid in my book. I’ve yet to determine why SHOW CREATE PROCEDURE is called, probably some requirement via Connector/J but this permission problem definitely needs fixing.

MySQL under Mac OS/X 10.5

Time to install MySQL on my new MacBook.

$ cd /opt
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.45-osx10.4-i686.tar.gz/from/http://mysql.mirrors.hoobly.com/
$ tar xvfz mysql-5.0.45-osx10.4-i686.tar.gz
$ cd mysql-5.0.45-ox10.4-i686
$ scripts/mysql_install_db
Installing MySQL system tables...
071129 22:10:48 [Warning] Setting lower_case_table_names=2 because file system for /opt/mysql-5.0.45-osx10.4-i686/data/ is case insensitive
OK
Filling help tables...
071129 22:10:48 [Warning] Setting lower_case_table_names=2 because file system for /opt/mysql-5.0.45-osx10.4-i686/data/ is case insensitive
OK

bin/mysqld_safe &
[1] 239
macbook:mysql-5.0.45-osx10.4-i686 rbradfor$ chown: /opt/mysql-5.0.45-osx10.4-i686/data/macbook.err: Operation not permitted
Starting mysqld daemon with databases from /opt/mysql-5.0.45-osx10.4-i686/data


$ bin/mysqladmin -uroot password 'sakila'
$ bin/mysql -uroot -psakila -e "SELECT VERSION()"
$ bin/mysqladmin -uroot -psakila shutdown


It was interesting that these normal steps under Linux provided two errors not normally seen.

1. Setting lower_case_table_names=2 because file system for /opt/mysql-5.0.45-osx10.4-i686/data/ is case insensitive

2. chown: /opt/mysql-5.0.45-osx10.4-i686/data/macbook.err: Operation not permitted

The first is interesting, is Mac like Windows in that sense? I’ll need to check that out.
The second occurs every time I start MySQL which is also intriguing, but for now doesn’t affect MySQL operation. It’s interesting that the permissions of the error file is the same as the user running it, so go figure.

iPhone bug

I discovered a bug on my iPhone. The issue was around Daylight saving. Thanks to Dock I was able to take screenshots which was much better then photos.

In Calendar when you select 1am on the scrollers, you get 1am as the start time. When you select 2am, the start time goes to 1am. When you select 3am, it goes to 3am as expected.



My Hacked iPhone

I installed Dock on my iPhone and it gave a very handy and wanted Screen Shot function. So now I can show my present iPhone screen. It looks like:

Apart from the obvious differences of different and more icons, I have by background behind icons, and I’ve turned off the text, so you need to know the icon means. Still, it’s wicked cool.

Installing Skype

A fresh install of my laptop with Ubuntu 7.10 after stuff just didn’t work causes pains sometimes of the installed programs. Skype being one of them.

Goto Skype for Linux and get the Ubuntu installer.

I needed some dependencies.

$ su -
$ apt-get install libqt4-gui libqt4-core
$ dpkg -i skype-debian_1.4.0.118-1_i386.deb
$ exit
$ skype

Voila, all my old preferences (from ~) work just nicely. I see it’s also a newer version.

My MacBook

I arrived home last night at midnight from San Francisco to a pile of mail and a number of packages. My shiny new MacBook had arrived. It was rather ridiculous that I wanted a HDD upgrade and I could no buy it in the store. They come with 160GB and have only one upgrade option to 250GB.

Nevertheless, I’m now trying to familiarize myself with Mac OS/X 10.5 (Leopard) which is rather foreign as I’ve never used a MacBook before.

So what was my motivations? After Hacking my iPhone I wanted to do more, and even try and write an app, so I needed a Mac OS. I actually wanted a MacBook when I first joined MySQL last year, but my request was not approved (“You have to be able to run Windows” was the response, and yes I told them it can run windows).

Combined with a my Q3 Bonus, I just had to have one. This has been a number of significant purchases in recent months, an iPhone and a Canon 40D. All I need now is a girlfriend and my bank balance will be in more serious trouble.

MySQL at Oracle Open World

Yes, it may seem rather strange to the readers but MySQL has a booth at this week’s Oracle Open World 2007 and I’m here in San Francisco Wednesday and Thursday. If your in SF come in and say hi. Check out the Official Press Release and MySQL listed Oracle Resources for this conference.

An interesting recent report Oracle Users Indicate Increase in Use of Open Source sheds some light of the significance of MySQL within the Oracle Community.

The Independent Oracle Users Group (IOUG) has released its second major research study focusing on open source adoption trends, “Open Source in the Enterprise: New Software Disrupts the Technology Stack.” Conducted for the IOUG by Unisphere Research and sponsored by MySQL AB, the study builds on a similar report issued in mid-2006 covering open source adoption trends in the enterprise stack. The findings point to ongoing interest in open source software solutions for operating systems, databases and middleware with most organizations reporting that they intend to increase their use of open source in general over the coming year. However, open source applications remain less widely adopted.

For more information read the full report here.

Using MySQL Table Checksum

The MySQL Table Checksum, part of the MySQL Toolkit (having to be renamed soon) is an invaluable community tool for use with MySQL. Most sites or installations of any volume will use MySQL Replication for one purpose or another, be it for read scalability, backups, testing, upgrading etc.

Why is it needed?
There are two primary compelling reasons. First, MySQL replication is an asynchronous process and there is no absolute guarantee that the Master Database and the Slave Database are the same (By definition that can be different). Second, MySQL does not provide any tools relating to checking, managing, reporting differences. Luckily the community has addressed this present lack of product feature in current versions of MySQL. Special thanks to Baron who has made this happen.

Should I be concerned?
Yes, you should. While some people will not like that I’ve made this alarming comment, a lot of organizations use MySQL Replication for backups. It would be unprofessional to be ignorant to make the assumption.

How to I get it?
Download MySQL Table Checksum. It is recommended that you download all components in the toolkit as there are many valuable tools then just this one.

MySQL Table Checksum will require Perl which is generally always installed with any Linux distribution by default (For Windows you need to do yourself). You will also require the Perl Modules DBI and DBD::mysql. Use CPAN for easy installation of these modules.

These are the steps I did (NOTE: this is a specific version, you should always check for newer versions)

wget http://internap.dl.sourceforge.net/sourceforge/mysqltoolkit/mysqltoolkit-1204.tar.gz
tar xvfz mysqltoolkit-1204.tar.gz
cd mysqltoolkit-1204/bin
./mysql-table-checksum

If everything is installed correctly, you should see.

Usage: mysql-table-checksum [OPTION]... HOST [HOST...]

Errors in command-line arguments:
  * No hosts specified.

mysql-table-checksum checksums MySQL tables efficiently on one or more HOSTs.
Each HOST is specified as a DSN and missing values are inherited from the first
HOST.  If you specify multiple HOSTs, the first is assumed to be the master.
For more details, please use the --help option, or try 'perldoc
mysql-table-checksum' for complete documentation.

How do I use it?
Start with the documentation, RTFM

perldoc mysql-table-checksum

There are a number of ways to run MySQL Table Checksum and different means of using arguments etc. This is what I do.

By Default:

./mysql-table-checksum u=root,p=sakila,h=localhost

DATABASE TABLE       CHUNK HOST      ENGINE      COUNT       CHECKSUM TIME WAIT STAT  LAG
xxx      table1          1 localhost InnoDB       NULL     1678710928    0    0 NULL NULL
xxx      table2          1 localhost InnoDB       NULL     3023415523    0    0 NULL NULL
xxx      table3          1 localhost InnoDB       NULL     1692517818    0    0 NULL NULL
xxx      table4          1 localhost InnoDB       NULL              0    0    0 NULL NULL
xxx      table5          1 localhost InnoDB       NULL     2295061143    0    0 NULL NULL
xxx      table6          1 localhost InnoDB       NULL     2238111875    0    0 NULL NULL
xxx      table7          1 localhost InnoDB       NULL      823770692    0    0 NULL NULL
xxx      table8          1 localhost InnoDB       NULL     2313561225    3    0 NULL NULL
xxx      table9          1 localhost InnoDB       NULL     3524358173    0    0 NULL NULL
xxx      table10         1 localhost InnoDB       NULL              0    0    0 NULL NULL

The output will provide a checksum via the MySQL CHECKSUM function. As this doesn’t provide a row count, the COUNT column is NULL.

A better method is to use the –replicate function. This provides the results into a Database Table (handy for lots of things) as well as getting table counts. This requires a pre-requisite table. For this example I’ve added it to the test database schema.

$ mysql -uroot -psakila test
mysql > CREATE TABLE checksum (
                db         char(64)     NOT NULL,
                tbl        char(64)     NOT NULL,
                chunk      int          NOT NULL,
                boundaries char(64)     NOT NULL,
                this_crc   char(40)     NOT NULL,
                this_cnt   int          NOT NULL,
                master_crc char(40)         NULL,
                master_cnt int              NULL,
                ts         timestamp    NOT NULL,
                PRIMARY KEY (db, tbl, chunk)
             );

My execution for this using this table

./mysql-table-checksum u=root,p=sakila,h=localhost --replicate=test.checksum

DATABASE TABLE    CHUNK HOST      ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
xxx      table1       1 localhost InnoDB       2236 cdd6689dbb23ff547540561c4815c717b3d01bf3    0 NULL NULL NULL
xxx      table2       1 localhost InnoDB       3157 f4d538a7a83168acb2cf3374a6edc4949809e723    0 NULL NULL NULL
xxx      table3       1 localhost InnoDB         99 daa0d8403feb200beac5db6456e92c86de6a9b84    0 NULL NULL NULL
xxx      table4       1 localhost InnoDB          0                                     NULL    0 NULL NULL NULL
xxx      table5       1 localhost InnoDB       1006 118b996e10f76153b786479b15f134c08cd21b62    0 NULL NULL NULL
xxx      table6       1 localhost InnoDB    2499397 6a60e07f45d5980bb5de3bd75e1b2ce1e5be25b8   29 NULL NULL NULL
xxx      table7       1 localhost InnoDB      11879 00929c2d6b9278f585b6b7346816bb7a0e483b9e    0 NULL NULL NULL
xxx      table8       1 localhost InnoDB        675 e8e17f9ed15986965ff678ca0c26cd5397ea272d    0 NULL NULL NULL
xxx      table9       1 localhost InnoDB    1730498 373e7fb3fec77b1d0edcfb6a5e28619e9c91d5e4   25 NULL NULL NULL
xxx      table10      1 localhost InnoDB    8557931 7913803bf65bbcf18e679255c657dba53045b88e   99 NULL NULL NULL

The screen output is great, I actually run the following command keeping the output. See later for more info,

./mysql-table-checksum u=root,p=sakila,h=localhost > checksum.mysql4.replicate.`date +%y%m%d.%H%M` &

The table contents are a little different.

mysql> select * from checksum limit 1G
*************************** 1. row ***************************
        db: xxx
       tbl: table20
     chunk: 1
boundaries: 1=1
  this_crc: f7c4d19f6342105f97a4b2770ee7b9094e57a6f2
  this_cnt: 2236
master_crc: f7c4d19f6342105f97a4b2770ee7b9094e57a6f2
master_cnt: 2236
        ts: 2007-11-08 15:37:16

What is most impressive is that in a Master/Slave environment these commands are all replicated, and a simple comparison of data in this table on each slave will confirm if your database is in sync. The docs also confirm, the structure of the tables are not compared so a slave may indeed have some changes.

Migration?
I’ve been able to use the checksum to test a MySQL 4 to MySQL 5 migration. I’ve been able to confirm via checksums as an initial verification that the data has indeed been loaded correctly.

The issue was using the log files for comparison between to instances is the TIME column value changed. As the format of the rows is spaced, it was not possible to easily cut as each schema name was a different length. A small inconvience for the benefit in the results.

I have found however that optimizing the schema in 5, e.g. INT for BIGINT and TIMESTAMP for DATETIME did cause the checksums to fail.

Conclusion
This is a quick introduction, this tool has a lot to offer and is only one in a whole toolkit.

Funny command line option for the day

I needed to start mysql without privileges after a database restore today, and while confirming the correct option which was –skip-grant-tables I came across an option which made me laugh.

$ mysqld --verbose --help
...
  --sporadic-binlog-dump-fail
                      Option used by mysql-test for debugging and testing of
                      replication.
...

And here is the Official Manual Entry

MySQL Conference Submissions have closed

If you didn’t get your proposal in for MySQL Conference 2008 , that’s too bad.

I often wondered from past conferences why submissions were needed so early, like 5 months before. Well, as being invited to be part of the MySQL Conference committee this year I now know why, and have a greater appreciation. With near 300 submissions, it takes time to review them, and this is just the first step in a number to get to a completed schedule for next years conference.

Also getting a sneak preview of what’s to come is really cool! I’m already excited.

Hacking my iPhone

I’ve finally taken the plunge after wanting to for some time. This is what I did, all steps are from using the iPhone directly, no computer required.

NOTE: I can’t take responsibility for anything that happens to your iPhone. As the warning for the installer program states, “This software comes with absolutely no warranty of any kind. If it should cause any harm to your iPhone or data, we shall not be held responsible.”. That said, there are instructions to Virginizing your iPhone to 1.0.2. So if you ever had to return the phone to Apple you would need to do this first.

Part 1. The Installer
NOTE: This only works if your Phone has firmware Version 1.1.1. If you have the iTunes button you have 1.1.1 (as at 01-Nov-2007). You can find our your Version by going Settings -> General -> About and look for the Version value.

1. Disable Keyboard Passcode Lock if enabled (Settings -> General -> Passcode Lock -> Off), Disable auto-lock (Settings -> General -> Auto-Lock -> Never), enable Wi-Fi (Settings -> Wi-Fi -> On) and get connected to a wireless network (Settings -> Wi-Fi -> Join Network)
2. Using Safari Browser, goto http://jailbreakme.com/.
3. Click on Install AppSnap link. The phone will appear to do nothing before it returns to the initial screen, with Slide to Unlock. (It took less then a minute for my iPhone to go back to Slide to unlock, other notes indicate up to 5 mins)
4. Slide to unlock. You will see a new icon called Installer. (Don’t click it yet)
5. Power off your phone, and power back on. (I’m uncertain how long this will take, as I did it slightly different, but don’t be alarmed if it takes at least 15 minutes this first time, as that what it took for me.

Note: At step 4 I selected the Installer button (it asked me to Update, which I did). This was bad idea, it went to Downloading package and never recovered after 5 minutes. I needed to do a reboot via holding down menu button. Click on the Installer button again, and it simply doesn’t start properly, going back to the main page. Turning off via power button freezes as well. After 5 minutes, tried holding down first the menu button, then the power button, no luck. Froze on the shutdown spinning icon for at least 15 mins.

Part 2. The Necessary Programs

You can now install the following programs when selecting Installer. Note: after each installation it will go back to the initial slide to unlock screen.
Note also the initial order, in particular the first 3 are important.

6. Installer -> System -> BSD Subsystem (Version 1.5, 6.6 M)
7. Installer -> Sources -> Community Sources (Version 3.0, 165 bytes) Update This happens automatically now.
8. Installer -> System -> OpenSSH (Version 4.6p1-1, 696k)
9. Installer -> System -> Term-vt100 (Version svn198, 81k)
10. Installer -> Package -> Summerboard (Version 3.0a11, 693k)

This then gave me access to SSH to my phone, SSH out to the big bad world, and ability to have different themes, changing the look and feel, see Summerboard themes for example.

Part 3. The fun applications.

I installed to start with the following application, you should choose what you like.

Installer -> Games -> Sudoku (Version 1.0-1, Size 24k)
Installer -> Utilties -> Erica’s Utilities (0.36, 42k)
Installer -> Utilities -> Erica’s iPhone Utils (0.06, 12k)
Installer -> Utilites -> Erica’s Ported Utils (0.06, 2.5M)
Installer -> Multimedia -> VoiceNotes (0.21, 60k)
Installer -> Productivity -> Crosswalk (1.01, 362k)

Practically everything is from www.modmyifone.com. Many references available online helped including here. Thanks James for the demo and points to success.

Some darn wicked stuff.

One of the cool things, being able to save Call History, SMS messages and Notes. Did via SSH with the following commands:

PATH=/var/root/bin:$PATH;export PATH
sqlite3 -line /private/var/root/Library/SMS/sms.db 'select address,text from message;'
sqlite3 -line /private/var/root/Library/Notes/notes.db 'select data from note_bodies;' | sed "s/^.*data = //"
sqlite3 -line /private/var/root/Library/CallHistory/call_history.db 'select "* " || address || " (" || ((duration/60)+1) || "  minutes)" from call;' | sed "s/^.*= //"

Ubuntu Tricks today

ctrl-alt-l – Lock Screen
ctrl-alt-d – Desktop

# Multi Cut-Paste History
apt-get install glipper

Add to System|Preferences|Sessions|Startup Programs

Thanks Yves

Perl Tricks

perl_modules.pl

#!/usr/bin/perl

use ExtUtils::Installed;
my $instmod = ExtUtils::Installed->new();
foreach my $module ($instmod->modules()) {
my $version = $instmod->version($module) || "???";
       print "$module -- $versionn";
}

remove_perl_module.pl

#!/usr/bin/perl -w
use ExtUtils::Packlist;
use ExtUtils::Installed;

$ARGV[0] or die "Usage: $0 Module::Namen";

my $mod = $ARGV[0];

my $inst = ExtUtils::Installed->new();

    foreach my $item (sort($inst->files($mod))) {
             print "removing $itemn";
             unlink $item;
          }

     my $packfile = $inst->packlist($mod)->packlist_file();
          print "removing $packfilen";
          unlink $packfile;

http://www.cpan.org/misc/cpan-faq.html

Ban the Backquote

I really don’t like the backquote character (`). This is primarily due to it’s use by MySQL in internal and community tools.
MySQL allows objects, .e.g table names, column names etc to contain both reserved words and spaces, providing they are appropriately surrounded by backquotes, so tools now simply enclose everything with backquotes. Really annoying.

Last week while on a client site I was told a funny story. The CEO of a company bought an iPhone however was unable to use it at work due to the WEP key for the Wireless access in his organization includes a backquote. I’ve confirmed it, the iPhone keyboard does not have a backquote key. So now, because he is the CEO, everybody will have to change their Wireless WEP key just because of the iPhone.

But it gets better. Around midnight last Saturday between Slate Plus (for Dinner & Pool) and going down to SOHO for more drinks, Farhan had to change a cron job. As his battery was running low, he was trying to find it, you guessed it, the backquote character on his OQO. Just lucky it’s Windoze and if you can find the character map application, you can insert the character.
Rob and Michelle were on hand, and with my new EOS 40D and 50mm 1.4f lens I was able to take a series of photos without flash and without alerting my unsuspecting subjects.

NY Users Group – Analyzing MySQL Status and your SQL

This month I continued my Performance Analysis talks at the Local NY MySQL Meetup. Previous discussions can be found here.

Our focus was a more in-depth look at gathering and reviewing MySQL Status and your applications SQL statements using MySQL Proxy. Even after preparing the slides over the weekend Jan added more functionality that was particularly interesting. So today while addressing a client issues I further extended this work to do even more funky monitoring.

Today’s monitoring.lua script does:

  • Logs to file, Date/Time, Query Time, Response Time, Rows Affected, Normalized SQL and Actual SQL for each query
  • Has histogram of tables used with read/write figures.
  • Has histogram of SQL with avg and max execution times.

You get the best of both worlds, you can see SQL access live, get statistics of this, and then be able to drill down to every instance of a given SQL statement for more information.

You can get a copy of my slides Here. You can get a full tar of my demo work here. This includes MySQL Proxy, My Bench for some benchmarks and the Sakila Sample Database.

Ubuntu 7.04 Fiesty

I upgraded my work laptop to Ubuntu 7.04 Fiesty. The process was not as smooth as I expected. First it complained about not enough space on /var, so I cleaned up sufficient space. It continued to complain about 3 times.

The install itself I was hoping would have been automated, so when starting at night I wake up, and it’s done, however there were a number of installation errors, and prompts to keep or override configuration files, which I had to do manually at least 10 times. This of course made my laptop unavailable for a number of hours.

Today I’ve found the first real problem. My Open Office has lost all it’s fonts that I’ve loaded on in the past. Arial for example is no longer available. Of course searching on the web for installing fonts gives you several links, do you think any of these worked?

I found in reviewing my backup that the directory /usr/share/fonts/truetype/msttcorefonts/ had been removed. Re-instating this gave me back my fonts.

LAST_INSERT_ID(expr) – The lesser known usage

I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.

Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …

Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation


If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

1.Create a table to hold the sequence counter and initialize it:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

2. Use the table to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 17.2.3.35, “mysql_insert_id()”.

Seems like a layman’s idea of Sequence support, but you are still restricted with the problems of a manual implementation. Transactions support, and if you use MyISAM, table level locking.

MySQL Camp II – Memorable Quotes Day 1

Better late then never, this week I finally have the chance to catch up on some overdue posts. At the first MySQL Camp I made a list of memorable Quotes, see Day 1, Day 2, Day 3. I didn’t get as much chance as last time, however here are some pearls of the recent MySQL Camp II.

“I walked in of the street for the free food. I’m here for the free education” – Adrian.

“I’m his boss, I’m here to make sure he’s really here, and not playing hooky.”

“Mashups, collating the worse bugs from multiple API’s all in one place” – OSCON badge

“Compiles 114,000 combinations of MySQL that we are interested in.” Sandro – Skoll Project — You mean to say there are are combinations your not interested in.

“Ok, people this is the second site were are going to crash today. You have heard you have been slash dotted, now you have been camped.”

Jay turning down the lights, to the whole auditorm, not just the stage. “That won’t work. My paper isn’t backlit.” — Andrew

Jeremy: “It’s a porn site.”
Sheeri: “technocation.com it’s not a porn site, I work for a porn site.”

Jeremy: “You don’t realize how many Google properties you use, google reader was down, google search was down, google maps was down.”
Sheeri: “I had to use Yahoo maps, I feel so dirty.”

Jay: “How many people are interested in a tour of the MySQL Source Code.”
Jeremy II: “It is a guided tour, isn’t it.”

“I can’t remember if was the cold, hot or luke warm”. — Bob, In the backup talk.

“How many environments have it. They all have the presumption of it.”

DateTime vs Timestamp

I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”

It’s a good MySQL introduction question, here are some general considerations for choosing one.

Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.

1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.

I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.

And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!

The woes of MySQL Community tools under Solaris

Yesterday I attempted to get a working MySQL environment to support the number of utilities we all use including mytop, innotop, mybench, mysqltoolkit. These products require a number of Perl Dependencies, and while that may be a rather trivial task under Linux and with the power of cpan, working on Solaris is a whole different story.

For the record, I’m working with Solaris 9 SPARC 64bit.

I won’t detail you with how hard it was to get to this point, except to say thanks to Jeremy, Baron and Frank so far. Here is where I’m at.

You need a number of pre-requisites, most from sunfreeware.com

Pre-Requisites

$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/libiconv-1.11-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/gcc-3.4.6-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/make-3.81-sol9-sparc-local.gz
$ gunzip libiconv-1.11-sol9-sparc-local.gz gcc-3.4.6-sol9-sparc-local.gz make-3.81-sol9-sparc-local.gz
$ pkgadd -d libiconv-1.11-sol9-sparc-local
$ pkgadd -d gcc-3.4.6-sol9-sparc-local
$ pkgadd -d make-3.81-sol9-sparc-local
$ wget http://search.cpan.org/CPAN/authors/id/A/AB/ABURLISON/Solaris-PerlGcc-1.3.tar.gz
$ gunzip Solaris-PerlGcc-1.3.tar.gz
$ tar xvf Solaris-PerlGcc-1.3.tar
$ cd Solaris-PerlGcc-1.3
$ perl Makefile.PL
$ make install
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSCHWERN/ExtUtils-MakeMaker-6.36.tar.gz
$ gunzip ExtUtils-MakeMaker-6.36.tar.gz
$ tar xvf ExtUtils-MakeMaker-6.36.tar
$ cd ExtUtils-MakeMaker-6.36
$ perl Makefile.PL
$ make install

Back to installing

$ PATH=/usr/local/bin:$PATH;export PATH
$ PATH=/usr/perl5/5.6.1/bin/:$PATH;export PATH

$ perlgcc Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadKey
netcontrol1:/tmp/TermReadKey-2.30# make install
make: *** No rule to make target `/usr/perl5/5.6.1/lib/sun4-solaris-64intConfig.pm', needed by `Makefile'.  Stop.

This is where the trail ends as my friend google has not helped me past this point, so I’m turning the community. If anybody can assist please let me know.

A MySQL Story

The Taso Dump

I’ve gotto go, my time has come. (maintenance window needed)
Could not get a lock. (table lock)
I needed a blocker to ensure a single thread. (–single-transaction)

Finally, got a lock. (–lock-tables)
Need to ensure no transactions lost.
Dump, Dump, Dump. (mysqldump)

I’m having flow problems.
Was the buffer to small? (key_buffer_size)
Was it the query cache? (query_cache)
My Dirty Pages may be too high? (innodb_max_dirty_pages_pct)
Or was it just Too Many Connections? (max_connections)
But it was just waiting on the flush (flush tables with read lock)

Time passes, Time Passes. No output yet.
Is it network saturation?
Is it IO bound?
Do I need a better flushing method (innodb_flush_method)
No, it was just the lag? (Seconds_behind_master)

Dump is complete (unlock tables)
Now it’s time to Purge (purge logs)

If there is an attempt to restore.
The backup would be empty.
Blackhole would be found as the cause.
In the end, all transactions lost!

If you weren’t at Friday dinner after MySQL Camp II you missed it.
It’s not meant to be MySQL grammar correct, it’s just some random words we were throwing around.

MySQL Camp II – Post Dinner

MySQL Camp II is complete. A small group of about 18 had post dinner at Tiny Thai in New York City. Some elected to drive from Brooklyn, they arrived at least 30 minutes after those of us that the subway.

I have a lot of notes to write, if ever the time permits. For now, the following few that joined for drinks are below. I know other people took photos of the camp, for a change I actually took none. If you want to add a link in comments of photos from the camp that would be great.

MySQL Camp II Post Dinner Drinks

Other sizes here