RE: mysqlimport --use-threads / mysqladmin processlist

[Rick James]

I'm skeptical that use-treads can every be very effective.

What order are the rows in?  They are probably in PRIMARY KEY order, which 
means that the INSERTing threads will be fighting over similar spots in the 
table.

Is it I/O bound when it is single-threaded?  If so, then there can't be any 
improvement with use-threads.

etc.

Suggest you file a bug with bugs.mysql.com.  If nothing else, the documentation 
should say more than it does.

 -Original Message-
 From: Róbert Kohányi [mailto:kohanyi.rob...@gmail.com]
 Sent: Tuesday, July 24, 2012 10:52 AM
 To: mysql@lists.mysql.com
 Subject: mysqlimport --use-threads / mysqladmin processlist
 
 I'm in the middle of migrating an InnoDB database to an NDBCluster. I
 use mysqldump to first create two dumps, the first one contains only
 the database schema, the second one contains only tab delimited data
 (via mysqldump --tab). I edit my InnoDB schema here and there
 (ENGINE=InnoDB to ENGINE=NDB, etc.) import it and after this I import
 the InnoDB data *as is* using mysqlimport.
 
 I use it like this:
 
 mysqlimport --local --use-threads=4 db dir/*.txt
 
 (dir of course cotains the tab delimited data I dumped before.)
 
 The import starts, and I check its progress via mysqladmin, like this:
 
 mysqladmin --sleep=1 processlist
 
 this is what I see: http://pastebin.com/raw.php?i=M23fWVjc
 
 Only a single process seems to be loading my data. Is this what I
 *should* see, or, in my case using 4 threads, should I see four
 processes? I'm not asking which one will be faster, I'm just simply
 confused because I don't know what to expect. If I start four different
 mysqlimport processes, each one importing different files, then I can
 see four different process in the mysql processlist.
 
 If it's matters, here is my server version (I use the official
 binaries).
 Server version: 5.5.25a-ndb-7.2.7-gpl MySQL Cluster Community Server
 (GPL)
 
 Regards,
 Kohányi Róbert
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: mysqlimport --use-threads / mysqladmin processlist

[Róbert Kohányi]

Yes, the rows are in primary key order, however each row contains
specific integer primary keys; I'm not inserting nulls into a table
where the primary key is auto increment, so I don't see why concurrent
inserts would fight for similar spots (although, I'm admittedly not a
MySQL hotshot, so the basis of my assumption is a *hunch* only).

I'm not sure (yet) if a single-threaded operation would run into an
i/o bottleneck. I didn't run mysqlimport using --use-threads=1 just
yet (will do if I have the time), but when I've ran it with
--use-threads=4 the import (of a ~500 MB dump) took more time than
running for different processes (I've split my tab delimited dumps
with split into four even pieces and imported those in four different
sessions).

Anyway, it seems that doing a simple import (from a dump, which isn't
tab delimited, but contains complete or extended inserts) takes the
same amount of time than doing a mysqlimport using --use-threads=4 and
as it turns out splitting my tab delimited dump is too complex to
handle gracefully, because my data contains newline characters all
over the place, so I've dropped the idea of this whole mysqlimport
thing for now. (I'll try the method of migrating an InnoDB database to
an NDBCluster described here[1] instead.)

If I have the time I'll write up a bug report, or documentation
enhancement request for this.

Thanks for the input!

Regards,
Kohányi Róbert

[1]: 
http://johanandersson.blogspot.se/2012/04/mysql-cluster-how-to-load-it-with-data.html

On Wed, Jul 25, 2012 at 6:49 PM, Rick James rja...@yahoo-inc.com wrote:
 I'm skeptical that use-treads can every be very effective.

 What order are the rows in?  They are probably in PRIMARY KEY order, which 
 means that the INSERTing threads will be fighting over similar spots in the 
 table.

 Is it I/O bound when it is single-threaded?  If so, then there can't be any 
 improvement with use-threads.

 etc.

 Suggest you file a bug with bugs.mysql.com.  If nothing else, the 
 documentation should say more than it does.

 -Original Message-
 From: Róbert Kohányi [mailto:kohanyi.rob...@gmail.com]
 Sent: Tuesday, July 24, 2012 10:52 AM
 To: mysql@lists.mysql.com
 Subject: mysqlimport --use-threads / mysqladmin processlist

 I'm in the middle of migrating an InnoDB database to an NDBCluster. I
 use mysqldump to first create two dumps, the first one contains only
 the database schema, the second one contains only tab delimited data
 (via mysqldump --tab). I edit my InnoDB schema here and there
 (ENGINE=InnoDB to ENGINE=NDB, etc.) import it and after this I import
 the InnoDB data *as is* using mysqlimport.

 I use it like this:

 mysqlimport --local --use-threads=4 db dir/*.txt

 (dir of course cotains the tab delimited data I dumped before.)

 The import starts, and I check its progress via mysqladmin, like this:

 mysqladmin --sleep=1 processlist

 this is what I see: http://pastebin.com/raw.php?i=M23fWVjc

 Only a single process seems to be loading my data. Is this what I
 *should* see, or, in my case using 4 threads, should I see four
 processes? I'm not asking which one will be faster, I'm just simply
 confused because I don't know what to expect. If I start four different
 mysqlimport processes, each one importing different files, then I can
 see four different process in the mysql processlist.

 If it's matters, here is my server version (I use the official
 binaries).
 Server version: 5.5.25a-ndb-7.2.7-gpl MySQL Cluster Community Server
 (GPL)

 Regards,
 Kohányi Róbert

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: mysqlimport doesn't work for me

[Carsten Pedersen]

It's been a long time sine I used mysqlimport, but you might want to try:

- using --fields-terminated-by rather than --fields-terminated
- losing (or escaping) the backticks in --columns=

- checking my.cnf to see if the client settings are the same for mysql 
and mysqlimport
- checking user privileges (are you using the same account in both 
instances?)

- checking the line delimiter and --lines-terminated-by

FWIW, I always prefer tab-delimited files over comma-separated ones. 
This gets around a lot of i18n issues.


/ Carsten

Den 03-01-2011 19:33, Jerry Schwartz skrev:

sigh

This works:

localhostTRUNCATE t_dmu_history;
Query OK, 0 rows affected (0.41 sec)

localhostLOAD DATA LOCAL INFILE 't_dmu_history.txt' INTO TABLE t_dmu_history
FIELDS TERMINATED BY , (`dm_history_dm_id`,`dm_history_customer_id`);

Query OK, 876211 rows affected (25.16 sec)
Records: 876211  Deleted: 0  Skipped: 0  Warnings: 0

localhostSELECT * FROM t_dmu_history LIMIT 4;
+--+--++
| t_dmu_history_id | DM_History_DM_ID | DM_History_Customer_ID |
+--+--++
|1 |13071 | 299519 |
|2 |13071 | 299520 |
|3 |13071 | 299521 |
|4 |13071 | 299522 |
+--+--++
4 rows in set (0.03 sec)

This does not work:

localhostTRUNCATE t_dmu_history;
localhostquit

C:\Users\Jerry\Documents\Access MySQL
Productionmysqlimport --columns=`dm_history_dm_id`,`dm_history_customer_id` 
--fields-terminated=','
  --local --password=xxx --pipe --user=access --verbose maintable_usa
t_dmu_history.txt
Connecting to localhost
Selecting database maintable_usa
Loading data from LOCAL file: C:/Users/Jerry/Documents/Access MySQL
Production/t_dmu_history.txt into t_dmu_history
maintable_usa.t_dmu_history: Records: 876211  Deleted: 0  Skipped: 0
Warnings: 1752422
Disconnecting from localhost

localhostSELECT * FROM t_dmu_history LIMIT 4;
+--+--++
| t_dmu_history_id | DM_History_DM_ID | DM_History_Customer_ID |
+--+--++
|1 |13071 |   NULL |
|2 |13071 |   NULL |
|3 |13071 |   NULL |
|4 |13071 |   NULL |
+--+--++
4 rows in set (0.00 sec)
=

Before you ask, the mysql CLI is also using a named pipe.

Windows Vista 32-bit
MySQL version 5.1.31-community
Mysqlimport Ver 3.7 Distrib 5.1.31, for Win32 (ia32)

What am I missing?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: mysqlimport doesn't work for me

[Jerry Schwartz]

-Original Message-
From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
Sent: Monday, January 03, 2011 1:48 PM
To: Jerry Schwartz
Cc: 'mos'; mysql@lists.mysql.com
Subject: Re: mysqlimport doesn't work for me

It's been a long time sine I used mysqlimport, but you might want to try:

- using --fields-terminated-by rather than --fields-terminated


[JS] Good catch! Unfortunately, it didn't fix the problem:

Connecting to localhost
Selecting database maintable_usa
Deleting the old data from table t_dmu_history
Loading data from LOCAL file: C:/Users/Jerry/Documents/Access_MySQL 
Tests/t_dmu_history.txt into t_dmu_history
maintable_usa.t_dmu_history: Records: 1  Deleted: 0  Skipped: 0  Warnings: 2
Disconnecting from localhost

I don't know if there's any way to find out what those warnings are.


- losing (or escaping) the backticks in --columns=

[JS] The loons who designed this system were fond of putting spaces in the 
table names. I've tried this particular table with and without the back-ticks.

Many of the table and field names are in Japanese, too. I shudder to think how 
that will work out.

- checking my.cnf to see if the client settings are the same for mysql
and mysqlimport


[JS] Good thought.

- checking user privileges (are you using the same account in both
instances?)


[JS] Yes.

- checking the line delimiter and --lines-terminated-by

FWIW, I always prefer tab-delimited files over comma-separated ones.
This gets around a lot of i18n issues.

[JS] No doubt.

/ Carsten

Den 03-01-2011 19:33, Jerry Schwartz skrev:
 sigh

 This works:

 localhostTRUNCATE t_dmu_history;
 Query OK, 0 rows affected (0.41 sec)

 localhostLOAD DATA LOCAL INFILE 't_dmu_history.txt' INTO TABLE 
 t_dmu_history
 FIELDS TERMINATED BY , (`dm_history_dm_id`,`dm_history_customer_id`);

 Query OK, 876211 rows affected (25.16 sec)
 Records: 876211  Deleted: 0  Skipped: 0  Warnings: 0

 localhostSELECT * FROM t_dmu_history LIMIT 4;
 +--+--++
 | t_dmu_history_id | DM_History_DM_ID | DM_History_Customer_ID |
 +--+--++
 |1 |13071 | 299519 |
 |2 |13071 | 299520 |
 |3 |13071 | 299521 |
 |4 |13071 | 299522 |
 +--+--++
 4 rows in set (0.03 sec)
 
 This does not work:

 localhostTRUNCATE t_dmu_history;
 localhostquit

 C:\Users\Jerry\Documents\Access MySQL
 Productionmysqlimport --columns=`dm_history_dm_id`,`dm_history_customer_id`
--fields-terminated=','
   --local --password=xxx --pipe --user=access --verbose maintable_usa
 t_dmu_history.txt
 Connecting to localhost
 Selecting database maintable_usa
 Loading data from LOCAL file: C:/Users/Jerry/Documents/Access MySQL
 Production/t_dmu_history.txt into t_dmu_history
 maintable_usa.t_dmu_history: Records: 876211  Deleted: 0  Skipped: 0
 Warnings: 1752422
 Disconnecting from localhost

 localhostSELECT * FROM t_dmu_history LIMIT 4;
 +--+--++
 | t_dmu_history_id | DM_History_DM_ID | DM_History_Customer_ID |
 +--+--++
 |1 |13071 |   NULL |
 |2 |13071 |   NULL |
 |3 |13071 |   NULL |
 |4 |13071 |   NULL |
 +--+--++
 4 rows in set (0.00 sec)
 =

 Before you ask, the mysql CLI is also using a named pipe.

 Windows Vista 32-bit
 MySQL version 5.1.31-community
 Mysqlimport Ver 3.7 Distrib 5.1.31, for Win32 (ia32)

 What am I missing?

 Regards,

 Jerry Schwartz
 Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032

 860.674.8796 / FAX: 860.674.8341
 E-mail: je...@gii.co.jp
 Web site: www.the-infoshop.com









-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqlimport remote host problem

[Claudio Nanni]

zcat /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h
192.168.0.224 -u root -p alba2

Cheers

Claudio



2009/3/11 Rob Wultsch wult...@gmail.com

 On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com
 wrote:

  OK, I've managed to do the same thing with just the mysql command line
  program:
 
 mysql -h 192.168.0.224 -u root -p alba2 
  /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql
 
  Works great. However, the sql file is normally gzipped, so Can I
 ungzip
  the file on the fly (and without removing the .gzip version) and pipe the
  contents as I did above? (Yes, I'm UNIX-impaired.)  Something like:
 
 mysql -h 192.168.0.224 -u root -p alba2  gzip -dc
  /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz
 
  But so that it works...
 
  ...Rene


 Pipe is your friend. You probably want something like:

 gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz
 | mysql -h 192.168.0.224 -u root -p alba2

Re: mysqlimport remote host problem

[René Fournier]

Thanks. That works great.


On 10-Mar-09, at 9:36 PM, Rob Wultsch wrote:

On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com  
wrote:


OK, I've managed to do the same thing with just the mysql command  
line

program:

  mysql -h 192.168.0.224 -u root -p alba2 
/Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql

Works great. However, the sql file is normally gzipped, so Can  
I ungzip
the file on the fly (and without removing the .gzip version) and  
pipe the

contents as I did above? (Yes, I'm UNIX-impaired.)  Something like:

  mysql -h 192.168.0.224 -u root -p alba2  gzip -dc
/Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz

But so that it works...

...Rene



Pipe is your friend. You probably want something like:

gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz
| mysql -h 192.168.0.224 -u root -p alba2



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqlimport remote host problem

[René Fournier]

OK, I've managed to do the same thing with just the mysql command line  
program:


	mysql -h 192.168.0.224 -u root -p alba2  /Backup/Latest/ 
alba2_2009-03-10_00h45m.Tuesday.sql


Works great. However, the sql file is normally gzipped, so Can I  
ungzip the file on the fly (and without removing the .gzip version)  
and pipe the contents as I did above? (Yes, I'm UNIX-impaired.)   
Something like:


	mysql -h 192.168.0.224 -u root -p alba2  gzip -dc /Backup/Latest/ 
alba2_2009-03-10_00h45m.Tuesday.sql.gz


But so that it works...

...Rene

On 10-Mar-09, at 7:38 PM, René Fournier wrote:

I'm writing script that, each night, copies a small database to my  
laptop on the local network. I'm having trouble getting it to work.  
Here's my syntax so far (run on the server):


mysqlimport --host=192.168.0.224 --user=root --password alba2  
alba2_2009-03-10_00h45m.Tuesday.sql


Which produces:

---
mysqlimport: Error: 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 '-03-10_00h45m IGNORE 0 LINES' at line 1, when  
using table: alba2_2009-03-10_00h45m

---

The sql file is produced by automysqlbackup...  Not sure what I'm  
missing, probably something obvious. Anyway, here's the first part  
of the sql file I want to import:


-- MySQL Administrator dump 1.4
--
-- --
-- Server version   5.0.67-log


/*!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' */;



