topic Re: Problem importing text files into a mysql database in Operating System - Linux

Problem importing text files into a mysql database

Andrew Kaplan — Tue, 01 Jun 2010 18:04:24 GMT

Hi there --

We have an archive server where the administrator, via a web interface, can import text files into a mysql database. The files are typically local to the machine. The operating system of the server is CentOS 5.4, and that of the mysql database is 5.0.77.

The database was originally on another server which was replaced by the one that is currently in use. The migration of the database from the older server involved creating a dump file, copying the file to the new machine, and importing it into the database. The version of the mysql database on the previous server was 5.0.45.

The problem that has started to occur is the following: When the administrator goes through the motions of importing a text file to the database, the text does not get imported even though there are no error messages appearing on-screen or in the mysqld.log file. The administrator can see what is already in the database, but he cannot add to it.

The web interface to the database utilizes perl modules and cgi files. I ran the cgi files from the command line to see if there were any error messages. There were none. The perl modules that were on the previous server were copied to the new server with the permissions kept intact. I also verified the user and database permissions on the new server match those on the older server.

I am at a loss as to why this is occurring, and I would appreciate any help I can get on this. I have enclosed the files that are involved with the importation of the text file into the database as a zip file attachment.

If there is need of further information, please let me know. Thanks in advance.

Re: Problem importing text files into a mysql database

Gerardo Arceri — Tue, 01 Jun 2010 18:44:39 GMT

Why instead of manually inserting the data you give mysqlimport a try,
http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

I've used it in the past to import data into live databases with great success.

If you still want to pursue your current approach i strongly suggest you make your perl script output the insert statements before running it.
Since you haven't supplied a sample text file it's hard to tell if you have an error on the parsing code.

Regards

Re: Problem importing text files into a mysql database

Andrew Kaplan — Tue, 01 Jun 2010 19:16:50 GMT

Hi there --

I tried your suggestion, and unfortunately I am getting a syntax error when I run the command.

The command syntax that I am using is the following:

mysqlimport archivedicom /home/ahk/RODICOM_04may10-1.txt;

Where archivedicom is the name of the database, and full pathname to text file in question immediately follows. I also tried this command using just the filename of the text file instead of including the entire path to the file.

Whenever the command is run, the following error message appears:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqlimport archivedicom /home/ahk/RODICOM_04may10-1.txt' at line 1

I checked the mysql homepage, and it appears my syntax is correct. What am I missing? thanks.

Re: Problem importing text files into a mysql database

Matt Palmer_2 — Wed, 02 Jun 2010 05:34:31 GMT

Hi,

I've just checked the mysql site and you get a error 1064 when you use reserved words. Have any of you guys looked through the import to check if something like that has happened?

hope that helps

regards

Matt

Re: Problem importing text files into a mysql database

Matt Palmer_2 — Wed, 02 Jun 2010 05:38:42 GMT

Hi,

also have you checked that you arent running more than one version of mysql on the box?

Is your path statement pointing to the correct binary? sometimes these errors are driven by version mismatches of mysql?

regards

Matt

Re: Problem importing text files into a mysql database

Gerardo Arceri — Wed, 02 Jun 2010 13:19:31 GMT

Can you provide a sample of two or three lines so we can take a look ? You might need to tweak mysqlimport so the file format is properly recognized.
By the way, do not forget to assign points, that's the way we thank each other!

Re: Problem importing text files into a mysql database

Andrew Kaplan — Wed, 02 Jun 2010 17:11:23 GMT

Hi there --

Thanks for everyone's reply. Regarding the question as to how many instances of the database are running on the server, the output shown below appears to show that two associated instances for one database are running at a given time on the server:

root 16008 1 0 Jun01 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql

mysql 16058 16008 0 Jun01 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

I stopped and restarted the mysqld daemon, and both instances came up.

I tried running the mysqlimport utility again using the following syntax:

mysqlimport --local --user xxx --password xxx archivedicom backupdicom.txt;

made sure to change the name of the file from RODICOM_04may10-1.txt to backupdicom.txt. The latter name reflects the name of the table within the archivedicom database. The command was run with just the filename as well as the full pathname: /home/ahk/backupdicom.txt.

I logged into the database as root and another user that has full access to the database. The command was run while either connected to the particular database, archivedicom, or when just logged into the database. The mysqlimport utility would not run unless I put the semi-colon at the end of the line.

The command failed in all instances with the 1064 error appearing on-screen.

As requested I have enclosed an abridged version of a text file that would normally be imported into the database via the cgi and perl scripts. If you need further information, please let me know.

Thanks for the help.

Re: Problem importing text files into a mysql database

TwoProc — Wed, 02 Jun 2010 20:30:35 GMT

Andrew, I've not run that importing tool before, but I've lots of others. Where, on the command line are you specifying which file is the control file for the load? This file should be specifying fields, lengths, and table names and column names to import into.

You need a control file, or mapping file that specifies what the mysql import tool is to do, and how, when, and where to put it. My guess is that your sql error is because the import tool is trying to read your data file for mapping/control/meta information. But, that's just a guess.

Re: Problem importing text files into a mysql database

Andrew Kaplan — Fri, 04 Jun 2010 14:19:39 GMT

Hi there --

I decided to start from scratch, and to that end, I rebuilt the new server, and installed the mysql database onto it. The configuration of the server has it running Fedora Core 13 32-bit with the MySQL server 5.1.47 version.

I have the dump files available from the existing database server. My plan is to do the following:

1. Create the databases along with the tables within them.
2. Import the dumpfiles into the respective databases.
3. Create a test file that will be used to determine if additions can be made to the databases.

I have the webmin utility available on both servers, and I can use it to reference the user and database permissions on the original server when setting up the same on the new server.

I was planning on using all command lines to accomplish this task, but I wanted to know if there are recommended GUI's that will accomplish steps one and two.

Re: Problem importing text files into a mysql database

TwoProc — Fri, 04 Jun 2010 15:58:41 GMT

A) I like your plan.

B) Re: Gui tools - I see them for Linux (but have not used them) in the standard repositories, the question becomes, do they exist for HPUX? I don't know.

Running apt-cache search mysql | grep -i admin on my Linux system reveals a few interesting possibilities:

kmysqladmin - Kde graphical frontend for mysql servers
mysql-admin - GUI tool for intuitive MySQL administration
phpmyadmin - MySQL web administration tool
tora - A graphical toolkit for database developers and administrators

If you can't find/load these tools for HPUX, I'd bet that it's possible to use these from a Linux workstation and just point it at the MySql server running on the HPUX host server.

Also search google.com for "HPUX Porting Archives" for more assistance in finding many tools / utilities that don't seem to be easily available for HPUX. It's not ALL there, but there is a LOT of good stuff in those repositories.

Good Luck