mysqldump issue

[nycepete]

i have fedora core 4 installed. i have a script that extracts data from one table to the next. now this script works fine in windows but in linux it writes the file but theirs no data in it???

much help needed thanks

/usr/bin/mysqldump -h localhost -u root --tab=/usr/etc/scripts/ --fields-terminated-by=";" --lines-\terminated-by="\n" --no-create-info ehpadmin Referral_Authorization

 

[TonyGroves]

That syntax works for me in Windows and Linux (Debian). (I'm assuming the backslash embedded in --lines-\terminated-by is a typing mistake!). Are you connecting to localhost in both cases?

 

[nycepete]

Yes localhost in both cases. the reason why i have the \ is becuas eim goign onto a new line. DO i need that ????
im new to linux. So right now it just writes a blank file, no data in it??? ANy reasons why???? or do i have it set up wrong?

 

[TonyGroves]

In a Linux shell, you don't have to break a long command-line, but if you want to break it, you would use a backslash before the carriage-return.

Is your table populated? What do you get when you do "SELECT COUNT(*) FROM ehpadmin.referral_authorization"?

Do you get any error messages?

 

[nycepete]

i have one record in their this is what my file reproduces:

using
/usr/bin/mysqldump -h localhost -u root --no-create-info ehpadmin Referral_Authorization > Referral.txt

almost their lil help please thanks

-- MySQL dump 10.9
--
-- Host: localhost Database: ehpadmin
-- ------------------------------------------------------
-- Server version 4.1.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `Referral_Authorization`
--


/*!40000 ALTER TABLE `Referral_Authorization` DISABLE KEYS */;
LOCK TABLES `Referral_Authorization` WRITE;
INSERT INTO `Referral_Authorization` VALUES ('MOMC8779394','AMAYA, JANNETH','F','19781203','11016 KITTRIDGE ST APT 1','N HOLLYWOOD','CA','91606','8187865951','MOMCG000','RADIAL COLLATERAL LIGAMENTSPRAIN','8410','','','','RETROACTIVE','20050609','YES','MARK','AMICO','8187270142','8188855480','PHYSICIAN','','','HUANG, BENJAMIN','320 S GARFIELD AVE SUITE #102','ALHAMBRA','CA','91801','6262814487','','RADIOLOGY','','','','','','','FOLLOW UP VISIT','','','','','','C90376','','','','RABIES IG, HEAT TREATED','WEDNESDAY, 9/28/2005','','PETE@WEST.COM','E01PRF059U002900','PETE','953864875','A');
UNLOCK TABLES;
/*!40000 ALTER TABLE `Referral_Authorization` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

[nycepete]

if you look at the above thats what im getting. In windows i use the same script and i get a flat csv file of data???? dont understand why im getting all that code


this code dosent works, it dosent even write the file???


/usr/bin/mysqldump -h localhost -u root --tab=/usr/etc/scripts/ --fields-terminated-by=";" --lines-terminated-by="\n" --no-create-info ehpadmin Referral_Authorization

 

[TonyGroves]

As I said, it works for me. But anyway, you might like to try a different approach. You can achieve the same result by using SQL, and it saves having to remember the syntax for a separate program.

You could try:
[tt]
SELECT *
INTO OUTFILE '/usr/etc/scripts/refauth.csv'
FIELDS TERMINATED BY ';'
FROM ehpadmin.referral_authorization
[/tt]

You can do this from the command line by saving the SQL to a file, and running:
[tt]
mysql -u root <myfile.sql
[/tt]
or without using a separate SQL file:
[tt]
mysql -u root -e "SELECT * INTO ..."
[/tt]

 

[nycepete]

Ok i cant get it to work here is my full script. I just want to write my table data to a csv file. Could i have a example based on my full script??? thanks

#!/bin/bash
/usr/bin/mysql -h localhost -u root ehpadmin < /usr/etc/scripts/script.sql
/usr/bin/mysqldump -h localhost -u root ehpadmin > backup.txt
/usr/bin/mysqldump -h localhost -u root --tab--fields-terminated-by=';'
--lines-terminated-by="\n" --no-create-info ehpadmin Referral_Authorization > Referral.txt
/usr/bin/mysql -h localhost -u root ehpadmin < /usr/etc/scripts/truncate.sql

 

[TonyGroves]

If my latest examples won't work, then it looks like a file permissions issue. Are you able to write into /tmp , or your home directory? Alternatively, it could be a MySQL privileges issue, but that's unlikely since you're using the root account.

 

[nycepete]

well this is my output and tony thanks for helping hopefully we can solve this. this is what the output....

why is it showing me all this garbage??? im using
i have full privildges

code:
#!/bin/bash
usr/bin/mysqldump -h localhost -u root --tab--fields-terminated-by=';'
--lines-terminated-by="\n" --no-create-info ehpadmin Referral_Authorization > Referral.txt

output:
-- MySQL dump 10.9
--
-- Host: localhost Database: ehpadmin
-- ------------------------------------------------------
-- Server version 4.1.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `Referral_Authorization`
--


/*!40000 ALTER TABLE `Referral_Authorization` DISABLE KEYS */;
LOCK TABLES `Referral_Authorization` WRITE;
INSERT INTO `Referral_Authorization` VALUES ('MOMC8779394','AMAYA, JANNETH','F','19781203','11016 KITTRIDGE ST APT 1','N HOLLYWOOD','CA','91606','8187865951','MOMCG000','S','S','','','','RETROACTIVE','20050605','YES','MARK','AMICO','8187270142','8188855480','PHYSICIAN','','','GERBERG, ERWIN','PO BOX 190','SIMI VALLEY','CA','93062','8002876838','','RADIOLOGY','','','PREGNANCY CARE','','','','','','','','','','F','','','','F','THURSDAY, 9/29/2005','','PETE@WEST.COM','E01PRF059V000900','PETE','953864875','A');
UNLOCK TABLES;
/*!40000 ALTER TABLE `Referral_Authorization` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

[TonyGroves]

That's not garbage. It's SQL code which can be used to restore the table. It's exactly what mysqldump is designed to produce. If all you want is a CSV file, then you would be better off forgetting about mysqldump and sticking with the SQL solution I posted; there's no reason why it wouldn't work, privileges and permissions aside.

 

[nycepete]

ok going to try it. but why in windows does it just give me the data usinf mysqldump and in linux it gives me everything???

 

[nycepete]

tony almost their. This writes no file????

my.sh file
#!/bin/bash
/usr/bin/mysql -h localhost -u root < /usr/etc/referral.sql


referral.sql
select * into outfile '/usr/etc/referral.txt' fields terminated by ';' from ehpadmin.Referral_Authorization

 

[ericbrunson]

mysqldump in windows cannot possibly output a CSV file like you describe.

 

[nycepete]

well this is what i have
c:\mysql\bin\mysqldump -h localhost -u root --tab=C:\temp\referrals\ --fields-terminated-by=";" --lines-terminated-by="\n" --no-create-info ehpadmin referral_authorization

and this is what it returns:
MOMC0394651;MANOKIAN GHARGH, ARSINEH;F;19830811;26 RIBVERA PKY;BURBANK;CA;91501;8182609474;MOMCG000;RADICULAR CYST;5228;;;;RETROACTIVE;20050823;YES;KATHY;CAIRO;8187270142;8188855480;PHYSICIAN;;;AD

 

[ericbrunson]

Okay, I totally put my foot in my mouth. Ignore my last comment.