Getting the Java (JRE) plugin working in FireFox under Linux.
cd /opt/firefox-1.5/plugins rm libjavaplugin_oji.so ln -s /opt/jdk1.5.0_06/jre/plugin/i386/ns7/libjavaplugin_oji.so .
by ronald
Getting the Java (JRE) plugin working in FireFox under Linux.
cd /opt/firefox-1.5/plugins rm libjavaplugin_oji.so ln -s /opt/jdk1.5.0_06/jre/plugin/i386/ns7/libjavaplugin_oji.so .
by ronald
I’ve had a bit of a windfall of books in the past 2 weeks. I purchased 2 books for the flights to/from the US, picked up an Amazon order on some Database Reference Material for some writings I’m doing, and quite a bounty at the MySQL Users Conference.
by ronald
I’ve decide to move from CentOS to Ubuntu as the Linux Distro on my laptop, so it was time to do a full backup to a new USB external drive.
I got a new Maxtor 300G 7200rpm ATA133 (16MB cache) HDD from Umart for AUD$149. I already had an unused USB 2.0 3.5″ external casing.
My first issue was, I couldn’t detect the new drive from my laptop. Switching to another desktop running CentOS, I got the following errors.
$ tail -f /var/log/messages May 6 12:36:53 marvin kernel: usb 1-1: new full speed USB device using address 4 May 6 12:36:57 marvin kernel: SCSI subsystem initialized May 6 12:36:57 marvin kernel: Initializing USB Mass Storage driver... May 6 12:36:57 marvin kernel: scsi0 : SCSI emulation for USB Mass Storage devices May 6 12:36:57 marvin kernel: Vendor: Genesys Model: USB to IDE Disk Rev: 0033 May 6 12:36:57 marvin kernel: Type: Direct-Access ANSI SCSI revision: 02 May 6 12:36:57 marvin kernel: usbcore: registered new driver usb-storage May 6 12:36:57 marvin kernel: USB Mass Storage support registered. May 6 12:36:58 marvin scsi.agent[3573]: disk at /devices/pci0000:00/0000:00:07.2/usb1/1-1/1-1:1.0/host0/target0:0:0/0:0:0:0 May 6 12:36:59 marvin kernel: usb 1-1: USB disconnect, address 4 May 6 12:36:59 marvin kernel: sda : READ CAPACITY failed. May 6 12:36:59 marvin kernel: sda : status=0, message=00, host=1, driver=00 May 6 12:36:59 marvin kernel: sda : sense not available. May 6 12:36:59 marvin kernel: sda: Write Protect is off May 6 12:36:59 marvin kernel: sda: assuming drive cache: write through May 6 12:37:00 marvin kernel: sda : READ CAPACITY failed. May 6 12:37:00 marvin kernel: sda : status=0, message=00, host=1, driver=00 May 6 12:37:00 marvin kernel: sda : sense not available. May 6 12:37:00 marvin kernel: sda: Write Protect is off May 6 12:37:00 marvin kernel: sda: assuming drive cache: write through May 6 12:37:00 marvin kernel: sda : READ CAPACITY failed. May 6 12:37:00 marvin kernel: sda : status=0, message=00, host=1, driver=00 May 6 12:37:00 marvin kernel: sda : sense not available. May 6 12:37:00 marvin kernel: sda: Write Protect is off May 6 12:37:00 marvin kernel: sda: assuming drive cache: write through May 6 12:37:00 marvin kernel: sda:<3>scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:00 marvin kernel: Buffer I/O error on device sda, logical block 0 May 6 12:37:00 marvin kernel: scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:00 marvin kernel: Buffer I/O error on device sda, logical block 0 May 6 12:37:01 marvin kernel: scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:01 marvin kernel: Buffer I/O error on device sda, logical block 262143 May 6 12:37:01 marvin kernel: scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:01 marvin kernel: Buffer I/O error on device sda, logical block 262143 May 6 12:37:01 marvin kernel: scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:01 marvin kernel: Buffer I/O error on device sda, logical block 0 May 6 12:37:01 marvin kernel: unable to read partition table May 6 12:37:01 marvin kernel: sda:<3>scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:01 marvin kernel: Buffer I/O error on device sda, logical block 0 May 6 12:37:01 marvin kernel: scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:01 marvin kernel: Buffer I/O error on device sda, logical block 262143 May 6 12:37:01 marvin kernel: scsi0 (0:0): rejecting I/O to device being removed May 6 12:37:01 marvin kernel: Buffer I/O error on device sda, logical block 0 May 6 12:37:01 marvin kernel: unable to read partition table May 6 12:37:01 marvin kernel: Attached scsi removable disk sda at scsi0, channel 0, id 0, lun 0
So, thinking it may be the pin switches, I unscrewed everything to change the pin selector from Cable Select to Master. Trying again, with the bare disk and just the connectors, I confirmed this was the issue. This is what one should expect.
May 6 13:00:28 marvin kernel: usb 1-1: new full speed USB device using address 6 May 6 13:00:29 marvin kernel: scsi2 : SCSI emulation for USB Mass Storage devices May 6 13:00:29 marvin kernel: Vendor: Genesys Model: USB to IDE Disk Rev: 0033 May 6 13:00:29 marvin kernel: Type: Direct-Access ANSI SCSI revision: 02 May 6 13:00:29 marvin kernel: SCSI device sda: 586114704 512-byte hdwr sectors (300091 MB) May 6 13:00:29 marvin kernel: sda: test WP failed, assume Write Enabled May 6 13:00:29 marvin kernel: sda: assuming drive cache: write through May 6 13:00:29 marvin kernel: sda: unknown partition table May 6 13:00:29 marvin kernel: Attached scsi removable disk sda at scsi2, channel 0, id 0, lun 0 May 6 13:00:30 marvin scsi.agent[4172]: disk at /devices/pci0000:00/0000:00:07.2/usb1/1-1/1-1:1.0/host2/target2:0:0/2:0:0:0
So working with /dev/sda which was expected, sd being for the USB connections, and a for the first.
Double check nothing on drive.
$ fdisk -l /dev/sda Disk /dev/sda: 300.0 GB, 300090728448 bytes 255 heads, 63 sectors/track, 36483 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk /dev/sda doesn't contain a valid partition table
Partition the drive into 3 x 100GB partitions.
$ fdisk /dev/sda Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. The number of cylinders for this disk is set to 36483. There is nothing wrong with that, but this is larger than 1024, and could in certain setups cause problems with: 1) software that runs at boot time (e.g., old versions of LILO) 2) booting and partitioning software from other OSs (e.g., DOS FDISK, OS/2 FDISK) Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) Command (m for help): m Command action a toggle a bootable flag b edit bsd disklabel c toggle the dos compatibility flag d delete a partition l list known partition types m print this menu n add a new partition o create a new empty DOS partition table p print the partition table q quit without saving changes s create a new empty Sun disklabel t change a partition's system id u change display/entry units v verify the partition table w write table to disk and exit x extra functionality (experts only) Command (m for help): p Disk /dev/sda: 300.0 GB, 300090728448 bytes 255 heads, 63 sectors/track, 36483 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-36483, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-36483, default 36483): +100000M Command (m for help): p Disk /dev/sda: 300.0 GB, 300090728448 bytes 255 heads, 63 sectors/track, 36483 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 1 12159 97667136 83 Linux Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 2 First cylinder (12160-36483, default 12160): Using default value 12160 Last cylinder or +size or +sizeM or +sizeK (12160-36483, default 36483): +100000M Command (m for help): p Disk /dev/sda: 300.0 GB, 300090728448 bytes 255 heads, 63 sectors/track, 36483 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 1 12159 97667136 83 Linux /dev/sda2 12160 24318 97667167+ 83 Linux Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 3 First cylinder (24319-36483, default 24319): Using default value 24319 Last cylinder or +size or +sizeM or +sizeK (24319-36483, default 36483): Using default value 36483 Command (m for help): p Disk /dev/sda: 300.0 GB, 300090728448 bytes 255 heads, 63 sectors/track, 36483 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 1 12159 97667136 83 Linux /dev/sda2 12160 24318 97667167+ 83 Linux /dev/sda3 24319 36483 97715362+ 83 Linux Command (m for help): v 15371 unallocated sectors Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks.
Confirm.
$ fdisk -l /dev/sda Disk /dev/sda: 300.0 GB, 300090728448 bytes 255 heads, 63 sectors/track, 36483 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 1 12159 97667136 83 Linux /dev/sda2 12160 24318 97667167+ 83 Linux /dev/sda3 24319 36483 97715362+ 83 Linux
Formating the 3 new Partitions. (Note: this takes a while)
$ mkfs.ext3 -v /dev/sda1 $ mkfs.ext3 -v /dev/sda2 $ mkfs.ext3 -v /dev/sda3
Create some mount points and test mount.
$ mkdir /u11 $ mkdir /u12 $ mkdir /u13 $ mount -t ext3 /dev/sda1 /u11 $ mount -t ext3 /dev/sda2 /u12 $ mount -t ext3 /dev/sda3 /u13
Add hard disk to fstab
by ronald
Finally back home after some R&R at Yosemite before leaving the US. In conclusion, to sum up my experience of the 4th Annual MySQL Users Conference “Excellent”.
Here’s my take. Friends, Functionality & New Features, the Future.
I’ve used MySQL now for over 6 years, and full time for a number of years, yet I’ve only become active in the MySQL community, particularly Planet MySQL in the past 6 months. Over that time, I’ve read a lot from members, and heard from many people. It was great at the conference to meet many of these people for the first time. The list includes: Community Members – Frank Mash,Mike Kruckenberg, Markus Popp, Roland Bouman, Giuseppe Maxia and Paul McCullagh. MySQL Employees – Mike Hillyer, Colin Charles, Jay Pipes, Mike Zinner, and New Contacts – Kristian Köhntopp, Jeremy Cole, Sheeri Kritzer, Taneli Otala, Laura Thompson just to start the list.
Not only was there plenty of discussion on Server Functionality, there was plenty of MySQL Client functionality including the MySQL Workbench, MySQL Migration Toolkit and the other MySQL GUI products.
There were a number of discussions on uses and implementations of MySQL in large web deployments. It would be great to see some more white papers here.
A few months ago I wrote an article A call to arms!. In some part, I was just giving my opinion and hoping to gee up some support and feedback from the community. Well, the MySQL 5 Pluggable Storage Architecture got a great boost with announcements of transactional storage engines Falcon by Jim Starkey, Solid and PBXT. Add details of InnoDB New Features, MyISAM additions, and indications of other wonderful if not entirely practical options. I’m sure there is much more in stall to come this year that wasn’t discussed.
A number of talks featured Cluster including Monday’s tutorial, and with 5.1 and beyond I can see next year there will be more discussion on successful Cluster implementations. There was a lot of talks about Scaling out. I’d like to see more practical examples, perhaps a detailed tutorial.
What does the future hold for MySQL? The MySQL Server and Storage Engine Roadmap provided an insight of the upcoming planned features and releases over the next 2 years. Of course, the marketplace can change quickly, and MySQL is in a great position to react to the needs of the community quickly.
And before your know it, the 5th Annual MySQL Conference will be in play.
One thing I had a chance to discuss with Jay Pipes after the conference, I wasn’t the first to mention, and plans are already in motion, was a number of talks just needed more time. Moving the schedule to 55 minute talks gives that extra time, even if it is open question time from the floor, but it also makes knowing when sessions are on much easier, if they always start at the top of the hour.
Frank (a.k.a Farhan Mashraqi) asked me what session I liked the most? Hard to say. Agile Database Techniques: Data Doesn’t Have to be a Four-Letter Word Anymore rated very highly, as the content was close to heart and my expertise. HackFest B: Creating New SHOW Commands by Brian Aker, showed just how easy it was to get into the MySQL source. Of course the internals are much more complex then this, but it was a good introduction. My favourite keynote was The Ubuntu Project: Improving Collaboration in the Free Software World. There were a number of talks I was disappointed in, as well as a number I didn’t get to due to 8 sessions in parallel.
I would have to say, that what impressed me most was no one single talk, but the functionality of the GRT Shell that Mike Zinner and his team have built into the GUI product line. I was very impressed, and I could see this providing extensive functionality and not just MySQL specific centric tools. This will be area I’ll be focussing on my contributions in the near future.
by ronald
Round 2. Question 2? From the MySQL Quiz Show. (you had to be there)
by ronald
Another one of the sessions at the MySQL Users Conference I attended was Tuning MySQL5 SQL and Stored Procedures by Guy Harrison from Quest Software. A global company with 6000+ customers.
Guy has written a number of Oracle Performance Books in the past. His work now is on the “Spotlight” product family – Database diagnositic tools converting data to graphical representations. For these products, MySQL 5 and InnoDB only is necessary, simply due to accessing the right internal information for presentation. There are Freeware MySQL product downloads.
In this presentation he stated, nothing he was talking about specifically was relatively new. He did make quite a funny comment, “He is now seeking refugee status in the MySQL Community”.
Guy is author of O’Reilly “MySQL Stored Procedure Programming” Book. I managed to get for free at the conference from the MySQL Quiz night, in addition to a shirt and cap for stumping a Guru.
His talk were on tools and techniques for tuning MySQL.
mysql> explain extended select ...; mysql> show warnings G Shows what the optimizer actually did. In this example, An IN was converted to EXISTS
There were 4 ways to provide optimizer hints.
In addition to the Show Query Log, there are Innodb specific commands, two in particular.
show status like 'innodb%' * innodb_buffer_pool_read_requests * innodb_data_read
Indexing and the optimizer
Not all indexes are created equal. In the following examples, each advancement improved performance.
Examples of SQL that can’t benefit from Indexes.
A comment from the audience was that derived tables can be of a benefit to a correlated sub-query in specific examples.
Stored Procedures provided a mixed blessing for performance.
A written routine to calculate prime numbers provided the following performance (from most expensive to least) MySQL SP, Oracle SP,PHP,Perl,Java ,VB.NET ,C (gcc). This showed an example that was excessively inefficient. On the other hand, if the program is network dependent (e.g. access a million rows, perform some statisical aggretation). Comparatively the same between Java and SP locally, but much better in a remote host mode.
Performance of SQL in a SP will dominate overall performance. Where SQL is tuned, goto tried and proven traditional optimisation techniques.
Loop Management
Some guidelines for Triggers.
For more information check out www.quest.com/mysql
by ronald
I didn’t take any photos myself at the MySQL User’s Conference. In reflection, perhaps I should. I did make it into some photos however. A few of them here.
Stolen from Jeremy Cole’s Photos at http://jcole.us/gallery/uc2006
by ronald
Thursday’s Keynote speaker at the MySQL Conference was Mark Shuttleworth talking on The Ubuntu Project: Improving Collaboration in the Free Software World.
Ubuntu from Canonical Ltd is fastly becoming one of the most popular Linux Distributions, based on Debian. Here were some notes from his presentation. Mark had some really powerful one or two line slides that typified both his presentation intention and the goal of Ubuntu.
Delivering on the Promise of Free Software
The New Deal of Free software
* Different Economics
* Different culture
Our vision is to build a complete community-driven distribution that can grow and substain itself through support and services.
Self substaining at a professional level. Community Driven.
We do the best work when we focus on extreme challenges.
Mark made a great comment about the Chinese Version, What was in English on any pages, for Mark was the saving grace, but for the Chinese user, that was the wall, so a committed emphasis on Internationalisation was important.
Ubuntu as you know it today is widely rated #1 desktop Linux with millons of users globally, with complete office and home desktop distributions. We started on the desktop, we are continuing to expand into different variants including the enterprise. The next release in June 2006 – Dappy – 6.06 will includ LTS (Long Term Support) 3 years for Desktop, 5 years for server.
We maintain relationships with the community ecosystem.
Free Software Relationships
Beating history into submission
Free software enables a different economic paradigm.
Embrace it.
A question from the audience was where does the name Ubuntu come from. The Answer was.
Ubuntu is common to many African languages. It means “human-esse”. The Ubuntu root is common to a lot of words. Some means included “The way we look after other people defines who we are. ”
Kubuntu – Has a meaning “For Humanity”.
by ronald
My talk at the MySQL Users Conference went well. MySQL For Oracle Developers was part of two talks, the second was by Mark Leith on MySQL For Oracle DBA’s.
I had a number of positive comments from attendees, including Ken Jacobs of Oracle who also contributed some valuable information in comparision of UPDATE/DELETE ORDER BY/LIMIT statements I was unaware of.
You can download my paper here.
My slides, like many talks just scratched the surface in the alloted. I’ve been working on additional reference material, a work in progress is available at MySQL For Oracle Developers. My continuatation on this will be dependent on feedback from the community of it’s intended value.
by ronald
Further to my earlier post Hacking MySQL Source (in a good way) in which I was having a compilation problem when re-compiling MySQL source (i.e. tt worked the first build, but failed on subsequent re-compiles, even with no changes).
I’ve been able to solve my re-compiling issue, with special thanks to Jan Kenschke of MySQL AB, who was near during the MySQL Quiz Show.
As suspected, there is no need to re-run the ./BUILD/compile-pentium-debug command as this cleans, a simple make command is sufficient (I needed to confirm no other command args where needed). By doing this, it also better highlighted the actual error, where previously is wasn’t. I didn’t keep a copy of the error, but in effect I needed to do the following in order to successfully recompile.
su - mysqldev cd mysql-5.1.10-beta-nightly-20060413 touch mysql-test/std_data/client-key.pem touch mysql-test/std_data/client-cert.pem touch mysql-test/std_data/cacert.pem touch mysql-test/std_data/server-cert.pem touch mysql-test/std_data/server-key.pem make # Success
I can’t answer why I had to do this manually (create these cert permissions), and why some level of make doesn’t (or does but I don’t know the command), but it works, and right now that’s important. Hopefully a MySQL guru can enlightment me.
From here, I was able to test as I’d written previously. I just need to confirm my changes work before reporting back, as well as providing some guidelines for debugging with gdb.
by ronald
Everybody can contribute to the MySQL Open Source Community. Here are some ideas:
If you are passionate about MySQL and you are attending the MySQL User Conference, then jump into the Open Source MySQL Community.
by ronald
At the MySQL User Conference – MySQL Quiz Show we were asked to Stump a MySQL Guru. Well I submitted a few questions, and the first one stumped the guru, being Jeremy Cole, and my prize was a MySQL tee-shirt.
The Question: What are the current supported languages of the GRT Environment used in the MySQL GUI products?
The Answer: Lua, C, C++, Java, Python and PHP
by ronald
Joe Kottke from FeedBurner in his MySQL Conference presentation today FeedBurner: Scalable Web Applications Using MySQL and Java mentioned a novel way of extracting SQL Select Statements from a MySQL Server.
The obvious MySQL ways include SHOW [FULL] PROCESSLIST, the Slow Query Log, and the General Query Log. In 5.1 you also use the INFORMATION_SCHEMA.PROCESSLIST.
Anyway, Joe mentioned he does a strings on tcpdump to extract the queries, and there was no need to restart the server (this point interested me). So, not letting a challenge go, having never used tcpdump.
Well, here’s how.
tcpdump -w mysqldump.txt -i lo
NOTE: You must log output to file with -w to get the full packets and not just headers.
In a seperate session.
$ mysql -uroot -p mysql -h `hostname`
mysql> select * from user;
mysql> exit
# I did a ctrl-C in the other terminal (not sure if necessary)
$ strings tcpdump.txt
select * from user
mysql
user
user
NOTE: localhost wasn’t valid, it had to be the hostname of the server. Of course you could access the server from a different client.
Of course there may be other or better ways, but it worked for me and was rather cool.
Perhaps this is a good reason to use SSL?
by ronald
Just to add some more features of the MySQL Migration Toolkit from the MySQL Conference presentation MySQL Migration Toolkit by Mike Zinner from MySQL AB.
You can extend and modify the funcionality of the Migration toolkit with the supplied Eclipse Java project. I’ll need to download the code to write some more detailed notes, but here were a few points from Mike.
Classes are in the com.mysql.grt.modules Package Space
The main configurable classes are nameed Migration????..java and ReverseEngineer???.java with ???? being for the different Database Products
Within the Eclipse project, you can incoporate other languages. For example, ReveserEngineerMySQL Jdbc.java for example has a callGrtFunction to code written in C. This I’m assuming will allow you to incoporate all the other languages of the GRT such as Python, Lua. (I’m still impressed you can actually do this, a mini project for another day to pull this apart.
A question from the audience was about Oracle User Defined data types. MigrationOracle.java for example can easily be extended to cater for the migration of user defined data types to a suitable valid MySQL alternative.
References
MySQL Migration Toolkit – Provides details, screenshots and Flash Tutorials for SQL Server, Oracle and Access Migrations.
by ronald
A Wednesday afternoon presentation at the MySQL Conference was MySQL Migration Toolkit by Mike Zinner from MySQL AB.
In summary, the Migration Toolkit currently provides the following feature set.
The product is built on the Generic Runtime Environment (GRT) which is used in other MySQL GUI Products. The GRT is a thin C Layer that allows you to easily add modules written in other languages (e.g. C/C++, Java, Lua, Python etc).
The following are the steps of the Migration process.
Some more relevent points on some of the individual steps.
Source/Target
Object selection
Object Mapping
Manual Editing
Schema Creation
Data Mapping
Bulk Transfer
Summary
I can see the possibility for the following enhancements.
References
MySQL Migration Toolkit – Provides details, screenshots and Flash Tutorials for SQL Server, Oracle and Access Migrations.
by ronald
HackFest B: Creating a New SHOW Command by Brian Aker at the MySQL Users Conference
Brian stepped through the steps for those attending to modify and deploy new functionality in the mysql server. Cool. The end result I would consider for an experienced developer as relatively easy (after avoiding the pitfalls).
NOTE: I wasn’t able to complete this successfully during the session, but I’ve posted this, so hopefully the input and review of others can help in overcoming the current issues. See Outstanding Issues throughout my notes.
The following commands were performed on CentOS 4.2. There may be some differences with different Linux Distros.
su -
useradd mysqldev
su - mysqldev
wget http://downloads.mysql.com/snapshots/mysql-5.1/mysql-5.1.10-beta-nightly-20060426.tar.gz
# NOTE: You should check the snapshots page for latest versions http://downloads.mysql.com/snapshots.php
tar xvfz mysql-5.1.10-beta-nightly-20060413.tar.gz
cd mysql-5.1.10-beta-nightly-20060413
# compile script will depend on H/W and requirements
./BUILD/compile-pentium-debug --prefix=/home/mysqldev
make install
scripts/mysql_install_db --datadir=/home/mysqldev/data
sql/mysqld --basedir=/home/mysqldev --datadir=/home/mysqldev/data &
NOTE: This can take a while, so it’s a good opportunity to leave this and come back at a later time. The –prefix allows you to install into the new users directory ensuring that you can test without affecting any currently installed mysql installations on your machine.
The BUILD directory contains many different compilations for platforms and variants. This version contains 41 specific scripts. For the purpose of our new SHOW command, we are going to run in debug mode for any necessary debugging.
Outstanding Issues
I ran this in a seperate terminal window.
su - mysqldev bin/mysql -e "SELECT VERSION()" +------------------------------------+ | VERSION() | +------------------------------------+ | 5.1.10-beta-nightly-20060413-debug | +------------------------------------+
This gives us a suitable source baseline.
The easiest means of developing a new SHOW command is to base this on an existing command. We are going to base this new SHOW command on the SHOW AUTHORS command.
We are going to be looking at the following files.
All these files are found under the sql directory in the source tree.
131: { "CONFERENCE", SYM(CONFERENCE_SYM)},
202: %token CONFERENCE_SYM ... 8267: | CONFERENCE_SYM 8268: { 8269: LEX *lex=Lex; 8270: lex->sql_command= SQLCOM_SHOW_CONFERENCE; 8271: } ... 9366: | CONFERENCE_SYM {}
112: SQLCOM_SHOW_CONFERENCE,
3515: case SQLCOM_SHOW_CONFERENCE: 3516: res= mysqld_show_conference(thd); 3517: break;
915: bool mysqld_show_conference(THD *thd);
229: bool mysqld_show_conference(THD *thd) 230: { 231: List<item> field_list; 232: Protocol *protocol= thd->protocol; 233: DBUG_ENTER("mysqld_show_conference"); 234: 235: field_list.push_back(new Item_empty_string("Name",100)); 236: 237: if (protocol->send_fields(&field_list, 238: Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) 239: DBUG_RETURN(TRUE); 240: 241: protocol->prepare_for_resend(); 242: protocol->store("Welcome to the MySQL User Conference 2006", system_charset_info); 243: if (protocol->write()) 244: DBUG_RETURN(TRUE); 245: send_eof(thd); 246: DBUG_RETURN(FALSE); 247: }
cd ..
./BUILD/compile-pentium-debug --prefix=/home/mysqldev
...
gmake[3]: *** Waiting for unfinished jobs....
/bin/chmod +x mysql-test-run-t
/bin/mv mysql-test-run-t mysql-test-run
gmake[3]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413/mysql-test'
gmake[2]: *** [all-recursive] Error 1
gmake[2]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413/mysql-test'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413'
gmake: *** [all] Error 2
Outstanding Issues
At this point I wasn’t able to continue, but here are notes I took of next steps.
make install gdb mysqld run --gdb --debug
In another terminal session.
su - mysqldev bin/mysql mysql> SHOW CONFERENCE;
I’m keen to see it work in my own environment and documented for others to try.
Brian moved on to creating a INFORMATION_SCHEMA query but we ran out of time to complete this. I’ve got some notes to document as a later date.
by ronald
In having a discussion with Paul McCullagh (the creator of PBXT transactional storage engine) and Taneli Otala MySQL AB CTO after the keynote presentation at the MySQL User Conference, Taneli made the following comment (paraphrased and reproduced with permission).
“I talk about PBXT in discussions shamelessly. The development of PBXT was excellent timing in the MySQL community landscape”.
It was an excellent commendation that MySQL AB management considered so highly the contributions from the community. As mentioned in the opening keynote, MySQL with the Storage Engine API with MySQL 5.1 has great potential to expand what options are available to user of MySQL.
Good work Paul. I like many others wish you the best in your continued development and contribution.
by ronald
It just dawned on me as this topic was mentioned in a MySQL Conference presentation yesterday in a manual process. So my thought is, why can’t the following functionality be added to the MySQL server.
My request for two new extensions to the SHOW STATUS command.
SAVE STATUS – This enables the current SHOW STATUS to be saved (or more specifically cached), you can only keep one copy per server instance.
DIFF STATUS – This shows the difference between the current SHOW STATUS and the last saved show status from the SAVE STATUS command.
This would quickly, easily and interactively via a mysql prompt enable a DBA to see the state of change in a more condensed form. The syntax may need to cater for Session/Global scope, and of course would need to be appropiately named, my examples is just an idea.
Of course you can write an easy script that does the same, and provide a diff, but why not include it directly?
by ronald
I’ve just attended Scott Ambler’s presentation on Agile Database Techniques: Data Doesn’t Have to be a Four-Letter Word Anymore at the MySQL Users Conference.
There is so much content on the topic, it’s impossible to present so much information in a short 45 minute session. I can speak with authority in regards to the same problem of condensing so much content given this issue with my own presentation MySQL for Oracle Developers.
I ask this question. Why is common sense considered such a radical approach? I state this because Agile Methodology approaches in so many ways are common sense, but “traditionalists” (and I use this term for several groups of existing IT dinosours), see change and continual improvement approaches as potentially evil, while they are constantly just trying to stay afloat daily with bloated, inefficient and overly complex legacy systems (I had to throw in several daggers at the same time, couldn’t resist).
This presentation echoed both a lot of my experiences and part of my writings and current projects. I have been a database modeller for over 16 years, and I have worked with Extreme Programming Agile Methodology for now over 6 years. Here are some abstract and unstructured bullet points from the presentation and some of my own comments intermingled. (Unfortunately due to other discussions I missed the initial 15 mins, however given the content and my own professional standings I can only fully understand what the initial content was).
Scott threw in a lot of Agile terms, and for most people in the audience I observed that quite a few of these terms were indeed foreign. I could easily see the potential for a Talk on Introduction to Agile – Applying to MySQL Projects.
One term I didn’t hear was YAGNI – You Ain’t Gonna Need It. This is an essential XP principle which in some ways sums up the common sense approach to software development. Don’t even thing about it until it’s made it’s way to the top of the customer requirements for the current interation.
I’ll also be getting the book “Refactoring Databases – Evolutionary Database Design” that was mentioned, I’m keen to read in depth more of the principles I so much promote myself. Sometimes I feel quite isolated in this area of Data Modelling colliding with Agile Methods.
I’ve written previously content that both re-inforces a number of points of this presentation and also complements Scott’s presentation in a number of ways. These include Unit Testing A Database and Database Modelling within an XP Methodology.
I recall this quote from a tee-shirt once owned, and I think is valid for certain IT professionals that continue to cling to traditional approaches in data modelling. Perhaps I should put it on an Agile slogan shirt for myself. Evolve or Die
In closing, I’ve been wanting to write a paper for quite some time titled “Better Productivity and Quality. An Agile Approach” to share my experiences. I’ve haven’t been able to put my thoughts down, having two other major writings in progress at present, but this presentation has only renewed my vigor.
by ronald
Mike writes a good summary of the MySQL Conference opening keynote State of the Dolphin: Interview with Kaj, Monty and David of MySQL.
I’d like to add just two comments.
Firstly, it was great to see community awards to Giuseppe, Roland, Marcus and Rasmus. Well done!
Second, it was a great thing to see on the slides a reference to Paul’s PBXT Transactional Storage Engine. A MySQL storage engine from the community. I’ve had a chance to meet Paul and have a number of great discussions with him. I wish him all the best, and I’m happy to contribute what I can to see his engine make it into the MySQL product in the future.
by ronald
Tuesday’s Keynote speaking from the MySQL Users Conference including a presentation from Greg Gianforte of RightNow Technologies on “Bootstrapping: Starting an Open Source Business With Almost No Money!” Read More.
Taking directly from Greg’s presentation, here are some key points for bootstraping that meant something to me.
Some great points. In summary, not having funds should not be an excuse, find another way . You just need to think creatively. I agree completely, thinking creatively is something I do well. Immerse yourself – talk to lots of people, develop a one page description, get it out to as many people as possible, talk to people, ask for an order. Of course sales is #1, but for me personally, and also within the essense of the presentation – Honesty. His passion about helping people to get started has also lead to publishing a book for bootstrappers (in paperback to save money).
One of the key goals is to “Help our clients to better serve our customers”. Greg was also correct in the need to correct the loss of ethics in the IT and software industry, something I fight for back at home.
A number of these points provided great relevence given I’ve worked for two failed Internet startups.
The closing quiz raised some great questions. Here they are. (Hopefully now reproducible in the Open Source community, oops too late now!)
I’ll save the expense by not giving any answers, but my favourite answer was for #9.
Greg started the presentation with the right words for me. Love Mountains, Love Skiing, check out our Careers. Anybody that knows me, knows that my plan for this year, it to be in the Northen Hemisphere for Winter, working for an organisation that is close to the snow, enabling me to go skiing. I just may have check it out.
by ronald
Last night, I attended Mike Zinner’s presentation of MySQL GUI’s, most specifically MySQL Workbench. In summary I was very impressed with the current work, and the future potential. I’ve been waiting for now about a year to get access to full strength MySQL Workbench (it has been available for some time, but still in early development), having previously used DB Designer for MySQL Data Modelling. One of the limitations is finding a quality tool that runs under Linux. Well MySQL workbench will satisfy this and far more in the future.
A quick summary of some of the glossy features.
The introduction of the GRT Shell, an interactive shell will provide near unlimited potential.
From the shell you can do everything you can do via the GUI, and you can do it multiple languages including Lua, Python, C/C++ Java and in future Ruby, as well as the potential of many more. The potential of Scripts and Plugins is limitless. Mike demonstated a number of simple examples, so I’ll be very keen now to review and contribute to this functionality now that I’ve seen it in action. MySQL is working actively for community contribution with the recent introduction of the MySQL Forge.
Some of the discussion was the future direction of technology. Presently, there is some limitation of reuse in the end user GUI. A rich GUI is provided, but is platform specific, providing a different look and feel in Linux, Mac OS and Windows. One of the options, and definitely of interest is the consideration of using Java as a standard GUI language.
One of the requests was for more feedback, greater community input and contribution. I agree there needs to be better means here, one of my suggestions was perhaps an interactive Feedback model built into beta products, actively prompting users to provide feedback to MySQL. This would also enable MySQL to gain greater insite into the users of the GUI products.
In summary, excellent work Mike, with a team of just 4 that supports and develops all the MySQL GUI products.
Here is an overview diagram of the MySQL Workbench.
by ronald
While many people will be blogging about the conference sessions and MySQL features, functionality and sessions, I thought it would be important to raise awareness of the creativity of developers often lost during the product lifecycle.
I met at the Speakers Function last night Jim Starkey, the founder of Netfrastructure, Inc, a company aquired by MySQL recently. At the conference was the official launch of a new transactional storage engine to be included in the MySQL 5.1 release is codenamed “Falcon”.
Too often, the flare and creativity of products, and also the initial history is lost when it reaches the marketing department of an organisation. In this case, after talking with Jim, we decided it was important to ensure the name “Falcon” remains. Why should the name conform to something generic, and quick frankly boring. So please join me.
by ronald
Everybody has been writing lately of their likes of sessions and events for the MySQL User Conference, time for my 2 cents worth, with a twist.
I’ll be leaving early tomorrow at 7am (Friday my time), that’s Thursday 2pm Conference time. A taxi, two trains, two planes, and some 21 hours later, I’ll be at San Jose. In time to get a car and head to Davis California, a small town, where I have lived previously. After some catchup, I’m off to Lake Tahoe, (all up about 3 hrs from San Jose), for some R & R, and a weekend of Skiing, starting at Heavenly. No snow in the last few days, but 4 feet in the past week, and a bumper season to date. (The added benefit is it’s much cooler, I’m not a warm weather person, and my current home in Brisbane Australia is just too warm for my liking)
A lazy Sunday afternoon drive back, and ready for the MySQL User Conference starting on Monday. My presentation is nearer to the end on Thursday, so I’ll have plenty of time to soak in what’s on offer. Too much on the MySQL front to mention, performance and new features including clustering highlights, but also keen to hear Tim O’Reilly as I’m become more interested in Web 2.0, where it is and where it’s going. I see this philosophy of Services rather then Products, and highly user driven in features, functionality and content, in line with some of my principles and strong thoughts of Extreme Programming and Agile Development Methodologies.
I’ve also had positive comments and feedback from a number of MySQL colleagues of the past months, so it will be great to expand not only my intellect from the conference content, but my circle of new friends from the MySQL community. (And places in the world to visit in the future)
by ronald
Sometimes you just can’t find words to describe bad code, and if you are forced into maintenance it can be a mindfield. I’m presently supporting an existing deployed Web Based Java application, which I’ve had no involvement with previously, and for lack of any complements it’s absolutely terrible.
How this code was ever written, and of course never reviewed (that’s another talk on software development) one can only shutter. If it was an isolated case, then perhaps, but the code is bloated, and riddled with unnecessary complexity. Basic 101 practices such as Standard CSS, well formed html pages (ie, not with double <html> and <body> tags), and Javascript functions that are not duplicated up the warzoo are things you can discover spending 2 mins looking at just the first screen before you even look at the Java Code.
Here is an example.
tempKeyStr = tempKeyStr.substring(0,2).concat(String.valueOf(Integer.parseInt(tempKeyStr.substring(2).trim())));
Ok, I guess I should give you a hint of the type of values in the tempKeyStr prior to this wonderful transformation. This string contains a 2 alphanumerical character prefix, then optionally a space, then a numeric number (usually 2, 3 or 4 digits). An example may be ‘XX 999′ or ‘AA99′.
This will help you to identify what this lines now does?
Ok, well to save the suspense, the purpose of the above line of code is to trim and squeeze any whitespace from the string. So something like the following would surfice.
tempKeyStr = tempKeyStr.replaceAll(" ","");
Of course refactoring would totally eliminate this line, placing the replaceAll syntax in a previous declaration.
Those observant Java guru’s would also state, but what if this was written 5 years ago, replaceAll is only available since 1.4. Yes, correct, but Pattern has always existed, replaceAll is just a convience wrapper for this, and this type conversion from String to Integer to int to String is unnecessary complexity.
On that note of refactoring, I was sent the following link recently Refactoring to the Max. As the title suggests, sometimes people go to far.
by ronald
Being a CVS Version Control Person, I’ve had to learn Subversion as part of Open Source Contribution. Both MySQL and JMeter use SVN.
Steps for integration of SVN into Eclipse IDE. Refer to Subclipse for more information.
by ronald
I don’t think I’ve ever installed MySQL via .rpm I have always installed via .tar.gz primarilarly because I’m an /opt system administrator from my old UNIX days. so my first experience installing on my CentOS 4.2 (aka RHEL 4).
$ rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
perl(DBI) is needed by MySQL-server-standard-5.0.19-0.rhel4.i386
Suggested resolutions:
perl-DBI-1.40-8.i386.rpm
$ rpm --import /usr/share/doc/centos-release-4/RPM-GPG-KEY
$ yum install perl-DBI-1.40-8
$ rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-server-standard ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h lamda.arabx password 'new-password'
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting MySQL...................................[FAILED]
So turn to my good friend Morgan for help. I'd already done a little google research, but it took Morgan about 15 secs to know the problem, and know he had already experienced this with other clients.
Some References:
https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=167551
http://fedora.redhat.com/docs/selinux-faq-fc3/index.html#id2825880
http://confluence.atlassian.com/display/DISC/Prerequisites+and+installation+on+Fedora+or+RHEL+Linux
http://bugs.mysql.com/bug.php?id=12676
Some Analysis:
$ system-config-securitylevel # shows that I have SELinux enabled. $ /usr/sbin/selinuxenabled && echo "yes" $ restorecon -R -v /var/lib
Ok, its working now. But wait there is no mysql command? Again, I'm .tar.gz user, had to realise the client is a seperate RPM.
rpm -ivh MySQL-client-standard-5.0.19-0.rhel4.i386.rpm
Well my first .rpm experience was, well eventful none the same.
by ronald
As mentioned in my earlier post Emulating Oracle Output Functionality, I’ll be speaking at the MySQL Users Conference on the topic of MySQL for Oracle Developers. Here is the second in a series of points regarding current MySQL Stored Procedures and Functions functionality.
Parameters passed to Procedures under normal operations can be considered positional parameters. For each parameter, a calling statement is required to pass the same number of parameters and in the same order. Named parameters allows the user to pass parmeters in the order of their choosing, and also not pass all parameters.
For Example, consider the following Oracle Stored Procedure definition.
EXAMPLE_PROCEDURE(num IN NUMBER DEFAULT 0, dte IN DATE DEFAULT SYSDATE, str IN VARCHAR2 DEFAULT NULL, str2 IN VARCHAR2 DEFAULT NULL);
This can be called in several ways, here are few examples.
EXAMPLE_PROCEDURE(); EXAMPLE_PROCEDURE(9, '01-MAR-2006', 'Hello World', NULL); EXAMPLE_PROCEDURE(dte => '01-MAR-2006'); EXAMPLE_PROCEDURE(dte => '01-MAR-2006', str2 => 'Hello World');
The key to being able to use named parameters, is the addition of the DEFAULT syntax in the variable definition.
Giuseppe Maxia has many MySQL stored procedures, and I applaud his initiate with The MySQL General Purpose Stored Routines Library. Included here is an implementation of handling named parameters. I can’t say I’ve used it personally, however I did browse the code (another Open Source benefit).
Oracle provides the capability like Java, which allows for multiple procedures to have the same name, but have a different set of parameter arguments.
Oracle provides the ability to overload a Procedure call with the same number of arguments and varying datatypes, for example, going with the earlier DBMS_OUTPUT.PUT_LINE analogy, Oracle has the following specification.
DBMS_OUTPUT.PUT_LINE (item IN NUMBER); DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2); DBMS_OUTPUT.PUT_LINE (item IN DATE);
Likewise, it possible to have procedures with different numbers and types of arguments.
EXAMPLE_PROCEDURE(num IN NUMBER); EXAMPLE_PROCEDURE(num IN NUMBER, str IN VARCHAR2); EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE); EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE, str IN VARCHAR2, str2 IN VARCHAR2); etc.
As MySQL growth continues and developers take up placing greater business logic in the database with Stored Procedures, there may be merit to consider these points. On the flip side, 10 years ago, business logic was placed in the database for many reasons including the extent of capable client programming languages, a Client/Server architecture and of course tight coupling with Oracles GUI products. One could argue a justification as to how much business logic is stored in the database, and how much is managed within an application. Given the advent of the Web, and multiple applications and clients assessing coporate data, it’s logical to place essential logic as close to the source of the data as possible, and the introduction of Stored Procedures in MySQL 5 released last year provided the capacity to consider this.
At this time, I would err on the conservative side with the use of Stored Procedures. The type of application, requirements and signficantly the age of the product (being a new product or existing legacy product) all affect the outcome. As mentioned, Giuseppe feels strongly regarding named parameters, and has provided a workaround. The benefit with Open Source is this FREEDOM clearly exists when the community contributes. This can only benefit MySQL in areas such as Stored Procedures.
Myself, only a few days ago over the weekend, I took an Java Open Source product, that lacked the capacity to support the calling of Stored Procedures via JDBC, developed it myself and then submitted my work back to the Apache community. I was even more happy when it was accepted unaltered, committed and in the nightly build for the next day. Read More. I’m now working on the next contribution to the same project, providing JDBC Transaction Support. While this may seem a sidetrack, I’m actually specifically using this product in testing and usability of MySQL, so ultimately everybody wins.
by ronald
I stand corrected on my earlier post Emulating Oracle Output Functionality (which I’ve updated) when I made a reference to MySQL catching up. That was not what I was implying, that MySQL had to catchup to Oracle. I was indeed making reference that MySQL is a 10 year old product, Oracle almost 30, and with a far greater historical R&D budget, MySQL as an organisation has for lack of a better term, entered the race at a later time. One could also argue it’s not a race, I’m just using this analogy.
For those that might have thought this in reading my earlier article, I’d encourage you to re-read my updated introduction for my clarification and correction.
The comment was made to me, “Oracle is very good at being Oracle” which is totally correct, and the “MySQL’s market now overlaps that of Oracle, and so there is competition.” Indeed Oracle, as a leader in the RDBMS industry has directed development in functionality and features.
We (being the MySQL product and Open Source) are indeed in competition (with other RDBMS products, both commercial and open source) , and will always be, however those that know me would also know that differing products serve to provide differing features and functionality in enterprise solutions. Some have strengths over others, and also weaknesses.
I am a strong advocate of larger enterprises currently using Oracle to embrace both Oracle and MySQL within an organisation for the benefit of serving differing purposes. I do not believe they are mutually exclusive. I believe there needs to be a looser coupling in the emerging marketplace. Of course, if a new company was starting tomorrow, and wanted to use Oracle, I would question the requirements as MySQL will serve the needs for most practical enterprise solutions. Had that question been asked 5 years ago, it would depend signficantly on the requirements, 10 years ago it would not have been a consideration.
by ronald
Given a new Linux Installation, the following is my recommendation for installation of MySQL for a experienced software developer giving flexibility in a development environment.
1. Under normal circumstances, most distros include MySQL either in a default server installation or on the distribution CD’s. You should first ensure MySQL is not installed.
2. All products can be downloaded from the MySQL Downloads page.
3. Download MySQL 5.1 Beta – Linux (non RPM, Intel C/C++ compiled, glibc-2.3), this product is close to production release and stable. This also includes both Server and Client as well as provides flexibility in installation location and multiple installations.
3. Download MySQL Administrator 1.1.6.
4. Download MySQL Query Browser 1.1.18.
5. Download MySQL 5.1 Manual
su - groupadd mysql useradd -g mysql mysql cd /opt tar xvfz mysql-5.1.7-beta-linux-i686-icc-glibc23.tar.gz ln -s mysql-5.1.7-beta-linux-i686-icc-glibc23 mysql tar xvfz mysql-administrator-1.1.6-linux-i386.tar.gz tar xvfz mysql-query-browser-1.1.18-linux-i386.tar.gz tar xvfz refman-5.1-en.html-chapter.tar.gz mv html-chapter mysql-manual-5.1 cd mysql echo "[mysqld] user=mysql basedir=/opt/mysql datadir=/opt/mysql/data port=3306" > my.cnf scripts/mysql_install_db cd /opt chown -R root /opt/mysql chown -R mysql /opt/mysql/data chgrp -R mysql /opt/mysql chown -R root /opt/mysql/bin cd /opt/mysql ./bin/mysqld_safe & ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h `hostname` -pnew-password password 'new-password' ./bin/mysqladmin -pnew-password shutdown echo "PATH=/opt/mysql/bin:/opt/mysql-administrator/bin:/opt/mysql-query-browser/bin:$PATH; export PATH" > /etc/profile.d/mysql.sh . /etc/profile.d/mysql.sh # Just for this session cp support-files/mysql.server /etc/rc.d/init.d chkconfig mysql.server on
The MySQL server will start automatically on System boot
To run the MySQL Client
mysql -uroot -pnew-password mysql
To run the MySQL Administrator
mysql-administrator
To run the MySQL Query Browser
mysql-query-browser
Now that I remember all this, perhaps RPM’s are the way to go, I just don’t like the structure in deployed files.