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 [email protected];
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON devdb.* to [email protected];
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 [email protected];

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/^.*= //"