Tip

Bug tracking, remote databases and workarounds in MySQL

Dealing with data in MySQL 5.0 can require extensive coding and patience. Take a helping hand from MySQL expert Mike Hillyer, an IT pro with MySQL Professional Certification and the top-ranked MySQL expert at Experts-Exchange.com.

In this tip, Hillyer explains how to connect to backup remote databases without using GUIs, recommends an open source solution database management solution for companies interested in superior parsing of MySQL databases and describes a workaround for a bug found in reproducing binary values in BIT columns.

Is there an open source application that companies could use to better parse MySql databases? What should a company that would like to be able to scan, search and sort log files down to a single event using keyword search function, as well as exporting data into Excel or a comparable format look for?

Mike Hillyer: MySQL Administrator does provide functionality for searching the log files for keywords and saving to a text format which could be imported into Excel.

You may also find the Practical Query Analyzer project to be of use. It is a multi-database query log analyzer that can identify trends in your logfiles.

Is there a way to write a SQL statement that will override (set to null) all foreign keys for loading a test development database?

Hillyer: When loading test data, you can issue the following statement to shut off foreign key checks in your session:

SET FOREIGN_KEY_CHECKS=0;

    Requires Free Membership to View

When you are finished loading your data, issue the following statement to turn foreign key checking back on:

SET FOREIGN_KEY_CHECKS=1;

Remember to have all foreign key requirements met before turning foreign key checks back on.

How can I set up my MySQL 5.0 database so that I get e-mail notification each time a record is added? Is there a way to do is using a shell or PHP script?

Hillyer: The simplest way is to add an e-mail send to the application adding the data. In MySQL 5.0, you could use a combination of a trigger and a user-defined function (UDF) to do this, with the UDF calling some form of C function for sending email.

I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE to load data into a table with large BIT columns. How do I load data so the value is correct with reproduceable binary values in the BIT columns?

More on MySQL:

Tips for tackling MySQL 5.0

MySQL, your SQL

Hillyer:This is being looked into. In the meantime, it appears that using a function such as PHP's pack() function to write binary values for the bit data is a workaround. You may wish to keep an eye on the bug tracking system to find out what progress is being made. Try searching for 'load data bit'.

What is the best method of backing up MySQL databases on Red Hat Enterprise servers without using a GUI for this process?

Hillyer: This depends on server activity and other needs. For servers with small tables and low activity, the mysqldump utility should work fine.

Users with larger tables may want to look at the mysqlhotcopy utility

Users with very active servers may want to look at solutions such as LVM snapshots or configuring a replication slave for backup purposes.

How do I connect to the remote database from a local VB6.0/MySQL application?

Hillyer:Odds are good that your Web hosting provider has protected the MySQL server with a firewall, so you may need to contact your provider and ask about SSH access to the server. Assuming you can be granted SSH access, look at this article for information on connecting via SSH tunnels.

Examples of connecting can be found in the various sample codes here.

This was first published in March 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.