Interesting question for MySQL DBA's: do you prefer using the mysql cli interface and know the SQL statements to execute in order to do your admin work like creating databases or do you prefer using the mysqladmin interface? Personally I prefer using the mysql cli interface and executing things like 'CREATE DATABASE foo' rather than 'mysqladmin create blah' - I suppose I like the SQL queries over remembering the other way of doing things.
I suppose, every now and again one gets to rant about something. As George loves to say of late when I rant on about stuff at the office and he's within earshot distance:
Would you like some cheese with your w(h)ine?
So I've been wondering why MySQL AB reverted a change which was in the Community Edition for months because they did not like the patch getting added to the Enterprise Edition.
Am I wrong to assume that the Community Edition is like the bleeding edge version of MySQL where various patches gets included and then move on over to the Enterprise Edition?
Picked up this from LiveJournal's Lisa.
Another gem from Jeremy Cole.
The Progress_rows number indicates progress within a given step of executing the query. For instance, if you run a SELECT with a GROUP BY that can’t be done using an index, you will see two cycles of Progress_rows: once with a State of “Copying to tmp table” and once with “Sending data".
One thing I like about the patch is getting an idea how far MySQL is in processing a query.
Peter Zaitsev lets us know where to find the MySQL Enterprise edition. He also mentions that he chatted to Monty regarding the lack of releases of the Community Edition. The short answer is grab the source tarballs from the MySQL FTP Server, and you will have to compile them yourself as MySQL are not distribution the binaries anymore.
As you might noticed there are no recent MySQL Community versions available for download from MySQL Download Area This applies both to binaries (which is expected with new polices) but also to the source files which were promised to be available.
So what is if you would like to use recent MySQL code while staying with community version ? I chatted with Monty on this topic today.
Some MySQL links from The Schwartz:
The November 2006 meetups for the Cape Town PHP Users Group and the Cape Town MySQL Users Group are going to be happening on the Thursday the 2nd of November. Please RSVP to either of the meetups or both if you are going to attend.
Interesting to hear that the Postgresql Project, who claim to have the worlds most advanced open source database, use MySQL to power their website!
As embarressing it is to say, we've been using the MySQL version of phpAdsNew for almost a year now ... its the only one we could find that could actually keep up with the # of hits that the web sites are getting :( Even with pg_autovacuum running ...
Obviously, it wasn't something I particularly wanted to *advertise* :(
Another trick which Kai Voigt showed is setting a prompt with MySQL:
Edit your ~/.my.cnf file and add the following under your [client] section:
prompt=(\u@\h) [\d]>\_
Which shows which username and host you have connected to the MySQL server as as well as the database you have connected to:
% mysql -u *snip* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6845008 to server version: *snip*Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
(*snip*@localhost) [(none)]> connect mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -AConnection id: 6845009
Current database: mysql(*snip*@localhost) [mysql]>
Kai Voigt demoed how to get MySQL Cluster up-and-running this afternoon. Notes are available here.
Little bug with MySQL 5.0.X and PHP 5.1.4. In previous versions of PHP and MySQL < 5.0.0 you used to be able to set an environment variable called "TZ" to specify your timezone:
<?php
putenv("TZ=Africa/Johannesburg");
?>
But with MySQL 5.0.0 or later one needs to specify your timezone as follows (note in this example I'm using PEAR::DB):
<?php
/**
* Override the timezone for our MySQL session
*/
$result = $dbh->query("SET @@session.time_zone='Africa/Johannesburg'");
if (PEAR::isError($result)) {
die ($result->getMessage());
}
?>
More information about this can be found on the MySQL Time Zone Support page.
Via phpcult:
Quite a useful gem for getting the MySQL client (cli) to use less for paging output. Very cool :)
mysql> \P less PAGER set to 'less' mysql> select foo,bar from table\G
Interesting posts by Chris and Ilia on their respective blogs.
In The addslashes() Versus mysql_real_escape_string() Debate Chris starts discussing the Google XSS exploit and then goes on to explaining about character set encoding and how one particular character set allows for this exploit:
Last month, I discussed Google's XSS Vulnerability and provided an example that demonstrates it. I was hoping to highlight why character encoding consistency is important, but apparently the addslashes() versus mysql_real_escape_string() debate continues. Demonstrating Google's XSS vulnerability was pretty easy. Demonstrating an SQL injection attack that is immune to addslashes() is a bit more involved, but still pretty straightforward.
In GBK, 0xbf27 is not a valid multi-byte character, but 0xbf5c is. Interpreted as single-byte characters, 0xbf27 is 0xbf (¿) followed by 0x27 ('), and 0xbf5c is 0xbf (¿) followed by 0x5c (\).
How does this help? If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is considered to be a single character, not two. Oops, there goes the backslash.
Ilia then goes onto writing in his piece mysql_real_escape_string() versus Prepared Statements he goes more into detail about the exploit.
Chris has written a compelling piece about how the use of addslashes() for string escaping in MySQL queries can lead to SQL injection through the abuse of multibyte character sets. In his example he relies on addslashes() to convert an invalid multibyte sequence into a valid one, which also has an embedded ' that is not escaped. And in an ironic twist, the function intended to protect against SQL injection is used to actually trigger it.
The problem demonstrated, actually goes a bit further, which even makes the prescribed escaping mechanism, mysql_real_escape_string() prone to the same kind of issues affecting addslashes(). The main advantage of the mysql_real_escape_string() over addslashes() lies in the fact that it takes character set into account and thus is able to determine how to properly escape the data. For example, if GBK character set is being used, it will not convert an invalid multibyte sequence 0xbf27 (¿’) into 0xbf5c27 (¿\’ or in GBK a single valid multibyte character followed by a single quote). To determine the proper escaping methodology mysql_real_escape_string() needs to know the character set used, which is normally retrieved from the database connection cursor. Herein lies the “trick”.
Various sources has picked up on the story of Oracle's purchase of Innobase OY for an undisclosed sum of money. According to Jeremy Zwadony:
As reported in several sources (Slashdot, InfoWorld, AP on Yahoo, Reuters), Oracle has acquired Innobase Oy for an undisclosed sum of money. This appears to be a strategic move by Oracle to put MySQL between a rock and hard place.
Innobase is the company that provides the underlying code for the InnoDB storage engine in MySQL. It's the de-facto choice for developers who need high concurrency, row-level locking, and transactions in MySQL. For many years now, MySQL AB and Innobase Oy (founded by Heikki Tuuri) have worked closely together to make that technology a seamless part of MySQL.
Like all of the MySQL code, InnoDB is dual licensed. That means you can freely use it under the GPL or buy a license for it if your usage would violate the GPL.
MySQL's public reaction right now isn't the "holy f$@%ing shit!" that likely occurred internally. Kaj Arno, MySQL's VP of Community Relations, sent out a message to many MySQL users today titled " MySQL AB Welcomes Oracle to the FOSS Database Market".
It is quite interesting seeing Oracle start purchasing an Open Source Database Engine writing company.
MySQL also issued a press release
"This announcement represents further validation of the open source movement. The beauty of open source software and the GPL license is freedom. As with all MySQL code, InnoDB is provided under the GPL license, meaning that users have complete freedom to use, develop, and modify the code base. We are pleased to see even broader industry acceptance of open source database technology. This also means that database developers now have even greater flexibility to use MySQL and Oracle in the same environment.”
Mike Hillyer from MySQL AB mentions on his weblog that there can only be one database:
Just a quick tip: let's say you are trying to restore a backup done with mysqldump that contains multiple databases, but you only want to restore a single database.
Start my launching mysql client with –one-database
mysql -u root -p --one-database mydatabase
Then use the SOURCE command:
source ~/multidatabase.sql
The –one-database option forces the mysql client to ignore statements intended for any database other than the one you specified when you launched the client, allowing you to restore your database without affecting any other databases.
Quite a useful little tip as one no longer needs to manually figure out what queries you need to recreate a database from a dump of all databases in a mysql dump.
I'm busy spending time adding more features to my PHP Application Framework called the Tshukudu Application Framework which currently is quite a liteweight PEARish framework for PHP4 and PHP5. It's moving towards dropping PHP4 in the near future.
I've spent some time fiddling with the idea of caching database query results to disk instead of to a memcached servers when in a shared hosting environment to reduce load on a database server when looking up data which does not change very often. By caching database queries, it reduces the amount of work the database has to do to look up data considering the larger the dataset, the more data has to flow between MySQL and PHP.
Quite surprisingly I stumbled across an article titled Caching PHP Programes with PEAR on O'Reilly written by Sebastian Bergmann. Interesting read none the less.
So I created a class called DB_Cache which currently just extends PEAR Cache as well as keeping a DB connection open for sending queries to. The Cache_Memcached connects to Memcached servers to retrieve and store data while DB_Cache (should more likely be called Cache_DB_File in reality) caches database result sets to a file on the filesystem via Cache (which it extends).
A couple ideas behind the framework is to provide functionality for speeding up PHP sites by having different levels of caching within a PHP application such as caching content from remote servers, caching database resultsets, etc.
Utilisation of the code looks something like:
<?php
require_once 'Tshukudu/DB/Cache.php';
$db_cache = new DB_Cache;
$users = $db_cache->query("SELECT * FROM users LIMIT 0,10", 'getall'));
Ugly but needs a slight rewrite to rather extend DB_MySQL in mycase instead of Cache.
Dave Child has released another useful cheat sheet for MySQL.
The MySQL cheat sheet is designed to act as a reminder and reference sheet, listing useful information about MySQL. It includes a list of the available functions in MySQL, as well as data types. It also includes a list of MySQL functions available in PHP, and a list of useful sample queries to select data from a database.
Jim Winstead, Jr. brings up a point which deals with the subject of bad code readability which Neil has brought up previously.
It's the simple things like taking the time to make your programming easier. For example Jim shows us the following example:
$query = "SELECT id,name,url,rss,md5sum,method,updated AS up,"
. " UNIX_TIMESTAMP(lastchecked) AS lastchecked,"
. " UNIX_TIMESTAMP(updated) AS updated"
. " FROM blogs "
. " WHERE updated > NOW() - INTERVAL 10 MINUTE AND method = 0"
. " ORDER BY up DESC"
. " LIMIT 10";
Which he goes onto mention that he is now doing it like:
$query= "SELECT id,name,url,rss,md5sum,method,updated AS up,
UNIX_TIMESTAMP(lastchecked) AS lastchecked,
UNIX_TIMESTAMP(updated) AS updated
FROM blogs
WHERE updated > NOW() - INTERVAL 10 MINUTE AND method = 0
ORDER BY up DESC
LIMIT 10
";
Which makes it easier to copy and paste into the mysql command line utility. I'm known for having extremely long lines of code for SQL queries when I was programming in PHP back in the day. Also in certain ways it can make it worse when you have long long lines which you are trying to debug and are unable to figure out quickly what your 900+ character SQL query is doing!
A line like:
$categories = $dbh->getall ("SELECT directory_company_category_map.company_id, directory_categories.id AS category_id, directory_sub_categories.id AS sub_category_id, directory_categories.category, directory_sub_categories.sub_category_name FROM directory_categories LEFT JOIN directory_sub_categories ON directory_sub_categories.category_id=directory_categories.id LEFT JOIN directory_company_category_map ON directory_company_category_map.category_id=directory_categories.id LEFT JOIN directory_companies ON directory_companies.id=directory_company_category_map.company_id WHERE directory_company_category_map.category_id=directory_categories.id AND directory_sub_categories.category_id=directory_categories.id AND directory_company_category_map.sub_category_id=directory_sub_categories.id AND directory_company_category_map.company_id='" . $listings[$i]['id'] . "'");
is quite a bit to process ;) It looks way better like:
<?php
$categories = $dbh->getall ("
SELECT
directory_company_category_map.company_id,
directory_categories.id AS category_id,
directory_sub_categories.id AS sub_category_id,
directory_categories.category,
directory_sub_categories.sub_category_name
FROM directory_categories
LEFT JOIN directory_sub_categories
ON directory_sub_categories.category_id=directory_categories.id
LEFT JOIN directory_company_category_map
ON directory_company_category_map.category_id=directory_categories.id
LEFT JOIN directory_companies
ON directory_companies.id=directory_company_category_map.company_id
WHERE
directory_company_category_map.category_id=directory_categories.id AND
directory_sub_categories.category_id=directory_categories.id AND
directory_company_category_map.sub_category_id=directory_sub_categories.id AND
directory_company_category_map.company_id='" . $listings[$i]['id'] . "'
");
?>
It's highly recommended to not run your MySQL Database server on the same box as your webserver.
Now there is a new HOWTO on the MySQL devzone.
One would most likely need to use a word replace to make more sense of the document as the terminology is muddled up inbetween the HOWTO.
HOWTO set up a MySQL Cluster for two servers (three servers required for true redundancy)
Should read "HOWTO set up a MySQL Cluster for two database servers (three servers required for true redundancy)"
This HOWTO was designed for a classic setup of two servers behind a loadbalancer. The aim is to have true redundancy - either server can be unplugged and yet the site will remain up.
End of the sentence should most likely read "the database will remain up."
If you are running FreeBSD and prefer to using ipfw make notes of which network cards are plugged into your firewall. On my firewall box I have four network cards. Two of these network cards are realteks (rl0 and rl1) and the other two are actons (dc0 and dc1):
I've hacked together a script which closes commenting and allowing trackback pings to blog entries that are older than 15 days. You can view it over here.
This is a early christmas present. It was written using PHP and PEAR DB (which should be in your include_path).
I was trying to get Kasia's perl based comment closing script to work but it did not and I was not in the mood for perl a few days ago and I hacked up the following php script.
It's been done by others before (Jeremy has it on his blog, mine is better cause it's longer!) but in the spirit of "not invented here" I wrote my own script which closes MT entries to comments and pings.
The idea is to cron it and forget about it and it has worked in that capacity well enough for the last few weeks.. so if anyone wants to use it (or they just like to amuse themselves by reading perl written by a Java programmer) the code is available here.
Remember you need to change the database connection details and you are good to do.
Brad has written his first MySQL patch which shows the last binlog and the event position. Really handy.
MySQL are moving in the right direction with their FLOSS license changes. The latest version of the FLOSS exception has apparently made it's rounds of review by the community as well as the MySQL lawyers and the MySQL team.
The FLOSS Exception is an exception to the terms and conditions of the GPL licensing for the MySQL clients that allows for greater compatibility between the GPL and other common Free Software/Open source licenses.
Yesterday I received my copy of the MySQL Certification Study Guide which I'm currently reading.
This book prepares one for the MySQL certification for both the MySQL 4 Core Certification and MySQL 4 Professional Certification.
Found a irritating 'show stopper' in MySQL 4.0.18 which causes replication to stop. Submitted a bug to the MySQL team to debug and fix. Hopefully it can be resolved shortly else I'll need to make a new plan to go and run the old script from Mark Jeftovic from easyDNS which was hacked up to copy the table contents to a new table and rename tables and whatnot.
I was desperate today to get more bang for the buck from my hardware, esp. seeing that various Full Text Search queries have been taking ages to run considering that the database table where these entries reside has exploded in size over the past couple of weeks, and that I've been way too busy coding php and writing way too much HTML for my liking. Being a PHP coder HTML was something I last really looked at while working at a previous employer. I have also written two CMS's when I was running my own ISP, which was the bomb when it came to CMS's, as it did everything which I required it to do and more.
Noticed that MySQL AB the developers of the MySQL Database server have addressed their open-source license problem.
Been taking a look at the MySQL Administrator GUI, which I've downloaded from the MySQL website.
According to good 'ol Yahooligan, Jeremy Zawodny, he is claiming that "God uses MySQL".
James Cox has placed a chapter about MySQL replication online for a Wrox book that was discontinued. It is available over here under the Creative Commons License.
I was taking a quick read over at Sterling's blog, when I noticed that he brought up some very interesting points regarding the content on the frontpage of the MySQL website.
Continue reading "Sterling has good comments over MySQL.com" »
