- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - Linux
- >
- Re: Problem importing text files into a mysql data...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-01-2010 11:04 AM
тАО06-01-2010 11:04 AM
Problem importing text files into a mysql database
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-01-2010 11:44 AM
тАО06-01-2010 11:44 AM
Re: Problem importing text files into a mysql database
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-01-2010 12:16 PM
тАО06-01-2010 12:16 PM
Re: Problem importing text files into a mysql database
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-01-2010 10:34 PM
тАО06-01-2010 10:34 PM
Re: Problem importing text files into a mysql database
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-01-2010 10:38 PM
тАО06-01-2010 10:38 PM
Re: Problem importing text files into a mysql database
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-02-2010 06:19 AM
тАО06-02-2010 06:19 AM
Re: Problem importing text files into a mysql database
By the way, do not forget to assign points, that's the way we thank each other!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-02-2010 10:11 AM
тАО06-02-2010 10:11 AM
Re: Problem importing text files into a mysql database
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-02-2010 01:30 PM
тАО06-02-2010 01:30 PM
Re: Problem importing text files into a mysql database
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-04-2010 07:19 AM
тАО06-04-2010 07:19 AM
Re: Problem importing text files into a mysql database
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-04-2010 08:58 AM
тАО06-04-2010 08:58 AM
Re: Problem importing text files into a mysql database
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