Sqsh + NULL Values
Hi, Anyone here familiar with sqsh? I'm trying to get bcp going here for replication between a MSSQL server(2000) to a MySQL server (5.0). Since I've not found any decent way to do the replication, I'm now using sqsh to do it via the CLI. $sqsh -S Server -U user -i ~/bcp.txt -L bcp_colsep=',' -L datetime='%Y-% m-%d %H:%M:%S' then using mysql's load data infile to do the inputing. I have a problem in that NULL values are not being returned as NULL. eg: the outputing bcp file will be AAA,2006-07-09 12:00:00,,BBB the field between the date and BBB is being treated as blank instead of NULLs and I'm getting into a problem here. Does anyone here has any pointers on how to get sqsh to output the fields as NULL eg: AAA,2006-07-09 12:00:00,NULL,BBB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to skip reading /etc/my.cnf by mysqld
Hi: I am trying to test mysql options and would like to have mysqld only read ~/.my.cnf instead of using the normal sequence to read /etc/my.cnf $datadir/my.cnf and ~/.my.cnf. This would prevent many unexpected issues in testing. But I could not find the way to disable the /etc/my.cnf. Any help would be appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sqsh + NULL Values
On Sat, 2006-10-07 at 13:55 +0800, Ow Mun Heng wrote: Hi, Anyone here familiar with sqsh? I'm trying to get bcp going here for replication between a MSSQL server(2000) to a MySQL server (5.0). Since I've not found any decent way to do the replication, I'm now using sqsh to do it via the CLI. $sqsh -S Server -U user -i ~/bcp.txt -L bcp_colsep=',' -L datetime='%Y-% m-%d %H:%M:%S' then using mysql's load data infile to do the inputing. I have a problem in that NULL values are not being returned as NULL. eg: the outputing bcp file will be AAA,2006-07-09 12:00:00,,BBB the field between the date and BBB is being treated as blank instead of NULLs and I'm getting into a problem here. Does anyone here has any pointers on how to get sqsh to output the fields as NULL eg: AAA,2006-07-09 12:00:00,NULL,BBB I found the solution. See here http://lotso.livejournal.com/81385.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to skip reading /etc/my.cnf by mysqld
Hi, Try, libexec/mysqld --verbose --help for mysqld options with variables or bin/mysqld_safe --verbose --help for mysqld options For instance I want only /etc/my.cnf read and skip reading of other default file, ./bin/mysqld_safe --defaults-file=/etc/my.cnf Hope this will do good. Thanks ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 07, 2006 11:41 AM Subject: How to skip reading /etc/my.cnf by mysqld Hi: I am trying to test mysql options and would like to have mysqld only read ~/.my.cnf instead of using the normal sequence to read /etc/my.cnf $datadir/my.cnf and ~/.my.cnf. This would prevent many unexpected issues in testing. But I could not find the way to disable the /etc/my.cnf. Any help would be appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 query takes 100x longer than MySQL 4.1.10
mos wrote: I have a simple query in MySQL 5.0.24: insert into table1 (col1) select distinct col1 from bigtable; that will run for 1:14:18. Both tables are MyISAM and table1 was just created with 2 columns and is empty. The select distinct col1 from bigtable takes only 2 minutes to run if I run it by itself (without the Insert statement), so why does inserting it into Table1 take over an hour? This worked fine under MySQL 4.1.10 BigTable has 30 million rows in it and will return approx 7000 distinct values. TIA Mike are your tables indexed? is col1 a primary key? have a look at optimising the table HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help on before insert trigger
I would like to prohibit the value 'xxx' on my column title, and if it does contain the value I would like to create an exception by assigning 'xxx' to the primary key id which is int(5). This is what I do but I get an error on its creation so I guess it's not the right way: CREATE TRIGGER testref BEFORE INSERT ON bookmarks FOR EACH ROW BEGIN if NEW.title like '%xxx%' set NEW.id='xxx'; END; the error: server version for the right syntax to use near ': set NEW.id='xxx' at line 4 any idea how to do that? thanx in advance pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on before insert trigger
At 16:23 +0200 10/7/06, Patrick Aljord wrote: I would like to prohibit the value 'xxx' on my column title, and if it does contain the value I would like to create an exception by assigning 'xxx' to the primary key id which is int(5). This is what I do but I get an error on its creation so I guess it's not the right way: CREATE TRIGGER testref BEFORE INSERT ON bookmarks FOR EACH ROW BEGIN if NEW.title like '%xxx%' set NEW.id='xxx'; END; the error: server version for the right syntax to use near ': set NEW.id='xxx' at line 4 any idea how to do that? thanx in advance The syntax for your IF statement isn't correct. You need a THEN after the condition. http://dev.mysql.com/doc/refman/5.0/en/if-statement.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on before insert trigger
Hi Patrick,
Patrick Aljord wrote:
I would like to prohibit the value 'xxx' on my column title, and if it
does contain the value I would like to create an exception by
assigning 'xxx' to the primary key id which is int(5).
This is what I do but I get an error on its creation so I guess it's
not the right way:
CREATE TRIGGER testref BEFORE INSERT ON bookmarks
FOR EACH ROW
BEGIN
if NEW.title like '%xxx%'
set NEW.id='xxx';
END;
the error:
server version for the right syntax to use near ':
set NEW.id='xxx' at line 4
You have your IF syntax a little wrong, try this:
CREATE TRIGGER testref BEFORE INSERT ON bookmarks
FOR EACH ROW
BEGIN
IF NEW.title LIKE '%xxx%' THEN
SET NEW.id ='xxx';
END IF;
END;
//
mysql INSERT INTO bookmarks values ('two', 'hawt xxx sex')//
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM bookmarks//
+--+--+
| id | title|
+--+--+
| one | one bookmark |
| xxx | hawt xxx sex |
+--+--+
2 rows in set (0.27 sec)
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? 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: need help on before insert trigger
I meant the error is: mysql CREATE TRIGGER testref BEFORE INSERT ON bookmarks - FOR EACH ROW - BEGIN - IF NEW.title LIKE '%xxx%' THEN - SET NEW.id ='xxx'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET NEW.id ='xxx'' at line 5 mysql END IF; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 mysql END; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on before insert trigger
thanx it works the trigger is created successfully but it has no effect. here it is: delimiter // create trigger testref before insert on bookmarks for each row begin declare dummy char(2); if new.title like '%xxx%' then set new.id='xxx'; end if; end; //create trigger testref before insert on bookmarks - for each row - begin - declare dummy char(2); - if new.title like '%xxx%' - then - set dummy = 'xxx'; - end if; - end; - // then: insert into bookmarks values (1, x); Query OK, 1 row affected, 1 warning (0.00 sec) the row is created with a warning. I would like to prevent it from being created. I would like the insert to be canceled if the value is equal to xxx. any idea how to cancel the insert? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on before insert trigger
On 10/7/06, Patrick Aljord [EMAIL PROTECTED] wrote: thanx it works the trigger is created successfully but it has no effect. here it is: delimiter // create trigger testref before insert on bookmarks for each row begin if new.title like '%xxx%' then set new.id='xxx'; end if; end; //create trigger testref before insert on bookmarks - for each row - begin - declare dummy char(2); - if new.title like '%xxx%' - then - set dummy = 'xxx'; - end if; - end; - // (those are the two different triggers I tried) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get the size of a row
Hi, I wanted to know the size of the data stored in a row of a table. I mean is there something like select size from table where x... -- Regards Abhishek Jain
Re: How to get the size of a row
In the last episode (Oct 07), abhishek jain said: I wanted to know the size of the data stored in a row of a table. I mean is there something like select size from table where x... If you mean something similar to totaling up the sizes reported by DUMP(field) in Oracle, no. The best you can get is to refer to http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html, and manually total up the field widths yourself. For variable-length fields, use LENGTH(field) when fetching the byte count. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving Database from PC to Apple
I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks.
How to force a column to be Float and not Decimal in MySQL 5.0?
I'm creating a table using a Select statement and several of the columns are mathematical expressions. Unfortunately MySQL 5.0 creates them as Decimal and I don't need that much precision. Is there a way to force MySQL to create these columns as float? Example: create table x1 select 123.1*2.1 as Colx; This creates Colx as Decimal and I'd like to have it as a Float. Is there a way to force it to use Float? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Database from PC to Apple
At 04:00 PM 10/7/2006, you wrote: I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. David, Try MySQLDump which is set up to do just this. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Database from PC to Apple
Thanks. Is this something I can do through phpMyAdmin? I'm not used to working with MySQL directly and don't understand exactly what this command means: shell mysqldump [options] --all-databasesDoes shell mean I have to be working in some sort of command line program? Also, if I can't figure this out and have to resort to creating new databases, should I just ignore the Collation feature, presumably letting it set a default setting, or should I enter a particular value? Thanks. - Original Message From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 2:26:19 PM Subject: Re: Moving Database from PC to Apple At 04:00 PM 10/7/2006, you wrote: I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. David, Try MySQLDump which is set up to do just this. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Database from PC to Apple
Hi David mysqldump is a command-line program which you can run through the terminal window. The Terminal application is in the Utilities directory under the Applications directory. The unix command-line interface is amazingly useful; even though it may seem a bit intimidating at first, it is well worth getting used to. You will probably want to drag the Terminal application down to the Dock so you won't have to dig around for it every time. (Or you can just open the Spotlight window with Command-Space and then type Terminal) You can also do the same thing with a gui tool, the MySQL administrator. There are three programs in this suite and they are all very nice (the newest one, the MySQL workbench, still crashes a bit but I'm sure that will improve.) You can download the gui tools here: http://dev.mysql.com/downloads/gui-tools/5.0.html and then from the MySQL Administrator program, choose Backup. Good luck. I use a MacBook Pro for MySQL work also (mostly developing things that will run on a linux server) and I have been very pleased with it. Douglas Sims [EMAIL PROTECTED] On Oct 7, 2006, at 5:01 PM, David Blomstrom wrote: Thanks. Is this something I can do through phpMyAdmin? I'm not used to working with MySQL directly and don't understand exactly what this command means: shell mysqldump [options] --all-databasesDoes shell mean I have to be working in some sort of command line program? Also, if I can't figure this out and have to resort to creating new databases, should I just ignore the Collation feature, presumably letting it set a default setting, or should I enter a particular value? Thanks. - Original Message From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 2:26:19 PM Subject: Re: Moving Database from PC to Apple At 04:00 PM 10/7/2006, you wrote: I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. David, Try MySQLDump which is set up to do just this. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Mike -- 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]
Re: Moving Database from PC to Apple
Ah, that's better. I learned how to use Terminal just the other day. I'll check out the other programs you recommended, too. Thanks. - Original Message From: Douglas Sims [EMAIL PROTECTED] To: David Blomstrom [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 3:33:59 PM Subject: Re: Moving Database from PC to Apple Hi David mysqldump is a command-line program which you can run through the terminal window. The Terminal application is in the Utilities directory under the Applications directory. The unix command-line interface is amazingly useful; even though it may seem a bit intimidating at first, it is well worth getting used to. You will probably want to drag the Terminal application down to the Dock so you won't have to dig around for it every time. (Or you can just open the Spotlight window with Command-Space and then type Terminal) You can also do the same thing with a gui tool, the MySQL administrator. There are three programs in this suite and they are all very nice (the newest one, the MySQL workbench, still crashes a bit but I'm sure that will improve.) You can download the gui tools here: http://dev.mysql.com/downloads/gui-tools/5.0.html and then from the MySQL Administrator program, choose Backup. Good luck. I use a MacBook Pro for MySQL work also (mostly developing things that will run on a linux server) and I have been very pleased with it. Douglas Sims [EMAIL PROTECTED] On Oct 7, 2006, at 5:01 PM, David Blomstrom wrote: Thanks. Is this something I can do through phpMyAdmin? I'm not used to working with MySQL directly and don't understand exactly what this command means: shell mysqldump [options] --all-databasesDoes shell mean I have to be working in some sort of command line program? Also, if I can't figure this out and have to resort to creating new databases, should I just ignore the Collation feature, presumably letting it set a default setting, or should I enter a particular value? Thanks. - Original Message From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 2:26:19 PM Subject: Re: Moving Database from PC to Apple At 04:00 PM 10/7/2006, you wrote: I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. David, Try MySQLDump which is set up to do just this. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED]
Too many open processes??
I am using mysql_pconnect from PHP to connect to our mysql server. However, whenever there is a temprorary surge in the number of users, i.e. concurrent users jump from 30 to 200 for like 5 minutes, Apache creates 200 processes and after the surge is over, they die gracefully, and # of processes goes down to ~ 30. However, this is not the case for MySQL. During the surge, it creates 200 processes and these processes stay there forever (till the next re-start), even though there are only 20-30 concurrent users after the surge. Is there a way to configure mysql so that it will kill a process after a certain period of idle time, just like Apache does? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting Up MySQL Administrator
I just downloaded MySQL Administrator and am now trying to set it up. Can anyone tell me what stored connection and Server Host mean? I'm using Apache on Windows XP, but I'm not sure what they mean by Server Host. 3306 is listed under Port by default. Also, what are the default username and password for MySQL? I have a username and password for my database, but I don't recall creating them for the overall MySQL program. As I recall, root serves as the username or password. Thanks.
Re: Setting Up MySQL Administrator
David Blomstrom wrote: I just downloaded MySQL Administrator and am now trying to set it up. Can anyone tell me what stored connection and Server Host mean? I'm using Apache on Windows XP, but I'm not sure what they mean by Server Host. 3306 is listed under Port by default. Also, what are the default username and password for MySQL? I have a username and password for my database, but I don't recall creating them for the overall MySQL program. As I recall, root serves as the username or password. Thanks. Assuming that you installed mysql (and keep in mind, we're talking about your MYSQL, not apache), then stored connection refers to the saved settings. Server Host is either the ip or the domain name of your server. If you're talking to your local MySQL server, then it's localhost. As far as the username and password, when you installed the mysql server itself, it should have asked you to create a password for the root user, just type that in. Username is the username that you set up, probably root. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting Up MySQL Administrator
OK, I'm halfway there. But I don't understand what you mean by saved settings. Is there some sort of default value I can try? Also, if I can't recover my password, is there a file I can open and retrieve it from? I tried it with localhost, Port 3306, Username: root and the password of one of my databases (but nothing under Stored Connection) and got MySQL error #1045 - Access denied for user 'root'@'localhost' [using password: YES] Thanks. - Original Message From: John Meyer [EMAIL PROTECTED] Assuming that you installed mysql (and keep in mind, we're talking about your MYSQL, not apache), then stored connection refers to the saved settings. Server Host is either the ip or the domain name of your server. If you're talking to your local MySQL server, then it's localhost. As far as the username and password, when you installed the mysql server itself, it should have asked you to create a password for the root user, just type that in. Username is the username that you set up, probably root.
Re: Setting Up MySQL Administrator
David Blomstrom wrote: OK, I'm halfway there. But I don't understand what you mean by saved settings. Is there some sort of default value I can try? Also, if I can't recover my password, is there a file I can open and retrieve it from? I tried it with localhost, Port 3306, Username: root and the password of one of my databases (but nothing under Stored Connection) and got MySQL error #1045 - Access denied for user 'root'@'localhost' [using password: YES] no, this is a password for the root user on your mysql server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many open processes??
In the last episode (Oct 07), Cabbar Duzayak said: I am using mysql_pconnect from PHP to connect to our mysql server. However, whenever there is a temprorary surge in the number of users, i.e. concurrent users jump from 30 to 200 for like 5 minutes, Apache creates 200 processes and after the surge is over, they die gracefully, and # of processes goes down to ~ 30. However, this is not the case for MySQL. During the surge, it creates 200 processes and these processes stay there forever (till the next re-start), even though there are only 20-30 concurrent users after the surge. Mysql doesn't create processes; it creates threads. You are almost certainly running an older Linux kernel which implements threads as processes that share the same memory space. They don't consume any memory on their own, so it doesn't really hurt to have a hundred unused ones. Is there a way to configure mysql so that it will kill a process after a certain period of idle time, just like Apache does? Each thread represents a client connection. mysql_pconnect uses a connection pool which keeps connections open between page loads. I assume php will drop unused connections after a time. Check the docs to see if there's a timeout you can shorten. Mysql also will cache a couple threads after all connections are closed, but it defaults to 4. Run SHOW VARIABLES LIKE 'thread_cache_size' to see what your server's set to. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
