Re: LOAD DATA INFILE with INNODB
Hi, i did it. If you have myisam tables tables rather than innodb say it. if you have specific os, say it. i think you should elaborate, or read carrefully dev.mysql.com/doc Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems importing dump 4.0 4.1
Hi, Hello. Check the character sets with the following statement: show variables like '%char%'; Here's the output: character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ What version of mysqldump did you use to perform a dump? Versions from 4.1.x distributions usually put SET NAMES='utf8' in the beginning of the dump file. These are the first 2 lines of the dump file: -- phpMyAdmin SQL Dump -- version 2.6.1-rc1 Now what should I do exactly to correctly import all foreign characters ? Thanks in advance Regards Roberto Jobet [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Thanks for your reply. What do you mean by right character set= ? The default character set for ver. 4.1 is latin1, right ? This set s= hould support all western european languages ! Regarding --default-ch= aracter-set command in chapter 10 is written that a way to change settin= gs is to recompile... Is there another less painful way to do this ? =0D = Thanks in advance Roberto Jobet Hello. Specify the right= character set with --default-character-set command line option for my= sql (if you're using it for reading dump). Chapter 10 of the manual co= uld be helpful. See: http://dev.mysql.com/doc/mysql/en/charset.html=0D = [EMAIL PROTECTED] [EMAIL PROTECTED] wro= te: Hi, I'm trying to import a db dump made on ver. 4.0, = on a new 4.1 vers=3D ion. All accented characters (french= , spanish) are replaced by a '?'=3D0D =3D What it's the r= ight way to import it ? Thanks in advance Rob= ert=3D o Jobet =3D0A=3D0A=3D0A=3D0A= =3D =3D0ANavighi a 4 MEGA e i primi = 3 mesi sono GRATIS. =3D0AScegli Liber=3D o Adsl Flat senza limiti su= http://www.libero.it=3D0A -- 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 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext/boolean search
Hi! Check the manual for ft_boolean_syntax variable. It defines what character is used for every operator. Space marks default operator - so you can change it. On Jun 06, Sebastian wrote: I created a search app with fulltext, boolean, etc. i have two forms, one that allows the user to just enter a basic search in a single input field and a more advanced form with additional input areas for advanced boolean searches.. now, when you do not specify a boolean operator, what does it default to? i am thinking it defaults to a search similar to using LIKE %string% Basically i want to know what default boolean operator i should use for a 'basic' search or should i not use a boolean for the 'basic' search? i want to provide decent search results without forcing the user to go to advanced mode. just looking for tips/suggestions to tweak the results it returns and anything else i should know. lastly... this might be more related to php, if anyone knows of a reliable function/class to highlight search terms, please let me know. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL (SQL) Newbie.. Need help with a Query
hi, that's the same. If you use between, mysql do the rest for you : mysql explain SELECT * FROM passengers WHERE - reservation_date_time = '2005-01-01 12:10:00' - AND reservation_date_time = '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.01 sec) mysql explain SELECT * FROM passengers WHERE - reservation_date_time between '2005-01-01 12:10:00'AND '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.00 sec) Mathias Selon Cory Robin [EMAIL PROTECTED]: I'm trying to return all records between two dates.. The fields are datetime fields... Which is better? The following or using BETWEEN? (A little lost here) SELECT * FROM passengers WHERE reservation_date_time = '2005-01-01 12:10:00' AND reservation_date_time = '2005-05-01 12:10:00'; -- 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: LOAD DATA INFILE with INNODB
hi, yes you did it and i failed to do so. thats why i am here. i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves only innodb as i mentioned in the very first post, i have read docs and it says that there are 3 ways i can start a transaction and i tried all three i.e. begin work statement, start transaction and SET AUTOCOMMIT=0; regards - Original Message - From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 11:08 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, i did it. If you have myisam tables tables rather than innodb say it. if you have specific os, say it. i think you should elaborate, or read carrefully dev.mysql.com/doc Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug Report (LOAD DATA FROM MASTER on MERGE Tables)
It would appear that LOAD DATA FROM MASTER processes databases and tables alphabetically. When a merge table is being copied, and it's name is alphabetically before some/any/all of it's components, the process fails with a 1017 couldn't find file error. Has this been fixed? If so, as of which version? Is this a bug on the master or the slave side? I ask that because I am replicating from 4.1.x to 5.0.x. Many thanks. Gordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems importing dump 4.0 4.1
Hello. If you're sure that data in your dump file is in latin1 character set, you could perform something like: mysql --default-character-set=latin1 dump_file Roberto Jobet [EMAIL PROTECTED] wrote: Hi, Hello. Check the character sets with the following = statement: show variables like '%char%'; Here's the output: ch= aracter_set_client utf8 character_set_connection utf8 character_set_d= atabase latin1 character_set_results utf8 character_set_server latin1= character_set_system utf8 character_sets_dir /usr/share/mysql/charse= ts/ What version of mysqldump did you use to perform a dump? Ver= sions from 4.1.x distributions usually put SET NAMES=3D'utf8' in the b= eginning of the dump file. These are the first 2 lines of the dump = file: -- phpMyAdmin SQL Dump -- version 2.6.1-rc1 Now what should = I do exactly to correctly import all foreign characters ? Thanks in ad= vance Regards Roberto Jobet [EMAIL PROTECTED] roberto= [EMAIL PROTECTED] wrote: Hi, Thanks for your reply. = What do you mean by right character set=3D ? The defau= lt character set for ver. 4.1 is latin1, right ? This set s=3D hould= support all western european languages ! Regarding --def= ault-ch=3D aracter-set command in chapter 10 is written that a way t= o change settin=3D gs is to recompile... Is there another = less painful way to do this ? =3D0D =3D Thanks in advance=0D = Roberto Jobet Hello. Specify the= right=3D character set with --default-character-set command l= ine option for my=3D sql (if you're using it for reading dump). Chap= ter 10 of the manual co=3D uld be helpful. See: http:/= /dev.mysql.com/doc/mysql/en/charset.html=3D0D =3D = [EMAIL PROTECTED] [EMAIL PROTECTED] wro=3D= te: Hi, I'm trying to import a db dump mad= e on ver. 4.0, =3D on a new 4.1 vers=3D3D ion. = All accented characters (french=3D , spanish) are replaced by a = '?'=3D3D0D =3D3D What it's the r=3D ight wa= y to import it ? Thanks in advance = Rob=3D ert=3D3D o Jobet =3D3D0A=3D3D0A=3D3D0A=3D= 3D0A=3D =3D3D=0D = =3D3D0ANavighi a 4 MEGA e i primi =3D 3 mesi sono GRA= TIS. =3D3D0AScegli Liber=3D3D o Adsl Flat senza limiti su=3D = http://www.libero.it=3D3D0A =0D = -- 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: Slave Dying
Hello. I recommend you to upgrade to 4.1.12 (4.0.24) because there were a lot of bug fixes as of 4.0.20. David Griffiths [EMAIL PROTECTED] wrote: We are running 4.0.20 on two servers (AMD Opteron and Xeon). Our slave has died twice in the last month with the following error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I've tried resetting replication by setting the master log file and position to the values that are given by show slave status in case it was a network hiccup, but the same error. After I did this, the slave's binary log file shows, /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 4 #691231 16:00:00 server id 1 log_pos 0 Rotate to colossus-bin.030 pos: 12435199 # at 47 #691231 16:00:00 server id 1 log_pos 0 Rotate to colossus-bin.030 pos: 12435199 So I went to the master, and turned the binary log into a text file using mysqlbinlog and scanned by hand the approximate time it died; I didn't see anything particularily interesting. I then use mysqlbinlog with the -j option (to start parsing at a particular spot; in this case, 12435199). The error I got was, ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1701209458, event_type: 44 Could not read entry at offset 12435199:Error in log format or read error Googling on some of the phrases in that error message didn't turn up much, other than it could be potentially be a hardware or disk-controller issue (we are using 3ware, self-built drivers) Anyone have any thoughts? This has been fairly recent (we had some max-allowed-packet issues till I bumped that up and reduced the size of the binary logs). The hardware and software has been in place nearly a year (except the kernel, which we bumped up to try to get around corruption in the Innodb data files on the Opteron master). David -- 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: sleeping processes
Does this weird behavior remain if you're connecting trough UNIX socket
to local instance of MySQL?
Ronny Melz [EMAIL PROTECTED] wrote:
Thanks so far for your advice,
Is it possible that your application doesn't close connection properly?
that is exactly what also I think is the problem's cause, but I am unable to
locate the place where it actually does happen. My code seems straightforward
and I had looked over it some other more experienced people which were unable
to find the bug as well... weird
Check with netstat the states of connections between your application
and server.
'$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql
show full processlist' up to the point where max_connections are reached:
then the mysql processlist reports max_connections+1 pids (including the
terminal I use to get the processlist) whereas a '$ netstat | grep mysql | wc
-l' does never return due to an ever increasing number of open connections.
Each of them is in state TIME_WAIT.
Do you see some sleeping processes
with ps utility or 'mysqladmin processlist' command?
AFAIK, 'mysqladmin processlist' prints the same as a 'mysql show full
processlist', right? It's max_connections sleeping processes plus the
processlist query.
Your hint to watch out for sleeping processes with ps was interesting.
Actually, I have some 14 processes ('ps lax | grep mysql') without running my
program but max_connections+14 if it is running. Each of the processes is
sleeping. I still don't have any idea, do you?
Any suggestions appreciated.
Ronny
--
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: Empty database name error
Hello. Leave --log-error option for mysqld_safe. As far as I understand it uses it for it's own log file (different from which mysqld uses). Utilities like strace could help you to find which process creates certain file. MySQL supports debugging as well. See: http://dev.mysql.com/doc/mysql/en/debugging-server.html Ed Kasky [EMAIL PROTECTED] wrote: I added the line to the configuration file and removed --log-error= line from startup line but it still creates the /usr/local/mysql/var/yoda2.err as well as /var/log/mysql/error.log I am assuming it is reading from my.cnf as I get the following when I run mysqladmin variables log_error | /var/log/mysql/error.log I also checked for possible duplicate my.cnf files but there is only one. Is there another way to check to be sure it's reading the configuration file? At 12:40 PM Sunday, 6/5/2005, Gleb Paharenko wrote -= -- 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: sleeping processes
On Monday 06 June 2005 10:58, Gleb Paharenko wrote: Does this weird behavior remain if you're connecting trough UNIX socket to local instance of MySQL? it does. At the moment, I try to approach the problem Carl proposed and starting from scratch. It works fine - hence the problem is not mysql, but most probably hidden somewhere in the source of the rest of the program I am improving. It bothers me a bit that I didn't try this earlier, since probably it was the wrong community I was posting to (nevertheless, the problem remains to locate the exact position of the error...). Thank you very much, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
no 3306 but mysqld running
can somebody explain the events below: mysql is running but its not listening on 3306,...as a result clients on remote machines are not able to connect.. The server name is spiti confirm that mysqld is running: [EMAIL PROTECTED] ~]$ ps -ef |grep [m]ysql root 31515 1 0 15:30 pts/300:00:00 /bin/sh ./mysqld_safe --user=mysql mysql31535 31515 0 15:30 pts/300:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/mysql --user=mysql --pid-file=/usr/mysql/spiti.ad.infosys.com.pid --skip-locking --socket=/var/lib/mysql/mysql.sock root 31683 28153 0 15:49 pts/000:00:00 mysql -u root -p running nmap to see open ports [EMAIL PROTECTED] ~]$ nmap localhost Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2005-06-07 15:55 EDT Interesting ports on localhost.localdomain (127.0.0.1): (The 1655 ports scanned but not shown below are in state: closed) PORT STATE SERVICE 22/tcpopen ssh 25/tcpopen smtp 80/tcpopen http 443/tcp open https 1/tcp open snet-sensor-mgmt Nmap run completed -- 1 IP address (1 host up) scanned in 0.179 seconds trying telnet from the server: [EMAIL PROTECTED] ~]$ telnet localhost 3306 Trying 127.0.0.1... telnet: connect to address 127.0.0.1: Connection refused telnet: Unable to connect to remote host: Connection refused trying to connect from remote client: [EMAIL PROTECTED] digz]$ mysql -h 172.25.243.145 -u root -p mysql Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.243.145' (111) successfull connection from server: [EMAIL PROTECTED] ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 to server version: 4.1.9 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql TIA digz *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS***
Re: Problems importing dump 4.0 4.1
Hi, Thanks for your help. Usually to export and import dump files I use phpmyadmin, so I'm not very much familiar with the mysqldump command-line utility :-)) In the command line that you specify below, do I have to indicate the db name ? Thanks in advance Regards Roberto Jobet Hello. If you're sure that data in your dump file is in latin1 character set, you could perform something like: mysql --default-character-set=latin1 dump_file Roberto Jobet [EMAIL PROTECTED] wrote: Hi, Hello. Check the character sets with the following = statement: show variables like '%char%'; Here's the output: ch= aracter_set_client utf8 character_set_connection utf8 character_set_d= atabase latin1 character_set_results utf8 character_set_server latin1= character_set_system utf8 character_sets_dir /usr/share/mysql/charse= ts/ What version of mysqldump did you use to perform a dump? Ver= sions from 4.1.x distributions usually put SET NAMES=3D'utf8' in the b= eginning of the dump file. These are the first 2 lines of the dump = file: -- phpMyAdmin SQL Dump -- version 2.6.1-rc1 Now what should = I do exactly to correctly import all foreign characters ? Thanks in ad= vance Regards Roberto Jobet [EMAIL PROTECTED] roberto= [EMAIL PROTECTED] wrote: Hi, Thanks for your reply. = What do you mean by right character set=3D ? The defau= lt character set for ver. 4.1 is latin1, right ? This set s=3D hould= support all western european languages ! Regarding --def= ault-ch=3D aracter-set command in chapter 10 is written that a way t= o change settin=3D gs is to recompile... Is there another = less painful way to do this ? =3D0D =3D Thanks in advance=0D = Roberto Jobet Hello. Specify the= right=3D character set with --default-character-set command l= ine option for my=3D sql (if you're using it for reading dump). Chap= ter 10 of the manual co=3D uld be helpful. See: http:/= /dev.mysql.com/doc/mysql/en/charset.html=3D0D =3D = [EMAIL PROTECTED] [EMAIL PROTECTED] wro=3D= te: Hi, I'm trying to import a db dump mad= e on ver. 4.0, =3D on a new 4.1 vers=3D3D ion. = All accented characters (french=3D , spanish) are replaced by a = '?'=3D3D0D =3D3D What it's the r=3D ight wa= y to import it ? Thanks in advance = Rob=3D ert=3D3D o Jobet =3D3D0A=3D3D0A=3D3D0A=3D= 3D0A=3D =3D3D=0D = =3D3D0ANavighi a 4 MEGA e i primi =3D 3 mesi sono GRA= TIS. =3D3D0AScegli Liber=3D3D o Adsl Flat senza limiti su=3D = http://www.libero.it=3D3D0A =0D = -- 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] 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: no 3306 but mysqld running
Digvijoy Chatterjee wrote: can somebody explain the events below: mysql is running but its not listening on 3306,...as a result clients on remote machines are not able to connect.. by default in PLD the 'skip-networking' option is turned on in mysqld.conf, maybe you have the same option in your mysql config. regards, Irek Sonina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: no 3306 but mysqld running
Have you tried to telnet into that port? Are you sure that it's open in your firewall? J.R. can somebody explain the events below: mysql is running but its not listening on 3306,...as a result clients on remote machines are not able to connect.. smime.p7s Description: S/MIME cryptographic signature
TimeStamp Output changed from 4.0 to 4.1 Problem
Hi, We would like to upgrade our current installation of Mysql 4.0.x to 4.1 to be able to switch fastly to the MySQL Cluster Solution. The problem is, that we were using the TIMESTAMP type quite heavily in the SELECTS, and we splitted the result using for example, substring in php, so we assume that position 4 to 6 is the month and so on. Now with the new structure of TIMESTAMP, all these functions fail. I had a look at several ways to get out there, but each solution was to change the queries itself. The SELECT timestamp+0 seemed to be the easiest one, does this hack work forever in any forthcomming versions? The problem of this is that it will take several month to do so, we have over 100.000 queries in over 5.000 files together with a lot of scripts on external servers. This step certainly has to be made some time, but we would like to do it over a certain period of time, where we could already using the cluster, which is really important for us to have as soon as possible. I read that 4.0 could be run in new mode (--new), where it already outputs the TIMESTAMP using the new representation. Is there nobody who has a similar solution to force 4.1 to act as a 4.0 regarding TIMESTAMP output ? This would be, for us, the best solution to change all the queries one by one, being able to test it on a 4.1 system, and to gain some time to do so while running on the clustered system. Any Help would be very appreciated. Many Thanks Best Regards, Tom Weber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
column permission for user
Hello, I want one of my MySQL-users only update one of my columns in a table. I used grant UPDATE (column1) on database1.table1 to 'user1'@'localhost' but the user1 can update other culumns too. The mysql-db-table entries for user1 after grant command are: - db: - - host: - - user: Select_priv: Y - tables_priv: Table_name: table1; Table_priv: - ; Column_priv: Update - columns_priv: Table_name: table1; Column_name: column1; Column_priv: Update What can I do? I use MySQL 4.0.3-beta-nt on Windows 2000 Server with PHP 4.2.2 and phpMyAdmin 2.3.2. Wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: no 3306 but mysqld running
You hit the nail on the head Irek,thanks the problem was skip networking was on. Regards Digz On Tue, 2005-06-07 at 17:34, Irek Słonina wrote: Digvijoy Chatterjee wrote: can somebody explain the events below: mysql is running but its not listening on 3306,...as a result clients on remote machines are not able to connect.. by default in PLD the 'skip-networking' option is turned on in mysqld.conf, maybe you have the same option in your mysql config. regards, Irek Słonina Aut disce Aut Discede Aut Vincere Aut Mori Either learn or leave Either conquer or die [EMAIL PROTECTED] #4043 *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.6: SP mysqldump
Hello mysql, Is mysqldump from 5.0 dumping a stored procedures? If it isn't, then when (if planned)? -- Best regards, Juri mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column permission for user
From: Wolfgang Gliese - db: - - host: - - user: Select_priv: Y - tables_priv: Table_name: table1; Table_priv: - ; Column_priv: Update - columns_priv: Table_name: table1; Column_name: column1; Column_priv: Update What can I do? I think you'd better reset the column_priv: Update in the tables_priv. This will give update privileges to all columns in the table (according to the documentation)... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change data prefix query
Hello, I have a table that has a varchar column in which I need to change a prefix for all records. Currently there are about 500 records and I did not want to do this by hand. It looks like this: [data here ] UP05000 UP05001 UP05002 UP05003 The identifier has now changed to EN so each records needs to be EN05000 EN05001 EN05002 etc. Can this be done with a query syntax? Or do I need to do this manually. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_connections
Hello, I have a problem with a database mask I use named torque. I get an error Cannot get connection from pool I beleive it is from the max_connections reaching their limit. I put the line max_connections=200 in the my.cnf file but when I check the show variables of the database through EMS mysqlmanager it says 100. I restarted the server etc. Any ideas as to why the max is not going to 200 in the variables list? Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change data prefix query
This query (or something like it) should do the job for you: update temp11 set description = replace(description, 'EN', 'UP'); Naturally, you need to change 'temp11' to your table name and 'description' to the name of the column containing the data you want to change. The replace() function is documented in the manual on this page: http://dev.mysql.com/doc/mysql/en/string-functions.html I've tested this query and it works fine. Here's the script I used: use tmp; select 'Drop/Create table'; drop table if exists temp11; create table if not exists temp11 (idno smallint not null, description varchar(10) not null, primary key(idno)); select 'Populate temp11'; insert into temp11 (idno, description) values (1, 'EN05000'), (2, 'EN05001'), (3, 'EN05002'); select 'Display temp11'; select * from temp11; select 'Update temp11'; update temp11 set description = replace(description, 'EN', 'UP'); select 'Display modified temp11'; select * from temp11; The other approach that comes to mind would be to unload all your data to a flat file via mysqldump, edit the flat file with a text editor so that all 'EN' become 'UP', then reload the modified data back into the database. A lot more work but still do-able if there are some gotchas in the data that you haven't told us about. Rhino - Original Message - From: Scott Purcell [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 11:57 AM Subject: change data prefix query Hello, I have a table that has a varchar column in which I need to change a prefix for all records. Currently there are about 500 records and I did not want to do this by hand. It looks like this: [data here ] UP05000 UP05001 UP05002 UP05003 The identifier has now changed to EN so each records needs to be EN05000 EN05001 EN05002 etc. Can this be done with a query syntax? Or do I need to do this manually. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 07/06/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 07/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: max_connections
Where are you getting this error from? Sounds like something on top of mysql that is doing the connection pooling perhaps. -Original Message- From: James Sherwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 12:28 PM To: mysql@lists.mysql.com Subject: max_connections Hello, I have a problem with a database mask I use named torque. I get an error Cannot get connection from pool I beleive it is from the max_connections reaching their limit. I put the line max_connections=200 in the my.cnf file but when I check the show variables of the database through EMS mysqlmanager it says 100. I restarted the server etc. Any ideas as to why the max is not going to 200 in the variables list? Thanks, James -- 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: max_connections
This error is thrown in a mask we use called Torque, it does the connection pooling but we have never had a problem with it such as this until now. James - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 1:23 PM Subject: RE: max_connections Where are you getting this error from? Sounds like something on top of mysql that is doing the connection pooling perhaps. -Original Message- From: James Sherwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 12:28 PM To: mysql@lists.mysql.com Subject: max_connections Hello, I have a problem with a database mask I use named torque. I get an error Cannot get connection from pool I beleive it is from the max_connections reaching their limit. I put the line max_connections=200 in the my.cnf file but when I check the show variables of the database through EMS mysqlmanager it says 100. I restarted the server etc. Any ideas as to why the max is not going to 200 in the variables list? Thanks, James -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Dying
That's on the schedule (and has been for a bit), but our slave seems to stop replicating every week or two. Combine that with weekly pushes, and other must-do stuff, it seems to always get dumped on the back burner. David Gleb Paharenko wrote: Hello. I recommend you to upgrade to 4.1.12 (4.0.24) because there were a lot of bug fixes as of 4.0.20. David Griffiths [EMAIL PROTECTED] wrote: We are running 4.0.20 on two servers (AMD Opteron and Xeon). Our slave has died twice in the last month with the following error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I've tried resetting replication by setting the master log file and position to the values that are given by show slave status in case it was a network hiccup, but the same error. After I did this, the slave's binary log file shows, /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 4 #691231 16:00:00 server id 1 log_pos 0 Rotate to colossus-bin.030 pos: 12435199 # at 47 #691231 16:00:00 server id 1 log_pos 0 Rotate to colossus-bin.030 pos: 12435199 So I went to the master, and turned the binary log into a text file using mysqlbinlog and scanned by hand the approximate time it died; I didn't see anything particularily interesting. I then use mysqlbinlog with the -j option (to start parsing at a particular spot; in this case, 12435199). The error I got was, ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1701209458, event_type: 44 Could not read entry at offset 12435199:Error in log format or read error Googling on some of the phrases in that error message didn't turn up much, other than it could be potentially be a hardware or disk-controller issue (we are using 3ware, self-built drivers) Anyone have any thoughts? This has been fairly recent (we had some max-allowed-packet issues till I bumped that up and reduced the size of the binary logs). The hardware and software has been in place nearly a year (except the kernel, which we bumped up to try to get around corruption in the Innodb data files on the Opteron master). David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: max_connections
I would then gather to say that the limit (hence the error) is being generate by Torque, which does the connection pooling, and has nothing to do directly with max_conn. Is there a config file or something that you can look for in torque? -Original Message- From: James Sherwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 12:51 PM To: mysql@lists.mysql.com Subject: Re: max_connections This error is thrown in a mask we use called Torque, it does the connection pooling but we have never had a problem with it such as this until now. James - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 1:23 PM Subject: RE: max_connections Where are you getting this error from? Sounds like something on top of mysql that is doing the connection pooling perhaps. -Original Message- From: James Sherwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 12:28 PM To: mysql@lists.mysql.com Subject: max_connections Hello, I have a problem with a database mask I use named torque. I get an error Cannot get connection from pool I beleive it is from the max_connections reaching their limit. I put the line max_connections=200 in the my.cnf file but when I check the show variables of the database through EMS mysqlmanager it says 100. I restarted the server etc. Any ideas as to why the max is not going to 200 in the variables list? Thanks, James -- 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] -- 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: column permission for user
Hello. Use SHOW GRANTS statement to see all permissions which user has and revoke unnecessary. See: http://dev.mysql.com/doc/mysql/en/show-grants.html http://dev.mysql.com/doc/mysql/en/grant.html Wolfgang Gliese [EMAIL PROTECTED] wrote: Hello, I want one of my MySQL-users only update one of my columns in a table. I used grant UPDATE (column1) on database1.table1 to 'user1'@'localhost' but the user1 can update other culumns too. The mysql-db-table entries for user1 after grant command are: - db: - - host: - - user: Select_priv: Y - tables_priv: Table_name: table1; Table_priv: - ; Column_priv: Update - columns_priv: Table_name: table1; Column_name: column1; Column_priv: Update What can I do? I use MySQL 4.0.3-beta-nt on Windows 2000 Server with PHP 4.2.2 and phpMyAdmin 2.3.2. Wolfgang -- 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: Problems importing dump 4.0 4.1
Hello. Yes, you should specify all necassary options (database, login, -p switch for password). Roberto Jobet [EMAIL PROTECTED] wrote: Hi, Thanks for your help. Usually to export and import dump files I= use phpmyadmin, so I'm not very much familiar with the mysqldump comman= d-line utility :-)) In the command line that you specify below, do I h= ave to indicate the db name ? Thanks in advance Regards Roberto = Jobet -- 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: max_connections
Yes, we have been looking into that as well, the problem is is that some changes we have made seemed to have helped and it takes 12hours or so for the error to occur, making it a slow process:) James - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 1:55 PM Subject: RE: max_connections I would then gather to say that the limit (hence the error) is being generate by Torque, which does the connection pooling, and has nothing to do directly with max_conn. Is there a config file or something that you can look for in torque? -Original Message- From: James Sherwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 12:51 PM To: mysql@lists.mysql.com Subject: Re: max_connections This error is thrown in a mask we use called Torque, it does the connection pooling but we have never had a problem with it such as this until now. James - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 1:23 PM Subject: RE: max_connections Where are you getting this error from? Sounds like something on top of mysql that is doing the connection pooling perhaps. -Original Message- From: James Sherwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 12:28 PM To: mysql@lists.mysql.com Subject: max_connections Hello, I have a problem with a database mask I use named torque. I get an error Cannot get connection from pool I beleive it is from the max_connections reaching their limit. I put the line max_connections=200 in the my.cnf file but when I check the show variables of the database through EMS mysqlmanager it says 100. I restarted the server etc. Any ideas as to why the max is not going to 200 in the variables list? Thanks, James -- 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] -- 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]
Mac OS X - Query Browser 1.0.2-alpha Crash on Launch
Just feedback to the MySQL team... Query Browser will not launch on OS X 10.3.7 Here is console log output... console.log dyld: /Applications/MySQL Query Browser.app/Contents/MacOS/MySQL Query Browser can't open library: /usr/lib/libstdc++.6.dylib (No such file or directory, errno = 2) Jun 7 14:51:30 kieranmac crashdump: Unable to determine CPSProcessSerNum pid: 29784 name: MySQL Query Browser Jun 7 14:51:30 kieranmac crashdump: Started writing crash report to: /Users/kieran/Library/Logs/CrashReporter/MySQL Query Browser.crash.log Jun 7 14:51:30 kieranmac crashdump: Finished writing crash report to: /Users/kieran/Library/Logs/CrashReporter/MySQL Query Browser.crash.log /console.log Here is crash log output.. crash.log Host Name: kieranmac.local Date/Time: 2005-06-07 14:51:30 -0400 OS Version: 10.3.7 (Build 7T62) Report Version: 2 Command: MySQL Query Browser Path:/Applications/MySQL Query Browser.app/Contents/MacOS/MySQL Query Browser Version: ??? (???) PID: 29784 Thread: Unknown Link (dyld) error: dyld: /Applications/MySQL Query Browser.app/Contents/MacOS/MySQL Query Browser can't open library: /usr/lib/libstdc++.6.dylib (No such file or directory, errno = 2) /crash.log and I don't have the missing file (/usr/lib/libstdc++.6.dylib) in my installation. The Query browser says OS X 10.3 on the download page ... so don't know what version (past or future) installs that file. -Regards, Kieran My hardware/software configurations (in case they are relevant to the problem): Dev Config = OS X 10.3.7 / Java 1.4.2_05 / WO 5.2.3 / XCode v1.5 / MySQL 4.0.23 / Connector-J 3.0.16 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change data prefix query
Hi,
thsi can be a solution, there are others :
mysql select substring(id,1,2), substring(id,3,length(id)) from mytable;
+---++
| substring(id,1,2) | substring(id,3,length(id)) |
+---++
| UP| 05000 |
| UP| 05001 |
| UP| 05002 |
| UP| 05003 |
| UP| 05004 |
+---++
5 rows in set (0.01 sec)
mysql
mysql
mysql update mytable set id=concat('EN',substring(id,3,length(id)));
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql select * from mytable;
+-+
| id |
+-+
| EN05000 |
| EN05001 |
| EN05002 |
| EN05003 |
| EN05004 |
+-+
5 rows in set (0.00 sec)
Mathias
Selon Scott Purcell [EMAIL PROTECTED]:
Hello,
I have a table that has a varchar column in which I need to change a prefix
for all records. Currently there are about 500 records and I did not want to
do this by hand. It looks like this:
[data here ]
UP05000
UP05001
UP05002
UP05003
The identifier has now changed to EN so each records needs to be
EN05000
EN05001
EN05002
etc.
Can this be done with a query syntax? Or do I need to do this manually.
Thanks,
Scott
--
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]
LOAD DATA INFILE - still confused
I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) The simple php script and the file, datafile.txt are both in the same directory. Where is MySQL looking for the file, datafile.txt? NOTE: the error reported indicates that MySQL is searching in for datafile.txt in a directory called mydatbasename. However, I am running the php script in a shared hosting environment and I am not able to view the directory mydatbasename (if it is actually a directory). Apparently the directory is outside my root? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default current_timestamp problem
Hello, I am trying to create simple reference table for some cookies I am creating. I wanted to put in a current_timestamp each time I do an insert so I can delete this data after 2 or 3 weeks. Here is the insert statement: CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp default current_timestamp) I am trying to insert the below statement, but it fails with a 1064 Error Code : 1064 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 'current_timestamp)' at line 1 (0 ms taken) I am running version 4.0.15-max-debug on windows. and following this link but It will not work. What can I be doing wrong? http://dev.mysql.com/doc/mysql/en/datetime.html Thanks, Scott - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default current_timestamp problem
Scott Purcell [EMAIL PROTECTED] wrote on 06/07/2005 04:29:56 PM: Hello, I am trying to create simple reference table for some cookies I am creating. I wanted to put in a current_timestamp each time I do an insert so I can delete this data after 2 or 3 weeks. Here is the insert statement: CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp default current_timestamp) I am trying to insert the below statement, but it fails with a 1064 Error Code : 1064 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 'current_timestamp)' at line 1 (0 ms taken) I am running version 4.0.15-max-debug on windows. and following this link but It will not work. What can I be doing wrong? http://dev.mysql.com/doc/mysql/en/datetime.html Thanks, Scott You are not reading far enough http://dev.mysql.com/doc/mysql/en/create-table.html The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column as of MySQL 4.1.2. See Section 11.3.1.2, TIMESTAMP Properties as of MySQL 4.1. You are not running 4.1.2 or newer, that's why you are getting an error trying to set the default value for a timestamp column. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: default current_timestamp problem
You do not need to set a default value if you want the current_timestamp. Just leave the default option out when creating your table and the server will do it for you. CREATE TABLE COOKIE_REF ( cookie_ref varchar(50), dat timestamp ); OR If you really want to put in a value for the default, use CREATE TABLE COOKIE_REF ( cookie_ref varchar(50), dat timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ); J.R. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 07, 2005 4:30 PM To: mysql@lists.mysql.com Subject: default current_timestamp problem Hello, I am trying to create simple reference table for some cookies I am creating. I wanted to put in a current_timestamp each time I do an insert so I can delete this data after 2 or 3 weeks. Here is the insert statement: CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp default current_timestamp) I am trying to insert the below statement, but it fails with a 1064 Error Code : 1064 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 'current_timestamp)' at line 1 (0 ms taken) I am running version 4.0.15-max-debug on windows. and following this link but It will not work. What can I be doing wrong? http://dev.mysql.com/doc/mysql/en/datetime.html Thanks, Scott - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [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]
Seriously.. When are we going to get subqueries?!
OK... Subqueries in 4.1 are totally broken. They don't use indexes. They're evil. We're told we have subqueries but there's no way anyone on earth could use them. To make matters worse a lot of developers are TRICKED into using them and assume that mysql would do the right thing but its a HUGE performance hit. So... 1. When will subqueries that actually use indexes be implemented? We've been promised this feature since 4.0 it was one of the biggest feature wins of 4.1. 2. If they won't be in 5.0 could you please abandon a feature for 5.0 and concentrate on subqueries? 3. If they won't be in 5.0 could you at least be honest and remove this feature since in the best case its useless and in the worse case its dangerous (god forbid someone should ship code that uses this)? Not trying to be obnoxious here but I really want this feature and the current implementation is very.. evil. :) Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_seeks_for_key in InnoDB
Thanks for your reply. I guess the root of my problem is that MySQL is making a poor choice of index, which I presume is based on the cardinality numbers of each index. When I run ANALYZE TABLE, these values can fluctuate wildly -- between 16 and 26,000, for example. According to the manual, ANALYZE TABLE counts cardinality by doing 10 random dives into each of the index trees. (this is up from 8 random dives in 4.1.11, which is good!) Maybe the accuracy of this measurement decreases as tables reach millions of rows? I would really like to avoid rewriting all of my queries to add USE INDEX and STRAIGHT JOIN, since some of them are quite complicated and I would prefer to leave the job to MySQL. My questions are: 1) Can the 10 random dives be made configurable? I would like to do an analyze table with 100 random dives if it would produce a more accurate count. 2) Is there some reason that my index trees would not be uniform? Is there anything I can do about this? 3) For InnoDB tables, does it make sense to always keep max_seeks_for_key at a low value (1, 100, ?) Many thanks in advance, -Bob - Original Message - From: [EMAIL PROTECTED] To: Bob O'Neill [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, June 03, 2005 3:20 PM Subject: Re: max_seeks_for_key in InnoDB Hi, you can use a hint to force specific index usage : http://lib.hutech.edu.vn/ebookonline/ebook1/0596003064/hpmysql-CHP-5-SECT-4.html But this is not a good idea since data change and index selectivity can become bad. Also, if the index scan + the table scan is bigger than a full table scan, even you will prefer FTS. So, according to selectivity, usage of an index can be a very bad idea. Thsi depends on how many rows your query retreives among the count(*) of the table. Mathias Selon Bob O'Neill [EMAIL PROTECTED]: I am having problems with MySQL inconsistently choosing the wrong index, or no index at all, for queries on tables with 20 million rows. Would it be a good idea for me to set max_seeks_for_key to 1 (or something less than 4 billion), in order to force MySQL to use an index? We are using InnoDB. Since InnoDB has clustered indexes, is there ever a good reason for MySQL to prefer a table scan? Thanks, -Bob -- 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: Seriously.. When are we going to get subqueries?!
They do use indexes if you use them to build derived tables and are pretty fast. The only case where I see them not using indexes when I think they should is when you use a sub-query for an IN() clause. Kevin Burton wrote: OK... Subqueries in 4.1 are totally broken. They don't use indexes. They're evil. We're told we have subqueries but there's no way anyone on earth could use them. To make matters worse a lot of developers are TRICKED into using them and assume that mysql would do the right thing but its a HUGE performance hit. So... 1. When will subqueries that actually use indexes be implemented? We've been promised this feature since 4.0 it was one of the biggest feature wins of 4.1. 2. If they won't be in 5.0 could you please abandon a feature for 5.0 and concentrate on subqueries? 3. If they won't be in 5.0 could you at least be honest and remove this feature since in the best case its useless and in the worse case its dangerous (god forbid someone should ship code that uses this)? Not trying to be obnoxious here but I really want this feature and the current implementation is very.. evil. :) Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
Greg Whalin wrote: They do use indexes if you use them to build derived tables and are pretty fast. The only case where I see them not using indexes when I think they should is when you use a sub-query for an IN() clause. I'm sorry.. yes.. They're not using indexes when within IN clauses which for me is 99.9% of the time. Maybe I'm wrong though and this is a rare but I don't think so... Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
- Original Message - From: Kevin Burton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 6:11 PM Subject: Re: Seriously.. When are we going to get subqueries?! Greg Whalin wrote: They do use indexes if you use them to build derived tables and are pretty fast. The only case where I see them not using indexes when I think they should is when you use a sub-query for an IN() clause. I'm sorry.. yes.. They're not using indexes when within IN clauses which for me is 99.9% of the time. Maybe I'm wrong though and this is a rare but I don't think so... Kevin I use subqueries and I thought that it was using the index? I might be wrong? This is MySQL on 4.1.11-nt. Here is the explain plan: mysql explain select count(*) from ptt_trans_06 where trans='ADD' and nai in (select scr_userid from ptt_home where number_of_sessions3); +++--++---+--+-+--+---+--+ | id | select_type| table| type | possible_keys | key | key_len | ref | rows | Extra | +++--++---+--+-+--+---+--+ | 1 | PRIMARY| ptt_trans_06 | ALL| NULL | NULL |NULL | NULL | 21621 | Using where | | 2 | DEPENDENT SUBQUERY | ptt_home | index_subquery | ptt_home_nai | ptt_home_nai | 250 | func | 1 | Using index; U sing where | +++--++---+--+-+--+---+--+ Isn't this using an index? I would agree that I use a lot of subqueries using the IN() clause. Lawrence Kennon
Re: LOAD DATA INFILE with INNODB
this is strange, when i do this..
===
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql insert into tbltemp (tmp_crdd_no,tmp_serial_no,tmp_date)
values('100','100',NOW());
Query OK, 1 row affected (0.00 sec)
mysql select * from tbltemp;
++--+---+--+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++--+---+--+
| 1 | 100| 100| 2005-06-08
03:16:58 |
++--+---+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.01 sec)
mysql select * from tbltemp;
Empty set (0.01 sec)
=
all is ok but when i load the file it autocommits.. example below
=
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql LOAD DATA INFILE '/usr/local/apache2/htdocs/test.csv' INTO TABLE
tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(tmp_crdd_no,tmp_serial_no,tmp_date);
Query OK, 47 rows affected (0.01 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
mysql rollback;
Query OK, 0 rows affected (0.00 sec)
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
i also checked server status variables before starting and after ending
transaction. everything looks fine but cant seem to figure out what is
heppeing here.i even upgraded to latest version, any help will be
appriciated thanks in advance
Haseeb Iqbal
- Original Message -
From: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 1:21 PM
Subject: Re: LOAD DATA INFILE with INNODB
hi,
yes you did it and i failed to do so. thats why i am here.
i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves
only
innodb as i mentioned in the very first post,
i have read docs and it says that there are 3 ways i can start a
transaction
and i tried all three i.e. begin work statement, start transaction and SET
AUTOCOMMIT=0;
regards
- Original Message -
From: [EMAIL PROTECTED]
To: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 11:08 AM
Subject: Re: LOAD DATA INFILE with INNODB
Hi,
i did it. If you have myisam tables tables rather than innodb say it.
if you have specific os, say it.
i think you should elaborate, or read carrefully dev.mysql.com/doc
Mathias
Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]:
i used start transaction before using SET AUTOCOMMIT=0; also i dont
see
any
difference between the two. if there is please elaborate.
- Original Message - .
From: [EMAIL PROTECTED]
To: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 3:17 AM
Subject: Re: LOAD DATA INFILE with INNODB
Hi,
you transaction is implicit, so there has been an autocommit.
Look at this example !
mysql start transaction;
^^
mysql load data infile 'd:\\ldfile.txt' into table ldfile;
Re: Seriously.. When are we going to get subqueries?!
DBA wrote: - Original Message - From: Kevin Burton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 6:11 PM Subject: Re: Seriously.. When are we going to get subqueries?! Greg Whalin wrote: They do use indexes if you use them to build derived tables and are pretty fast. The only case where I see them not using indexes when I think they should is when you use a sub-query for an IN() clause. I'm sorry.. yes.. They're not using indexes when within IN clauses which for me is 99.9% of the time. Maybe I'm wrong though and this is a rare but I don't think so... Kevin I use subqueries and I thought that it was using the index? I might be wrong? This is MySQL on 4.1.11-nt. Here is the explain plan: Nope... its a bit confusing but no... it's not. The subquery ITSELF is using queries but the parent query which is using your ptt_trans_06 table is not ... Notice the Type: ALL and Key: NULL Luckly though you only have 21k rows so its not that big of a deal. KEvin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
yes he is right i am doing the same. and giving full path. i have other problems with load data infile :( - Original Message - From: Frank Bax [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 08, 2005 1:39 AM Subject: Re: LOAD DATA INFILE - still confused At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
as it turns out it was a MYSQL BUG
http://bugs.mysql.com/bug.php?id=11151error=no
thanks anyway
regards Haseeb
- Original Message -
From: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 08, 2005 3:32 AM
Subject: Re: LOAD DATA INFILE with INNODB
this is strange, when i do this..
===
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql insert into tbltemp (tmp_crdd_no,tmp_serial_no,tmp_date)
values('100','100',NOW());
Query OK, 1 row affected (0.00 sec)
mysql select * from tbltemp;
++--+---+--+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++--+---+--+
| 1 | 100| 100| 2005-06-08
03:16:58 |
++--+---+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.01 sec)
mysql select * from tbltemp;
Empty set (0.01 sec)
=
all is ok but when i load the file it autocommits.. example below
=
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql LOAD DATA INFILE '/usr/local/apache2/htdocs/test.csv' INTO TABLE
tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(tmp_crdd_no,tmp_serial_no,tmp_date);
Query OK, 47 rows affected (0.01 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
mysql rollback;
Query OK, 0 rows affected (0.00 sec)
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
i also checked server status variables before starting and after ending
transaction. everything looks fine but cant seem to figure out what is
heppeing here.i even upgraded to latest version, any help will be
appriciated thanks in advance
Haseeb Iqbal
- Original Message -
From: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 1:21 PM
Subject: Re: LOAD DATA INFILE with INNODB
hi,
yes you did it and i failed to do so. thats why i am here.
i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves
only
innodb as i mentioned in the very first post,
i have read docs and it says that there are 3 ways i can start a
transaction
and i tried all three i.e. begin work statement, start transaction and
SET
AUTOCOMMIT=0;
regards
- Original Message -
From: [EMAIL PROTECTED]
To: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 11:08 AM
Subject: Re: LOAD DATA INFILE with INNODB
Hi,
i did it. If you have myisam tables tables rather than innodb say it.
if you have specific os, say it.
i think you should elaborate, or read carrefully dev.mysql.com/doc
Mathias
Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]:
i used start transaction before using SET AUTOCOMMIT=0; also i dont
see
any
difference between the two. if there is please elaborate.
- Original Message - .
Re: LOAD DATA INFILE - still confused
Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WebHosting with MySQL.......
Hi Friends, I'm Ashok. I tried one c-cgi script which is get two input data from the user (thro' browser) and store it into MySQL Database, which is running in my m/c as a service. It's working well in my local pc. Now i want to host that cgi pgm into one site (where cgi scriptings r allowed). My doubt is how can i invoke the database connectivity for that. is it neccessary that server must have the MySQL DB? Help me pls. Thanks and Regards, Ashok Kumar.P.S. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WebHosting with MySQL.......
Ashok Kumar wrote: Hi Friends, I'm Ashok. I tried one c-cgi script which is get two input data from the user (thro' browser) and store it into MySQL Database, which is running in my m/c as a service. It's working well in my local pc. Now i want to host that cgi pgm into one site (where cgi scriptings r allowed). My doubt is how can i invoke the database connectivity for that. is it neccessary that server must have the MySQL DB? Help me pls. I'm in the dark as to what c-cgi exactly is and thus how you connect to the MySql in the first place, but nevertheless I assume you must be establishing the database connection first hand in your script. This being the case, you should be able to apply a different hostname than 'localhost' in your connection script. Remember to verify that the grant privileges reflect the client not being on localhost. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help converting tables to excel format
I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format?
