DBA’s working in environments where MySQL database is for production critical applications are constantly pushed to their limits. Database Users and DBA’s are humans too. So, a dropped table here or a misconstrued MySQL statement there is not out of ordinary. Recovering from these kinds of user errors is extremely vital to production databases. Zmanda Recovery Manager (ZRM) for MySQL, is a good way to recover from user errors. Consider this example - You [DBA] find out that an user has made an unforced error which is affecting your production database. You further find out that this has happened in the last half hour. Lucky for you, you use ZRM for MySQL in your environment to make nightly backups. You spring into action! You first verify that a nightly backup was run. You then make an incremental backup now. Using ZRM for MySQL, you identify the point at which the user …

When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert "a" and "A" into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: "A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set."

Here is an example:
The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).

PLAIN TEXT CODE:

1.  
2. CREATE TABLE text1 (
3.   `text` varchar(50) character set latin1 collate latin1_general_cs NOT NULL …

As per this post on Devshed forums I'm pasting here some sample code (taken from Mike Hillyer's excellent "Accessing MySQL BLOB columns using Visual Basic 6"), hope it helps.
Code uses a simpler table structure and due to the fact that I didn't bother about reproducing the "autoincrement" field it will get you into troubles if trying to run the sample multiple times.
You'll notice that basically the only different thing is the connection string!

1. 'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING
2. Dim conn As ADODB.Connection
3. Set conn = New ADODB.Connection
4. conn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_2_0\examples\empbuild\test.fdb;UID=SYSDBA;PW D=masterkey"
5. conn.CursorLocation = adUseClient …

Pam @ Groklaw is tracking Novell's marketing efforts around SUSE Linux, post the Microsoft pact. Novell's UK office has started blanketing the inboxes of its customers with patent FUD:

The patent cooperation agreement enables Microsoft and Novell to give customers assurance of protection against patent infringement claims. It gives customers confidence that the technologies they use and deploy in their environments are compliant with the two companies’ patents.

As part of this agreement, Microsoft will provide a covenant not to assert its patent rights against customers who have purchased SUSE Linux Enterprise Server or other covered products from Novell, and Novell will provide an identical covenant to customers who have a licensed …

I've been needing this for some time; the ability to search for apt-get packages on Ubuntu when I don't know the name of the package. Turns out there's a corresponding utility for doing this:

shell> apt-cache search <search string>


You'll want to be sure your repository is up to date with apt-get update. Today I need DBI to get Perl connected to MySQL, but can't figure out what the apt-get package name is (I tried "DBI", "perl-DBI", "DBI-perl"). A search immediately gives me what I need:

shell> apt-cache search dbi
libdbd-mysql-perl - A Perl5 database interface to the MySQL database
libdbi-perl - Perl5 database interface by Tim Bunce
libxml-sax-perl - Perl module for using and building Perl SAX2 XML processors


And find that apt-get install libdbi-perl gets me right back on the road.

Ubuntu (in …

Finally, it's time to start putting MySQL 5.1.12-beta through the wringer. First order of business, convert the existing table schema to one that supports partitioning...

I made some minor changes to the configuration for partitioning, namely innodb_file_per_table and innodb_open_files. I set innodb_open_files to 1000 based on the tables and partitions I plan on supporting.

This is what the new table schema looks like with partitioning:

CREATE TABLE `network_daily` (
  `entity_id` int(11) NOT NULL default '0',
  `buyer_entity_id` int(11) NOT NULL default '0',
  `buyer_line_item_id` int(11) NOT NULL default '0',
  `seller_entity_id` int(11) NOT NULL default '0',
  `seller_line_item_id` int(11) NOT NULL default '0',
  `size_id` int(11) NOT NULL default '0',
  `pop_type_id` int(11) NOT NULL default '0',
  `country_group_id` int(11) NOT …

Tonight is a game night/potluck (aka have a large crowd of people descend on my house to play board games).

So what do I do while waiting for people to show up? Read RSS.

Tonight's find:
http://radar.oreilly.com/archives/2006/11/ten_things_i_wa.html

Tim is commenting on what people want in a cell phone. What do I find interesting in this?

Integrate with other non-phone communication methods (e.g. email and IM, for phones that don't support it), and use all the same metrics as in #1 above to give me an address book that reflects my true social network.

Somewhere back in the beginnings of the 90's I wrote a problem that sat in my .forward file to capture all of the email addresses that went through my email account. Why?

Because at the time I found I …

You are tired of writing your UDFs in C or never wanted to write them in C at all ? How about writing them in lua ?

LUA is

• easy to learn
• easy to embed
• easy to use

functions

As example we want to read the real startup time of the errorlog.

-- we are run with the permissions of the mysqld
--
-- let's try the read the "ready for connections" from the errorlog and look
-- for the last [Note]:
--
-- 061124 17:28:39 [Note] /usr/sbin/mysqld-max: ready for connections.

local f = assert(io.open(params[1], "r"))
local readysince = nil

while true do
        local line = f:read()

        if not line then break end

        local match = string.match(line, "^([0-9]+ [0-9:]+) %[Note%]")

        if match then
                readysince = match
        end
end …

I am happy to announce version 0.3 of mylvmbackup, a tool that performs consistent backups of a MySQL server's tables using Linux LVM snapshots.

Special thanks go to Fred Blaise, who contributed the majority of the new features that have been added to this new release:

• It is now possible to use an external configuration file /etc/mylvmbackup.conf to store the options. This is probably more convenient than having to pass a slew of options on the command line or having to hack the script itself to change the default values. This new feature requires the Config::IniFiles Perl module to be installed, a sample configuration file is included in the package.
• The logging to the console has been …

I am happy to announce version 0.3 of mylvmbackup, a tool that performs consistent backups of a MySQL server's tables using Linux LVM snapshots.

Special thanks go to Fred Blaise, who contributed the majority of the new features that have been added to this new release:

• It is now possible to use an external configuration file /etc/mylvmbackup.conf to store the options. This is probably more convenient than having to pass a slew of options on the command line or having to hack the script itself to change the default values. This new feature requires the Config::IniFiles Perl module to be installed, a sample configuration file is included in the package.
• The logging to the console has been …