Re: How to use LAST_INSERT_ID() or mysql_insert_id() ?
I know this is an old query, but still, problems . . . I've tried these both, but something is amiss - probably my interpretation. Every time I try to use LAST_INSERT_ID() I get a complaint that the query has a problem. E.G., $qry=$qry.;select LAST_INSERT_ID(); will blow up on me every time USE ONE QUERY AT A TIME. Since last_insert_id() depends on the connection involved, no query done on a different connection between your insert and getting last_insert_id() will affect the result you get. I've evaluated $qry, and it works just fine until I append the piece that should let me recover the auto increment value that was created during the insert process. One query at a time. Gordon L. Burditt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to set autocommit
Ben, I have added the following item to the TODO list of September 2002. I guess it will appear only in the MySQL-4.0 branch, as 4.0 will be the new stable branch within a few months. ... September, 2002: Add a global MySQL my.cnf option autocommit_default=0. ... Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: Ben Goswami [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 29, 2002 7:52 AM Subject: how to set autocommit Hi, Where should I change the autocommit setting to 0 (i.e no Autocommit). I'm inserting row from perl script using DBI. I would like to bring up mySQL server with autocommit false, so that I can explicitly commit from the front end. But with lot of research in various docs I could not find where to changer the settings. If I supply it in the connect string, it does not take it. Any help is appreciated - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to use LAST_INSERT_ID() or mysql_insert_id() ?
On Monday, 29. July 2002 06:33, databarn wrote: Hi, E.G., $qry=$qry.;select LAST_INSERT_ID(); will blow up on me every time I've evaluated $qry, and it works just fine until I append the piece that should let me recover the auto increment value that was created during the insert process. Fortunately its not possible to combine multiple commands in a single query (that would be a security hole). You have to send two queries instead, or you can use the php function mysql_insert_id after executing the 1st query. I'm using, according to phpinfo(), MySQL 3.23.32 and PHP 4.0.5 on Win2K with IIS 5.0. I recommend to upgrate your MySQL and PHP-Version. Regards Georg mysql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Provider reqd. for myODBC
Glory! I'm looking for a Provider for the myODBC Driver to connect my Visual Basic code with my mySQL database. I have in kknowledge one such provider known as MSADSQL..but niether do i know where to find it nor do i know whether it's the right thing or not. T. Edison Jr. __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
why does MySQL need to access the internet? my firewall wants to know
Hi, When I try to start MySQL, I get alerts from my personal firewall that MySQL is trying to access the internet...why would this happen? I'm currently using (or trying to use) Abriasoft's Merlin desktop, which includes apache. I would have thought that if I'm running a apache on my own computer, there would be no need to access the internet. I would guess that this has something to do with Abriasoft, except that this also happened to me when i was running MySQL without the aid of merlin or anything else. I'd appreciate it if anyone could shed some light on this matter... Thanks, Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Locking + Timeout
Hi, I have a question regard the table locking. Let say there is a table ,mytablename type=MyISAM in my database. And lets say there are 2 users who are currently log in. First user locks a table write: mysql lock tables mytablename write; Query OK, 0 rows affected (0.00 sec) Then,second user wishes to query something from mytablename. mysqlselect * from mytablename; ...(pending or in a queue)... I know that the second query will be pending untill the 1-st user issues the unlock tables command. So,i am wondering is it the second query will be waiting there 'forever' if the 1-st user 'never' issue the unlock tables command? Or is there any method maybe like 'waiting_timeout' that I can set.If the waiting time 'variable' second,then the query will automatically be deleted Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table Locking + Timeout
if you need table or datarow locking , why don't you use innodb table type with transactions ? bye, thomas First user locks a table write: mysql lock tables mytablename write; Query OK, 0 rows affected (0.00 sec) Then,second user wishes to query something from mytablename. mysqlselect * from mytablename; ...(pending or in a queue)... I know that the second query will be pending untill the 1-st user issues the unlock tables command. So,i am wondering is it the second query will be waiting there 'forever' if the 1-st user 'never' issue the unlock tables command? Or is there any method maybe like 'waiting_timeout' that I can set.If the waiting time 'variable' second,then the query will automatically be deleted - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
make create table ...select * from, perf
Hi, I have a problem. I tried finding a proper solution, but could not. 1) The query create table if not exists temp2.t1 select * from temp1.t1; does not copy the primary key details etc. It copies everything else. Any work around for this ? 2) What is the best way to check if a table exists in a database ? Currently I do a show tables like tablename query, but it takes approx 20 millis for me now, while a normal update on a table column takes less than 5 millis. (This figure is from my code not directly with mysql. Also I may have upto 500 tables in my database). Any suggestions on the above would be helpful. Thanks in advance Gibu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE LOW_PRIORITY
MySQL question: If I use UPDATE LOW_PRIORITY will the client then have to wait for the update to finish or is UPDATE LOW_PRIORITY the same as INSERT DELAYED ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Network Privileges
How can I grant a privilege to a specified user to a specific DB? If I grant Host=% User=joe in the user table, joe can access a l l DBs on the server - even the mysql-DB. Thanks for help Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL ERROR 1062: Duplicate entry '1' for key 1
MySQL Question I run sql version 3.23.42. LOAD DATA INFILE /usr/lists INTO TABLE lists; ERROR 1062: Duplicate entry '1' for key 1 Please how do i run this successfully. Ive tried the REPLACE option without any luck. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Network Privileges
Pada Mon, 29 Jul 2002 11:47:30 +0200 KSausW [EMAIL PROTECTED] menulis : How can I grant a privilege to a specified user to a specific DB? If I grant Host=% User=joe in the user table, joe can access a l l DBs on the server - even the mysql-DB. grant set_of_privileges on db.table to user@host identified by password; -- printk(??? No FDIV bug? Lucky you...\n); 2.2.16 /usr/src/linux/include/asm-i386/bugs.h MySQL 3.23.51 : up 38 days, Queries : 353.795 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 1148: The used command is not allowed with this MySQL version
Jeffrey, Sunday, July 28, 2002, 8:14:43 PM, you wrote: JAK Could you help me understand how to correct this error: JAK Error 1148: The used command is not allowed with this MySQL version JAK I have looked in the online manual under this section for help: JAK 4.2.4 Security issues with LOAD DATA LOCAL JAK Unfortunately I am very new to MySQL and don't have a clue as to how fix this error. I am using Windows. I think I need to modify the My.ini file under the mysqld section... I think it JAK should read local-infile=1. However when I try that, it still get this error when calling LOAD LOCAL FILE from a client application. Yes, I did start and stop the server. LOAD DATA LOCAL must be enabled in the client side, too. Put in [mysql] section on my.ini entry local-infile=1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denited error
VINOD, Saturday, July 27, 2002, 6:57:04 AM, you wrote: V Subsequently I have loaded above codings V (post_data.php) on the web and the product.txt file V into the /tmp directory which is available on web. V This /tmp directory is having read, write, delete, V execute permissions. Now the error is V I am able to display the content of the table count on V the screen. When it is executing the load query, it is V showing the error on the browser that V COUNT=006 V Access denied for user:root@localhost (Using Passwor- V dYES) V Please suggest me how to over come the problem If you load data from server host with LOAD DATA statement you must have FILE privilege. It's a global privilege. http://www.mysql.com/doc/P/r/Privileges_provided.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: display stuck .... ! ! ! !
toby, Saturday, July 27, 2002, 10:21:50 AM, you wrote: t i ve a lil problem t i run this query n get a result set [skip] t what should i do ? I think your question is more suitable for php mailing list. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: re: OR in the JOIN clause -- very very slow!
Erick, Monday, July 29, 2002, 3:58:36 AM, you wrote: EP i have a couple of questions about my SQL query. EP 1. if i want all rows in table 1 that may or may not have a counterpart EP row in table 2, i use LEFT JOIN. this is HORRIBLY slow! why? if i make it EP a simple join (i.e., without the words LEFT JOIN) then it is a little EP faster. EP 2. secondly, i need to have an OR in my join clause. EPwhere EP (table1.col1 = table2.col1 or table1.col2 = table2.col1) EP this is for a search procedure. if i remove the OR, it is much faster. EP how can i get around this? i need to have the OR because of the search EP engine in the website, but i need it to be fast. EP 3. based on the OR condition, what kind of indices can i use? If your select is slow, use EXPLAIN SELECT to get info about your statement: http://www.mysql.com/doc/E/X/EXPLAIN.html Besides you didn't gave any info about your tables stucture, haven't provided your query. Check also the following sections of the manual which some info about index usage, LEFT JOIN and WHERE clause : http://www.mysql.com/doc/M/y/MySQL_indexes.html http://www.mysql.com/doc/W/h/Where_optimisations.html http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Network Privileges
KSausW, Monday, July 29, 2002, 12:47:30 PM, you wrote: K How can I grant a privilege to a specified user to a specific DB? K If I grant K Host=% K User=joe K in the user table, joe can access a l l DBs on the server - even the K mysql-DB. Table 'user' stores global privileges. For database privileges table 'db' is used. How to set up privileges, look at: http://www.mysql.com/doc/G/R/GRANT.html In your case it would be something like that: GRANT ALL ON database_name.* TO joe@% IDENTIFIED BY joe_password; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why does MySQL need to access the internet? my firewall wants to know
Hi, Are you sure that is trying to access the internet. It may be trying to open the port, remember that MySQL needs 3306 (by default) to communicate with the server. I think if you just open that port, everything will be ok. Bye and Good Luck. --- Matthew K. Gold [EMAIL PROTECTED] wrote: Hi, When I try to start MySQL, I get alerts from my personal firewall that MySQL is trying to access the internet...why would this happen? I'm currently using (or trying to use) Abriasoft's Merlin desktop, which includes apache. I would have thought that if I'm running a apache on my own computer, there would be no need to access the internet. I would guess that this has something to do with Abriasoft, except that this also happened to me when i was running MySQL without the aid of merlin or anything else. I'd appreciate it if anyone could shed some light on this matter... Thanks, Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to set autocommit
Heikki , Thanks for your reply. So what should I do in the mean time. Is there a work around. 1. Can I put in the connect string? like DBI-connect(DBI:mysql:database=devdb;host=localhost, {'RaiseError' = 1, 'AutoCommit = 0}); This does not work. 2. Should I use begin like ($sth is the the DB handle) $sth-do(begin) $sth-do(insert into...) $sth-rollback() or anything else. I tried from setting up a mysql prompt, and still does not take it like mysqlset autocommit=0 mysqlinsert into... mysqlrollback when I do that it says Error 1196 Warning: Some non-transactional changed tables couldn't be rolled back Thanks Ben - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 28, 2002 11:37 PM Subject: Re: how to set autocommit Ben, I have added the following item to the TODO list of September 2002. I guess it will appear only in the MySQL-4.0 branch, as 4.0 will be the new stable branch within a few months. ... September, 2002: Add a global MySQL my.cnf option autocommit_default=0. ... Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: Ben Goswami [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 29, 2002 7:52 AM Subject: how to set autocommit Hi, Where should I change the autocommit setting to 0 (i.e no Autocommit). I'm inserting row from perl script using DBI. I would like to bring up mySQL server with autocommit false, so that I can explicitly commit from the front end. But with lot of research in various docs I could not find where to changer the settings. If I supply it in the connect string, it does not take it. Any help is appreciated - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
assertion failed invalid signature in file details.php
i get this error in mysql 3.23.51 with snortreport (http://www.circuitsmaximus.com/download.html) is this an mysql error or snortreport error ? regards, bart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: why does MySQL need to access the internet? my firewall wants to know
Yes, it is most certainly because mysql wants to open port 3306. If it's zone alarm, it probably says it's trying to access 127.0.0.1. -Original Message- From: Francisco Reinaldo [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 7:26 AM To: Matthew K. Gold; MySQL Subject: Re: why does MySQL need to access the internet? my firewall wants to know Hi, Are you sure that is trying to access the internet. It may be trying to open the port, remember that MySQL needs 3306 (by default) to communicate with the server. I think if you just open that port, everything will be ok. Bye and Good Luck. --- Matthew K. Gold [EMAIL PROTECTED] wrote: Hi, When I try to start MySQL, I get alerts from my personal firewall that MySQL is trying to access the internet...why would this happen? I'm currently using (or trying to use) Abriasoft's Merlin desktop, which includes apache. I would have thought that if I'm running a apache on my own computer, there would be no need to access the internet. I would guess that this has something to do with Abriasoft, except that this also happened to me when i was running MySQL without the aid of merlin or anything else. I'd appreciate it if anyone could shed some light on this matter... Thanks, Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to set autocommit
Ben, - Original Message - From: Ben Goswami [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 29, 2002 4:40 PM Subject: Re: how to set autocommit Heikki , Thanks for your reply. So what should I do in the mean time. Is there a work around. 1. Can I put in the connect string? like DBI-connect(DBI:mysql:database=devdb;host=localhost, {'RaiseError' = 1, 'AutoCommit = 0}); This does not work. 2. Should I use begin like ($sth is the the DB handle) $sth-do(begin) $sth-do(insert into...) $sth-rollback() or anything else. I tried from setting up a mysql prompt, and still does not take it like mysqlset autocommit=0 mysqlinsert into... mysqlrollback when I do that it says Error 1196 Warning: Some non-transactional changed tables couldn't be rolled back SET AUTOCOMMIT = 0 is the right way to do this. But are you doing changes to MyISAM type tables? They are non-transactional. Please check with SHOW CREATE TABLE that your tables are of the InnoDB type. Thanks Ben Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 28, 2002 11:37 PM Subject: Re: how to set autocommit Ben, I have added the following item to the TODO list of September 2002. I guess it will appear only in the MySQL-4.0 branch, as 4.0 will be the new stable branch within a few months. ... September, 2002: Add a global MySQL my.cnf option autocommit_default=0. ... Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: Ben Goswami [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 29, 2002 7:52 AM Subject: how to set autocommit Hi, Where should I change the autocommit setting to 0 (i.e no Autocommit). I'm inserting row from perl script using DBI. I would like to bring up mySQL server with autocommit false, so that I can explicitly commit from the front end. But with lot of research in various docs I could not find where to changer the settings. If I supply it in the connect string, it does not take it. Any help is appreciated - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Fwd: urgent: problem in deleting multi table
Thank you for your fine bug report, thanks to which, I was able to fix a problem. Final fix will come up in 4.0.3, but this is a patch that fixes it temporarily : = /mnt/work/mysql-4.0/sql/sql_delete.cc 1.80 vs edited = *** /tmp/sql_delete.cc-1.80-1656Tue Jul 23 18:31:17 2002 --- edited//mnt/work/mysql-4.0/sql/sql_delete.ccMon Jul 29 15:26:47 2002 *** *** 227,233 table-used_keys=0; tempfiles[counter] = new Unique (refposcmp2, (void *) table-file-ref_length, !table-file-ref_length, MEM_STRIP_BUF_SIZE); } } --- 227,233 table-used_keys=0; tempfiles[counter] = new Unique (refposcmp2, (void *) table-file-ref_length, !table-file-ref_length + 1, MEM_STRIP_BUF_SIZE); } } -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication questions
Hello everyone, The mysql documentation about replication is great at describing how to replicate fomr one master server. If i want to replicate many db servers to one replication server is there a way I can edit the my.cnf file to do this. Or would it be better to have another conf file for the other master running under another mysql instance?? Has anyone had any experience with this? What would be the best way to handle this? I am going to eventually replicate 10 masters on 1 db.. Thanks for the help... Sincerely, Chad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need help on Multi-table update queries
Hi, I m using MySQL 4.0.2 which now supports multi-table update query I am getting error while throwing multi-table UPDATE query, I don't know what the problem is To generate an error run below query, it will create a 2 InnoDB table and inserts a dummy record in it CREATE TABLE `systemdefault` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB; CREATE TABLE `userpreference` ( `id` int(11) NOT NULL auto_increment, `SysDefaultId` int(11) default NULL, `fld1` int(11) default NULL, `fld2` tinyint(1) unsigned default NULL, `OperatorId` int(11) default NULL, PRIMARY KEY (`id`), KEY `ix_SysDefaultId_userpreference` (`SysDefaultId`), FOREIGN KEY (`SysDefaultId`) REFERENCES `test.systemdefault` (`id`) ) TYPE=InnoDB; INSERT INTO systemdefault VALUES(1), (2), (3); INSERT INTO userpreference(sysdefaultid,fld1,fld2,operatorid) VALUES(1,99,NULL,NULL), (2,88,0,NULL), (3,77,1,NULL), (1,NULL,NULL,1), (2,NULL,NULL,1),(3,NULL,NULL,1); Now throw following UPDATE query UPDATE UserPreference LEFT JOIN UserPreference AS Preference ON UserPreference.SysDefaultId = Preference.SysDefaultId SET UserPreference.fld1 = Preference.fld1, UserPreference.fld2 = Preference.fld2 WHERE Preference.OperatorId IS NULL OR UserPreference.OperatorId = 1; It throws following Error ERROR 1105: Unknown error Anybody have an idea wht's wrong in above UPDATE statement? THX IN ADVANCE - Deep __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why does MySQL need to access the internet? my firewall wants to know
thanks, everyone for your answers. It was trying to access 127.0.0.1, and I now know that this is the same thing as localhost. best, Matt - Original Message - From: Dan Vande More [EMAIL PROTECTED] To: Francisco Reinaldo [EMAIL PROTECTED]; Matthew K. Gold [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Monday, July 29, 2002 9:51 AM Subject: RE: why does MySQL need to access the internet? my firewall wants to know Yes, it is most certainly because mysql wants to open port 3306. If it's zone alarm, it probably says it's trying to access 127.0.0.1. -Original Message- From: Francisco Reinaldo [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 7:26 AM To: Matthew K. Gold; MySQL Subject: Re: why does MySQL need to access the internet? my firewall wants to know Hi, Are you sure that is trying to access the internet. It may be trying to open the port, remember that MySQL needs 3306 (by default) to communicate with the server. I think if you just open that port, everything will be ok. Bye and Good Luck. --- Matthew K. Gold [EMAIL PROTECTED] wrote: Hi, When I try to start MySQL, I get alerts from my personal firewall that MySQL is trying to access the internet...why would this happen? I'm currently using (or trying to use) Abriasoft's Merlin desktop, which includes apache. I would have thought that if I'm running a apache on my own computer, there would be no need to access the internet. I would guess that this has something to do with Abriasoft, except that this also happened to me when i was running MySQL without the aid of merlin or anything else. I'd appreciate it if anyone could shed some light on this matter... Thanks, Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table Locking + Timeout
lorenzo.kh, Monday, July 29, 2002, 11:59:49 AM, you wrote: lk I have a question regard the table locking. lk Let say there is a table ,mytablename type=MyISAM in my database. lk And lets say there are 2 users who are currently log in. lk First user locks a table write: lk mysql lock tables mytablename write; lk Query OK, 0 rows affected (0.00 sec) lk Then,second user wishes to query something from mytablename. lk mysqlselect * from mytablename; lk ...(pending or in a queue)... lk I know that the second query will be pending untill the 1-st user issues the lk unlock tables command. lk So,i am wondering is it the second query will be waiting there 'forever' if lk the 1-st user 'never' issue the unlock tables command? lk Or is there any method maybe like 'waiting_timeout' that I can set.If the waiting time 'variable' second,then the query will automatically be lk deleted For the INSERT DELAYED you can set up variable delayed_insert_timeout: http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cannot create column having ENUM(
ilya, Friday, July 26, 2002, 3:43:20 PM, you wrote: Description: i It is impossible to create column having ENUM() type. Not sure if i it is a bug as it doesn't make too much sense but at least error i message is very wrong. How-To-Repeat: i mysql create temporary table test ( test enum () ); i ERROR 1074: Too big column length for column 'test' (max = 255). i Use BLOB instead Thank you for bug report. We know about this problem that will be solved. In a future MySQL will allow for enum a blank string ( ) not an empty string(). -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE LOW_PRIORITY
Jacob, Monday, July 29, 2002, 12:19:14 PM, you wrote: JFL MySQL question: JFL If I use UPDATE LOW_PRIORITY will the client then have to wait for the JFL update to finish or is UPDATE LOW_PRIORITY the same as INSERT DELAYED ? It's not the same as INSERT DELAYED. Client will wait for the UPDATE to complete. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL ERROR 1062: Duplicate entry '1' for key 1
Prince, Monday, July 29, 2002, 12:58:22 PM, you wrote: PCA MySQL Question PCA I run sql version 3.23.42. PCA LOAD DATA INFILE /usr/lists INTO TABLE lists; PCA ERROR 1062: Duplicate entry '1' for key 1 PCA Please how do i run this successfully. Ive tried the REPLACE option without PCA any luck. I have no problem on 4.0.2 ... Can you send your table and data file for testing? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
crashing problem with Coldfusion, Mysql, and MyODBC
Hi - I'm having some problems with using MySQL, MyODBC, and Coldfusion together, so I thought I'd post here and see if anyone had any ideas on how to fix the problems. I see other people have had similar issues, so hopefully someone can help me out. So, we've running on Solaris 2.7, with Mysql 3.23.49 (using InnoDB tables...), Coldfusion 5, unixODBC-2.2.2, and MyODBC (both versions 2.50.39 and 3.51.02). Coldfusion and mySQL are running on the same machine. We've gotten everything to basically work - we can do everything we need to in Coldfusion, but we're having the dreaded crashing problem. When we put our Coldfusion application under load, or basically just click a lot of links in the application at once, Coldfusion crashes and restarts. The same application running against Oracle with the oracle native driver doesn't crash - this is code we've run for a long time in a different environment, so we don't think it's a code problem. Anyway - we suspect the problem is some sort of 'thread-safe' problem. Originally we used the binary distributions of MySQL and MyODBC, and then realized the Solaris binary that's available isn't threadsafe. So we built everything from source, following these instructions: http://dbforums.com/showthread.php?threadid=174934 MySQL ColdFusion unixODBC MyODBC and Solaris - how to succeed! of course, adding in the InnoDB option to the mysql compilation. But even after going through all this, we still had the crashing problems. So - I've seen other people post about having the crashing problem - are there any obvious things I may have missed? How do I know if MyODBC and mysqld have been compiled to be thread safe? Here are some concrete questions: Is there a way to force Coldfusion and MyODBC to connect to MySQL using network sockets instead of the Unix sockets? I'd like to test that to see if the behavior is different. I tried to follow the mysql documentation about compiling a thread-safe client: http://www.mysql.com/doc/T/h/Threaded_clients.html one thing I don't understand: The documentation states compiling with thread-safe options will create a thread-safe client library libmysqlclient_r - do I need to make certain that libmyodbc links against the libmysqlclient_r.so, instead of the generic libmysqlclient.so? I've seen posts in the past on this mailing list, and other places, about people having problems using mysql + coldfusion together, so if anyone can help, or has any ideas, please respond to the list or directly to me at [EMAIL PROTECTED] Thanks for any help! Thank you- dana = Dana Quinn [EMAIL PROTECTED] Unix-type guy __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyODBC with Visual C++
Hello All, I need help with MyODBC, I need to create a front-end for MySQL using Visual C++ 6.0. Have any one used MyODBC with Visual C++? I need a source code how to create a ODBC connection using MyODBC drivers. Please help, Thanking in Advance... Regards Nitesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation problem, help! help!
I'm having a devil of a time installing mysql 3.23.51 on SuSE 8 from the source tarball. I did ./config, then make. There are the last few lines from make: libmysql.c: In function `mysql_real_connect': libmysql.c:1325: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1325: too few arguments to function `gethostbyname_r' libmysql.c:1325: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/home/perl/downloads/db/mysql-3.23.51/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/perl/downloads/db/mysql-3.23.51' make: *** [all-recursive-am] Error 2 Here's some info from the mysql debug tool: Submitter-Id: [EMAIL PROTECTED] Originator: [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Configure/Make problem, SuSE 8.0, mysql-3.23.51 Severity: non-critical Priority: low Category: mysql Class:support Release: mysql-3.23.51 (Source distribution) Environment: machine: Dell Optiplex GX1, os: SuSE Linux 2.4.18-4GB, target: ? libraries ? System: Linux hdeteam 2.4.18-4GB #1 Wed Mar 27 13:57:05 UTC 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1394238 Mar 23 12:34 /lib/libc.so.6 -rw-r--r--1 root root 25361424 Mar 23 12:05 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 12:05 /usr/lib/libc.so Configure command: CFLAGS=-O3 CC=gcc CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \ --with-named-curses-libs=/usr/lib/curses \ --with-mysqld-ldflags=-all-static I've read docs until my eyes are popping out. I need a bit of a rescue here. TIA. /dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto-increment and normalisation
A reviewer of a project using MySQL write that use of MySQL auto_increment columns often breaks the rules of normalisation and promotes poor table design. We can't find anything suggesting that in the MySQL documentation, or in our own experience with MySQL auto-increment. Can anyone clarify? Thanks. Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
date functions query problem
Hello, I am having trouble with a particular problem. I am trying to find a formulate query that calculates the no of weekdays and no of weekend day in any given month. I already have a method of generating the number of days in a given month but at that point I am at a loss. Any ideas? Thanks Rob --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation problem, please help
I'm having a devil of a time installing mysql 3.23.51 on SuSE 8 from the source tarball. I did ./config, then make. There are the last few lines from make: libmysql.c: In function `mysql_real_connect': libmysql.c:1325: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1325: too few arguments to function `gethostbyname_r' libmysql.c:1325: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/home/perl/downloads/db/mysql-3.23.51/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/perl/downloads/db/mysql-3.23.51' make: *** [all-recursive-am] Error 2 Here's some info from the mysql debug tool: Submitter-Id: [EMAIL PROTECTED] Originator: [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Configure/Make problem, SuSE 8.0, mysql-3.23.51 Severity: non-critical Priority: low Category: mysql Class:support Release: mysql-3.23.51 (Source distribution) Environment: machine: Dell Optiplex GX1, os: SuSE Linux 2.4.18-4GB, target: ? libraries ? System: Linux hdeteam 2.4.18-4GB #1 Wed Mar 27 13:57:05 UTC 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1394238 Mar 23 12:34 /lib/libc.so.6 -rw-r--r--1 root root 25361424 Mar 23 12:05 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 12:05 /usr/lib/libc.so Configure command: CFLAGS=-O3 CC=gcc CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \ --with-named-curses-libs=/usr/lib/curses \ --with-mysqld-ldflags=-all-static I've read docs until my eyes are popping out. I need a bit of a rescue here. TIA. /dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL ERROR 1062: Duplicate entry '1' for key 1
In the last episode (Jul 29), Prince Chidi Ajuzie said: MySQL Question I run sql version 3.23.42. LOAD DATA INFILE /usr/lists INTO TABLE lists; ERROR 1062: Duplicate entry '1' for key 1 Please how do i run this successfully. Ive tried the REPLACE option without any luck. You probably have a TINYINT AUTOINCREMENT field, which means you can only have 127 rows in your table. Make it an INT or BIGINT. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DATE INTERVAL question.
Hi, I am trying to run a query that will check to see if a time field is less than the the time in the field plus ten minutes. The field must also be greater than 00:00:00. The query below executes without error but doesn't return rows. Can I do this or is there a better way. I'm using MySQL in conjunction with PHP. SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' AND EDIT_LOCK 0 AND EDIT_LOCK EDIT_LOCK + INTERVAL 10 MINUTE; TIA, Steve. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Write Conflict when accessing MySQL tables in Access (link table)
I think this has something to do with the TIMESTAMP field people have been talking about. - Jeff Rozyckihttp://www.Racquetman.com Digital Publishers http://www.MvCool.com/digital On Fri, 26 Jul 2002, Nathon Jones wrote: Date: Fri, 26 Jul 2002 16:31:43 +0100 From: Nathon Jones [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Write Conflict when accessing MySQL tables in Access (link table) Hi, Can anyone help me with this problem? Sort of related, I think, to my last message regarding errors with Access telling me that someone else was also accessing my database so I couldn't alter or delete data. Anyway, the dialog that comes up says Write Conflict and then I am asked to Copy to Clipboard or Drop Changes. Of course, copying to clipboard and then pasting adds a new record on the end with the appended information...only problem is, it is leaving the original and won't let me delete it!! (Write Conflict). Dang nuisance, doing my head in! Thanks in advance for any help offered. Nathon - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Odd Question on automatic start of Mysql upon boot
The setup command, at the command prompt type setup this will bring up a configuration tool (Im running RH7.2), one of the choices is System Services, under that you can adjust services to start/stop automatically upon boot. This is done after an RPM install or installation upon OS install. This was how I automated the starting and stopping of the MYSQL service. As for the safe_mysqld concern, I was just wondering if there were any caveats to running mysql under safe_mysqld? Is this the normal process name that mysql runs under? I was expecting mysqld or mysql.server to be the process found when running the command ps -ef. I guess Im just reading into the safe_mysql naming convention. From what Im interpretting your statements below as is this: safe_mysql is the normal process that mysql runs under and there is no security issues with this process. Thanks for the help, hopefully above clears up what I was trying to say. ~dK -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Friday, July 26, 2002 5:38 PM To: David Kramer; [EMAIL PROTECTED] Subject: Re: Odd Question on automatic start of Mysql upon boot At 10:24 -0700 7/26/02, David Kramer wrote: I automated the MYSQL start up process using the setup command. the setup command? Everything works fine except that I noticed safe_mysql is the daemon running, shouldnt this be mysqld or mysql.server? I suppose it depends on what the setup command does... If it installs mysql.server, what you observe wouldn't be unusual, because mysql.server starts safe_mysqld, which starts mysqld. Im completely lost here, and I feel running Safe_mysql is a major security issue? Why? What problems do you think this causes? Anyone's thoughts on this? Thx, DK David Kramer Software Developer Reflect.com Direct: 415.369.4856 Cell: 650.302.7889 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
order by date
This must be a regularly appearing problem, which is why i'm surprised I can't find a webpage on it.. I have $result = mysql_query(SELECT title, url, description, author, date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC); Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th etc rather than 31st, 30th and so on. can anyone tell me where to look to read all about it. Thanks Jules - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to have read-only table files (.myi, .myd, .frm)
I am wondering if I can somehow have tables read only at the OS level and still have MySQL work as normal (at least for SELECTs). I can make the table files read only, but when I try to select something, I get the error similar to your_table.MYD cannot be found. The funny thing is if I change the file to read-write, perform a select, and then change it back to read-only, it will work from then on for that MySQL session. Must have something to do with the cache... If anyone has successfully placed MySQL on a CD or some other read-only media, I would appreciate any tips. It looks like I could pack the file with myisampack and the distribute it (?). Thanks, Mike Z - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Jon Frisby---MySQL help needed
Hi Jon, Whatever you have suggested so far hasn't worked. I thought that maybe something is missing in my stating the problem and implementing your suggested solutions. So here is a more expanded picture of the queries: $sth=$dbh-prepare(INSERT INTO TABLE1 (id,var1,var2) VALUES (?,?,?)); $sth-execute(,$var1,$var2); $sth=$dbh-prepare(SELECT \@t1id:=LAST_INSERT_ID()); $sth-execute(); $sth=$dbh-prepare(INSERT INTO TABLE1A (Aid,id,var1) VALUES (?, @t1id, $var1); $sth-execute(,,$var1); Please take a note that in the last prepare statement I have also tried \@t1id ,\@t1id, LAST_INSERT_ID(). I have used these in 'execute' statement too with question marks (?) in the prepare statement. Any new idea please? Thanks, Aamer Try: $sth-execute(...); Sorry for the confusion. Of course I tried the above statement. I took it granted that we would mean the above when we say the following: execute(, \@t1id, $var1...); I still get the same result. I hope you don't quit on me. Thanks. Hrm... Ah! If your code is doing a prepare on a statement of the form: INSERT INTO x VALUES(@t1id, ...) Then you don't need to give @t1id as a parameter in the execute statement... You should only need to provide params to execute for each ? that appears in the INSERT statement... -JF sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-Table Referential Delete Clarification with MySQL 3.23.51
Guys, I know that version 3.23.51 of MySQL does not handle deleting data from multiple tables directly. I am in a situation where i have to delete millions of records from a dozen tables. The delete logic is not simple, i have to check a number of tables before i can actually delete a row from a given table. All of my databases run in a 24x7 env. In order to reduce my scheduled downtime here's what i am planning to do, ofcourse this will be tested against the test env first. Please let me know your feedback. I would surely appreciate to hear about any other better way of accomplishing this task. 1) create temporary (not actually a TEMPORARY) tables for all the tables to be purged using CREATE TABLE ... statement. The structure of the table would be the same as the original table to be purged. I could have used CREATE TABLE ... AS SELECT * FROM statement for creating a temp table, but this statement does not create the indexes, PK, auto_increment et al... 2) Load the data from the original purge table into the temp table based upon my delete logic. 3) Drop the original purge table. 4) Rename the temp table that i have created to the name of the purge table that i have dropped. Let me know if this sounds as a viable solution. I would sure like to hear anything about the table locking and grant issues that i might encounter. Thanks Sri - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Answer: how to get 64 indexes
After many headaches, rantings, and emails, i have FINALLY figured out how to get 64 indexes out of MySQL tables. There are 3 changes you need to make to the source code. in file mysql_priv.h change the line:typedef ulong key_map to:typedef ulonglong key_map in file include/myisam.h change:#define MI_MAX_KEY 32 to:#define MI_MAX_KEY 64 in file include/unireg.h change:#define MAX_KEY 32 to:#define MAX_KEY 64 then recompile mysqld. I think its a sad situation that i could find numerous references to using 64 keys, none more specific than: you need to change some things in the source code or you need to change some things in myisam.h and unireg.h, and some other things regards sean peters [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multi-Table Referential Delete Clarification with MySQL 3.23.51
Forgot to mention that all of my tables are of TYPE: MyISAM Thanks Sri [EMAIL PROTECTED] wrote: Guys, I know that version 3.23.51 of MySQL does not handle deleting data from multiple tables directly. I am in a situation where i have to delete millions of records from a dozen tables. The delete logic is not simple, i have to check a number of tables before i can actually delete a row from a given table. All of my databases run in a 24x7 env. In order to reduce my scheduled downtime here's what i am planning to do, ofcourse this will be tested against the test env first. Please let me know your feedback. I would surely appreciate to hear about any other better way of accomplishing this task. 1) create temporary (not actually a TEMPORARY) tables for all the tables to be purged using CREATE TABLE ... statement. The structure of the table would be the same as the original table to be purged. I could have used CREATE TABLE ... AS SELECT * FROM statement for creating a temp table, but this statement does not create the indexes, PK, auto_increment et al... 2) Load the data from the original purge table into the temp table based upon my delete logic. 3) Drop the original purge table. 4) Rename the temp table that i have created to the name of the purge table that i have dropped. Let me know if this sounds as a viable solution. I would sure like to hear anything about the table locking and grant issues that i might encounter. Thanks Sri - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Linking with Thread-Safe Library Problems
I am porting a multi-threaded MySQL client application that is currently working fine on my development Solaris box to multiple platforms. For some reason that I cannot figure out, my autoconf script is failing on Linux looking for the mysql_thread_init() function in libmysqlclient_r. From the config.log, I see that the simple test program is: int main () { mysql_thread_init(); return 0; } To determine why it was failing, I tried compiling this simple program on my Solaris 2.7 machine, linking with -lpthread and -lmysqlclient_r: gcc abc.c -o abc -lpthread -lmysqlclient_r It compiles fine on Solaris 2.7. On my Red Hat 7.2 (kernel 2.4.18) machine, I have compiled and installed MySQL 3.23.51 with the --enable-thread-safe-client configure option. When I try to compile the exact same program, I get a linker error: /tmp/ccEsSfhd.o: In function `main': /tmp/ccEsSfhd.o(.text+0x7): undefined reference to `mysql_thread_init' The libmysqlclient_r library (libmysqlclient_r.so.10.0.0) does exist and is obviously being found (otherwise I would get a library not found error when specifying -lmysqlclient_r). So, I cannot determine why on Linux, after I have built the thread-safe library, the linker still cannot find mysql_thread_init. Any suggestions very welcomed. Thanks, rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[4]: TRUNCATE TABLE
Hello Andrew. My apologies. I somehow completely mixed up my answer. This answer was intended to a posting which asked why indexes are not copied on CREATE ... SELECT. Don't know how I managed to fit this answer onto your posting without noticing. Day dreaming or something. :-( Greetings, Benjamin. On Sat 2002-07-27 at 20:53:45 +0300, [EMAIL PROTECTED] wrote: BP Hi. BP First, when you start an independend question, please start a new BP thread, but at least change the subject accordingly. This is not independent question. Manual say what TRUNCATE TABLE will recreate table from frm file, but when i use TRUNCATE TABLE, index file will not recreated, becouse it have old data and it size is biger than after CREATE TABLE ... If table recreated from table.frm why it not recreate indexes file? BP Because this is the documented behaviour: BP http://www.mysql.com/doc/C/R/CREATE_TABLE.html ;-) I spoke about TRUNCATE TABLE, not about CREATE ... SELECT ... -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date
That's a really weird problem. Perhaps someone else could reproduce it if it's a bug in the most recent MySQL. I can think of an immediate way to solve it; use the type (datetime?), which is really a string 20020729. I don't see how this could be sorted incorrectly, but check your types. On Mon, 29 Jul 2002, julian haffegee wrote: Date: Mon, 29 Jul 2002 17:59:00 +0100 From: julian haffegee [EMAIL PROTECTED] To: MySQL General List [EMAIL PROTECTED] Subject: order by date This must be a regularly appearing problem, which is why i'm surprised I can't find a webpage on it.. I have $result = mysql_query(SELECT title, url, description, author, date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC); Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th etc rather than 31st, 30th and so on. can anyone tell me where to look to read all about it. Thanks Jules - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation woes, please help
I'm having a devil of a time installing mysql 3.23.51 on SuSE 8 from the source tarball. I did ./config, then make. There are the last few lines from make: libmysql.c: In function `mysql_real_connect': libmysql.c:1325: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1325: too few arguments to function `gethostbyname_r' libmysql.c:1325: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/home/perl/downloads/db/mysql-3.23.51/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/perl/downloads/db/mysql-3.23.51' make: *** [all-recursive-am] Error 2 Here's some info from the mysql debug tool: Submitter-Id: [EMAIL PROTECTED] Originator: [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Configure/Make problem, SuSE 8.0, mysql-3.23.51 Severity: non-critical Priority: low Category: mysql Class:support Release: mysql-3.23.51 (Source distribution) Environment: machine: Dell Optiplex GX1, os: SuSE Linux 2.4.18-4GB, target: ? libraries ? System: Linux hdeteam 2.4.18-4GB #1 Wed Mar 27 13:57:05 UTC 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1394238 Mar 23 12:34 /lib/libc.so.6 -rw-r--r--1 root root 25361424 Mar 23 12:05 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 12:05 /usr/lib/libc.so Configure command: CFLAGS=-O3 CC=gcc CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \ --with-named-curses-libs=/usr/lib/curses \ --with-mysqld-ldflags=-all-static /dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
load data
Hi to all, I changed Linux from version 7.2 to 7.3 and now when I try to load data using LOAD DATA LOCAL INFILE I got the following error: The used command is not allowed with this MySQL version. I enable --local-infile=1 But I'm still getting the same error. Any help, is greatly appreciate it Thanks in advanced Nato - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Jon Frisby---MySQL help needed
On Mon, Jul 29, 2002 at 01:13:30PM -0400, Aamer Rauf wrote: Hi Jon, Whatever you have suggested so far hasn't worked. I thought that maybe something is missing in my stating the problem and implementing your suggested solutions. So here is a more expanded picture of the queries: $sth=$dbh-prepare(INSERT INTO TABLE1 (id,var1,var2) VALUES (?,?,?)); $sth-execute(,$var1,$var2); $sth=$dbh-prepare(SELECT \@t1id:=LAST_INSERT_ID()); $sth-execute(); $sth=$dbh-prepare(INSERT INTO TABLE1A (Aid,id,var1) VALUES (?, @t1id, $var1); $sth-execute(,,$var1); Doesn't this chain of events reuse the variable $sth, which essentially closes the old statement handler? Doesn't that lose state? I'm guessing... Please take a note that in the last prepare statement I have also tried \@t1id ,\@t1id, LAST_INSERT_ID(). I have used these in 'execute' statement too with question marks (?) in the prepare statement. Any new idea please? Try grabbing the new ID manually, before the second prepare: my $id = $dbh-{'mysql_insertid'}; Then, manually use that as a bind variable. Thanks, Aamer -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
memory decay ..
Hi!! Is ok that when I do a dump the memory utilized it is not released. what can I do to solve this problem?? (mysql 2.23.37) thanks sandra - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Jon Frisby---MySQL help needed
Whatever you have suggested so far hasn't worked. I thought that maybe something is missing in my stating the problem and implementing your suggested solutions. So here is a more expanded picture of the queries: $sth=$dbh-prepare(INSERT INTO TABLE1 (id,var1,var2) VALUES (?,?,?)); $sth-execute(,$var1,$var2); $sth=$dbh-prepare(SELECT \@t1id:=LAST_INSERT_ID()); $sth-execute(); After this, try the following: $sth2 = $dbh-prepare(SELECT \@t1id); $sth2-execute(); $tmp = $sth2-fetchrow_arrayref(); print ID: . $tmp-[0]; See if you're getting a number here or not... $sth=$dbh-prepare(INSERT INTO TABLE1A (Aid,id,var1) VALUES (?, @t1id, $var1); Note that using @t1id in double-quotes without escaping the @ will *never* work, because Perl will think you are trying to use a Perl variable, when in reality @t1id is a MySQL variable. $sth-execute(,,$var1); Why are you sending unused bound parameters? Try this: $sth = $dbh-prepare(INSERT INTO TABLE1A (Aid,id,var1) VALUES (0, \@t1id, $var1); $sth-execute(); -JF - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DATE INTERVAL question.
Hi. On Mon 2002-07-29 at 11:35:03 -0400, [EMAIL PROTECTED] wrote: I am trying to run a query that will check to see if a time field is less than the the time in the field plus ten minutes. This does not make sense. This will always be true: A A + 10 = 0 10 = true The field must also be greater than 00:00:00. The query below executes without error but doesn't return rows. Can I do this or is there a better way. I'm using MySQL in conjunction with PHP. SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' AND EDIT_LOCK 0 AND EDIT_LOCK EDIT_LOCK + INTERVAL 10 MINUTE; Well, does SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' return any rows? What about SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' AND EDIT_LOCK 0 EDIT_LOCK 0 should work as expected. The rest should resolve to true, so I do not see any reason why you do not get the desired result. Exclude PHP from the equation, i.e. run the query in the mysql command line client. Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date
Hi, Change the alias to date_format(date,'%D %M %Y') AS mydate, so you are sure that you sort by the date field not my the formatted date. Be aware that if you sort my the formatted string, MySql is going to treat it as a string and therefore sort it as a string. 11th July 2002 2st July 2002 if we compare them as strings but 07-11-2002 07-02-2002 if we compare them as dates. Bye and Good Luck! --- julian haffegee [EMAIL PROTECTED] wrote: This must be a regularly appearing problem, which is why i'm surprised I can't find a webpage on it.. I have $result = mysql_query(SELECT title, url, description, author, date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC); Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th etc rather than 31st, 30th and so on. can anyone tell me where to look to read all about it. Thanks Jules - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date
Hi. On Mon 2002-07-29 at 14:24:54 -0400, [EMAIL PROTECTED] wrote: That's a really weird problem. Perhaps someone else could reproduce it if it's a bug in the most recent MySQL. It's not a bug. I can think of an immediate way to solve it; use the type (datetime?), which is really a string 20020729. I don't see how this could be sorted incorrectly, but check your types. On Mon, 29 Jul 2002, julian haffegee wrote: Date: Mon, 29 Jul 2002 17:59:00 +0100 From: julian haffegee [EMAIL PROTECTED] To: MySQL General List [EMAIL PROTECTED] Subject: order by date This must be a regularly appearing problem, which is why i'm surprised I can't find a webpage on it.. I have $result = mysql_query(SELECT title, url, description, author, date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC); Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th etc rather than 31st, 30th and so on. Of course, you sort by date which is aliased to DATE_FORMAT(date,'%D %M %Y'), which is a usual text, which has not reason to be sorted numerically, as you would expected it to. The solution is simply to sort by the original date value, which is only possible, if you change the alias from date to something else, e.g. pretty_date (btw, it's generally considered bad style to overload existing names). SELECTtitle, url, description, author, DATE_FORMAT(date,'%D %M %Y') AS pretty_date FROM documents_tbl AS d, url_tbl AS u WHERE d.title_id = u.url_id ORDER BY date DESC If you really want to sort by day first, and month next, you would have to make a seperate sorting column (which you ignore in your application, of course): SELECTtitle, url, description, author, DATE_FORMAT(date,'%D %M %Y') AS pretty_date, DATE_FORMAT(date,'%d%m%Y') AS order_date FROM documents_tbl AS d, url_tbl AS u WHERE d.title_id = u.url_id ORDER BY order_date DESC Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: DATE INTERVAL question.
Ya I ran them from the command line and they all work except for when I do the EDIT_LOCK + INTERVAL 10 MINUTE. It actually returns NULL. Very Strange. -Steve. -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 4:13 PM To: Steve Bradwell Cc: [EMAIL PROTECTED] Subject: Re: DATE INTERVAL question. Hi. On Mon 2002-07-29 at 11:35:03 -0400, [EMAIL PROTECTED] wrote: I am trying to run a query that will check to see if a time field is less than the the time in the field plus ten minutes. This does not make sense. This will always be true: A A + 10 = 0 10 = true The field must also be greater than 00:00:00. The query below executes without error but doesn't return rows. Can I do this or is there a better way. I'm using MySQL in conjunction with PHP. SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' AND EDIT_LOCK 0 AND EDIT_LOCK EDIT_LOCK + INTERVAL 10 MINUTE; Well, does SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' return any rows? What about SELECT EDIT_LOCK FROM ordmaster WHERE ORDER_NO = '5' AND EDIT_LOCK 0 EDIT_LOCK 0 should work as expected. The rest should resolve to true, so I do not see any reason why you do not get the desired result. Exclude PHP from the equation, i.e. run the query in the mysql command line client. Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: load data
Hi. On Mon 2002-07-29 at 14:56:42 -0400, [EMAIL PROTECTED] wrote: Hi to all, I changed Linux from version 7.2 to 7.3 There is nothing such as Linux 7.2 or 7.3. The current stable Linux version is 2.4.18, the development version 2.5.29. What you are probably referring to is some kind of Linux based distribution and from the version numbers I guess at RedHat? and now when I try to load data using LOAD DATA LOCAL INFILE I got the following error: The used command is not allowed with this MySQL version. I enable --local-infile=1 But I'm still getting the same error. Did you have a look at http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html If that does not help, be more specific about what you have done and what happened. local-infile is an option to the client _and_ to the server! And it won't work if it is disabled on either side. Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: memory decay ..
Hi. On Mon 2002-07-29 at 16:33:40 -0300, [EMAIL PROTECTED] wrote: Hi!! Is ok that when I do a dump the memory utilized it is not released. what can I do to solve this problem?? (mysql 2.23.37) Please be more specific. How do you notice, the memory is not released. Please quote the output. Does this happen on the client or the server? If it is on the server (or you are running both on the same machine), are you sure that this isn't simply due to MySQL using the buffers it is set to? Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and Filesystems
On Thu, Jul 25, 2002 at 12:07:48PM -0300, João Paulo Vasconcellos wrote: Hello everybody, someone can tell me the best FS to run MyISAM along with InnoDB tables ? I don't know that there is a best one, but I use ReiserFS and it works quite well. I'd stay away from a non-journaling filesystem (ext2) if you plan to store lots of data. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 60 days, processed 1,267,536,721 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Penalty for non-null defaults ?
On 28 Jul 2002, at 17:56, Paul DuBois wrote: At 14:34 -0700 7/28/02, Charlie wrote: Is there any storage / performance penalty for specifying '' or 0 as default values, as opposed to allowing null's in the table structure? NULL values take less storage space. (One bit per NULL value in a row, if I remember correctly). But '' and 0 values also take only one bit of storage space, according to http://www.mysql.com/doc/D/y/Dynamic_format.html And if you don't have any nullable columns in your table, no bytes will be needed for the NULL bits, so it appears that having NULL values actually takes slightly *more* space. sql, query -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
do you need to define not null and unique?
Hello Just wnated to calrify something here. If you define your id in a table to be a primary key, do you also need to define it as 'NOT NULL' and 'UNIQUE'. I've notice in some examples like the following off the mysql site: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); that they say the id col is not null, but do they have to since they've already defined it to be a primary key? Thanks Desmond sql _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHPMyAdmin and mySQL
From suggestions I received on this mySQL list I installed phpMyAdmin from the Mandrake 8.2 CDrom, which is the system I am running.So I am presuming that there are a number of members on the list using phpMyAdmin. When I use the following command in a browser, phpMyAdmin starts up and then tells me: Access denied for user 'root@localhost' (using password NO) When I try to edit the config.inc.php it comes up as a read only file. When I change the permissions on it, things go screwy. Any help would be appreciated. Thanks in advance. Bill. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Penalty for non-null defaults ?
On Mon, Jul 29, 2002 at 06:05:02PM -0400, Keith C. Ivey wrote: On 28 Jul 2002, at 17:56, Paul DuBois wrote: At 14:34 -0700 7/28/02, Charlie wrote: Is there any storage / performance penalty for specifying '' or 0 as default values, as opposed to allowing null's in the table structure? NULL values take less storage space. (One bit per NULL value in a row, if I remember correctly). But '' and 0 values also take only one bit of storage space, according to http://www.mysql.com/doc/D/y/Dynamic_format.html And if you don't have any nullable columns in your table, no bytes will be needed for the NULL bits, so it appears that having NULL values actually takes slightly *more* space. My understanding is that if you mark a column as NOT NULL then MySQL only needs as much space as you'd expect to store the data. However, if the column may contain NULLs, then MySQL marks null values with a special bit in the record header. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 61 days, processed 1,269,328,636 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL hardware concerns
Hi, Were planning on buying more hardware soon and have been pondering the machine configuration mentioned in Rasmus Lerdorfs Programming PHP book (OReilly): a squid cache redirector sending traffic to multiple Apache servers each running a MySQL slave which is replicated from a master MySQL server. It would look something like: Squid cache redirector | Apache1 Apache2 Apache3 MySQL slave MySQL slave MySQL slave | Master MySQL server Although we dont yet make use of it, our code has been optimized to send writes to a master database and keep the reads local. Were at about 99.92% read key efficiency with our MySQL/Apache/PHP application. Up until now weve been pseudo-scaling by adding dual 1.0 GHz p3 machines with 1 GB memory and running both apache + mysql on them without replication. Although this has worked for us, it has not allowed us to accommodate many users at once on any single server. What hardware configuration and vendors would you suggest for a database server (MySQL) accommodating upwards of 100,000 users at once? So far weve used only Intel Pentium machines and thusly were hesitant to make a leap in architecture. Right now we dont have the funds to make a mistake in buying a larger, corporate solution so were doing a lot of reading and watching. If you were to follow the schema above, what hardware would you choose? Another question: would a mysql master doing writes only and replicating to multiple servers work better with more clock speed or more cache i.e. would a dual 700Mhz Xeon with 1-2Mb cache (more cache) work better than a 1.4Ghz Xeon with fixed 512k cache (more clock cycles)? I have also heard mixed opinions about a performance loss with 2 Xeon processors-- can anyone confirm this? Thanks in advance, Jeremy Hiatt -- _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL hardware concerns
On Tue, Jul 30, 2002 at 01:05:18AM +, Jeremy Hiatt wrote: Hi, Were planning on buying more hardware soon and have been pondering the machine configuration mentioned in Rasmus Lerdorfs Programming PHP book (OReilly): a squid cache redirector sending traffic to multiple Apache servers each running a MySQL slave which is replicated from a master MySQL server. It would look something like: Squid cache redirector | Apache1 Apache2 Apache3 MySQL slave MySQL slave MySQL slave | Master MySQL server Yes, it works well. Good choice. Although we dont yet make use of it, our code has been optimized to send writes to a master database and keep the reads local. Were at about 99.92% read key efficiency with our MySQL/Apache/PHP application. Up until now weve been pseudo-scaling by adding dual 1.0 GHz p3 machines with 1 GB memory and running both apache + mysql on them without replication. Although this has worked for us, it has not allowed us to accommodate many users at once on any single server. Makes sense. What hardware configuration and vendors would you suggest for a database server (MySQL) accommodating upwards of 100,000 users at once? So far weve used only Intel Pentium machines and thusly were hesitant to make a leap in architecture. Right now we dont have the funds to make a mistake in buying a larger, corporate solution so were doing a lot of reading and watching. If you were to follow the schema above, what hardware would you choose? Can you translate 100,000 users into database numbers? How many SELECTs per second, UPDATEs per second, and so on? That'd help a lot. Another question: would a mysql master doing writes only and replicating to multiple servers work better with more clock speed or more cache i.e. would a dual 700Mhz Xeon with 1-2Mb cache (more cache) work better than a 1.4Ghz Xeon with fixed 512k cache (more clock cycles)? I have also heard mixed opinions about a performance loss with 2 Xeon processors-- can anyone confirm this? I suspect that you'll get more bang for the buck out of memory than CPU power. Once you're above 1GHz of power, the CPU ceases to be the bottleneck unless you're really pumping a lot of data... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 61 days, processed 1,270,149,448 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: do you need to define not null and unique?
At 16:32 -0700 7/29/02, Desmond Lee wrote: Hello Just wnated to calrify something here. If you define your id in a table to be a primary key, do you also need to define it as 'NOT NULL' and 'UNIQUE'. I've notice in some examples like the following off the mysql site: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); that they say the id col is not null, but do they have to since they've already defined it to be a primary key? A PRIMARY KEY must be defined to be NOT NULL, a UNIQUE index need not be. If you define a column as AUTO_INCREMENT, MySQL will automatically define it as NOT NULL (at least, it will as of some 3.23.xx version of MySQL). If you define a column as a PRIMARY KEY and as UNIQUE, you may end up with two unique indexes on the column, so you don't want to do that. Thanks Desmond - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: Complex SQL assistance
Jon, This is exactly what was needed. You are correct in that we're using the mysql foo.sql syntax for our importing. It is the only way we can get access to the hosting companies DB server. Except of course via PHP but that isn't suited to mass importing remotely. Thank you for your suggestion on the INSERT IGNORE syntax. The load on the Database isn't terribly important as the query will run infrequently. The main concern was that the information in the table be updated if it exists and if it doesn't it needs to be inserted. My apologies for not stating in my original email that the column I called x was a unique primary key field. Replace wasn't an option in this case as there are fields that can't be updated remotely that must still contain their original data after the update has occurred on other fields. Many thanks for your assistance. -Corey - Original Message - From: Jon Frisby [EMAIL PROTECTED] To: Gerald Clark [EMAIL PROTECTED]; Corey Wallis [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, July 27, 2002 8:16 AM Subject: RE: Fw: Complex SQL assistance SQL does not exist in a vacuum. You have to run a client. The client is part of or runs under some scripting language ( perl , php, sh, command.com ) which in turn runs on an operating system. You have to somehow pass values that you want to select, update, or insert. So it really not possible to run JUST SQL. What are you using? mysql foo.sql Our nightly report generation script is a 400 line .sql file. Our hourly report generation script is a 280 line .sql file. No scripting language using some DB API to feed queries to the server -- just mysql whatever.sql. What one can accomplish with this approach is quite limited by the fact that MySQL's SQL implementation doesn't rise to the level of full programming language unlike, for example, Oracle's PL/SQL in which it is quite possible to write loops, conditional logic, etc. Now, to address the original question: If field X is a unique field (PRIMARY KEY or UNIQUE INDEX) you could try this: INSERT IGNORE INTO whatever SET x = '1234', ...; UPDATE whatever SET ... WHERE x = '1234'; Under ideal circumstances this results in a bit of redundant DB access, which may or may not matter to you depending on your circumstances. If there is no uniqueness constraint on field x, then this technique will not work and it's unlikely that just SQL (MySQL's SQL anyway) will be adequate. The TODO list for MySQL contains exactly this item though (update a row if it exists, otherwise insert it, aka REPLACE INTO behavior for UPDATEs) but no estimate as to when it will be included. -JF Corey Wallis wrote: Peoples, I'm currently trying to work out if this is possible by SQL. I have the need to use SQL and only SQL to achieve the following. If a record exists and meets a certain criteria (i.e. field X = '1234') then update the record. If the record doesn't exist then insert it. For reasons too complex to go into at this stage using anything except SQL is not possible. Any and all suggestions welcome. -Corey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL hardware concerns
Can you translate 100,000 users into database numbers? How many SELECTs per second, UPDATEs per second, and so on? That'd help a lot. I believe roughly 70% of our queries are SELECTs, 29% UPDATEs, and less than a percent for both INSERTs and DELETEs. MySQL on localhost (3.23.46) up 4+22:08:37 Queries Total: 38,217,014 Avg/Sec: 89.86 Now/Sec: 131.80 Slow: 0 Threads Total: 1 Active: 1 Cached: 0 Key Efficiency: 99.98% Bytes in: 3,696,152,003 Bytes out: 4,006,033,106 +--++ | Variable_name| Value | +--++ | Handler_delete | 2056 | | Handler_read_first | 86116 | | Handler_read_key | 38126269 | | Handler_read_next| 66568466 | | Handler_read_prev| 0 | | Handler_read_rnd | 26653054 | | Handler_read_rnd_next| 4229676008 | | Handler_update | 12940207 | | Handler_write| 171166 | | Key_blocks_used | 15582 | | Key_read_requests| 77465425 | | Key_reads| 14742 | | Key_write_requests | 470685 | | Key_writes | 468967 | | Uptime | 425398 | +--++ This is from a production server and isn't as efficient as the work in progress on development servers (note handler_read_rnd_next), but these are accurate enough for scaling. Note that many queries take place server-side in automatic calculations (cron). We're leaning towards a dual 1GHz (512k cache) box with 4GB memory and SCSI raid 1-0. Comparitively the figures above are from a dual 1GHz (256k cache) running both apache+mysql, with 1GB memory and SCSI also. As I mentioned before this will be our main DB. Do you think this the best move for our money? How do I calculate how many Apache/MySQL Slave sub-servers I can add before our master maxes out? This seems like a hit and miss way to scale, hardware wise. For a few thousand $$$ in new hardware expenses I'd much prefer a hit. Thanks, Jeremy _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SHOW syntax and hyphen in a dbname
Dear Folks, I'm not sure this is a bug or the SQL syntax. I created a db named a-b that includes a hyphen. % mysql a-b mysql show tables ; +-+ | Tables_in_a-b | +-+ | a | | b | +-+ 7 rows in set (0.00 sec) mysql show tables from a-b ; ERROR 1064: You have an error in your SQL syntax near 'a-b' at line 1 I think the syntax should accept such a DB's name and this is a bug. Anybody knows how the SQL should handle the hyphen? Sincerely yours. Takanori Ugai -- Takanori Ugai internet: [EMAIL PROTECTED] Document Processing Lab. FUJITSU LABORATORIES LTD. Kawasaki Japan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW syntax and hyphen in a dbname
On Tue, Jul 30, 2002 at 01:45:59PM +0900, Takanori Ugai wrote: Dear Folks, I'm not sure this is a bug or the SQL syntax. I created a db named a-b that includes a hyphen. % mysql a-b mysql show tables ; +-+ | Tables_in_a-b | +-+ | a | | b | +-+ 7 rows in set (0.00 sec) mysql show tables from a-b ; ERROR 1064: You have an error in your SQL syntax near 'a-b' at line 1 I think the syntax should accept such a DB's name and this is a bug. Anybody knows how the SQL should handle the hyphen? Quote it: mysql show tables from `a-b`; Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 61 days, processed 1,271,831,535 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERROR 2002
Very sorry for my childish question, I'm just starting to learn about MySQL Following is my procedure in fixing the problem of installation on Intel PC, and Linux RH7.3. 1. RPM installation 2. When execute mysql, there is a message ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/msql.sock' (111) 3. Follow the Binary installation 4. At ./scripts/mysql_install_db, there is a message ./bin/mysqld: Shutdown Complete 5. At ./bin/safe_mysqld --user=mysql , there is a message mysqld ended 6. mysql command still cannot be executed successfully with the message in the 2nd step. --- Chai Wutiwiwatchai Furui Laboratory, Department of Computer Science, Tokyo Institute of Technology --- _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PHPMyAdmin and mySQL
On 29 Jul 2002, William Bradley wrote: From suggestions I received on this mySQL list I installed phpMyAdmin from the Mandrake 8.2 CDrom, which is the system I am running.So I am presuming that there are a number of members on the list using phpMyAdmin. I am one of the developers of phpMyAdmin. I'm not sure which version of phpMyAdmin is shipped with Mandrake 8.2, but I can tell you that it is in your best interests to upgrade to the latest version (2.3.0-rc4 and 2.3.0 final in 2 weeks). When I use the following command in a browser, phpMyAdmin starts up and then tells me: Access denied for user 'root@localhost' (using password NO) It sounds like you don't have any password in the password field for the basic authentication method. When I try to edit the config.inc.php it comes up as a read only file. When I change the permissions on it, things go screwy. Sounds like some weird setup in Mandrake. See my advice above re upgrading. (Filter fodder: sql, query) -- Robin Hugh Johnson E-Mail : [EMAIL PROTECTED] Home Page : http://www.orbis-terrarum.net/?l=people.robbat2 ICQ# : 30269588 or 41961639 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
THIS IS NOT AN ADVERTISEMENT, ERROR 2002
Very sorry for my childish question, I'm just starting to learn about MySQL Following is my procedure in fixing the problem of installation on Intel PC, and Linux RH7.3. 1. RPM installation 2. When execute mysql, there is a message ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/msql.sock' (111) 3. Follow the Binary installation 4. At ./scripts/mysql_install_db, there is a message ./bin/mysqld: Shutdown Complete 5. At ./bin/safe_mysqld --user=mysql , there is a message mysqld ended 6. mysql command still cannot be executed successfully with the message in the 2nd step. --- Chai Wutiwiwatchai Furui Laboratory, Department of Computer Science, Tokyo Institute of Technology --- _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW syntax and hyphen in a dbname
Thank you all to answer me Quotation. mysql show tables from `a-b`; It is working well. In messasge [EMAIL PROTECTED], Jeremy Zawodny [EMAIL PROTECTED] wrote: On Tue, Jul 30, 2002 at 01:45:59PM +0900, Takanori Ugai wrote: Dear Folks, I'm not sure this is a bug or the SQL syntax. I created a db named a-b that includes a hyphen. % mysql a-b mysql show tables ; +-+ | Tables_in_a-b | +-+ | a | | b | +-+ 7 rows in set (0.00 sec) mysql show tables from a-b ; ERROR 1064: You have an error in your SQL syntax near 'a-b' at line 1 Takanori Ugai -- Takanori Ugai internet: [EMAIL PROTECTED] Document Processing Lab. FUJITSU LABORATORIES LTD. Kawasaki Japan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ODBC
Sorry, I know nothing about it so I'm asking... (wasn't long ago I could get MySQL installed and running. ;-) I'm using Mac OS X (Server). I installed MySQL... if ODBC is not included, does anyone know where or how I get it for OS X and MySQL? (I've read the MyODBC site but it doesn't seem to list OS X. I've read a bit about iODBC, but don't understand if that's what I want or not.) Any info is great and appreciated. Lloyd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
cant store checkbox vale ..... ?????
hi guys im stuck with check boxes now n id b really gratefull if anyone d help plx the thing is i cnt get the cheked value from a checkbox input type=checkbox name=news_letter_subs sign me up fo weekly news letter and updates/input the processing for this is: $isql = insert into usr_inf (email, firstName, lastName, news_letter_subs) . values (' . $email . ', ' . $firstName . ', ' . $lastName . ',' . $news_letter_subs . ') ; after a million checks on the passed data all the fileds get stored in my db except fo news_letter_subs i get a 0 fo it in my db :S where m i mucking it up guyz plz hlp thnx a million toby . _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: cant store checkbox vale ..... ?????
Toby, This is not a MySQL question, it is an issue of PHP and would be best asked on a PHP forum. But in any case, the value tag is missing in your checkbox HTML. Shashank sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: time field query problems.
Hi. On Mon 2002-07-29 at 14:41:30 -0400, [EMAIL PROTECTED] wrote: Sorry to repost but I typed in the wrong sql statement in my previous post. Ah. Okay. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; Regardless of the original problem, you should use EDIT_LOCK NOW() - INTERVAL 10 MINUTE because this variant has no expression on the left side and therefore could use an index, if there is one on EDIT_LOCK (MySQL does not optimize expressions, in the few cases where this would be possibible). -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. Oh. Is it really a TIME field, not TIMESTAMP? In this case you would compare a time (without date) with a whole datetime value from NOW(). These values cannot be compared reasonable. Additionally +- INTERVAL only works on DATE or DATETIME/TIMESTAMP values, otherwise you have to use DATE_SUB/DATE_ADD. In this case, you would need something like SELECT EDIT_LOCK FROM ordmaster WHERE EDIT_LOCK DATE_FORMAT( NOW() - INTERVAL 10 MINUTE, %T ) AND ORDER_NO = '5' AND EDIT_LOCK 0; This does not handle the special cases on day change, but I presume that this already had been thought of, or else a TIME instead of a DATETIME field makes no sense. Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Time Allocation Issue
I'm writing a web application in PHP to do referee scheduling for soccer games. For each game (a single row in a table) I have a cell for referee. The cell stores an integer that I can do a join on with another table of people. I need a way to prevent people from being able to schedule themselves for multiple slots at the same time. (All I care about is start time, for now I'm going to ignore the issue of a game ending after the next one has started.) I have tried doing just a unique index on referee, date, and time but the issue is I use a 0 to indicate that the slot is open. Because of that I don't see a way to do it natively in mysql as such each time I do an insert I think I am going to need to do a select right before to make sure there no conflicts. Is there any better more efficient way to do this? Perhaps natively in mysql. TIA Thanks Jefferson Cowart [EMAIL PROTECTED] Support Open Instant Messaging Protocols http://www.petitiononline.com/openIM/petition.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP-DB] time field query problems.
Steve, For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? The best way to compare time in PHP is to use the MySQL RDBMS that is managing/retrieving the data for you. Recommendation 1: do not use a Time field (you did mean the back 'half' of a Date-time field didn't you?). Because you are (apparently only) using this field to temporarily lock a row, the value is only ever used for computation (cf display). A Timestamp field is best for computation - a Time field for presentation. Consider also storing such data as an integer field or beware the automatic update feature for Timestamp fields. Recommendation 2: re-consider the (default) use of NULL - this may be the root of the question you're asking: what if the row has never been 'locked' and attempt the (above) SELECT? (then the last comparison clause would be illogical) If the default were zero (0 or 00:00:00) and the retrieval logic updated slightly, things should be less complicated. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP-DB] Time Allocation Issue
Jefferson, I'm writing a web application in PHP to do referee scheduling for soccer games. For each game (a single row in a table) I have a cell for referee. The cell stores an integer that I can do a join on with another table of people. I need a way to prevent people from being able to schedule themselves for multiple slots at the same time. (All I care about is start time, for now I'm going to ignore the issue of a game ending after the next one has started.) I have tried doing just a unique index on referee, date, and time but the issue is I use a 0 to indicate that the slot is open. Because of that I don't see a way to do it natively in mysql as such each time I do an insert I think I am going to need to do a select right before to make sure there no conflicts. Is there any better more efficient way to do this? Perhaps natively in mysql. TIA Speaking for referees everywhere, I'd say not to bother with 'time' because I wouldn't want to cope with more than one 90-minute game in a day - but perhaps you're working on rapid-fire short-game tournaments or somesuch... There are two issues here: firstly has a referee been assigned to control each/every game - or does this (one) game have a referee assigned to it? Secondly, when a referee is assigned, is (s)he in fact 'available'. Sounds like we should be using a project planning package! The first question is answered by SELECT gameId FROM games WHERE refereeId = 0; and/or SELECT refereeId FROM games WHERE gameId = ?; The second 'fails' if you can SELECT gameId FROM games WHERE refereeId = ? AND gameDate = ? AND gameTime = ? (ie no rows returned implies the referee is available, one row that (s)he is assigned, and more than one row that you have a scheduling snafu!) The two queries are logically quite separate. Will attempting to implement both aspects in a single query actually help your system? MySQL: Insofar as your realisation that games occupy time slots 'from' and 'to' moments in time, you might like to check out the BETWEEN comparator. Many consider it good practise to 'check' with a SELECT before performing an UPDATE or INSERT. Soccer refereeing: Please consider that hard-working referees deserve a rest between matches! Also that running another game is not the only reason why a referee might not be available to you. Hope this helps, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
time field query problems.
Sorry to repost but I typed in the wrong sql statement in my previous post. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? Thanks, Steve. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php