--
-- Create schema alba2
--

CREATE DATABASE IF NOT EXISTS alba2;
USE alba2;

--
-- Definition of table `alba2`.`accounts`
--

DROP TABLE IF EXISTS `alba2`.`accounts`;
CREATE TABLE  `alba2`.`accounts` (
 `id` smallint(5) unsigned NOT NULL auto_increment,
 `fp` varchar(40) NOT NULL,
 `created` int(10) unsigned NOT NULL default '0',
 `status` enum('Active','Inactive') NOT NULL default 'Active',
 `account_name` varchar(40) NOT NULL,
 `account_full_name` varchar(40) NOT NULL,
 `address` varchar(40) NOT NULL,
 `city` varchar(40) NOT NULL,
 `province` varchar(10) NOT NULL,
 `postcode` varchar(10) NOT NULL,
 UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `alba2`.`accounts`
--


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub...@renefournier.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqlimport remote host problem

[Darryle Steplight]

Hi Rene,
 Just a head's up. You might want to keep your username/password
credentials private.

On Tue, Mar 10, 2009 at 10:16 PM, René Fournier m...@renefournier.com wrote:
 OK, I've managed to do the same thing with just the mysql command line
 program:

        mysql -h 192.168.0.224 -u root -p alba2 
 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql

 Works great. However, the sql file is normally gzipped, so Can I ungzip
 the file on the fly (and without removing the .gzip version) and pipe the
 contents as I did above? (Yes, I'm UNIX-impaired.)  Something like:

        mysql -h 192.168.0.224 -u root -p alba2  gzip -dc
 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz

 But so that it works...

 ...Rene

 On 10-Mar-09, at 7:38 PM, René Fournier wrote:

 I'm writing script that, each night, copies a small database to my laptop
 on the local network. I'm having trouble getting it to work. Here's my
 syntax so far (run on the server):

 mysqlimport --host=192.168.0.224 --user=root --password alba2
 alba2_2009-03-10_00h45m.Tuesday.sql

 Which produces:

 ---
 mysqlimport: Error: 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 '-03-10_00h45m IGNORE 0 LINES' at line 1, when using table:
 alba2_2009-03-10_00h45m
 ---

 The sql file is produced by automysqlbackup...  Not sure what I'm missing,
 probably something obvious. Anyway, here's the first part of the sql file I
 want to import:

 -- MySQL Administrator dump 1.4
 --
 -- --
 -- Server version       5.0.67-log


 /*!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'
 */;


 --
 -- Create schema alba2
 --

 CREATE DATABASE IF NOT EXISTS alba2;
 USE alba2;

 --
 -- Definition of table `alba2`.`accounts`
 --

 DROP TABLE IF EXISTS `alba2`.`accounts`;
 CREATE TABLE  `alba2`.`accounts` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `fp` varchar(40) NOT NULL,
  `created` int(10) unsigned NOT NULL default '0',
  `status` enum('Active','Inactive') NOT NULL default 'Active',
  `account_name` varchar(40) NOT NULL,
  `account_full_name` varchar(40) NOT NULL,
  `address` varchar(40) NOT NULL,
  `city` varchar(40) NOT NULL,
  `province` varchar(10) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  UNIQUE KEY `id` (`id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

 --
 -- Dumping data for table `alba2`.`accounts`
 --


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub...@renefournier.com



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqlimport remote host problem

[wultsch]

Note the space after the -p , alba2 will be the defaut database *after* he is 
prompted and corrctly give the password for r...@whateverhishostis . 

As he did not give the password, and is not connecting to an that one could get 
to from the net he really has not given out particularly useful info if we had 
ill intents.


Sent from my Verizon Wireless BlackBerry

-Original Message-
From: Darryle Steplight dstepli...@gmail.com

Date: Tue, 10 Mar 2009 22:20:26 
To: René Fournierm...@renefournier.com
Cc: mysqlmysql@lists.mysql.com
Subject: Re: mysqlimport remote host problem


Hi Rene,
 Just a head's up. You might want to keep your username/password
credentials private.

On Tue, Mar 10, 2009 at 10:16 PM, René Fournier m...@renefournier.com wrote:
 OK, I've managed to do the same thing with just the mysql command line
 program:

        mysql -h 192.168.0.224 -u root -p alba2 
 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql

 Works great. However, the sql file is normally gzipped, so Can I ungzip
 the file on the fly (and without removing the .gzip version) and pipe the
 contents as I did above? (Yes, I'm UNIX-impaired.)  Something like:

        mysql -h 192.168.0.224 -u root -p alba2  gzip -dc
 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz

 But so that it works...

 ...Rene

 On 10-Mar-09, at 7:38 PM, René Fournier wrote:

 I'm writing script that, each night, copies a small database to my laptop
 on the local network. I'm having trouble getting it to work. Here's my
 syntax so far (run on the server):

 mysqlimport --host=192.168.0.224 --user=root --password alba2
 alba2_2009-03-10_00h45m.Tuesday.sql

 Which produces:

 ---
 mysqlimport: Error: 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 '-03-10_00h45m IGNORE 0 LINES' at line 1, when using table:
 alba2_2009-03-10_00h45m
 ---

 The sql file is produced by automysqlbackup...  Not sure what I'm missing,
 probably something obvious. Anyway, here's the first part of the sql file I
 want to import:

 -- MySQL Administrator dump 1.4
 --
 -- --
 -- Server version       5.0.67-log


 /*!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'
 */;


 --
 -- Create schema alba2
 --

 CREATE DATABASE IF NOT EXISTS alba2;
 USE alba2;

 --
 -- Definition of table `alba2`.`accounts`
 --

 DROP TABLE IF EXISTS `alba2`.`accounts`;
 CREATE TABLE  `alba2`.`accounts` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `fp` varchar(40) NOT NULL,
  `created` int(10) unsigned NOT NULL default '0',
  `status` enum('Active','Inactive') NOT NULL default 'Active',
  `account_name` varchar(40) NOT NULL,
  `account_full_name` varchar(40) NOT NULL,
  `address` varchar(40) NOT NULL,
  `city` varchar(40) NOT NULL,
  `province` varchar(10) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  UNIQUE KEY `id` (`id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

 --
 -- Dumping data for table `alba2`.`accounts`
 --


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub...@renefournier.com



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=wult...@gmail.com

Re: mysqlimport remote host problem

[Rob Wultsch]

On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com wrote:

 OK, I've managed to do the same thing with just the mysql command line
 program:

mysql -h 192.168.0.224 -u root -p alba2 
 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql

 Works great. However, the sql file is normally gzipped, so Can I ungzip
 the file on the fly (and without removing the .gzip version) and pipe the
 contents as I did above? (Yes, I'm UNIX-impaired.)  Something like:

mysql -h 192.168.0.224 -u root -p alba2  gzip -dc
 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz

 But so that it works...

 ...Rene


Pipe is your friend. You probably want something like:

gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz
| mysql -h 192.168.0.224 -u root -p alba2

Re: mysqlimport huge table

[Baron Schwartz]

Hello Shuly,

Try this.  
http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

On Tue, Feb 24, 2009 at 1:08 PM, Shuly Avraham sh...@cshl.edu wrote:
 Hi,

 I have a huge table with about 50 millions of rows.
 I dumped the table using mysqldump -T , as text dump, and now trying to
 import it to a database on another server, but it keeps hanging.
 Are there any options or server variables I can set to help out with it?

 Thanks,
 Shuly.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=ba...@xaprb.com





-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqlimport huge table

[Shuly Avraham]

Thanks for the resource!

However, I am loading into a MyISAM table, while logging is disabled.

In a matter of fact, I was able to load the data at the end, after  
disabling the keys. But now, as I try to create the keys, it takes a  
huge amount of time. there are 250 Million rows in one of the tables.

My key_buffer_size is set to 4G, on a machine with 16G ram.

Are there any other system variable I could set in order to speed it up?

Thanks,
Shuly.

On Feb 24, 2009, at 5:17 PM, Baron Schwartz wrote:


Hello Shuly,

Try this.  http://www.mysqlperformanceblog.com/2008/07/03/how-to- 
load-large-files-safely-into-innodb-with-load-data-infile/


On Tue, Feb 24, 2009 at 1:08 PM, Shuly Avraham sh...@cshl.edu wrote:

Hi,

I have a huge table with about 50 millions of rows.
I dumped the table using mysqldump -T , as text dump, and now  
trying to

import it to a database on another server, but it keeps hanging.
Are there any options or server variables I can set to help out  
with it?


Thanks,
Shuly.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com






--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqlimport/load data infile is using a temp file - why?

[Ananda Kumar]

Mysql use tmpdir,
when ever there is any index creation.

regards
anandkl


On 8/21/08, jthorpe [EMAIL PROTECTED] wrote:

 Hi,

 I've been trying to import a 10G dump file using mysqlimport
 and it is eventually failing because it runs out of tmpdir
 space -- I get Errcode: 28.

 I was surprised that it was using a temp file at all. I've
 looked in the documentation and other sources but have not been
 able to find anything about this file.

 This is on v5.0.51.  The table is innodb and has 75 columns with
 65% of them tinyint, 20% float, and the rest char,datetime,int.
 The primary key is composite on two columns (int,char).
 The data file that is being imported is on the database server.

 I'll try this again and monitor the status parameters to see what
 it is doing, but I was wondering if anyone already knows?

 Thanks,
 John

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport/load data infile is using a temp file - why?

[Moon's Father]

You should increase parameter named max_bulk_insert_buffer_size and
max_allowed_packet.

On 8/21/08, Ananda Kumar [EMAIL PROTECTED] wrote:

 Mysql use tmpdir,
 when ever there is any index creation.

 regards
 anandkl


 On 8/21/08, jthorpe [EMAIL PROTECTED] wrote:
 
  Hi,
 
  I've been trying to import a 10G dump file using mysqlimport
  and it is eventually failing because it runs out of tmpdir
  space -- I get Errcode: 28.
 
  I was surprised that it was using a temp file at all. I've
  looked in the documentation and other sources but have not been
  able to find anything about this file.
 
  This is on v5.0.51.  The table is innodb and has 75 columns with
  65% of them tinyint, 20% float, and the rest char,datetime,int.
  The primary key is composite on two columns (int,char).
  The data file that is being imported is on the database server.
 
  I'll try this again and monitor the status parameters to see what
  it is doing, but I was wondering if anyone already knows?
 
  Thanks,
  John
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 




-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Re: mysqlimport problem with , inside fields

[Anoop kumar V]

Hey Scott -

I dont think you can use , with mysqlimport as a field separator if it is
part of the data. use something else - I used the pipe | character...

This is what worked for me:

C:\mysqlimport --fields-enclosed-by= --fields-terminated-by=|
--lines-terminated-by=\r\n --ignore-lines=1 --user=root --password test
c:\result.csv --verbose=TRUE --ignore=tr
ue
Enter password: 
Connecting to localhost
Selecting database test
Loading data from SERVER file: c:/result.csv into result
test.result: Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from localhost

C:\type result.csv

48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460
48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460
48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460
48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460
48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460
48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460
48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3   [SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460


mysql select info from result;
++
| info   |
++
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
|  pop3   [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 |
++
7 rows in set (0.00 sec)

Let me know how it goes..

Anoop
(anokun7)

On 2/18/07, Scott Hamm [EMAIL PROTECTED] wrote:


I've been trying to import fields that contains the comma character ','
inside double quotes '', with the results following:

code

mysqlimport --fields-optionally-enclosed-by= --fields-terminated-by=,
--lines-terminated-by=\r\n --ignore-lines=1 --user=root --password shark
c:\documents and settings\shamm\desktop\result.csv

Enter password: **

mysqlimport: Error: Row 48 was truncated; it contained more data than
there
were input columns, when using table: result


Line 48:

48, 14.729606, 10.1.1.22, 10.182.167.209, TCP, pop3  
[SYN,
ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460



mysql desc result;
+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| No  | int(10) unsigned | NO   | | |   |
| Time| text | NO   | | |   |
| Source  | text | NO   | | |   |
| Destination | text | NO   | | |   |
| Protocol| text | NO   | | |   |
| Info| text | NO   | | |   |
+-+--+--+-+-+---+
6 rows in set (0.03 sec)

C:\Documents and Settings\shammmysql --version
mysql  Ver 14.12 Distrib 5.0.26, for Win32 (ia32)

/code

--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.






--
Thanks and best regards,
Anoop

Re: Mysqlimport complex question

[Christian Hammers]

On 2006-10-04 Scott Hamm wrote:
 OBJECTIVE:
  INPUT:
   E524541015.txt:20061004|,535,999|Scot|Hamm|,410|||101 Walter
 Rd|Dover|MD|76709|,041|
... 
 Been trying to get mysqlimport to use these characters to no avail, how do
 I get around to it?

I can't answer your question but think that you're following the wrong
approach instead of the simple rule: 
 Use SQL for storing and calculating and a script language for parsing and 
formatting.
It's quite easy with regular expressions (they can be made more readable
with /x):

cat input.txt | perl -n -e
  '/^(.{14}):(.{8})\|,(\d+),(\d+)\|[^\|]+\|[^\|]+\|,(\d+)\|.*\|,(\d+)\|$/ 
  print INSERT INTO t VALUES (\$1\,$2,$3,$4,$5,$6);\n;'

The output is ready to be piped into mysql:
  INSERT INTO t VALUES (E524541015.txt,20061004,535,999,410,041);

bye,

-christian-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport csv file import problem

[Thomas Spahni]

On Sun, 27 Aug 2006, Jim Seymour wrote:

 Hi,
 
 I download a csv file from Yahoo in this format:
 
 ABIAX
20.63
2006-08-3
 ACEIX
 8.78
2006-08-3
 CIGAX
10.08
2006-08-3
 FSCTX
22.25
2006-08-3
 GGOAX
20.55
2006-08-3
 HWLAX
 23.3
2006-08-3
 HWMAX
28.74
2006-08-3
 MLEIX
96.37
2006-08-3
 NBPBX
18.98
2006-08-3
 PSVIX
32.43
2006-08-3
 PTRAX
 10.3
2006-08-3
 RGACX
30.89
2006-08-3
 ^DJI
  11242.6
2006-08-3
 ^IXIC
  2092.34
2006-08-3
 ^GSPC
  1280.27
2006-08-3
 
 My table for this data is in this format
 
 | 2006-08-02 | 20.72 |  8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 |
 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 |
 
 Is there a way to get mysqlimport to pull the data from specific
 column/row to insert into a specified field? Trying to find an easier
 way than typing all of the data into a text file for import.
 
 Thanks,
 

Hi Jim,

that needs some preprocessing, but 'sed' is your friend. You could use 
some shell script doing the work for you:

#!/bin/sh
BLANK=' '
TAB='   '
WHITESPACE=${BLANK}${TAB}
DATE=$(cat mydatafile.csv | sed \
-e 3 !d \
-e s/^[$WHITESPACE]*// \
-e s/.*/''/)
DATA=$(cat mydatafile.csv | sed \
-e /[A-Z]/ d \
-e /-/ d \
-e s/^[$WHITESPACE]*// \
-e s/.*/''/ | tr '\012' ',' | sed \
-e s/,*$//)
echo INSERT INTO mytable VALUES(${DATE},$DATA);
exit 0
# end of shell script


The resulting queries can be piped into the mysql client.

HTH,
Thomas Spahni

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport csv file import problem

[Jim Seymour]

On Mon, 2006-08-28 at 13:47 +0200, Thomas Spahni wrote:
 On Sun, 27 Aug 2006, Jim Seymour wrote:
 
  Hi,
  
  I download a csv file from Yahoo in this format:
  
  ABIAX
 20.63
 2006-08-3
  ACEIX
  8.78
 2006-08-3
  CIGAX
 10.08
 2006-08-3
  FSCTX
 22.25
 2006-08-3
  GGOAX
 20.55
 2006-08-3
  HWLAX
  23.3
 2006-08-3
  HWMAX
 28.74
 2006-08-3
  MLEIX
 96.37
 2006-08-3
  NBPBX
 18.98
 2006-08-3
  PSVIX
 32.43
 2006-08-3
  PTRAX
  10.3
 2006-08-3
  RGACX
 30.89
 2006-08-3
  ^DJI
   11242.6
 2006-08-3
  ^IXIC
   2092.34
 2006-08-3
  ^GSPC
   1280.27
 2006-08-3
  
  My table for this data is in this format
  
  | 2006-08-02 | 20.72 |  8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 |
  96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 |
  
  Is there a way to get mysqlimport to pull the data from specific
  column/row to insert into a specified field? Trying to find an easier
  way than typing all of the data into a text file for import.
  
  Thanks,
  
 
 Hi Jim,
 
 that needs some preprocessing, but 'sed' is your friend. You could use 
 some shell script doing the work for you:
 
 #!/bin/sh
 BLANK=' '
 TAB='   '
 WHITESPACE=${BLANK}${TAB}
 DATE=$(cat mydatafile.csv | sed \
 -e 3 !d \
 -e s/^[$WHITESPACE]*// \
 -e s/.*/''/)
 DATA=$(cat mydatafile.csv | sed \
 -e /[A-Z]/ d \
 -e /-/ d \
 -e s/^[$WHITESPACE]*// \
 -e s/.*/''/ | tr '\012' ',' | sed \
 -e s/,*$//)
 echo INSERT INTO mytable VALUES(${DATE},$DATA);
 exit 0
 # end of shell script
 
 
 The resulting queries can be piped into the mysql client.

Thanks Thomas,

From what I had read it looked like I would have to do something like
this. This will save me a lot of time.

Thanks Again,
-- 
Jim Seymour [EMAIL PROTECTED]


signature.asc
Description: This is a digitally signed message part

Re: mysqlimport, \r\n and \n

[sheeri kritzer]

A batch script or shell script can easily be written to do this.

-Sheeri

On 2/20/06, Daniel Kasak [EMAIL PROTECTED] wrote:
 I've got some import scripts that are giving me trouble.

 Some MOFOs keep changing the format of the data they give us, and
 sometimes I loose half the records. When this happens, I change the line
 terminator from \r\n to \n  ...  or from \n to \r\n.

 It's starting to get to me. Is there any way to ease my pain here, short
 of importing with one format, counting the number of records, truncating
 the table, importing wit the other format, counting the number of
 records, and then selecting the format with the most number of records?

 --
 Daniel Kasak
 IT Developer
 NUS Consulting Group
 Level 5, 77 Pacific Highway
 North Sydney, NSW, Australia 2060
 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
 email: [EMAIL PROTECTED]
 website: http://www.nusconsulting.com.au

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport, \r\n and \n

[Daniel da Veiga]

On 2/22/06, sheeri kritzer [EMAIL PROTECTED] wrote:
 A batch script or shell script can easily be written to do this.

 -Sheeri

 On 2/20/06, Daniel Kasak [EMAIL PROTECTED] wrote:
  I've got some import scripts that are giving me trouble.
 
  Some MOFOs keep changing the format of the data they give us, and
  sometimes I loose half the records. When this happens, I change the line
  terminator from \r\n to \n  ...  or from \n to \r\n.
 
  It's starting to get to me. Is there any way to ease my pain here, short
  of importing with one format, counting the number of records, truncating
  the table, importing wit the other format, counting the number of
  records, and then selecting the format with the most number of records?
 
  --
  Daniel Kasak
  IT Developer
  NUS Consulting Group
  Level 5, 77 Pacific Highway
  North Sydney, NSW, Australia 2060
  T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
  email: [EMAIL PROTECTED]
  website: http://www.nusconsulting.com.au
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



I would stick with the VBA solution, heh, if you use it you can even
call Word and use save as, in fact converting your document to
plain ol' windows text file... Then I guess you can go back to Access
and just let it roll...

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport, \r\n and \n

[George Law]

Daniel,

What about just running your import file through dos2unix before piping
it to mysql?

That way, it would leave \n alone (since that is unix format) and
replace any occurrances of 
\r\n with plan old \n.

--
George
 

 -Original Message-
 From: Daniel Kasak [mailto:[EMAIL PROTECTED] 
 Sent: Monday, February 20, 2006 11:21 PM
 To: mysql@lists.mysql.com
 Subject: mysqlimport, \r\n and \n
 
 I've got some import scripts that are giving me trouble.
 
 Some MOFOs keep changing the format of the data they give us, 
 and sometimes I loose half the records. When this happens, I 
 change the line terminator from \r\n to \n  ...  or from \n to \r\n.
 
 It's starting to get to me. Is there any way to ease my pain 
 here, short of importing with one format, counting the number 
 of records, truncating the table, importing wit the other 
 format, counting the number of records, and then selecting 
 the format with the most number of records?
 
 --
 Daniel Kasak
 IT Developer
 NUS Consulting Group
 Level 5, 77 Pacific Highway
 North Sydney, NSW, Australia 2060
 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
 email: [EMAIL PROTECTED]
 website: http://www.nusconsulting.com.au
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport, \r\n and \n

[Daniel Kasak]

George Law wrote:

Daniel,

What about just running your import file through dos2unix before piping
it to mysql?

That way, it would leave \n alone (since that is unix format) and
replace any occurrances of 
\r\n with plan old \n.
  
I could think of a lot of things I could do if this were all running on 
Linux.


Unfortunately the import routine is being triggered from MS Access ... 
and come to think of it, I'm using 'load data infile' and not 
'mysqlimport', but anyway, you get the idea. AFAIK there is no way to 
trigger anything useful via ODBC. I could write a Perl script, chuck it 
in my cgi-bin folder, and opening Firefox from Access, passing the 
script some pointers to the file. But that's dodgy. I suppose while I'm 
working with Access I'd better get used to dodgy solutions, eh? ;-)


--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport, \r\n and \n

[SGreen]

A little less dodgy is to write a VBA routine within Access to do the 
filtering. Alternatively, if you don't want to trust access to do it, you 
can write a VBScript or JScript routine and run it through the shell (yes, 
Windoze has shell scripts, too)

There are all kinds of things you can do.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Daniel Kasak [EMAIL PROTECTED] wrote on 02/21/2006 04:13:28 
PM:

 George Law wrote:
  Daniel,
 
  What about just running your import file through dos2unix before 
piping
  it to mysql?
 
  That way, it would leave \n alone (since that is unix format) and
  replace any occurrances of 
  \r\n with plan old \n.
  
 I could think of a lot of things I could do if this were all running on 
 Linux.
 
 Unfortunately the import routine is being triggered from MS Access ... 
 and come to think of it, I'm using 'load data infile' and not 
 'mysqlimport', but anyway, you get the idea. AFAIK there is no way to 
 trigger anything useful via ODBC. I could write a Perl script, chuck it 
 in my cgi-bin folder, and opening Firefox from Access, passing the 
 script some pointers to the file. But that's dodgy. I suppose while I'm 
 working with Access I'd better get used to dodgy solutions, eh? ;-)
 
 -- 
 Daniel Kasak
 IT Developer
 NUS Consulting Group
 Level 5, 77 Pacific Highway
 North Sydney, NSW, Australia 2060
 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
 email: [EMAIL PROTECTED]
 website: http://www.nusconsulting.com.au
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: mysqlimport debug options

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/refman/5.0/en/the-dbug-package.html



P. Evans wrote:

 Hello Listers,

   Can anyone explain what are valid values for the 'debug options' on a 
 mysqlimport ? The manuals just say :

   --debug[=debug_options], -# [debug_options] 

   Write a debugging log. The debug_options string often is 'd:t:o,file_name'.



   What is d: ? t: ? o  (ok,thats relatively easy but just to be sure...) ?



   This is mysql  Ver 14.12 Distrib 5.0.15, for sun-solaris2.9 (sparc) using 
 readline 5.0



   Thanks

   Pierre 

 

 

   

 -

 Yahoo! Shopping

  Find Great Deals on Holiday Gifts at Yahoo! Shopping 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

re: mysqlimport blocks all access to database

[Joseph Blackette]

Yes I am appending to the end of an existing database. So why are rows 1
to N locked if I'm only adding rows at N+1? Wouldn't the write
privileges apply to rows being modified? And during this period even an
interactive mysql shell hangs until the mysqlimport completes. i.e. I
the database is pretty much inaccessible until mysqlimport completes.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
Sent: Monday, March 28, 2005 12:30 AM
To: [EMAIL PROTECTED]
Subject: Re: mysqlimport blocks all access to database

Hi Joseph,

 I have been trying to use mysqlimport to load a primarily read only
 database with data at regular intervals. My problem occurs when my
 tables are myisam. In this case all access to the database and the
 tables blocks until mysqlimport completes. The -lock-tables=false
 parameter does not help.  Is this the normal operation? If not how can
I
 still have concurrent read access to the database while mysqlimport is
 running?


Correct me if I am wrong, but the write request has privilege. So ANY
read
request is queued until the write request finishes. Otherwise you would
get inconsistent read results.

Am I correct assuming that you are appending the imported data to the
existing data in the database ?

Best regards

Nils Valentin
Tokyo / Japan
www.be-known-online.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport blocks all access to database

[valentin_nils]

Hi Joseph,

 I have been trying to use mysqlimport to load a primarily read only
database with data at regular intervals. My problem occurs when my
tables are myisam. In this case all access to the database and the
tables blocks until mysqlimport completes. The -lock-tables=false
parameter does not help.  Is this the normal operation? If not how can I
still have concurrent read access to the database while mysqlimport is
running?


Correct me if I am wrong, but the write request has privilege. So ANY read
request is queued until the write request finishes. Otherwise you would
get inconsistent read results.

Am I correct assuming that you are appending the imported data to the
existing data in the database ?

Best regards

Nils Valentin
Tokyo / Japan

www.be-known-online.com






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport --replace

[Clodoaldo Pinto]

Please do not consider the previous email. I found my error.
mysqlimport is behaving exactly as expected.

Regards, Clodoaldo Pinto


On Sat, 29 Jan 2005 15:53:48 -0200, Clodoaldo Pinto
[EMAIL PROTECTED] wrote:
 I need to update a table with mysqlimport.
 
 I would like to update the table lines with the same unique key and
 mantain the other lines untouched.
 
 When I use mysqlimport --replace the table lines with the same
 unique key are updated but the others are deleted.
 
 Is it the expected behavior? How to avoid the other lines being deleted?
 
 Using FC3. The server version is 4.0.23-standard and the mysqlimport
 version is Ver 3.4 Distrib 4.0.23.
 
 The command line:
 
 /usr/local/bin/mysqlimport --replace --local
 --fields-terminated-by=';' --fields-optionally-enclosed-by=''
 --lines-terminated-by='\n' --fields-escaped-by='' --host=localhost
 --user=* --password=* --verbose dbname /path/to/txt/Temp.txt
 import.log 2importErr.log
 
 import.log:
 
 Connecting to localhost
 Selecting database dbname
 Loading data from LOCAL file: /path/to/txt/Temp.txt into Temp
 dbname.Temp: Records: 2415  Deleted: 0  Skipped: 0  Warnings: 0
 Disconnecting from localhost
 
 importErr.log is empty.
 
 Table structure:
 CREATE TABLE Temp (
   team int(10) unsigned NOT NULL default '0',
   points int(11) NOT NULL default '0',
   wus int(11) NOT NULL default '0',
   day date NOT NULL default '-00-00',
   PRIMARY KEY  (team, day)
 ) TYPE=MyISAM;
 
 Regards, Clodoaldo Pinto


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport and \ as data

[Victor Pendleton]

Can you run a find and replace to double up the backslashes?
\ -- \\

-Original Message-
From: Hans van Dalen
To: [EMAIL PROTECTED]
Sent: 4/28/04 3:46 AM
Subject: mysqlimport and \ as data

Hi Group,

Does anybody have any expierence with mysqlimport and a comma separated 
file with data wich contains field data with a: \. For example I have
the 
data in de column path: C:\temp.

When I import this I got something like : c:||emp ...

Does anybody know how to solve this problem? This \ occurs to much to do

the job manually ;-)

Thanks a lot.
Hans


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport question

[Victoria Reznichenko]

Ron McKeever [EMAIL PROTECTED] wrote:
 I'm trying to use mysqimport instead of LOAD DATA INFILE from a shell
 script. I notice an option for mysqlimport is not working or im doing it
 wrong.
 
 This works with LOAD DATA INFILE  :
 mysql -e LOAD DATA INFILE 'x' INTO TABLE x IGNORE 2 LINES
 
 
 but when i try:
 
 mysqlimport --ignore-lines=2 -uroot -ppassword month 0114.txt
 
 It says unknown option --ignore-lines=? Am i doing this wrong?

Your version of mysqlimport doesn't support this option.
--ignore-lines option was added in v4.0.2 of MySQL

 
 Also will mysqlimport work only if the .txt file is the same name as the
 table in the db( mine is month.Jan04, but the files are always mmdd.txt)
 
 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport

[Egor Egorov]

May Yang [EMAIL PROTECTED] wrote:
 Dear everyone,
 
 I'd like to ask you a question, thanks in advance.
 
 Q: How long will it take to import 100GB data into MySQL DB by command mysqlimport 
 ?
 

Depends on how many records are to be inserted. Hard to tell not knowing your hardware 
and record length. But anyway, it will take hours or tens of hours.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport question

[Victoria Reznichenko]

Cersosimo, Steve [EMAIL PROTECTED] wrote:
 
 Am I wrong to assume mysqlimport is supposed to emulate the LOAD DATA INFILE
 syntax?  I cannot find the command line option to turn on the CONCURRENT
 flag.
 

CONCURRENT currently is not supported by mysqlimport.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport 'can't get stat of [file]' error

[gerald_clark]

Check file permissions.
The file must be readable by the user 'mysql'.
Will Tyburczy wrote:

I've unable to load data from files into existing tables. When I give the command: mysqlimport [database] [filename] I keep getting the following error.

mysqlimport: Error: Can't get stat of '[filename]' (Errcode: 13), when using table: [table]

I get a similar error when trying to use LOAD DATA INFILE from within mysql.  The text 
files all have the extension .table
I'm sure that I'm just missing something simple, but I've spent a good amount of time 
trying to figure this out and haven't been able to.  Any advice on what's happening or 
how to get around this would be really appreciated.
Thanks,

Will Tyburczy

___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport 'can't get stat of [file]' error

[Victoria Reznichenko]

Will Tyburczy [EMAIL PROTECTED] wrote:
 
 I've unable to load data from files into existing tables. When I give the command: 
 mysqlimport [database] [filename] I keep getting the following error.
 
 mysqlimport: Error: Can't get stat of '[filename]' (Errcode: 13), when using table: 
 [table]
 
 I get a similar error when trying to use LOAD DATA INFILE from within mysql.  The 
 text files all have the extension .table
 I'm sure that I'm just missing something simple, but I've spent a good amount of 
 time trying to figure this out and haven't been able to.  Any advice on what's 
 happening or how to get around this would be really appreciated.
 

$ perror 13
Error code  13:  Permission denied

You doesn't have permission on that file.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport problems

[Neil Belch]

Thanks Paul, the options file worked fine!

Neil Belch

- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Neil Belch [EMAIL PROTECTED]; mySQL List
[EMAIL PROTECTED]
Sent: Thursday, September 04, 2003 2:48 AM
Subject: Re: mysqlimport problems


 At 0:34 +0100 9/4/03, Neil Belch wrote:
 Hi all, new to the list and new to mySQL...
 
 I've only been at it for a few days and am looking at importing text
 files to databases.
 I'm having trouble with mysqlimport and would appreciate any help or
 solutions as to what I'm doing wrong - I'm sure its a dumb mistake!
 
 I tried LOAD DATA INFILE and it worked fine. Here's the code I used for
it:
 
 LOAD DATA LOCAL INFILE 'master_name.txt'
 INTO TABLE master_name
 FIELDS TERMINATED BY ':::'
 ENCLOSED BY ''
 LINES TERMINATED BY '\r\n'
 
 This works fine. Every record is in the table and all the data is in
there.
 
 
 When I try the same thing with mysqlimport, the records are there
 but there's no data in any of the fields!
 The code I used for it:
 
 mysqlimport -L -h localhost -u supercontact -ppassword
 --fields-terminated-by=::: --fields-enclosed-by=\
 --lines-terminated-by=\r\n contactDB master_name.txt
 
 However, that whole command above won't fit on the DOS prompt in one
 go, so I have to enter most of the options in one command, then the
 remaining ones along with the db name and txt file in a second
 command - it seems to work as the records are still added but
 there's no data.

 Eh? That can't work; you must enter all the arguments in a *single*
command.

 You may be able to shorten the command by omitting -h localhost, it
 should default to the local host anyway.  Or you could put options in
 an option file such as C:\my.cnf:

 [mysqlimport]
 local
 host=localhost
 user=supercontact
 fields-terminated-by=:::

 etc.

 Of course, if you don't want to use those options the next time you
 run mysqlimport, you'll need to remove them from the option file.


 (On another note, is there a way to fit the command onto one line in
 the DOS prompt?)
 
 Any help is greatly appreciated.
 Thanks,
 
 
 Neil Belch


 --
 Paul DuBois, Senior Technical Writer
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com

 Are you MySQL certified?  http://www.mysql.com/certification/


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport command question

[Fortuno, Adam]

Darryl,

Sorry for not responding sooner.

Looking at this. First I'd recommend delimiting by something other than
tabs. I typically use pipes '|' as my delimiter. Tabs and commas can be
problematic for a variety of reasons.

Second, double check your dates. All I can think of is SQL Server syntax
right now, but I remember having some problems with dates being imported in
the past. If memory serves, I had to put them in -MM-DD format. That
should clean you're import file up.

Regards,
Adam

-Original Message-
From: Darryl Hoar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 5:10 PM
To: 'Fortuno, Adam'; [EMAIL PROTECTED]
Subject: RE: mysqlimport command question


To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE: mysqlimport command question


Darryl,

Provide a copy of the table's details either with a describe
table output or
the table's definition and a sample of the input file's top 5-rows.

Regards,
Adam

OK, here is the table structure:
mysql describe emp2;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| empnum | varchar(4)  | YES  | | NULL|   |
| name   | varchar(30) | YES  | | NULL|   |
| fname  | varchar(20) | YES  | | NULL|   |
| lname  | varchar(30) | YES  | | NULL|   |
| email  | varchar(60) | YES  | | NULL|   |
| ext| varchar(4)  | YES  | | NULL|   |
| listit | char(1) | YES  | | NULL|   |
| bdm| int(11) | YES  | | NULL|   |
| bdd| int(11) | YES  | | NULL|   |
| hdate  | datetime| YES  | | NULL|   |
++-+--+-+-+---+
10 rows in set (0.00 sec)

mysql

the first 5 records of emp2.txt are:
100   ALBERT   JEFFREYALBERT
[EMAIL PROTECTED] N 3 6 04/11/88
101   ALBERT   STEVEN ALBERT
[EMAIL PROTECTED] N 811 08/19/97
105   ARKEMA   DORMANDARKEMA
[EMAIL PROTECTED] N 729 10/01/87
110   ARKEMA   DUANE  ARKEMA
[EMAIL PROTECTED] N 428 10/28/91
125   BECKER   ALOYSIUS   BECKER
[EMAIL PROTECTED] N1118 03/01/93

Sorry about the wrap, but in the file each is on its own line (no wrap).
Cut and paste causes a
wrap in outlook.

thanks,
Darryl

-Original Message-
From: Darryl Hoar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 4:31 PM
To: [EMAIL PROTECTED]
Subject: mysqlimport command question


greetings,
I have created a text file in windows (from a database) that
has the format
empnum name fname lname ext email listit bm bd hd

bm, bd are integers and hd is date.

I have tried to import using the follow command

#mysqlimport -u root -pmypass -d --local iweb emp2.txt

it imports the 97 records with 970 errors.  When I look at the
records created, the empnum field is properly populated, but all
other fields are NULL.

what am I screwing up here ?

thanks,
Darryl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?[EMAIL PROTECTED]

--

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqlimport problems

[Paul DuBois]

At 0:34 +0100 9/4/03, Neil Belch wrote:
Hi all, new to the list and new to mySQL...

I've only been at it for a few days and am looking at importing text 
files to databases.
I'm having trouble with mysqlimport and would appreciate any help or 
solutions as to what I'm doing wrong - I'm sure its a dumb mistake!

I tried LOAD DATA INFILE and it worked fine. Here's the code I used for it:

LOAD DATA LOCAL INFILE 'master_name.txt'
INTO TABLE master_name
FIELDS TERMINATED BY ':::'
ENCLOSED BY ''
LINES TERMINATED BY '\r\n'
This works fine. Every record is in the table and all the data is in there.

When I try the same thing with mysqlimport, the records are there 
but there's no data in any of the fields!
The code I used for it:

mysqlimport -L -h localhost -u supercontact -ppassword 
--fields-terminated-by=::: --fields-enclosed-by=\ 
--lines-terminated-by=\r\n contactDB master_name.txt

However, that whole command above won't fit on the DOS prompt in one 
go, so I have to enter most of the options in one command, then the 
remaining ones along with the db name and txt file in a second 
command - it seems to work as the records are still added but 
there's no data.
Eh? That can't work; you must enter all the arguments in a *single* command.

You may be able to shorten the command by omitting -h localhost, it
should default to the local host anyway.  Or you could put options in
an option file such as C:\my.cnf:
[mysqlimport]
local
host=localhost
user=supercontact
fields-terminated-by=:::
etc.

Of course, if you don't want to use those options the next time you
run mysqlimport, you'll need to remove them from the option file.

(On another note, is there a way to fit the command onto one line in 
the DOS prompt?)

Any help is greatly appreciated.
Thanks,
Neil Belch


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport command question

[Fortuno, Adam]

Darryl,

Provide a copy of the table's details either with a describe table output or
the table's definition and a sample of the input file's top 5-rows.

Regards,
Adam

-Original Message-
From: Darryl Hoar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 4:31 PM
To: [EMAIL PROTECTED]
Subject: mysqlimport command question


greetings,
I have created a text file in windows (from a database) that has the format
empnum name fname lname ext email listit bm bd hd

bm, bd are integers and hd is date.

I have tried to import using the follow command

#mysqlimport -u root -pmypass -d --local iweb emp2.txt

it imports the 97 records with 970 errors.  When I look at the
records created, the empnum field is properly populated, but all
other fields are NULL.

what am I screwing up here ?

thanks,
Darryl

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport command question

[Darryl Hoar]

To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE: mysqlimport command question


Darryl,

Provide a copy of the table's details either with a describe
table output or
the table's definition and a sample of the input file's top 5-rows.

Regards,
Adam

OK, here is the table structure:
mysql describe emp2;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| empnum | varchar(4)  | YES  | | NULL|   |
| name   | varchar(30) | YES  | | NULL|   |
| fname  | varchar(20) | YES  | | NULL|   |
| lname  | varchar(30) | YES  | | NULL|   |
| email  | varchar(60) | YES  | | NULL|   |
| ext| varchar(4)  | YES  | | NULL|   |
| listit | char(1) | YES  | | NULL|   |
| bdm| int(11) | YES  | | NULL|   |
| bdd| int(11) | YES  | | NULL|   |
| hdate  | datetime| YES  | | NULL|   |
++-+--+-+-+---+
10 rows in set (0.00 sec)

mysql

the first 5 records of emp2.txt are:
100   ALBERT   JEFFREYALBERT
[EMAIL PROTECTED] N 3 6 04/11/88
101   ALBERT   STEVEN ALBERT
[EMAIL PROTECTED] N 811 08/19/97
105   ARKEMA   DORMANDARKEMA
[EMAIL PROTECTED] N 729 10/01/87
110   ARKEMA   DUANE  ARKEMA
[EMAIL PROTECTED] N 428 10/28/91
125   BECKER   ALOYSIUS   BECKER
[EMAIL PROTECTED] N1118 03/01/93

Sorry about the wrap, but in the file each is on its own line (no wrap).
Cut and paste causes a
wrap in outlook.

thanks,
Darryl

-Original Message-
From: Darryl Hoar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 4:31 PM
To: [EMAIL PROTECTED]
Subject: mysqlimport command question


greetings,
I have created a text file in windows (from a database) that
has the format
empnum name fname lname ext email listit bm bd hd

bm, bd are integers and hd is date.

I have tried to import using the follow command

#mysqlimport -u root -pmypass -d --local iweb emp2.txt

it imports the 97 records with 970 errors.  When I look at the
records created, the empnum field is properly populated, but all
other fields are NULL.

what am I screwing up here ?

thanks,
Darryl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?[EMAIL PROTECTED]

--

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport: Error: Can't get stat of

[James E Hicks III]

Please ignore this this problem as I found the answer. For those inquring minds,
I added a -L to the mysqlimport command and we are all happy now.

James


-Original Message-
From: James E Hicks III [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 2:48 PM
To: Mysql
Subject: mysqlimport: Error: Can't get stat of


Why am I getting this error? I have looked on google and it said to use
the full path when naming the import file. I have done this and still
get the error. What else could I be doing wrong?

#
mysqlimport -d --fields-optionally-enclosed-by=\' --fields-terminated-by=, --use
r=userid --password=pword DB_2update /fullpathto/thefile.SQL
mysqlimport: Error: Can't get stat of '/fullpathto/thefile.SQL' (Errcode: 13), w
hen using table: thefile




For the filter.
sql, query



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport: Error: Can't get stat of

[Don Read]

On 24-Mar-2003 James E Hicks III wrote:

snipage

 mysqlimport: Error: Can't get stat of '/fullpathto/thefile.SQL' (Errcode:
 13), when using table: thefile
 

localhost.dread$ perror 13
Error code  13:  Permission denied
localhost.dread$ 

-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport: Error: Can't get stat of

[Dan Wright]

You're having a permissions problem.

From the Load data infile section of the manual:
For security reasons, when reading text files located on the server, the
files must either reside in the database directory or be readable by all.
Also, to use LOAD DATA INFILE on server files, you must have the FILE
privilege on the server host.

Try chmoding it 666 or moving it to the same partition as your MySQL data
dir.

Dan


-Original Message-
From: James E Hicks III [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 2:48 PM
To: Mysql
Subject: mysqlimport: Error: Can't get stat of


Why am I getting this error? I have looked on google and it said to use
the full path when naming the import file. I have done this and still
get the error. What else could I be doing wrong?

#
mysqlimport -d --fields-optionally-enclosed-by=\' --fields-terminated-by=, -
-use
r=userid --password=pword DB_2update /fullpathto/thefile.SQL
mysqlimport: Error: Can't get stat of '/fullpathto/thefile.SQL' (Errcode:
13), w
hen using table: thefile




For the filter.
sql, query

James E Hicks III
Noland Company
2700 Warwick Blvd
Newport News, VA 23607
757-928-9000 ext 435
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport: Error: Can't get stat of

[Jennifer Goodie]

Probably a permission problem.  If it cannot read the file it cannot import
it.
monster perror 13
Error code  13:  Permission denied


-Original Message-
From: James E Hicks III [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 11:48 AM
To: Mysql
Subject: mysqlimport: Error: Can't get stat of


Why am I getting this error? I have looked on google and it said to use
the full path when naming the import file. I have done this and still
get the error. What else could I be doing wrong?

#
mysqlimport -d --fields-optionally-enclosed-by=\' --fields-terminated-by=, -
-use
r=userid --password=pword DB_2update /fullpathto/thefile.SQL
mysqlimport: Error: Can't get stat of '/fullpathto/thefile.SQL' (Errcode:
13), w
hen using table: thefile




For the filter.
sql, query

James E Hicks III
Noland Company
2700 Warwick Blvd
Newport News, VA 23607
757-928-9000 ext 435
[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqlimport error

[Stefan Immel]

 it is NO permission Problem, the file ist world readable and 
 the user CAN access the DB.
stupid me, it WAS a permission problem. The permission for a directory in the path 
wasn't right.

sql, mysql or what else the spam filter needs.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: mysqlimport error

[Egor Egorov]

On Tuesday 18 February 2003 12:08, Stefan Immel wrote:

 we get a strange error when trying to import data into our database:
 mysqlimport: Error: Can't get stat of 'KUNDEN' (Errcode: 13), when using
 table: KUNDEN

 it is NO permission Problem, the file ist world readable and the user CAN
 access the DB.

Check if the user that mysql server is ran under has permission to the file, 
and check also privileges on the directories.





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: [ mysqlimport ]

[Joseph Bueno]

Hi,

You should not use mysqlimport but mysql:
mysql my_db  file.sql

See http://www.mysql.com/doc/en/mysqldump.html
for details.

Regards,
Joseph Bueno

Elby Vaz wrote:

I created a file.sql with mysqldump.

mysqldump my_db my_table  file.sql

What I do to get this file with the mysqlimport?

mysqlimport ??

Thanks,
e.





_
MSN Messenger: converse com os seus amigos online.  
http://messenger.msn.com.br


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail 
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: [ mysqlimport ]

[Stefan Hinz, iConnect \(Berlin\)]

Elby,

 I created a file.sql with mysqldump.
  mysqldump my_db my_table  file.sql

 What I do to get this file with the mysqlimport?
  mysqlimport ??

You do this with mysql in batch mode, not with mysqldump:

shell mysql your_database  file.sql

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  Geschäftsführer / CEO iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: Elby Vaz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, January 30, 2003 9:39 PM
Subject: [ mysqlimport ]


 I created a file.sql with mysqldump.

  mysqldump my_db my_table  file.sql

 What I do to get this file with the mysqlimport?

  mysqlimport ??

 Thanks,
 e.





 _
 MSN Messenger: converse com os seus amigos online.
 http://messenger.msn.com.br


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: [ mysqlimport ]

[gerald_clark]

You don't.
you use the file as input to the mysql client.

mysql  file.sql

Elby Vaz wrote:


I created a file.sql with mysqldump.

mysqldump my_db my_table  file.sql

What I do to get this file with the mysqlimport?

mysqlimport ??

Thanks,
e.









-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport access denied errors

[Paul DuBois]

At 13:04 + 12/20/02, Imran Khan wrote:

Hi,
I'm working on SOlaris 8 with MySQL v3.23.54.

I'm trying to give a user 'user1' access to one of my databases 
'db1', and specifically one table 'table1'.

As root I've run:
msql grant all on db1 to user1@localhost

-and it appears to work fine.

Try this instead:

grant all on db1.* to user1@localhost



However when as the solaris user 'user1', I run the following from 
the shell prompt:
$:/usr/local/mysql/bin/mysqlimport db1 table1.txt
I get:

/usr/local/mysql/bin/mysqlimport: Error: Access denied for user: 
'user1@localhost' (Using password: NO), when using table: table1

--

I want this user 'user1' to be able to run the mysqlimport command 
without prompting for a password.

Can you help?

thanks.


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: mysqlimport

[Egor Egorov]

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[vinita vigine Murugiah]

HI Egor
OK, I want to create new databases, to do that I need database 
create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given permission 
to access other databases.
Thanks

Egor Egorov wrote:

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[Paul DuBois]

At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:
HI Egor
OK, I want to create new databases, to do that I need database 
create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given 
permission to access other databases.

No, it should be db_name.*

Thanks

Egor Egorov wrote:

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[vinita vigine Murugiah]

Hi paul

Paul DuBois wrote:

 At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

 HI Egor
OK, I want to create new databases, to do that I need database 
 create privilege.

 Grant CREATE ON ##
 to ddb@localhost IDENTIFIED BY 'passwd'

 What should go into #, is it a *? but I should NOT given 
 permission to access other databases.


 No, it should be db_name.* 

Then I can ONLY create database called db_name,  then every time when 
I create a database, I have to ask the root to give me create permission 
on xxx database. This is inconvenient. Is there any other work around??
Thanks



 Thanks

 Egor Egorov wrote:

 vinita,
 Monday, September 23, 2002, 9:12:24 AM, you wrote:

 vvM I can create a database called test,
 vvM mysql create database test;
 vvM Query OK, 1 row affected (0.03 sec)

 vvM BUT I can't create any other databases???

 vvM mysql create database tem;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 
 'tem'

 vvM mysql create database proj;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 
 'proj'

 Seems user 'ddb' or anonymous user has privileges only on the database
 'test'.



 -
 Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[Paul DuBois]

At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote:
Hi paul

Paul DuBois wrote:

At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

HI Egor
OK, I want to create new databases, to do that I need database 
create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given 
permission to access other databases.


No, it should be db_name.*

Then I can ONLY create database called db_name,  then every time 
when I create a database, I have to ask the root to give me create 
permission on xxx database. This is inconvenient. Is there any other 
work around??

As far as I can tell, you're not making any sense:

- First you say you want to grant permission for a given database, and
   *NOT* to access other databases.
- Now you say you don't want to have to specify permissions for given
   databases.

You can't have it both ways.  Do you want permission for a specific
database, or for all databases?

If I am misinterpreting you, then please be more specific.

Thanks



Thanks

Egor Egorov wrote:

vinita,
Monday, September 23, 2002, 9:12:24 AM, you wrote:

vvM I can create a database called test,
vvM mysql create database test;
vvM Query OK, 1 row affected (0.03 sec)

vvM BUT I can't create any other databases???

vvM mysql create database tem;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem'

vvM mysql create database proj;
vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj'

Seems user 'ddb' or anonymous user has privileges only on the database
'test'.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[vinita vigine Murugiah]

Paul DuBois wrote:

 At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote:

 Hi paul

 Paul DuBois wrote:

 At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

 HI Egor
OK, I want to create new databases, to do that I need database 
 create privilege.

 Grant CREATE ON ##
 to ddb@localhost IDENTIFIED BY 'passwd'

 What should go into #, is it a *? but I should NOT given 
 permission to access other databases.



 No, it should be db_name.*


 Then I can ONLY create database called db_name,  then every time 
 when I create a database, I have to ask the root to give me create 
 permission on xxx database. This is inconvenient. Is there any other 
 work around??


 As far as I can tell, you're not making any sense:

 - First you say you want to grant permission for a given database, and
   *NOT* to access other databases.
 - Now you say you don't want to have to specify permissions for given
   databases.

 You can't have it both ways.  Do you want permission for a specific
 database, or for all databases?

 If I am misinterpreting you, then please be more specific.

HI sorry for the confusion.
well.. I want to create a database where I can give *any* name 
(ofcourse, the database doesn't exist).

Are you trying to say, I need to ask the root, to give me create 
privilege on database xxx, even before it exist, then only I can 
create the database xxx. Then every time when I want to create a new 
database, I need to ask the root to give create privilege ???

thanks


 Thanks



 Thanks

 Egor Egorov wrote:

 vinita,
 Monday, September 23, 2002, 9:12:24 AM, you wrote:

 vvM I can create a database called test,
 vvM mysql create database test;
 vvM Query OK, 1 row affected (0.03 sec)

 vvM BUT I can't create any other databases???

 vvM mysql create database tem;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to 
 database 'tem'

 vvM mysql create database proj;
 vvM ERROR 1044: Access denied for user: 'ddb@localhost' to 
 database 'proj'

 Seems user 'ddb' or anonymous user has privileges only on the 
 database
 'test'.




 -
 Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




 -
 Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Re: mysqlimport

[Paul DuBois]

sql,query


At 11:49 +1000 9/24/02, vinita vigine Murugiah wrote:
Paul DuBois wrote:

At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote:

Hi paul

Paul DuBois wrote:

At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote:

HI Egor
 OK, I want to create new databases, to do that I need
database create privilege.

Grant CREATE ON ##
to ddb@localhost IDENTIFIED BY 'passwd'

What should go into #, is it a *? but I should NOT given
permission to access other databases.



No, it should be db_name.*


Then I can ONLY create database called db_name,  then every time
when I create a database, I have to ask the root to give me create
permission on xxx database. This is inconvenient. Is there any
other work around??


As far as I can tell, you're not making any sense:

- First you say you want to grant permission for a given database, and
*NOT* to access other databases.
- Now you say you don't want to have to specify permissions for given
databases.

You can't have it both ways.  Do you want permission for a specific
database, or for all databases?

If I am misinterpreting you, then please be more specific.

HI sorry for the confusion.
well.. I want to create a database where I can give *any* name
(ofcourse, the database doesn't exist).

Are you trying to say, I need to ask the root, to give me create
privilege on database xxx, even before it exist, then only I can
create the database xxx. Then every time when I want to create a
new database, I need to ask the root to give create privilege ???

Well, yes, you need to have privileges on a database to be able to create
it.  However, if you have the global CREATE privilege, you can create *any*
database.  So maybe that's what you want.  The GRANT statement for
this will look like:

GRANT CREATE ON *.* TO ddb@localhost IDENTIFIED BY 'passwd';

*.* as a level specifier means global.

Is that what you want?

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Re: mysqlimport

[Paul DuBois]

sql,query


HI sorry for the confusion.
well.. I want to create a database where I can give *any* name
(ofcourse, the database doesn't exist).

Are you trying to say, I need to ask the root, to give me create
privilege on database xxx, even before it exist, then only I can
create the database xxx. Then every time when I want to create a
new database, I need to ask the root to give create privilege ???


Well, yes, you need to have privileges on a database to be able to create
it.  However, if you have the global CREATE privilege, you can create *any*
database.  So maybe that's what you want.  The GRANT statement for
this will look like:

GRANT CREATE ON *.* TO ddb@localhost IDENTIFIED BY 'passwd';

*.* as a level specifier means global.

Is that what you want?


Yes! here I go again!!
If I get the global CREATE, yes I can create any databases, but also
it will allows me to create tables under any database?? isn't that
right??

Right.  CREATE applies both to databases and to tables.  There are
not separate create-database and create-table privileges.

Is it possible, i can create new databases (with any name) but I
will not have any access to other existing database??
Thanks

No.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: RE: mysqlimport usage

[Egor Egorov]

James,
Tuesday, July 23, 2002, 8:32:10 PM, you wrote:

JC There didn't seem to be any responses to this question last time I posted.
JC So I'll try again.  Does anyone know where to locate mysqlimport logs, if
JC there are any?

No, mysqlimport doesn't write logs and you can't see warnings.
mysqlimport just send LOAD DATD INFILE command:
http://www.mysql.com/doc/L/O/LOAD_DATA.html





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mysqlimport usage

[James Ching]

There didn't seem to be any responses to this question last time I posted.
So I'll try again.  Does anyone know where to locate mysqlimport logs, if
there are any?

TIA

James

-Original Message-
From: James Ching [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 18, 2002 4:54 PM
To: [EMAIL PROTECTED]
Subject: mysqlimport usage


Hi,

Does anyone know where to view mysqlimport logs?  After running with
--replace and I can see a number of rows recorded as deleted and some as
warnings.  I can't locate any history anywhere - not in the current
directory, not in mysql installation location, not in in syslog...

Thanks much

James


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysqlimport

[Victoria Reznichenko]

Jonas,
Friday, May 17, 2002, 4:54:18 PM, you wrote:

JO Receive the message: 

JO mysqlimport: Error: The used command is not 
JO allowed with this MySQL version, when using table: t_parameter.

Did you tried to use mysqlimport with --local option? 
If so, you should specify local-infile for MySQL client and 
server. Look at:
 http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html

JO What´s this??
JO Thanks,
JO /Jonas




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport problem

[Egor Egorov]

Scott,

Tuesday, April 9, 2002, 6:20:38 PM, you wrote:


SA I been using mysqlimport for a long time with no problems.

SA But today when I try (as root)

SA mysqlimport admin /usr/local/systems/admin/data/person.txt -p

SA I get

SA mysqlimport: Error: Can't get stat of
SA '/usr/local/systems/admin/data/person.txt' (Errcode: 13), when using
SA table:
SA person

SA even though stat /usr/local/systems/admin/data/person.txt

SA File: /usr/local/systems/admin/data/person.txt
SA   Size: 1497   Blocks: 8 Regular File
SA Access: (0444/-r--r--r--) Uid: (0/root)  Gid: (0/
SA root)
SA Device: 305Inode: 245470 Links: 1
SA Access: Tue Apr  9 15:54:34 2002
SA Modify: Tue Apr  9 01:00:01 2002
SA Change: Tue Apr  9 15:35:52 2002

SA What can cause it to stop working all of a sudden?

[]

Looks weird to me. Stat is ok, but mysqlimport can't get the file..
Maybe, try to take a look at the path to the file, maybe execute
permission for a directory is missing?

The other source of the problem could be that error 13 belongs to a
table, not to a file. Check permissions for table files.

SA Scott





-- 
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport

[Paul DuBois]

At 22:04 -0700 3/25/02, Keith Spiller wrote:
Hello,

mysqlimport --replace --fields-terminated-by=\| --verbose 
--user=user --password=mysql database tables.sql

I'm trying to use the mysqlimport command.  I have multiple tables 
listed in my table.sql file.  But the command requires that the 
filesname be the same as the table you try to import.  Is there 
anyway to import all of the tables at once?

How would it know which data line goes in which table?



Keith


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport error!

[Gerald Clark]

That means the server does not have permission to read the file.

James E. Hicks III wrote:

 What exactly does this error mean?
 
 mysqlimport: Error: Can't get stat of '/home/jameshicks/NONSTOCK' (Errcode: 13),
 when using table: NONSTOCK
 
 James
 
 
 mysql
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport and remote host

[Gerald Clark]

If you can ssh to the host you should be able to
use scp to copy the file over.



Lucy wrote:
 
 Hi
 I'd successfully loaded a text file to a previously created table on my
 localhost/server.  It all works fine.
 I'm having trouble doing the same to a remote host.  I've checked the manual
 for the syntax for --local, which is probably the problem.  I'm not allowed
 to FTP the file to the remote's database server.
 
 1.  I have to initially go through SSH to access the remote host server.
 Fine, I get through to the shell prompt.
 2.  I use username, password, and DB Name to access the remote server, which
 I handle through the mysqlimport options OK.
 3.  I understand the basic mysql syntax except for interpreting the --local
 option :
 shellmysqlimport [options] database textfile1
 
 How do I specify the --local option to tell the remote host where to find
 the location of the textfile on my pc?
 I've also placed a copy of textfile on the pc's d drive (outside subdirecs),
 to reduce complications of forward/back slashes system differences.  Same
 error code Errcode2, can't find file for table.  Do I need to specify
 something else with --local, such as the Port Number?  I tried specifying
 via the SSH port, but no go.
 
 Some variations I've tried unsuccessfully include:
 shell mysqlimport ...etc... databaseName --local textfile1
 shell mysqlimport ...etc... databaseName --local "d:textfile1"
 shell mysqlimport ...etc... databaseName --local "d:\\subdir\\textfile1"
 shell mysqlimport ...etc... databaseName --local "d:subdir.textfile1"
 shell mysqlimport ...etc... databaseName --local "d:\subdir\textfile1"
 shell mysqlimport ...etc... databaseName --local "d:/subdir/textfile1"
 shell mysqlimport ...etc... databaseName --local -P xx
 "d:\\subdir\\textfile1"
 
 Can anyone help me with syntax to specify the --local option to tell the
 remote host where to find the location of the textfile on my pc?  Cheers,
 Lucy
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport and remote host

[Lucy]

Hi Gerald
Thanks a lot for the suggestion, but I'm not sure it will solve my prob,
'cos I dont want to copy a whole DB, and it's not on the same server anyway.

I think it's my --local syntax  in the mysqlimport (given in earlier message
posted) that's dodgy.  I've also tried an equals sign,  doubling the "\"
character for Windows 2000 and also Unix style `/' character:
shellmysqlimport ..etc..  DBName --local="d:\\textfile1"
shellmysqlimport ..etc..  DBName --local='d:/textfile1'
shellmysqlimport ..etc..  DBName -L textfile1
shellmysqlimport ..etc..  DBName -L "d:\\textfile1"
shellmysqlimport ..etc..  DBName -L 'd:/textfile1'
shellmysqlimport ..etc..  DBName -L="d:\\textfile1"
The error code still states can't find file for the table (already created).
(I'm trying to import textfile from pc Win2000 to remote host, via SSH, and
using mysqlimport options for username, host, password, not allowed to FTP
to remote DB server).
Cheers, Lucy


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport puzzle

[Atle Veka]

sounds strange, but if you do it this way, you should have no problems:

mysqldump test_database  test_database.dump
mysql test_database  test_database.dump

will import everything in test_database.dump into your database..

somehow, you had selected your database, but that got dropped ?


Atle

On Tue, 20 Feb 2001, John Jensen wrote:

 
 Yesterday, I was having fun writing mysql scripts and importing them. 
 I would do a drop database and re-import, editing the data in the 
 script.
 
 Today, I couldn't for the life of me do an import on a mysql export 
 file, even using commands stored from yesterday, in bash_history.
 I am getting errors like:
 
 ERROR 1046 at line 10: No Database Selected.
 
 Line 10 just says "CREATE TABLE Test (" with only comment preceeding, 
 until I realized that mysqldump was not putting:
 
 CREATE DATABASE Test;
 USE Test;
 
 at the beginning, so mysqlimport was useless where the database did 
 not yet exist. Do I have to manually add those lines to transfer a 
 dump file to a new machine?
 
 
 John Jensen
 520 Goshawk Court
 Bakersfield, CA 93309
 661-833-2858
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport - LOAD DATA LOCAL INFILE

[Rus]

I suppose that only way to reorganize unique field is to use another table.

- Original Message - 
From: Irmund Thum [EMAIL PROTECTED]
To: Rus [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, February 13, 2001 8:53 AM
Subject: Re: mysqlimport - LOAD DATA LOCAL INFILE


 Rus schrieb:
  
  Maybe it's problem with end_of_line symbol.
  You could use PHP to insert records directly into db.
  
  - Original Message -
  From: Irmund Thum [EMAIL PROTECTED]
  Cc: [EMAIL PROTECTED]
  Sent: Monday, February 12, 2001 2:30 PM
  Subject: mysqlimport - LOAD DATA LOCAL INFILE
  
 
 thanks - but it was another problem (or better 2);
 reorganzing primary key, unique and NOT NULL for some fields
 I've got 106 of the 137 lines into the database and this is good enough
 for the tutorial site
 i.t
 -- 
 http://it97.dyn.dhs.org/
  IrmundThum
 +49 179 6998564 
 +49 6374 992541


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqlimport - LOAD DATA LOCAL INFILE

[Irmund Thum]

Rus schrieb:
 
 Maybe it's problem with end_of_line symbol.
 You could use PHP to insert records directly into db.
 
 - Original Message -
 From: Irmund Thum [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Sent: Monday, February 12, 2001 2:30 PM
 Subject: mysqlimport - LOAD DATA LOCAL INFILE
 

thanks - but it was another problem (or better 2);
reorganzing primary key, unique and NOT NULL for some fields
I've got 106 of the 137 lines into the database and this is good enough
for the tutorial site
i.t
-- 
http://it97.dyn.dhs.org/
 IrmundThum
+49 179 6998564 
+49 6374 992541
 Kryptographische Unterschrift mit S/MIME