how to use replication to forward transactions

Normal replication goes from host A to host B. But using the mysql my.cnf directive log_slave_updates you can store and forward transactions from an intermediary log to another host. So in this example, B is a slave of A; B specifies log_slave_updates, and then C as a slave of B gets all transactions from both A and B. Not quite multi-master support.

I have used this when cutting over a new system where existing hosts X & Y are replicating and I want to add a new host that will set up a new pair A & B.

Hope this is useful.

corrupt frm files for innodb tables

I've encountered a new mysql error for many (all) tables when innodb was starved for memory. The error:

Incorrect information in file: '...frm'

and any attempt to repair the table failed.

Much consternation!

But this occurred not because the table was corrupt but because I tried to give too much memory to the innodb buffer pool.

The real culprit in the error log was

InnoDB: Error: cannot allocate 2516598784 bytes of InnoDB: memory with malloc!

InnoDB: by InnoDB 228832092 bytes. Operating system errno: 12

Reducing the innodb-buffer-pool allocation to a more sane level for the box eliminated the apparent table corruption.

database tables as log files

I have noticed over the years, a tendency of some developers to put logging information into database tables. Since the database is supposed to be the repository of corporate information and not a dumping ground, this is arguably not a good idea.

A better approach is to use flat files appropriate to your environment for logging. If you need them to be searchable either in or out of the application, then use a search engine technology (Lucene comes to mind) to index the log information and make it available to your consumers.

Quick and easy sql server to flat file

There are many times you need a flat file (csv for instance) to move from one db type to another. SQL Server bcp is just the ticket and combined with select into lets you create just what you want for those pesky data format translations.

The trick is to create a table using select into then use bcp to output it. Any data transformations you need to make (i.e., changing date formats) should be done when you build the temp table. In the simplest form the steps are:

1. select * into tempdb..mynewtable (This both creates mynewtable and puts the data into it.)
2. from a dos command line: bcp tempdb..mynewtable out mynewtable.csv -c -S server -U user -P password -t',' The -n flag tells SQL Server to just ouptut the data in character format.
3. do what you will with the csv file. Import it into a spreadsheet, load it into mysql using load data, or load it into Oracle using sqlldr.

So one issue you may run into, especially with dates, is that one system's format isn't necessarily the others. The easiest way to handle this is to make your target tempdb data column match what you need. For SQL Server just use the convert function and one of the many date flags. To do this you may have to create your tempdb table manually then simply insert data into it.

couldn't resist this picture

In case of network connectivty issues.....check your cable.

Why I love this job.

I'm just getting started using Oracle. So, naturally I need a nice visual client to work with. Even though I run a Mac it's easier to run this on my Parallels Windows/XP session due to VPN issues (don't ask). So, off I go to install a client.

Get Oracle Insta-client 11.01.

Get sqlplus too while I'm at it.

Find and change environment settings in Windows.

Get tnsnames.ora file from a unix box that I know works.

Test sqlplus. Yippee it works!

Get Toad.

Toad crashes; it won't support 11.x. Gotta downgrade to 10.x

Get another Oracle Insta-client 10.2

Change environment settings in Windows again.

Start Toad.

Uh oh. missing DLL.

Find DLL on web, download, install, reboot.

Yea! Toad works and connects.

Life should not be this hard.

database diagrams

At one time or another we have all been handed a new system and its related database(s). How do you wrap your arms around the entities and grasp the main concepts. For me, nothing beats an ERD and although Erwin and Microsoft Visio do good jobs at presenting these, the best product I've worked with for reverse-engineering a database is SchemaSpy. And it's free! As in beer. Check it out the next time you need a diagram. (I have no relationship with the author of SchemaSpy; I just think the product is really cool and wish I could write Java like this.

http://schemaspy.sourceforge.net/