Working with Fixed Length Outfiles using MySQL and PHP

Creating ASCII Fixed Length Outfiles using MySQL

1. Step 1. Create a FileSpecification Mysql table.
2. Step 2. Export your MySQL data using the FileSpecification Table as a map

Details:

Step 1. Create a FileSpecification Mysql table.

Create a File Specification Table:

CREATE TABLE `filespecification` (
`id` int(11) NOT NULL auto_increment,
`speccode` varchar(50) NOT NULL default '',
`fieldname` varchar(100) NOT NULL default '',
`startpos` varchar(10) NOT NULL default '',
`endpos` varchar(10) NOT NULL default '',
`length` varchar(10) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;

Manually populate it using PHPMYAdmin or your favorite gui MySQL client.

Step 2. Export your MySQL data using the FileSpecification Table as a map

** Details forthcoming ***

Load a FLat File Into an Array

** Details forthcoming ***
check out unpack() function in php

Other Notes

Though CHAR() (as opposed to VARCHAR()) data is stored fixed-length, it is retrieved from the database without the padding. So data type definitions themselves do not seem to help.

And metadata definitions of tables are not easily retrievable until MySQL 5.x, which have information schema’s.

From the forum below, the winning answer seems to be the RPAD() function. Use it when retrieving data from database in the SQL query thus:

SELECT rpad( test, 10, ‘ ‘ )
FROM `test`
WHERE 1

Full solution forthcoming…
mysql_query(“SELECT RPAD( FName, 11 ,’ ‘ ) , RPAD( LName, 18 ,’ ‘ ) “) ; //etc
Continue reading Working with Fixed Length Outfiles using MySQL and PHP…

A.4.2 How to Reset Root Password in MySQL

If you have forgotten the root user password for MySQL, you can restore it with the following procedure:

1. Take down the mysqld server – kill `cat /mysql-data-directory/hostname.pid`

2. Restart mysld with the –skip-grant-tables option.

3. Connect to the mysqld server mysql -h hostname mysql

4. FLUSH PRIVILEGES to enable grant tables

5. Change the root password.

Details/Notes of Steps

1. Take down the mysqld server -

Do not kill -9 The server’s process file runs in .pid format, and is usually in the MySQL database directory, so:kill `cat /mysql-data-directory/hostname.pid`
This procedure requires root access to your Unix server.
2. Restart mysld with the –skip-grant-tables option.

3. Connect to the mysqld server with

mysql -h hostname mysql

and change the password with a GRANT command.
4. FLUSH PRIVILEGES to enable grant tables

5. Change the root password.

source: paraphrased from p. 723 of MySQL Reference Manual, printed in 2002 for MySQL v. 4

Key notes

The book/docs mention at the bottom of instructions that you will get an error until you execute FLUSH PRIVILEGES. But they did not include this as a step Before using the GRANT command.
Important: Use the cli mysql client to do this. I tried to use phpmyadmin and it kicked me out after flush privileges and prompted for login – a reasonable security feature of phpmyadmin. But what you want to do is to be able to get access to your mysql server, then enable privileges, then change privileges so you can get in later.
Hope these instructions save someone else the headache.

MYSQL – export and email automation

Situation

You want to export data from a MYSQL table and email it in .CSV format on a regular schedule. This was on Darwin on Mac OS X server, but works on any similarly configured *Nix of course.

Solution

Build Query

Here I am exporting a CSV formatted file suitable for import into Excel.
SELECT * INTO OUTFILE '/tmp/weeklyupdates.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' from profiletable
WHERE (entereddt > DATE_SUB( curdate( ) , INTERVAL 7 DAY ) );

Save this sql command file as updateseloutfile.sql

Bash script to run the query:

#!/bin/bash
rm /tmp/updates.csv
DBS=`mysql -uUsername -pPassword mysqldbnamehere < /scripts/updateseloutfile.sql`

save the script as updatescript.sh and test everything by executing script:

./updatescript.sh

Email Results

Add to the shell script a line to email the results:

(cat /scripts/updatesplaintextemailmessage.txt; uuencode /tmp/updates.csv /tmp/updates.csv) | mail -b samplebcc@antioch--college.edu -s "Weekly Web Updates" toaddress@antioch--college.edu

This line also adds some email text stored in updatesplaintextemailmessage.txt. It encodes the updates.csv file and sends it as an attachment to toaddress@, bcc�ing samplebcc@.

So you end up with 3 small text files:

updateseloutfile.sql – the sql file that exports data
updatesplaintextemailmessage.txt – the plain text message for email
updatescript.sh – the shell script that does the work

Schedule Export

Now set up the cron scheduler to run the shell script weekly:

check crontab listing: crontab -l
edit crontab: crontab -e
and add line to run your script weekly: 00 03 * * 6 /scripts/updatescript.sh

That�s It! You now have weekly export of MYSQL data being emailed in .CSV format to designated email address. If this is of help to anyone else, I�d appreciate a quick note.

Learn more about crontab by typing: man crontab -s5 The regular man crontab does not have any good examples.

References

• Sending email with attachments on UNIX systems
• crontab
• crontab 5