RE: MySQL Poll
I suppose it depends on your needs and/or exactly what you want to accomplish, but I use urSQL (www.urbanresearch.com/ursql) to access data on MySQL, MSSQL, MS Access, and Paradox databases. I use urSQL frequently to do ad hoc queries and basic SQL tasks... I wouldn't really call it an Admin tool, but more of an general SQL tool or utility, and I do find it useful. -Original Message- From: Marvin Cummings To: [EMAIL PROTECTED] Sent: 7/14/02 10:17 PM Subject: MySQL Poll If it's ok with the site admin, I'd like to post a poll to see what utilities are being used to administer mysql on w2k. I was dreading having to actually use a command prompt to manage and administer mysql until I found this mysql-front tool, which seems to handle all of those responsibilities. So I guess my question is what tools are being used? Again this is on a w2k server running apache php4. - 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
Rotation of errorlog fails
Description: The command mysqladmin -h host -u user -ppass flush-logs does rotate the logs of mysql but not the errorlog. How-To-Repeat: Self compiled mysql on debian. Fix: Not a real solution but an manual restart of the mysql server reactivates the errorlog. Submitter-Id: submitter ID Originator:Jan Albrecht ([EMAIL PROTECTED]) Organization: MySQL support: none Synopsis: The mysqladmin flush-logs command does not rotate the errorlog. Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.49 (Source distribution) Environment: OS: Debian woody System: Linux glyct001 2.4.12 #5 SMP Fri May 3 10:43:40 CEST 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 15 16:20 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1170812 Feb 4 08:03 /lib/libc-2.2.5.so -rw-r--r--1 root root 2667532 Feb 4 08:03 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 4 08:03 /usr/lib/libc.so Configure command: ./configure --prefix=/bms/database/mysql --localstatedir=/bms/database/content --enable-assembler --with-mysql-user=mysql --with-libwrap --with-berkeley-db - 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: Innodb
Ki, mysqld-max-nt.exe is the same as mysqld-max.exe, except that you can also use named pipes as the client communication channel in mysqld-max-nt.exe. There should be no difference in stability. There are some small bug fixes in 3.23.51 which were not yet in .49. 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: Ki Mien [EMAIL PROTECTED] To: Ki Mien [EMAIL PROTECTED]; [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED]; Cal Evans [EMAIL PROTECTED] Sent: Monday, July 15, 2002 7:40 AM Subject: RE: Innodb Hi Cal, Thanks for the tip. But any reasons as to why 3.23.51-max is preferred to 3.23.49-max-nt? cheers, Ki Mien -Original Message- From: Cal Evans [EMAIL PROTECTED] Sent: Saturday, July 13, 2002 11:34 PM To: Ki Mien [EMAIL PROTECTED], [EMAIL PROTECTED] [EMAIL PROTECTED], Heikki Tuuri [EMAIL PROTECTED] Subject: RE: Innodb 3.23.51-max if you are using Windows. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Ki Mien [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 10:17 AM To: [EMAIL PROTECTED]; Heikki Tuuri Subject: Innodb Hi all, Can anyone out there tell me which version of mysql is more stable or preffered for using Innodb. I am currently using mysql version 3.23.49-max-nt-log. cheers, Ki Mien Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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: user
Dear Michal, I am not sure, but I think you can create an user will all the rights in mysql - user (database - table) - this will solve your problem with full access to all databases. And then you can use other tables in database mysql to define rights of this user to table user. Have a look to manual pages: 4.2 General Security Issues and the MySQL Access Privilege System http://www.mysql.com/doc/P/r/Privilege_system.html Regards, Jan - Original Message - From: Michal Dvoracek Sent: Monday, July 15, 2002 7:40 AM Subject: user Hello, is possible to create user which will be not able to create other users and will have full access to all existing databases and databases which will be created in future? Thank you. Regards, Michal Dvoracek - 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: ODBC and TimeStamp
What error? What schema? Try TIMESTAMP(14) Pat... - Original Message - From: Todd Cary [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 14, 2002 11:31 PM Subject: ODBC and TimeStamp I have a MySQL TimeStamp in my table. If I try to do a query via ODBC, I get an error on the field. Any suggestions? Todd -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [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 - 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: Innodb
Hi Heikki, Thanks for the help. What do you mean by small bugs? Do you mind naming the few that caused the most problems? cheers, Ki Mien -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, July 15, 2002 4:46 PM To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject:Re: Innodb Ki, mysqld-max-nt.exe is the same as mysqld-max.exe, except that you can also use named pipes as the client communication channel in mysqld-max-nt.exe. There should be no difference in stability. There are some small bug fixes in 3.23.51 which were not yet in .49. 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: Ki Mien [EMAIL PROTECTED] To: Ki Mien [EMAIL PROTECTED]; [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED]; Cal Evans [EMAIL PROTECTED] Sent: Monday, July 15, 2002 7:40 AM Subject: RE: Innodb Hi Cal, Thanks for the tip. But any reasons as to why 3.23.51-max is preferred to 3.23.49-max-nt? cheers, Ki Mien -Original Message- From: Cal Evans [EMAIL PROTECTED] Sent: Saturday, July 13, 2002 11:34 PM To: Ki Mien [EMAIL PROTECTED], [EMAIL PROTECTED] [EMAIL PROTECTED], Heikki Tuuri [EMAIL PROTECTED] Subject: RE: Innodb 3.23.51-max if you are using Windows. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Ki Mien [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 10:17 AM To: [EMAIL PROTECTED]; Heikki Tuuri Subject: Innodb Hi all, Can anyone out there tell me which version of mysql is more stable or preffered for using Innodb. I am currently using mysql version 3.23.49-max-nt-log. cheers, Ki Mien Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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: user
Jan Kudrman wrote: Dear Michal, I am not sure, but I think you can create an user will all the rights in mysql - user (database - table) - this will solve your problem with full access to all databases. I think that the better choice is to use GRAND command: grant all on *.* to 'newuser'@'yourhost' identified by 'somepass'; Above will create user named newuser with all privileges on all databases (including mysql) but without grant privilege (can't create new users). Something like that will create user with all provileges on all dbs and with power of creating new users: grant all on *.* to 'newuser'@'yourhost' identified by 'somepass' with grant option; For more information take a look on GRAND command. And then you can use other tables in database mysql to define rights of this user to table user. Have a look to manual pages: 4.2 General Security Issues and the MySQL Access Privilege System http://www.mysql.com/doc/P/r/Privilege_system.html Regards, Jan (...) Best Regards, -- Jarek - 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: select query
MySQL doesn't support sub-selects...You must use joins and temporary tables. Check: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html it gives some info about it. On Fri, 2002-07-12 at 16:37, Narcis GRATIANU wrote: When I try this: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) LIMIT 0, 100 I got this error message: You have an error in your SQL syntax near 'SELECT MAX(s2.price) FROM shop s2 WHERE s1.article' at line 3 My shop table contains this data: +-++---+ | article | dealer | price | +-++---+ |0001 | A | 3.45 | |0001 | B | 3.99 | |0002 | A | 10.99 | |0003 | B | 1.45 | |0003 | C | 1.69 | |0003 | D | 1.25 | |0004 | D | 19.95 | +-++---+ and the table was created with this commnad: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); Thank You ! -- Diana Soares - 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: Replication problems
Thanks a lot. I've fixed my problems with this. Best regards. - Original Message - From: Gerald Clark [EMAIL PROTECTED] To: Santiago Alba [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 12, 2002 3:53 PM Subject: Re: Replication problems Did you copy the master.info file over too? Santiago Alba wrote: Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+--- - --+-+---+-+-+-- - -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+--- - --+-+---+-+-+-- - -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+--- - --+-+---+-+-+-- - -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+--- - --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+--- - --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+--- - --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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 table corruption...
Chris, Sunday, July 14, 2002, 5:44:49 AM, you wrote: CB I'm new to the list. CB I'm writing because I've been having trouble with table corruption. I'm CB using isam tables and i've been using myisamchk under linux to check them. CB alot of the time it just reports that some of the tables were'nt closed CB properly so i repair them but after doing that i find that the tables are CB ruined. myisamchk is used for checking and repairing MyISAM tables, to repair ISAM tables you should use isamchk. CB I read that the linux 2.2.14 kernel can be a problem with mysql but i'm CB running the 2.4.8 kernel. CB Also, I did backup the databases using mysqldump...but after tryign to CB restore by: CB mysql --user=whoever --password=whatever --host=whereever CB database_restore.txt CB i get errors...I think that when the backup was created bad data was written CB to the file. What errors did you receive? CB Is there supposed to be a minimum file size before your are supposed to run CB myisamchk, or is there something i should be running before i run myisamchk? You shouldn't run myisamchk when mysqld is running. -- 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: users privileges
tlr7425, Monday, July 15, 2002, 3:38:32 AM, you wrote: t I've read the Adding New Users Section and the initial and post setup t section of the manual, but I still need some clarification: t After an install and a working MySQL I see it like this (Mac OS XS): t there are 3 users: t mysql - created manually after installing MySQL and has a password t root - a mysql root user whose password has been set t me - myself, using my machine t (I have also run chown on mysql to user mysql.) t What privileges need to be set and at what levels? First of all I suggest you to set up password for your user 'root'. As to privileges for users it depends on their tasks ... Check the following link, it may help you: http://www.mysql.com/doc/P/r/Privileges_provided.html You can read here about privileges provided by MySQL. -- 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: Re: newbie output
tlr7425, Saturday, July 13, 2002, 11:55:18 PM, you wrote: t Thank you, I hope this is the right place to be asking these questions. If t there is a more newbie place please let me know. Here's more output I t need help with, I think it's self-explanatory except that I don't how to t rectify it. The following is a continuation my original message and a t reply. t First, I tried what what was recommended in the response to my original (far t below), as follows: t Welcome to Darwin! t [zbase:~] mysql% /usr/share/mysql/mysql.server start t [zbase:~] mysql% Starting mysqld daemon with databases from t /Users/mysql/Documents/dbs t 020713 16:39:15 mysqld ended t I've seen that before so I tried (sorry to copy it all): t Welcome to Darwin! t [mbase:~] mysql% mysql_install_db t Preparing db table t Preparing host table t Preparing user table t Preparing func table t Preparing tables_priv table t Preparing columns_priv table t Installing all prepared tables t ERROR: 1 Can't create/write to file './mysql/db.frm' (Errcode: 13) [skip] t ERROR: 1 Can't create/write to file './mysql/db.frm' (Errcode: 13) t 020713 16:42:03 /usr/libexec/mysqld: Shutdown Complete [skip] t How can I rectify what I see as an install_db failure? $ perror 13 Error code 13: Permission denied Check your permissions on the MySQL data dir, MySQL must be owner of the data dir. -- 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: Restoring DB
Charitha, Monday, July 15, 2002, 8:54:03 AM, you wrote: C Is there any way in mysql to restore the data back after flushing off C all the data already present using a single command. C I am having backup of the db and i want to restore the data back in the C database by flushing off all the data already present. It depends on how did you backup of your database and what options did you use. Did you use mysqldump or mysqlhotcopy or what? F.e. if you made database backup with mysqldump you can do mysql database_name backup_file Note: if you don't use --add-drop-table option of mysqldump and you restore data in the same database (which contains old data), you will get errors such as: 'Table 'table_name' already exists' or 'Duplicate entry 'x' for key x' -- 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: Rotation of errorlog fails
jan, Monday, July 15, 2002, 10:01:04 AM, you wrote: Description: ja The command mysqladmin -h host -u user -ppass flush-logs does rotate the logs of mysql but not the errorlog. How-To-Repeat: ja Self compiled mysql on debian. Fix: ja Not a real solution but an manual restart of the mysql server reactivates the errorlog. It's a normal behavior of error logs. -- 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: user
Michal, Monday, July 15, 2002, 8:40:58 AM, you wrote: MD is possible to create user which will be not able to create other MD users and will have full access to all existing databases and MD databases which will be created in future? No. If you set up all privileges for your user on *.*, user will have also privileges on the database 'mysql'. Even if user doesn't have grant_priv, he can edit privlege tables manually. -- 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: user
On Mon, 15 Jul 2002 11:58:33 +0200 Jarek Jarzebowski [EMAIL PROTECTED] wrote: Jan Kudrman wrote: Dear Michal, I am not sure, but I think you can create an user will all the rights in mysql - user (database - table) - this will solve your problem with full access to all databases. I think that the better choice is to use GRAND command: grant all on *.* to 'newuser'@'yourhost' identified by 'somepass'; Above will create user named newuser with all privileges on all databases (including mysql) but without grant privilege (can't create new users). Such a user CAN create new users (by adding appropriate records in mysql tables). The only trouble is that he cannot do flush-privileges, so users created this way will be valid after the next restart of the database. - 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
3.23.51 problems
I have been using mysql for a while and I am now building two new servers to provide web/database services. I have had a number of problems with the mysql installation. BINARY DISTRIBUTION I have had very mixed success with the binary distribution. It assumes /tmp/mysql.sock for the socket but by default /tmp is only accessible by root. I can change the socket for the server and the client, but then I cannot for example install the Perl DBD modules as they always attempt to use /tmp/mysql.sock Changing the rights to /tmp does not always fix the problem - sometimes the mysqld processes do not start (I get nothing logged) and sometimes the system reboots. I have found that after the system has rebooted itself mysql seems to come up correctly if it is using /tmp/mysql.sock I decided to try the source distribution SOURCE DISTRIBUITON When I run the ./configure script I get the message: configure: error: cannot find sources in . or .. I get the same message with all of the options I have tried passing. As it seems I cannot compile the software I am left with the apparently unstable binary as my only option. Where should I go from here ? - 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: Replication problems
Hi, That wasn't the problem. The problem was in master.info file. Thanks a lot. - Original Message - From: Ralf Narozny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 12, 2002 4:39 PM Subject: Re: Replication problems Hello! You will get problems when you set the 2nd slaves server-id the same as other one. server-ids MUST be unique...once made that mistake too... Greetings Ralf Gerald Clark wrote: Did you copy the master.info file over too? Santiago Alba wrote: Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql show slave status; +--+-+-+---+ --+-+---+-+-+ --- -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+ --+-+---+-+-+ --- -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+ --+-+---+-+-+ --- -++--+ 1 row in set (0.00 sec) mysql show processlist; ++-+---+--+-+--+ --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+ --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+ --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - 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
Replication slave repeatedly gets error 1159 from master
Description: Every 60 seconds, the replication slave gets an error 1159 from the master server. Thirty seconds later, it reconnects to the master. Then the cycle starts all over again. I have master-connect-retry=30 in the config file. Here is an extract of the replication slave's error log: 020715 12:05:33 Error reading packet from server: (server_errno=1159) 020715 12:06:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:06:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 020715 12:06:33 Error reading packet from server: (server_errno=1159) 020715 12:07:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:07:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 In between disconnections, replication *appears* to be occurring correctly, but it is rather disturbing that there seems to be some kind of communication problem between the master and slave servers. This problem did not occur in version 3.23.49, by the way. It appeared when we upgraded to 3.23.51. How-To-Repeat: Set up a master/slave replication pair of servers. Fix: Not known. Submitter-Id: submitter ID Originator:David Harper Organization: Wellcome Trust Sanger Institute, Hinxton, Cambridge CB10 1SA, England MySQL support: none Synopsis: Replication slave repeatedly gets error 1159 from master Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.51 (Official MySQL binary) Environment: System: OSF1 mars V5.1 732 alpha Machine: alpha Some paths: /bin/perl /bin/make /usr/local/bin/gmake /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf5.1/3.0.3/specs Configured with: ../configure Thread model: single gcc version 3.0.3 Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root system17 Mar 30 2001 /lib/libc.a - ../ccs/lib/libc.a lrwxrwxrwx 1 root system17 Mar 30 2001 /usr/lib/libc.a - ../ccs/lib/libc.a Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --with-prefix=/usr/local/mysql --with-mysqld-ldflags=-all-static --disable-shared '--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared 'CC=cc -pthread' 'CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' 'CXXFLAGS=-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' 'CXX=cxx -pthread' Perl: This is perl, version 5.005_03 built for alpha-dec_osf - 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
Stored Procedures
dear all, Does Mysql support stored procedures ? best regards Menrit - 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: Innodb
Ki, please consult the manuals http://www.mysql.com/doc/N/e/News-3.23.x.html and http://www.innodb.com/ibman.html#InnoDB_history. Regards, Heikki - Original Message - From: Ki Mien [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, July 15, 2002 12:48 PM Subject: RE: Innodb Hi Heikki, Thanks for the help. What do you mean by small bugs? Do you mind naming the few that caused the most problems? cheers, Ki Mien -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, July 15, 2002 4:46 PM To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: Innodb Ki, mysqld-max-nt.exe is the same as mysqld-max.exe, except that you can also use named pipes as the client communication channel in mysqld-max-nt.exe. There should be no difference in stability. There are some small bug fixes in 3.23.51 which were not yet in .49. 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: Ki Mien [EMAIL PROTECTED] To: Ki Mien [EMAIL PROTECTED]; [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED]; Cal Evans [EMAIL PROTECTED] Sent: Monday, July 15, 2002 7:40 AM Subject: RE: Innodb Hi Cal, Thanks for the tip. But any reasons as to why 3.23.51-max is preferred to 3.23.49-max-nt? cheers, Ki Mien -Original Message- From: Cal Evans [EMAIL PROTECTED] Sent: Saturday, July 13, 2002 11:34 PM To: Ki Mien [EMAIL PROTECTED], [EMAIL PROTECTED] [EMAIL PROTECTED], Heikki Tuuri [EMAIL PROTECTED] Subject: RE: Innodb 3.23.51-max if you are using Windows. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Ki Mien [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 10:17 AM To: [EMAIL PROTECTED]; Heikki Tuuri Subject: Innodb Hi all, Can anyone out there tell me which version of mysql is more stable or preffered for using Innodb. I am currently using mysql version 3.23.49-max-nt-log. cheers, Ki Mien Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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: Stored Procedures
No. http://www.mysql.com/doc/N/u/Nutshell_4.1_development_release.html * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: root [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 10:06 AM To: [EMAIL PROTECTED] Subject: Stored Procedures dear all, Does Mysql support stored procedures ? best regards Menrit - 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
problems with MYODBC and MYOLDB drivers
HI Lately I tried to connect MSQL with new Autocad 2002. In earlier version of Autocad it was impossible. In fact Autocad 2002 also doesn't support Mysql , but when I try to connect Mysql with Autocad2002 by MYODBC it works.The problem is that always when try to look on the table when I connect to database, table always is opening on read only mode. So I don't now what to do to edit table on AutoCad. On the other side when I try to connect Autocad2002 with Mysql by MYOLEDB, witch (I suppose) is much ,more faster mode then MYODBC Autocad connect to database and I see tables of that database, but when I try to view or edit table I have an announcement reads as follows DATABASE ERROR An error occurred in executing the SQL statement SQL Error Information cursor operationconflict Code 2304 SQLSTATE 09000 I just start using Mysql and Databases but I think that my problems I have are connected with configutration of Mysql or MyODBC or MyOLEDB not Autocad mike - 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
Hi all, has anybody ever seen this? And knows what it means? thx. You have an error in your SQL syntax near 's self-supporting ribbed form was probably not strong enough to render the desig' at line 1 regards Wilbert mysql error / query - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [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 error
[snip] has anybody ever seen this? And knows what it means? thx. You have an error in your SQL syntax near 's self-supporting ribbed form was probably not strong enough to render the desig' at line 1 [/snip] Ye, we have all seen this. It means that there is something wrong with your query. However, we cannot help you to figure that out because you have not provided enough information, such as the entire query or information about the table(s) your are querying. Jay sql, mysql, query * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * A day without sunshine is, like, night. - 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
Yes, it simply means that you have single quotes inside the string you try to save, and you need to escape them. Alain Fontaine Consultant developer VAlain S.A. http://www.valain.lu/ -Message d'origine- De : W. Enserink [mailto:[EMAIL PROTECTED]] Envoyé : lundi 15 juillet 2002 15:07 À : [EMAIL PROTECTED] Objet : mysql error Hi all, has anybody ever seen this? And knows what it means? thx. You have an error in your SQL syntax near 's self-supporting ribbed form was probably not strong enough to render the desig' at line 1 regards Wilbert mysql error / query - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [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 - 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
UDF: Funcion definida por el usuario
Hola listeros :) acabo de construir e instalar una nueva funcion (muy simple) en MySQL, la ucwords() de PHP. (Hacer mayuscula la primera letra de cada palabra de un texto; funciona solamente con datos char y varchar, es decir hasta 255 characteres). Si les interesa (solamante para linux), les explico como se hace. Ha habido alguien en esta lista que me ha propuesto hace tiempo hacer algo parecido, pero en aquel entonces yo no tenia mucho tiempo. Pues bien, companero, si lees este mensaje (disculpen, ya no se quien era) y todavia te interesa crear nuevas funciones, dimelo. Un saludo, Daniel BI - 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: ODBC and TimeStamp
Patrick - I have the following Sql SELECT statement: SELECT FirstName, LastName, ChangeTime FROM Members; If I execute the statement using ADO (uses a ODBC connection), the result set is empty. When the ChangeTime is removed, I get a result set. Todd -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [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: select query
Hi, Sorry but MySQL does not support subqueries yet. Bye and Good Luck! --- Narcis GRATIANU [EMAIL PROTECTED] wrote: When I try this: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) LIMIT 0, 100 I got this error message: You have an error in your SQL syntax near 'SELECT MAX(s2.price) FROM shop s2 WHERE s1.article' at line 3 My shop table contains this data: +-++---+ | article | dealer | price | +-++---+ |0001 | A | 3.45 | |0001 | B | 3.99 | |0002 | A | 10.99 | |0003 | B | 1.45 | |0003 | C | 1.69 | |0003 | D | 1.25 | |0004 | D | 19.95 | +-++---+ and the table was created with this commnad: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); Thank You ! - 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! Autos - Get free new car price quotes http://autos.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
Interesting
I did a select * as such: mysql select * from Sqs; +-+-++ | RecordState | Sqs | SqsKey | +-+-++ | L | unknown | 1 | +-+-++ 1 row in set (0.00 sec) Then, look at the message that delete from gives me '0 rows affected' mysql delete from Sqs; Query OK, 0 rows affected (0.02 sec) However, it did indeed delete one row, since now I get an empty set when I do a select *. mysql select * from Sqs; Empty set (0.00 sec) Anybody knows why, then, delete gave me a '0 rows affected' message instead of saying '1 row affected'? Mysql version 3.23.49 Regards, Bhavin. - 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
Beta training program
Can someone plz send me the posting from mysqlab concerning the beta training , I deleted it accidentally... Thanks 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: Help - query suggestion needed - interesting case
Hi, Since subqueries are not allowed in MySQL, this is what I would do: Create a temporary table with the id's containing multiple dates. Inner join your table with the temporary table. Even if MySQL allowed subqueries, this is what will probably happen behind the scene. Bye and Good Luck! --- Mihail Manolov [EMAIL PROTECTED] wrote: :) Is this some sort of a joke? I am grouping using event_id, which makes your query useless because it will return just the first time row per each event_id. Thanks anyway. I may have to use second query... :-( Mihail - Original Message - From: Bhavin Vyas [EMAIL PROTECTED] To: Mihail Manolov [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 10:51 PM Subject: Re: Help - query suggestion needed - interesting case How about: SELECT event_id, time, count(DISTINCT time) AS Ranges FROM events GROUP BY event_id HAVING Ranges 1 - Original Message - From: Mihail Manolov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 2:58 PM Subject: Help - query suggestion needed - interesting case Greetings, I am stuck with this problem: I have the following table: event_id time 1002000-10-23 1002000-10-23 1012000-10-24 1012000-10-25 I need to know all event_id's that have multiple times + time columns. Is it possible to get that result in just one query? The result should be something like this: event_id time 1012000-10-24 1012000-10-25 I managed to get all event_id's that have multiple times, but I don't know how to get the time column in the same query. Here is my current query: SELECT event_id, count(DISTINCT time) AS Ranges FROM events GROUP BY event_id HAVING Ranges 1 Please help me to find a single query that will return the time column as well. Mihail - 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! Autos - Get free new car price quotes http://autos.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: ODBC and TimeStamp
[snip] I have the following Sql SELECT statement: SELECT FirstName, LastName, ChangeTime FROM Members; If I execute the statement using ADO (uses a ODBC connection), the result set is empty. When the ChangeTime is removed, I get a result set. [/snip] Can you show us a DESCRIBE TABLE for Members? Thanks! Jay sql, mysql, query Auntie Em: Hate you, hate Kansas, taking the dog. Dorothy * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * - 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: Interesting
Hello! Whenever deleting without a WHERE clause, there's always this '0 rows affected' message. I consider that a bug. Greetings Ralf Bhavin Vyas wrote: I did a select * as such: mysql select * from Sqs; +-+-++ | RecordState | Sqs | SqsKey | +-+-++ | L | unknown | 1 | +-+-++ 1 row in set (0.00 sec) Then, look at the message that delete from gives me '0 rows affected' mysql delete from Sqs; Query OK, 0 rows affected (0.02 sec) However, it did indeed delete one row, since now I get an empty set when I do a select *. mysql select * from Sqs; Empty set (0.00 sec) Anybody knows why, then, delete gave me a '0 rows affected' message instead of saying '1 row affected'? Mysql version 3.23.49 Regards, Bhavin. - 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 -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Help - query suggestion needed - interesting case
Hello! Francisco Reinaldo wrote: Hi, Since subqueries are not allowed in MySQL, this is what I would do: Create a temporary table with the id's containing multiple dates. Inner join your table with the temporary table. Even if MySQL allowed subqueries, this is what will probably happen behind the scene. Bye and Good Luck! --- Mihail Manolov [EMAIL PROTECTED] wrote: :) Is this some sort of a joke? I am grouping using event_id, which makes your query useless because it will return just the first time row per each event_id. Thanks anyway. I may have to use second query... :-( Mihail - Original Message - From: Bhavin Vyas [EMAIL PROTECTED] To: Mihail Manolov [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 10:51 PM Subject: Re: Help - query suggestion needed - interesting case How about: SELECT event_id, time, count(DISTINCT time) AS Ranges FROM events GROUP BY event_id HAVING Ranges 1 - Original Message - From: Mihail Manolov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 2:58 PM Subject: Help - query suggestion needed - interesting case Greetings, I am stuck with this problem: I have the following table: event_id time 1002000-10-23 1002000-10-23 1012000-10-24 1012000-10-25 I need to know all event_id's that have multiple times + time columns. Is it possible to get that result in just one query? The result should be something like this: event_id time 1012000-10-24 1012000-10-25 I managed to get all event_id's that have multiple times, but I don't know how to get the time column in the same query. Here is my current query: SELECT event_id, count(DISTINCT time) AS Ranges FROM events GROUP BY event_id HAVING Ranges 1 How about SELECT event_id, time FROM events GROUP BY event_id, time HAVING count(*) 1 ; ??? -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Interesting
That syntax will always report zero rows affected, but it is very fast. If you want to know how many rows were deleted, use something like DELETE * FROM Sqs WHERE 1=1. The latter query will be much slower, as it examines each record. Nick -Original Message- From: Bhavin Vyas [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 9:40 AM To: [EMAIL PROTECTED] Subject: Interesting I did a select * as such: mysql select * from Sqs; +-+-++ | RecordState | Sqs | SqsKey | +-+-++ | L | unknown | 1 | +-+-++ 1 row in set (0.00 sec) Then, look at the message that delete from gives me '0 rows affected' mysql delete from Sqs; Query OK, 0 rows affected (0.02 sec) However, it did indeed delete one row, since now I get an empty set when I do a select *. mysql select * from Sqs; Empty set (0.00 sec) Anybody knows why, then, delete gave me a '0 rows affected' message instead of saying '1 row affected'? Mysql version 3.23.49 Regards, Bhavin. - 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
Replication from InnoDB - MyISAM
I have a 3.23.51 server with InnoDB tables. I want to replicate one of the databases over to some 3.23.49 servers with MyISAM tables. The tables do have some auto-increment columns. I expect that only valid data is going to be written to the binlog, so there shouldn't be an issue with invalid data appearing in my MyISAM tables. Can I expect any problems with this setup? sql, query, stupid filter (perhaps the filter can be expanded to look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). * Philip Molter * Texas.net Internet * http://www.texas.net/ * [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
character occurrence count in MySQL
Hi all, I am looking for a way to count the number of a character occurrences in a string with MySQL. For example, I have this string 1;333;72227;00;19;1;1; and I would like to return the number of ; found in this string. Is this possible? Also, I wanted to return fields that contain EXACTLY 2 ; in the string, but with LIKE or REGEXP, it returned everything that has 2 or more ; occurrences. Does anybody have a way to return the exact match? Thanks, Stephanie - 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
AW: Interesting
This is a known problem, not a bug in mysql. You can get the affected rows by entering this sql statement: delete from Sqs where 10 Greetings, Richard -Ursprüngliche Nachricht- Von: Ralf Narozny [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 15. Juli 2002 16:16 An: Bhavin Vyas Cc: [EMAIL PROTECTED] Betreff: Re: Interesting Hello! Whenever deleting without a WHERE clause, there's always this '0 rows affected' message. I consider that a bug. Greetings Ralf Bhavin Vyas wrote: I did a select * as such: mysql select * from Sqs; +-+-++ | RecordState | Sqs | SqsKey | +-+-++ | L | unknown | 1 | +-+-++ 1 row in set (0.00 sec) Then, look at the message that delete from gives me '0 rows affected' mysql delete from Sqs; Query OK, 0 rows affected (0.02 sec) However, it did indeed delete one row, since now I get an empty set when I do a select *. mysql select * from Sqs; Empty set (0.00 sec) Anybody knows why, then, delete gave me a '0 rows affected' message instead of saying '1 row affected'? Mysql version 3.23.49 Regards, Bhavin. - 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 -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Replication slave repeatedly gets error 1159 from master
i have the same probem since i ugraded my slave from 3.23.50 to 3.23.51 Pierre - Original Message - From: David Harper [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 1:26 PM Subject: Replication slave repeatedly gets error 1159 from master Description: Every 60 seconds, the replication slave gets an error 1159 from the master server. Thirty seconds later, it reconnects to the master. Then the cycle starts all over again. I have master-connect-retry=30 in the config file. Here is an extract of the replication slave's error log: 020715 12:05:33 Error reading packet from server: (server_errno=1159) 020715 12:06:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:06:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 020715 12:06:33 Error reading packet from server: (server_errno=1159) 020715 12:07:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:07:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 In between disconnections, replication *appears* to be occurring correctly, but it is rather disturbing that there seems to be some kind of communication problem between the master and slave servers. This problem did not occur in version 3.23.49, by the way. It appeared when we upgraded to 3.23.51. How-To-Repeat: Set up a master/slave replication pair of servers. Fix: Not known. Submitter-Id: submitter ID Originator: David Harper Organization: Wellcome Trust Sanger Institute, Hinxton, Cambridge CB10 1SA, England MySQL support: none Synopsis: Replication slave repeatedly gets error 1159 from master Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.51 (Official MySQL binary) Environment: System: OSF1 mars V5.1 732 alpha Machine: alpha Some paths: /bin/perl /bin/make /usr/local/bin/gmake /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf5.1/3.0.3/specs Configured with: ../configure Thread model: single gcc version 3.0.3 Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root system17 Mar 30 2001 /lib/libc.a - ../ccs/lib/libc.a lrwxrwxrwx 1 root system17 Mar 30 2001 /usr/lib/libc.a - ../ccs/lib/libc.a Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread- safe-client --enable-local-infile --with-prefix=/usr/local/mysql --with-mysq ld-ldflags=-all-static --disable-shared '--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared 'CC=cc -pthread' 'CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' 'CXXFLAGS=-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' 'CXX=cxx -pthread' Perl: This is perl, version 5.005_03 built for alpha-dec_osf - 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: Interesting
- Original Message - From: Ralf Narozny [EMAIL PROTECTED] To: Bhavin Vyas [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 9:16 AM Subject: Re: Interesting Bhavin Vyas wrote: [snip] Hello! Whenever deleting without a WHERE clause, there's always this '0 rows affected' message. I consider that a bug. Greetings Ralf MySQL optimizes DELETEs with no where clause to a truncate-style operation. Because of this, it does not know the number of rows that were deleted. It does this, because it does it in a much faster way than having to delete row-by-row. (see http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DEL ETE) It is usally good practice to never issue DELETEs without WHERE clauses, as they can be disasterous, and in most cases it is not what you want to do. If you want to have a row count, you can put in a bogus WHERE clause that evaluates to true, e.g: DELETE FROM blah WHERE 1=1 -Mark - 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: Use MySQL and Visual Basic 6.0
I've built a whole program with vb6 and mysql database back end. Never had any problems. I used the mysql odbc for windows to access the database. What are you using? Matthew Scarrow ComIT Solutions Inc. www.comit.ca Phone: 519-442-0100 Fax: 519-442-0429 -Original Message- From: Carlos Rovetto [mailto:[EMAIL PROTECTED]] Sent: Sunday, July 14, 2002 9:18 PM To: [EMAIL PROTECTED] Subject: Use MySQL and Visual Basic 6.0 Hi. I am begining using MySQL with Visual Basic 6.0 in Windows 98 first edition. Some times i to receive this message but i don't know why. Error on rename of '.\video\products.MYI' to '.\video\#sql2-fffa6015-3.MYI' (Errcode: 13) The SQL instruction is: alter table products add (unitprice double(7,2), precio2 double(7,2)) I don't know if use MySQL with VB and Windows is good idea. Thanks Carlos _ MSN. Más Útil cada Día. http://www.msn.es/intmap/ - 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: Interesting
http://www.mysql.com/doc/D/E/DELETE.html In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records. Alain Fontaine Consultant developer VAlain S.A. http://www.valain.lu/ -Message d'origine- De : Bhavin Vyas [mailto:[EMAIL PROTECTED]] Envoyé : lundi 15 juillet 2002 18:40 À : [EMAIL PROTECTED] Objet : Interesting I did a select * as such: mysql select * from Sqs; +-+-++ | RecordState | Sqs | SqsKey | +-+-++ | L | unknown | 1 | +-+-++ 1 row in set (0.00 sec) Then, look at the message that delete from gives me '0 rows affected' mysql delete from Sqs; Query OK, 0 rows affected (0.02 sec) However, it did indeed delete one row, since now I get an empty set when I do a select *. mysql select * from Sqs; Empty set (0.00 sec) Anybody knows why, then, delete gave me a '0 rows affected' message instead of saying '1 row affected'? Mysql version 3.23.49 Regards, Bhavin. - 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
Default created date field
Hello all, I'm new to this list and MySQL, so I hope this question isn't too rudimentary. In SQL Server I was able to create a datetime field that used the default getdate() function so that I woudl get timestamps on all created rows automatically, like: create table mytable ( createddate datetime not null default getdate() ) I'm having trouble doing this in MySQL, but it keeps complaining. All I have to work on is: create table mytable ( createddate datetime not null default now() ) But it doesn't like now(). I can't find anything else to try in the MySQL docs. Any ideas? --- Shawn Poulson [EMAIL PROTECTED] _ 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: Interesting
I'm using version 3.23.53 on Win 2k. The same things happens to me. I've also noticed that if you don't specify a WHERE clause and you have a Auto-incrementing ID field, it is reset to zero and the next record you create starts at 1 again. Surely this is wrong as well? In Other RDBMS's after DELETE * Table. The ID field still remembers the last ID so the next (first) record after performing a delete all, will increment from the last ID. BTW, I've just installed MySQL-Front. It got me up and running in no time! :-) Nick : -Original Message- : Anybody knows why, then, delete gave me a '0 rows affected' : message instead : of saying '1 row affected'? - 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 display the warning message
I have been trying to use the LOAD data command to load the data instead of using the sql insert into table. It works fine but I have got some warnings, how can I see these warnings, is there a log for it, how can I see these warnings, is there a log for it? - 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: character occurrence count in MySQL
Hello! The regexp to match only 2 occurences of ';' would be: regexp '^[^;]*;[^;]*;[^;]*$' to find the number of ';' I only have an idea for something complicated: length(your str) - length(replace(your str,';','')) StephanieTan wrote: Hi all, I am looking for a way to count the number of a character occurrences in a string with MySQL. For example, I have this string 1;333;72227;00;19;1;1; and I would like to return the number of ; found in this string. Is this possible? Also, I wanted to return fields that contain EXACTLY 2 ; in the string, but with LIKE or REGEXP, it returned everything that has 2 or more ; occurrences. Does anybody have a way to return the exact match? Thanks, Stephanie - 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 -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: UDF: Funcion definida por el usuario
On Monday, 15. July 2002 15:28, Daniel BI wrote: Hola listeros :) Hola! please write in english, or use the spanish mailing list ([EMAIL PROTECTED]) Thx Georg - 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: Replication slave repeatedly gets error 1159 from master
Yes, I have a cluster installed with 3.23.49 and I have any problem. Now, I am installing a new cluster with 3.23.51 version and it's appearing these problems. Could anybody help us? Thanks in advance. Santiago. - Original Message - From: Pierre Baridon [EMAIL PROTECTED] To: David Harper [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 15, 2002 4:29 PM Subject: Re: Replication slave repeatedly gets error 1159 from master i have the same probem since i ugraded my slave from 3.23.50 to 3.23.51 Pierre - Original Message - From: David Harper [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 1:26 PM Subject: Replication slave repeatedly gets error 1159 from master Description: Every 60 seconds, the replication slave gets an error 1159 from the master server. Thirty seconds later, it reconnects to the master. Then the cycle starts all over again. I have master-connect-retry=30 in the config file. Here is an extract of the replication slave's error log: 020715 12:05:33 Error reading packet from server: (server_errno=1159) 020715 12:06:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:06:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 020715 12:06:33 Error reading packet from server: (server_errno=1159) 020715 12:07:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:07:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 In between disconnections, replication *appears* to be occurring correctly, but it is rather disturbing that there seems to be some kind of communication problem between the master and slave servers. This problem did not occur in version 3.23.49, by the way. It appeared when we upgraded to 3.23.51. How-To-Repeat: Set up a master/slave replication pair of servers. Fix: Not known. Submitter-Id: submitter ID Originator: David Harper Organization: Wellcome Trust Sanger Institute, Hinxton, Cambridge CB10 1SA, England MySQL support: none Synopsis: Replication slave repeatedly gets error 1159 from master Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.51 (Official MySQL binary) Environment: System: OSF1 mars V5.1 732 alpha Machine: alpha Some paths: /bin/perl /bin/make /usr/local/bin/gmake /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf5.1/3.0.3/specs Configured with: ../configure Thread model: single gcc version 3.0.3 Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root system17 Mar 30 2001 /lib/libc.a - ../ccs/lib/libc.a lrwxrwxrwx 1 root system17 Mar 30 2001 /usr/lib/libc.a - ../ccs/lib/libc.a Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread- safe-client --enable-local-infile --with-prefix=/usr/local/mysql --with-mysq ld-ldflags=-all-static --disable-shared '--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared 'CC=cc -pthread' 'CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' 'CXXFLAGS=-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' 'CXX=cxx -pthread' Perl: This is perl, version 5.005_03 built for alpha-dec_osf - 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
Weighted average
Not wanting to reinvent the wheel here, I wonder if anyone else has ever done a rating system. There'll be two columns: title_id for the title of the movie being rated and then a rating from one to ten. What would be the most efficient SQL statement to find a title's weighted average? Many thanks. -- Ian Evans - 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: question about safe_mysqld
Haany, Sunday, July 14, 2002, 6:16:30 AM, you wrote: HB i am just installing the new mysql 3.23.51 for my HB linux 7.3. HB The problem is after unzipping the tar.gz file and HB running the mysql_install_db (which runs perfectly), HB when i tried to run the safe_mysql , the mysqld is HB ended immediately. HB [1] 3207 HB # Starting mysqld daemon with database from HB /usr/tmp/mysql-3.23.51-pc-linux-gnu-i686/data HB 020713 20:07:38 mysqld ended Look into your host_name.err file in the MySQL data dir. HB and if i just run mysqld: HB bin/mysqld: Fatal error: Can't find messagefile HB '/usr/local/share/mysql/english/errmsg.sys' Set up path to error message file with --language options of mysqld or create a symlink. HB so how i run mysql server? HB when i run the ./configure it says that i dont have to HB configure it. Is there any step that i forget to do? HB please help.. -- 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: mysqld-max-nt 4.0.1-alpha shutdown problem
Roman, Monday, July 15, 2002, 8:07:07 AM, you wrote: RV When mysqld-max-nt.exe is started with options RV --console --standalone RV and after issuing mysqladmin -u root shutdown RV mysqld reports a shutdown, but does not exit. RV mysqld-max don't have this problem. RV OS Windows 2000. I tested it on 4.0.2 and it worked as expected. Seems, this problem was fixed. As to 4.0.1. it can be fixed by running mysqladmin shutdown twice :) -- 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: Stored Procedures
root, Monday, July 15, 2002, 6:05:54 PM, you wrote: r Does Mysql support stored procedures ? No, MySQL doesn't yet support stored procedures. -- 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: CREATE FUNCTION
Sameh, Sunday, July 14, 2002, 3:09:53 PM, you wrote: SA Can anybody help me figuring how to control granting users SA permissions to created functions in MySQL like we do for SELECT, INSERT, SA ...etc or it is not possible and every one logged in to MySQL could SA simply use them like any other native function? SA Regards Take a look at: http://www.mysql.com/doc/C/R/CREATE_FUNCTION.html You must have INSERT and DELETE privileges on the database 'mysql' to create and drop functions. -- 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: Interesting
Bhavin, Monday, July 15, 2002, 7:39:52 PM, you wrote: BV I did a select * as such: BV mysql select * from Sqs; BV +-+-++ BV | RecordState | Sqs | SqsKey | BV +-+-++ BV | L | unknown | 1 | BV +-+-++ BV 1 row in set (0.00 sec) BV Then, look at the message that delete from gives me '0 rows affected' BV mysql delete from Sqs; BV Query OK, 0 rows affected (0.02 sec) BV However, it did indeed delete one row, since now I get an empty set when I BV do a select *. BV mysql select * from Sqs; BV Empty set (0.00 sec) BV Anybody knows why, then, delete gave me a '0 rows affected' message instead BV of saying '1 row affected'? Because you are using DELETE statement without WHERE clause. It's described in the MySQL manual: http://www.mysql.com/doc/D/E/DELETE.html In this case MySQL simply re-creates table. -- 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: Default created date field
Shawn, Monday, July 15, 2002, 5:51:01 PM, you wrote: SP I'm new to this list and MySQL, so I hope this question isn't too SP rudimentary. SP In SQL Server I was able to create a datetime field that used the default SP getdate() function so that I woudl get timestamps on all created rows SP automatically, like: SP create table mytable ( SPcreateddate datetime not null default getdate() SP ) SP I'm having trouble doing this in MySQL, but it keeps complaining. All I SP have to work on is: SP create table mytable ( SPcreateddate datetime not null default now() SP ) SP But it doesn't like now(). I can't find anything else to try in the MySQL SP docs. You can't use functions for the default values. If you want to specify current date and time as a default value, take a look at TIMESTAMP column type: http://www.mysql.com/doc/D/A/DATETIME.html -- 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: Default created date field
On Monday, 15. July 2002 16:51, Shawn Poulson wrote: Hi, I'm having trouble doing this in MySQL, but it keeps complaining. All I have to work on is: create table mytable ( createddate datetime not null default now() ) Just use timestamp instead datetime. 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
RE: Error 150 on 3.23.51
Thanks for the response! Actually, we did notice the on delete null issue, and we even found another. Basically, we have been using PostgreSQL (yikes!), and it just wasn't as 'smart' as MySQL w/ InnoDB. We could actually create foreign keys on tables that didn't even exist yet! But! MySQL to the rescue! It errored out (as you would expect). Thanks for all the help! -James -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 1:11 AM To: [EMAIL PROTECTED] Subject: Re: Error 150 on 3.23.51 James, - Original Message - From: James Kelty [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, July 13, 2002 12:43 AM Subject: Error 150 on 3.23.51 Hello, We have compiled in InnoDB support for MySQL 3.23.51. We would LOVE to take advantage of the transactions and foreign key assignments that it provides. We are having an issue, with the following SQL, however: All of the alter table statements are ending with this error: ERROR 1005: Can't create table './everbase/#sql-955_b.frm' (errno: 150) I found references to a bug that was fixed in .51, but I can't find anything as to what is going on. We think we have the syntax right, but some help would be appreciated. Thanks! -James P.S. I also included my my.cnf file as well and the bottom. create table FailedPaymentContact ( FailedPaymentContactGenKey int AUTO_INCREMENT PRIMARY KEY, ClearingHouseCreditCardFKey int NOT NULL, INDEX iFailedPaymentContact_ClearingHouseCreditCardFKey(ClearingHouseCreditCardFKe y), ContactDate timestamp NOT NULL, ValidContactTypeFKey int NOT NULL, INDEX iFailedPaymentContact_ValidContactTypeFKey(ValidContactTypeFKey), EmployeeFKey varchar(50) NOT NULL, INDEX iFailedPaymentContact_EmployeeFKey(EmployeeFKey) ) TYPE = InnoDB; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N400038 FOREIGN KEY ( ClearingHouseCreditCardFKey ) REFERENCES ClearingHouseCreditCard (ClearingHouseCreditCardGenKey) ON DELETE SET NULL ON UPDATE CASCADE ; you have specified ClearingHouseCreditCardFKey int NOT NULL but want it to be set NULL on the delete of the parent row: ON DELETE SET NULL. At least this will produce error 150. InnoDB does not support ON UPDATE CASCADE. Are there ever updates of referenced keys? Usually referenced keys are primary keys, and primary key updates are rare. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - 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: Replication slave repeatedly gets error 1159 from master
At 04:26 AM 7/15/2002, David Harper wrote: Description: Every 60 seconds, the replication slave gets an error 1159 from the master server. Thirty seconds later, it reconnects to the master. Then the cycle starts all over again. I have master-connect-retry=30 in the config file. Here is an extract of the replication slave's error log: 020715 12:05:33 Error reading packet from server: (server_errno=1159) 020715 12:06:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:06:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 david, i just posted the exact same question last week. apparently it is not a problem. if the message bothers you (it bothered me!) if you put these lines in my.cnf: set-variable = slave_net_timeout=3600 set-variable = net_read_timeout=3600 it will reduce the error msg frequency to once per hour. change 3600 to a higher number if you want it even less frequently. -jsd- - 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: Default created date field
But timestamp type will change on every update. I only want the default value to be the current time/date when the row is inserted and then not change. From: Georg Richter [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED], Shawn Poulson [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Default created date field Date: Mon, 15 Jul 2002 17:56:18 +0200 On Monday, 15. July 2002 16:51, Shawn Poulson wrote: Hi, I'm having trouble doing this in MySQL, but it keeps complaining. All I have to work on is: create table mytable ( createddate datetime not null default now() ) Just use timestamp instead datetime. Regards Georg mysql,query --- Shawn Poulson [EMAIL PROTECTED] _ 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: Replication slave repeatedly gets error 1159 from master
Check the list. This question has been asked before many time. You can set net_read_timeout variable to bigger value to avoid this problem. Still no one knows (or answered) impact of net_read_timeout and wait_timeout. which one takes precedence. Nilesh -Original Message- From: Santiago Alba [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 11:35 AM To: Pierre Baridon; David Harper; [EMAIL PROTECTED] Subject: RE: Replication slave repeatedly gets error 1159 from master Yes, I have a cluster installed with 3.23.49 and I have any problem. Now, I am installing a new cluster with 3.23.51 version and it's appearing these problems. Could anybody help us? Thanks in advance. Santiago. - Original Message - From: Pierre Baridon [EMAIL PROTECTED] To: David Harper [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 15, 2002 4:29 PM Subject: Re: Replication slave repeatedly gets error 1159 from master i have the same probem since i ugraded my slave from 3.23.50 to 3.23.51 Pierre - Original Message - From: David Harper [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 1:26 PM Subject: Replication slave repeatedly gets error 1159 from master Description: Every 60 seconds, the replication slave gets an error 1159 from the master server. Thirty seconds later, it reconnects to the master. Then the cycle starts all over again. I have master-connect-retry=30 in the config file. Here is an extract of the replication slave's error log: 020715 12:05:33 Error reading packet from server: (server_errno=1159) 020715 12:06:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:06:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 020715 12:06:33 Error reading packet from server: (server_errno=1159) 020715 12:07:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:07:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 In between disconnections, replication *appears* to be occurring correctly, but it is rather disturbing that there seems to be some kind of communication problem between the master and slave servers. This problem did not occur in version 3.23.49, by the way. It appeared when we upgraded to 3.23.51. How-To-Repeat: Set up a master/slave replication pair of servers. Fix: Not known. Submitter-Id: submitter ID Originator: David Harper Organization: Wellcome Trust Sanger Institute, Hinxton, Cambridge CB10 1SA, England MySQL support: none Synopsis: Replication slave repeatedly gets error 1159 from master Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.51 (Official MySQL binary) Environment: System: OSF1 mars V5.1 732 alpha Machine: alpha Some paths: /bin/perl /bin/make /usr/local/bin/gmake /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf5.1/3.0.3/specs Configured with: ../configure Thread model: single gcc version 3.0.3 Compilation info: CC='cc -pthread' CFLAGS='-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' CXX='cxx -pthread' CXXFLAGS='-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root system17 Mar 30 2001 /lib/libc.a - ../ccs/lib/libc.a lrwxrwxrwx 1 root system17 Mar 30 2001 /usr/lib/libc.a - ../ccs/lib/libc.a Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread- safe-client --enable-local-infile --with-prefix=/usr/local/mysql --with-mysq ld-ldflags=-all-static --disable-shared '--with-named-thread-libs=-lpthread -lmach -lexc -lc' --disable-shared 'CC=cc -pthread' 'CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all' 'CXXFLAGS=-O4 -ansi_alias -fast -inline speed -speculate all -noexceptions -nortti' 'CXX=cxx -pthread' Perl: This is perl, version 5.005_03 built for alpha-dec_osf - 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
RE: Weighted average
[snip] Not wanting to reinvent the wheel here, I wonder if anyone else has ever done a rating system. There'll be two columns: title_id for the title of the movie being rated and then a rating from one to ten. What would be the most efficient SQL statement to find a title's weighted average? [/snip] What do you mean by weighted? Taking into account the number of votes for each and then weighting them against each other? To do an average you would do the following; SELECT title_id, AVG(rating) FROM movie GROUP BY title_id To do a weighted average as above where number of votes count; SELECT title_id, (AVG(rating) / COUNT(title_id)) AS weighted FROM movie GROUP BY title_id HTH! Jay - 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
Changing MyISAM to InnoDB
For the life of me I can not figure out why I can not create or change my tables from MyISAM to InnoDB. I create the table with InnoDB and MySQL creates the table as MyISAM. I am using MySQL-max-nt. Any ideas how I can change the tables or even create them as InnoDB for that matter? Thanks in advanced. - 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: NATURAL JOIN
On Fri, 2002-07-12 at 22:41, Richard Clarke wrote: So this works as expected, mysql create table test_1 (id int,value char); Query OK, 0 rows affected (0.00 sec) mysql create table test_2 (id int,val char); Query OK, 0 rows affected (0.00 sec) mysql insert into test_1 values (1,a),(2,b),(3,c); ERROR 1054: Unknown column 'a' in 'field list' mysql insert into test_1 values (1,'a'),(2,'b'),(3,'c'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql insert into test_2 values (2,'d'),(3,'e'),(4,'f'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from test_1 NATURAL JOIN test_2; +--+---+--+--+ | id | value | id | val | +--+---+--+--+ |2 | b |2 | d| |3 | c |3 | e| +--+---+--+--+ 2 rows in set (0.00 sec) -- But what is wrong with this: CREATE TABLE `items` ( `item_id` int(11) NOT NULL auto_increment, `centro_id` int(11) NOT NULL default '0', `name` varchar(50) default NULL, `description` varchar(255) default NULL, `url` varchar(255) default NULL, `directory_id` int(11) default NULL, `cost` int(11) default NULL, PRIMARY KEY (`item_id`), KEY `directory_id` (`directory_id`) ) TYPE=MyISAM; CREATE TABLE `shop_directory` ( `directory_id` int(11) NOT NULL auto_increment, `short_name` varchar(32) default NULL, `long_name` varchar(128) default NULL, `description` text, `parent_id` int(11) NOT NULL default '0', PRIMARY KEY (`directory_id`) ) TYPE=MyISAM; INSERT INTO items VALUES (5,58,'Swim','wet','http://www.bri.com/wet',3,50); INSERT INTO items VALUES (4,58,'Beach','beach','http://www.bri.com/beach',3,50); INSERT INTO items VALUES (7,58,'Bed','bed','http://www.bri.com/bed',2,60); INSERT INTO items VALUES (9,58,'sun','sun sun sun','www.bri.com/sun',1,34); INSERT INTO shop_directory VALUES ('','a','aaa','a',0); INSERT INTO shop_directory VALUES ('','b','bbb','b',1); INSERT INTO shop_directory VALUES ('','c','ccc','c',2); mysql select * from items NATURAL JOIN shop_directory; Empty set (0.00 sec) What gives... why isn't it joining on directory_id. Using an inner join with where condition it works fine.. but INNER JOIN should work also.. no? The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables. Richard Hi, See: ..with a USING clause that names all columns that exist in both tables. Your query is giving an empty result because it is using 2 columns that exist in both tables: directory_id and description, and the description value in both tables isn't equal. If you change the column name description in one of your tables it will work -- or just do the select * from items JOIN shop_directory USING (directory_id); Your first example worked because the only column used to join was the id (since you gave different names to the other column). -- Diana Soares - 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: Replication from InnoDB - MyISAM
Philip, - Original Message - From: Philip Molter [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 15, 2002 5:55 PM Subject: Replication from InnoDB - MyISAM I have a 3.23.51 server with InnoDB tables. I want to replicate one of the databases over to some 3.23.49 servers with MyISAM tables. The tables do have some auto-increment columns. I expect that only valid data is going to be written to the binlog, so there shouldn't be an issue with invalid data appearing in my MyISAM tables. Can I expect any problems with this setup? MySQL only writes committed transactions to the binlog, and consequently only replicates committed transactions. Fortunately the MySQL replication puts commands SET INSERT_ID = ... to the binlog to replicate inserts to tables with auto-increment columns. Thus small differences in auto-increment algorithms in different tables types do not cause problems in replication. Thus replication InnoDB table - MyISAM table should always work without problems. sql, query, stupid filter (perhaps the filter can be expanded to look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] Best regards, Heikki Innobase Oy - 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
null last_insert_id and replace ? !
Hello, I ve got mysql 4.0.1 I don't understand what happen. I use to make replace other than insert in order to avoid error in php scripts. I have a table with on primary key : CREATE TABLE `nlconfig` ( `id` tinyint(4) NOT NULL auto_increment, `nomnews` varchar(255) NOT NULL default '', `hebergeur` varchar(255) NOT NULL default '', `fromc` varchar(255) NOT NULL default '[EMAIL PROTECTED]', `fromonline` varchar(255) NOT NULL default 'contact', `limitconf` tinyint(2) NOT NULL default '7', `host` varchar(255) NOT NULL default 'localhost', `user` varchar(255) NOT NULL default 'root', `passwd` varchar(255) NOT NULL default '', `db` varchar(255) NOT NULL default 'newsfr', `tablenews` varchar(255) NOT NULL default 'news', `tabletemp` varchar(255) NOT NULL default 'temp', `tablelog` varchar(255) NOT NULL default 'log', `tableconfig` varchar(255) NOT NULL default 'nlconfig', `admin_pass` varchar(255) NOT NULL default 'pikatchu', `limitlog` tinyint(2) NOT NULL default '10', `url` varchar(255) NOT NULL default '', `pathtopmn` varchar(255) NOT NULL default 'http://www.test.fr/fr/', `langfile` varchar(255) NOT NULL default 'lang-french.php', `welcome_subj` varchar(255) NOT NULL default 'bienvenue', `welcome_msg` text NOT NULL, `sub_msg` text NOT NULL, `pied` tinytext NOT NULL, `validation` tinyint(1) NOT NULL default '1', `description` text, `newstemplate` varchar(100) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; When inserting with mysqlfront windows client : replace into nlconfig values (last_insert_id(),'abc', 'nexen', 'a@', 'a', '', 'localhost', 'root' , '', 'newsfr', 'news', 'temp', 'log', 'nlconfig', 'lucas', '', '', 'http://www.test.fr/fr/', 'lang-french.php', '','','','', '1','','' ); 1st on row affected 2nd 2rows. I get no error. But from a php 4.06 script I get : errno 1062: erromsg : Duplicata du champ '127' pour la clef 1 That means : duplicate value for key 1. That the same error i get with NULL in id with the windows client ?! Could you explain me what happen. I only want to insert a new row when values are different from what is on the table, and to replace the row that there is when there is the same id or when all the data in query is the same ! Is it for that, that replace is for ? Thanks. ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.comm - 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
sql replication, fault tolerance, sql-relay
Have any of you guys come across this, http://www.firstworks.com/sqlrelay.html, as a way of managing fault tolerance from clients accessing mysql databases? Richard - 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
sql: create duplicate record
Hi I want to write sql query to duplicate a row. It has a PRI key called ID. The MySQL database looks like IDNameAddress 345 Aman42, sb 346 347... I want to duplicate the ID=345. Any suggestions ? Thanks in advance. Aman _ Send and receive Hotmail on your mobile device: http://mobile.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: Help with Insert.. Select From... Query
On Sun, 2002-07-14 at 03:59, karl wrote: Hello, I need help. I am trying to insert into TABLEA records from TABLEB where the records do not exist in TABLEA (the primary key in TABLEA works fine). I am trying to limit the result set each time (as an example to 10 records from TABLEB). The first time I perform the query, I get the number of records limited (10), but when I perform the same query again - HOPING TO GET 10 NEW RECORDS - I get none. I'm left with a total of 10 records in TABLEA. I know what is happening, I am looking up the exact same records (using same where clause) each time in TABLEB, and because the query is limited (to 10 for example), no new records are retrieved for TABLEA. But this is precisely what I need to do, and that is fill TABLEA 10 records at a time from TABLEB. There are certain limitations, as in I can't update TABLEB, TABLEB must be left alone and can only be queried from. I thought a complex left join would do the trick, but it seems that you are not able to perform a query of this sort when doing an insert into... select from.. I've included this code, along with the error produced. If you have any ideas, please shoot them my way! Thanks, Karl insert into rep (repid,submitid,lastactv,status) select 88,submit_idx2.submitid,now(),1 from submit_idx2 left join rep on submit_idx2.submitid = rep.submitid where rep.submitid is null and TO_DAYS(NOW()) - TO_DAYS(submitdate) = 1 order by submit_idx2.submitid asc limit 10; The above SQL errors. Here is that error message: C:\mysql\My Queriesc:\mysql\bin\mysql earch 0insertrep.txt ERROR 1066 at line 5: Not unique table/alias: 'rep' Hi, You can't use the table you are updating in the select clause: http://www.mysql.com/doc/I/N/INSERT_SELECT.html * The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting I don't know if i understood you, but if you can only insert 10 records per time, can't you use: insert into rep (repid,submitid,lastactv,status) select 88,submit_idx2.submitid,now(),1 from submit_idx2 order by submit_idx2.submitid asc limit $var,10; where $var will be: * 0 for the first time you run the query, * 10 for the second time, * 20 for the third -- Diana Soares - 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: Help with Insert.. Select From... Query
The problem with this limit, is that the search will occur from many different sources, each of these will not be privy to the last limit set. -Original Message- From: Diana Soares [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 10:52 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Help with Insert.. Select From... Query On Sun, 2002-07-14 at 03:59, karl wrote: Hello, I need help. I am trying to insert into TABLEA records from TABLEB where the records do not exist in TABLEA (the primary key in TABLEA works fine). I am trying to limit the result set each time (as an example to 10 records from TABLEB). The first time I perform the query, I get the number of records limited (10), but when I perform the same query again - HOPING TO GET 10 NEW RECORDS - I get none. I'm left with a total of 10 records in TABLEA. I know what is happening, I am looking up the exact same records (using same where clause) each time in TABLEB, and because the query is limited (to 10 for example), no new records are retrieved for TABLEA. But this is precisely what I need to do, and that is fill TABLEA 10 records at a time from TABLEB. There are certain limitations, as in I can't update TABLEB, TABLEB must be left alone and can only be queried from. I thought a complex left join would do the trick, but it seems that you are not able to perform a query of this sort when doing an insert into... select from.. I've included this code, along with the error produced. If you have any ideas, please shoot them my way! Thanks, Karl insert into rep (repid,submitid,lastactv,status) select 88,submit_idx2.submitid,now(),1 from submit_idx2 left join rep on submit_idx2.submitid = rep.submitid where rep.submitid is null and TO_DAYS(NOW()) - TO_DAYS(submitdate) = 1 order by submit_idx2.submitid asc limit 10; The above SQL errors. Here is that error message: C:\mysql\My Queriesc:\mysql\bin\mysql earch 0insertrep.txt ERROR 1066 at line 5: Not unique table/alias: 'rep' Hi, You can't use the table you are updating in the select clause: http://www.mysql.com/doc/I/N/INSERT_SELECT.html * The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting I don't know if i understood you, but if you can only insert 10 records per time, can't you use: insert into rep (repid,submitid,lastactv,status) select 88,submit_idx2.submitid,now(),1 from submit_idx2 order by submit_idx2.submitid asc limit $var,10; where $var will be: * 0 for the first time you run the query, * 10 for the second time, * 20 for the third -- Diana Soares - 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
Mysqlhotcopy error
I am looking into using the mysqlhotcopy.pl script to back-up my databases. When I try to run it, I get the following error: The getpwuid function is unimplemented at C:\mysql\scripts\mysqlhotcopy.pl line 79. I'm not familiar enough with perl to even begin debugging the script myself.. Does anybody recognize this symptom and know whether I'm missing a perl module or something easy like that? Tab - 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: ODBC and TimeStamp
Jay - Here is the table structure. If I include the MEM_Code field, the ODBC SELECT throws and exception, but I can understand that since CHAR is usually a one character field. The MEM_Change_Date field is another story. When it is included, the result is empty. Strange?!? Todd # # Table structure for table 'MEMBERS' # CREATE TABLE MEMBERS ( MEM_NUM int(11) NOT NULL default '0', MEM_LINK int(11) default NULL, MEM_NAME varchar(70) default NULL, MEM_FIRSTNAME varchar(30) default NULL, MEM_LASTNAME varchar(30) default NULL, MEM_SPOUSE varchar(30) default NULL, MEM_CHILDREN varchar(70) default NULL, MEM_ADDRESS1 varchar(30) default NULL, MEM_CITY1 varchar(30) default NULL, MEM_STATE1 varchar(30) default NULL, MEM_ZIP1 varchar(10) default NULL, MEM_PHONE1 varchar(14) default NULL, MEM_ADDRESS2 varchar(30) default NULL, MEM_CITY2 varchar(30) default NULL, MEM_STATE2 varchar(30) default NULL, MEM_ZIP2 varchar(10) default NULL, MEM_PHONE2 varchar(14) default NULL, MEM_CODE char(2) default NULL, problem MEM_CLASS int(11) default NULL, MEM_BOATS varchar(70) default NULL, MEM_INTERNET varchar(50) default NULL, MEM_PW varchar(15) default NULL, MEM_CHANGE_DATE timestamp(14) NOT NULL, problem MEM_UP_FNAME varchar(30) default NULL, MEM_UP_LNAME varchar(30) default NULL, MEM_PW_CHG char(1) default 'N', PRIMARY KEY (MEM_NUM), KEY IDX_MEM_FNAME (MEM_FIRSTNAME), KEY IDX_MEM_LNAME (MEM_LASTNAME), KEY IDX_MEM_NUM (MEM_NUM) ) TYPE=MyISAM; Jay Blanchard wrote: [snip] I have the following Sql SELECT statement: SELECT FirstName, LastName, ChangeTime FROM Members; If I execute the statement using ADO (uses a ODBC connection), the result set is empty. When the ChangeTime is removed, I get a result set. [/snip] Can you show us a DESCRIBE TABLE for Members? Thanks! Jay sql, mysql, query Auntie Em: Hate you, hate Kansas, taking the dog. Dorothy * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [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
Calculating weeks
Trying to count weeks! I am doing a personal accounting system in php/mysql. I have a report section that groups and calculates expenses into running totals, so that I can see total amount spent in each category. I would like to add a break down to this that will show me the average weekly amount. The calculation is easy enough, but I can not figure out how to get the number of weeks being calculated. Any ideas would be greatly appreciated. Right now I am putting in the number of weeks in manually. Paul W. Reilly Product Management/Web Developer Demosphere International, Inc. 703-536-1600 ext. 31 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.375 / Virus Database: 210 - Release Date: 7/10/2002 - 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: Innodb
Hi, Thanks for all the help. Appreciate it. cheers, Ki Mien -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, July 15, 2002 8:24 PM To: Ki Mien [EMAIL PROTECTED], [EMAIL PROTECTED] [EMAIL PROTECTED] Subject:Re: Innodb Ki, please consult the manuals http://www.mysql.com/doc/N/e/News-3.23.x.html and http://www.innodb.com/ibman.html#InnoDB_history. Regards, Heikki - Original Message - From: Ki Mien [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, July 15, 2002 12:48 PM Subject: RE: Innodb Hi Heikki, Thanks for the help. What do you mean by small bugs? Do you mind naming the few that caused the most problems? cheers, Ki Mien -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] Sent: Monday, July 15, 2002 4:46 PM To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: Innodb Ki, mysqld-max-nt.exe is the same as mysqld-max.exe, except that you can also use named pipes as the client communication channel in mysqld-max-nt.exe. There should be no difference in stability. There are some small bug fixes in 3.23.51 which were not yet in .49. 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: Ki Mien [EMAIL PROTECTED] To: Ki Mien [EMAIL PROTECTED]; [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED]; Cal Evans [EMAIL PROTECTED] Sent: Monday, July 15, 2002 7:40 AM Subject: RE: Innodb Hi Cal, Thanks for the tip. But any reasons as to why 3.23.51-max is preferred to 3.23.49-max-nt? cheers, Ki Mien -Original Message- From: Cal Evans [EMAIL PROTECTED] Sent: Saturday, July 13, 2002 11:34 PM To: Ki Mien [EMAIL PROTECTED], [EMAIL PROTECTED] [EMAIL PROTECTED], Heikki Tuuri [EMAIL PROTECTED] Subject: RE: Innodb 3.23.51-max if you are using Windows. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Ki Mien [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 10:17 AM To: [EMAIL PROTECTED]; Heikki Tuuri Subject: Innodb Hi all, Can anyone out there tell me which version of mysql is more stable or preffered for using Innodb. I am currently using mysql version 3.23.49-max-nt-log. cheers, Ki Mien Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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 Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... Take part in the Penning on Squares Chinese Essay Writing Contest. Submit your masterpieces or vote for your favourite works, and win attractive prizes. Be there or be SQUARE! http://sea.litcontest.lycosasia.com cOntact @ Lycos http://contact.lycosasia.com = 10MB for email and filestore + lots of other goodies... - 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:
RE: Replication from InnoDB - MyISAM
How does transaction works in this scenario?. Is rollbacked transaction written into bin-log files?. Nilesh -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 1:30 PM To: Philip Molter; [EMAIL PROTECTED] Subject: Re: Replication from InnoDB - MyISAM Philip, - Original Message - From: Philip Molter [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, July 15, 2002 5:55 PM Subject: Replication from InnoDB - MyISAM I have a 3.23.51 server with InnoDB tables. I want to replicate one of the databases over to some 3.23.49 servers with MyISAM tables. The tables do have some auto-increment columns. I expect that only valid data is going to be written to the binlog, so there shouldn't be an issue with invalid data appearing in my MyISAM tables. Can I expect any problems with this setup? MySQL only writes committed transactions to the binlog, and consequently only replicates committed transactions. Fortunately the MySQL replication puts commands SET INSERT_ID = ... to the binlog to replicate inserts to tables with auto-increment columns. Thus small differences in auto-increment algorithms in different tables types do not cause problems in replication. Thus replication InnoDB table - MyISAM table should always work without problems. sql, query, stupid filter (perhaps the filter can be expanded to look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] Best regards, Heikki Innobase Oy - 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: Replication from InnoDB - MyISAM
On Mon, Jul 15, 2002 at 02:40:14PM -0400, Nilesh Shah wrote: : How does transaction works in this scenario?. Is rollbacked transaction : written into bin-log files?. To quote Heikki from below: MySQL only writes committed transactions to the binlog, and consequently only replicates committed transactions. : - Original Message - : From: Philip Molter [EMAIL PROTECTED] : Newsgroups: mailing.database.mysql : Sent: Monday, July 15, 2002 5:55 PM : Subject: Replication from InnoDB - MyISAM : : : I have a 3.23.51 server with InnoDB tables. I want to replicate : one of the databases over to some 3.23.49 servers with MyISAM tables. : The tables do have some auto-increment columns. I expect that only : valid data is going to be written to the binlog, so there shouldn't : be an issue with invalid data appearing in my MyISAM tables. Can : I expect any problems with this setup? : : MySQL only writes committed transactions to the binlog, and consequently : only replicates committed transactions. : : Fortunately the MySQL replication puts commands : : SET INSERT_ID = ... : : to the binlog to replicate inserts to tables with auto-increment : columns. : Thus small differences in auto-increment algorithms in different tables : types do not cause problems in replication. : : Thus replication InnoDB table - MyISAM table should always work without : problems. : : sql, query, stupid filter (perhaps the filter can be expanded to : look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). : : * Philip Molter : * Texas.net Internet : * http://www.texas.net/ : * [EMAIL PROTECTED] : : Best regards, : : Heikki : Innobase Oy * Philip Molter * Texas.net Internet * http://www.texas.net/ * [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: Changing MyISAM to InnoDB
Alternatives for creating InnoDB tables: 1) When you start mysql, you can pass a parameter to set default-table-type=InnoDB; Then, when you create tables, InnoDB type table is the default type, or 2) In order to create InnoDB type table, you need to create the table in the following way: CREATE TABLE `my_table` (`field1` TINYINT (3) UNSIGNED DEFAULT '0') TYPE = InnoDB; To change between table types: ALTER TABLE my_table TYPE = target_table_type; Hope this help. Best regards, Michael -Original Message- From: Otoniel Cantu` [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 10:18 AM To: [EMAIL PROTECTED] Subject: Changing MyISAM to InnoDB For the life of me I can not figure out why I can not create or change my tables from MyISAM to InnoDB. I create the table with InnoDB and MySQL creates the table as MyISAM. I am using MySQL-max-nt. Any ideas how I can change the tables or even create them as InnoDB for that matter? Thanks in advanced. - 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
MyISAM v. InnoDB
When dealing with a considerable number of records (10s of thousands) in a particular table, which is better to use? Currently we are using MyISAM and the queries on those tables are kind of slow. I've set the most frequently used (in a query) columns as keys and that isn't speeding it up any. I'm wondering if switching to InnoDB might solve my problem? Chris - 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: Replication from InnoDB - MyISAM
I am sorry. I should have paid more attention to Heikki email. Nilesh -Original Message- From: Philip Molter [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 2:43 PM To: Nilesh Shah Cc: [EMAIL PROTECTED] Subject: Re: Replication from InnoDB - MyISAM On Mon, Jul 15, 2002 at 02:40:14PM -0400, Nilesh Shah wrote: : How does transaction works in this scenario?. Is rollbacked transaction : written into bin-log files?. To quote Heikki from below: MySQL only writes committed transactions to the binlog, and consequently only replicates committed transactions. : - Original Message - : From: Philip Molter [EMAIL PROTECTED] : Newsgroups: mailing.database.mysql : Sent: Monday, July 15, 2002 5:55 PM : Subject: Replication from InnoDB - MyISAM : : : I have a 3.23.51 server with InnoDB tables. I want to replicate : one of the databases over to some 3.23.49 servers with MyISAM tables. : The tables do have some auto-increment columns. I expect that only : valid data is going to be written to the binlog, so there shouldn't : be an issue with invalid data appearing in my MyISAM tables. Can : I expect any problems with this setup? : : MySQL only writes committed transactions to the binlog, and consequently : only replicates committed transactions. : : Fortunately the MySQL replication puts commands : : SET INSERT_ID = ... : : to the binlog to replicate inserts to tables with auto-increment : columns. : Thus small differences in auto-increment algorithms in different tables : types do not cause problems in replication. : : Thus replication InnoDB table - MyISAM table should always work without : problems. : : sql, query, stupid filter (perhaps the filter can be expanded to : look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). : : * Philip Molter : * Texas.net Internet : * http://www.texas.net/ : * [EMAIL PROTECTED] : : Best regards, : : Heikki : Innobase Oy * Philip Molter * Texas.net Internet * http://www.texas.net/ * [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
InnoDB tables max number of keys
Is there an upper limit on the number of indexes in InnoDB tables in MySQL? I can't find anything about it in their documentation at www.innodb.com I am designing a database where one table has around 70 keys so far. yes, it's normal, and all of those keys are commonly searched on. thanks sean peters Senior Programmer, WireData Inc. [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
MySQL FreeBSD LinuxThreads
Hello, I have noticed that MySQL server almost hangs up the system while processing a big table (perl script using fetchrow). I didn't experienced this before - may be it's because of LinuxThreads? MySQL launches 38 running processes, but shows only 10 mysql query threads (they are all sleeping except 2 or 3). Thanking you in advance, Oleg Borodkin - 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
Problem using JDBC to obtain metadata
Hi all, I tried to use mm.mysql (2.0.14) to access the tables in mysql (3.23.51). I can't retrieve metadata like tables' comment through the following code: // get connection conn=ds.getConnection(); // get metadata dbmd=conn.getMetaData(); //get resultset for all tables metadata rs = dbmd.getTables(null,null,%,null); //print out the info through a jsp while (rs.next()){ out.println(rs.getString(TABLE_NAME) + - + rs.getString(REMARKS)); } RESULTS: tablename_1 - tablename_2 - tablename_3 - tablename_4 - Would someone kindly show me if there is anything I did wrong in the code or show me the right way to obtain tables' comment through JDBC? Greatly appreciated. Best regards, Michael --- Michael Tam - NFI Database Developer Natural Resources Canada [EMAIL PROTECTED] Pacific Forestry Center Phone: (250) 383-8074 506 West Burnside Road Fax: (250) 363-0775 Victoria, BC V8Z 1M5 - 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
Presentation
Hi, anybody have any presentation showing about mysql. For example topics: History, Phisical structure, Logic structure, etc. I need to make a presentation about mysql. If somebody could help me. I would appreciate Alexander sql, query ___ Yahoo! Encontros O lugar certo para encontrar a sua alma gêmea. http://br.encontros.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
how to retrieve the 2 last char of a string stored in db
how to retrieve the 2 last char of a string stored in db ? : SELECT LENGTH(db) as len , SUBSTRING(db from len-2 ) from nlconfig; NOT WORK .! In php by example, there is a substr with a negative pos that give char at end ! no in mysql ! ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.comm - 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
mysqlgui
I've compiled fltk and fl_editor successfully. But I can't find edfile.o to compile mysqlgui. - 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: Fwd: MySQL 4.0.2 my.cnf
Hi! Thank you for your test case, thanks to which a bug was fixed when --bind-address option is used. Fix will be implemented in next MySQL versions, but this is a patch that fixes it: = mysqld.cc 1.321 vs edited = *** /tmp/mysqld.cc-1.321-14677 Mon Jul 1 11:38:44 2002 --- edited/mysqld.ccMon Jul 15 23:03:40 2002 *** *** 2871,2877 Tells the master that updates to the given database should not be logged tothe binary log, 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {bind-address, OPT_BIND_ADDRESS, Ip address to bind to, !(gptr*) my_bind_addr, (gptr*) my_bind_addr, 0, GET_ULONG, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {bootstrap, OPT_BOOTSTRAP, Used by mysql installation scripts, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, --- 2871,2877 Tells the master that updates to the given database should not be logged tothe binary log, 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {bind-address, OPT_BIND_ADDRESS, Ip address to bind to, !(gptr*) my_bind_addr, (gptr*) my_bind_addr, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {bootstrap, OPT_BOOTSTRAP, Used by mysql installation scripts, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, -- 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
RE: Weighted average
[snip] What do you mean by weighted? Taking into account the number of votes for each and then weighting them against each other? [/snip] Yes, essentially each individual movie will show its raw average (people think this movie is a 7/10) while an overall top rated list will take into affect that 10,000 people voted on Road to Perdition while one person voted for Freddie got Fingered and gave it a 10. Obviously, Freddie's not the most popular film even though its average is 10. I created a dummy table for this and used your select statement: mysql SELECT titleid, (AVG(rating) / COUNT(titleid)) AS weighted - FROM movieratings - GROUP BY titleid; +-+--+ | titleid | weighted | +-+--+ | 1 | 0.288000 | | 9 | 0.072748 | | 19 | 1.25 | | 21 | 1.80 | | 31 | 8.00 | | 199 | 1.00 | I think I put my slow brain in today...should I be using an order by? Here's the same data showing the number of votes: +-+---+--+ | titleid | votes | weighted | +-+---+--+ | 1 |25 | 0.288000 | | 9 |99 | 0.072748 | | 19 | 8 | 1.25 | | 21 | 5 | 1.80 | | 31 | 1 | 8.00 | | 199 |10 | 1.00 | +-+---+--+ - 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: Weighted average
[snip] I created a dummy table for this and used your select statement: mysql SELECT titleid, (AVG(rating) / COUNT(titleid)) AS weighted - FROM movieratings - GROUP BY titleid; +-+--+ | titleid | weighted | +-+--+ | 1 | 0.288000 | | 9 | 0.072748 | | 19 | 1.25 | | 21 | 1.80 | | 31 | 8.00 | | 199 | 1.00 | I think I put my slow brain in today...should I be using an order by? Here's the same data showing the number of votes: +-+---+--+ | titleid | votes | weighted | +-+---+--+ | 1 |25 | 0.288000 | | 9 |99 | 0.072748 | | 19 | 8 | 1.25 | | 21 | 5 | 1.80 | | 31 | 1 | 8.00 | | 199 |10 | 1.00 | +-+---+--+ [/snip] Yes, you should use an order by, ORDER BY weighted HTH Jay sql, mysql, query Dont worry, it only seems kinky the first time. * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * - 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: MyISAM v. InnoDB
switching to InnoDB won't help. Take a look at optimizing your queries. Are you using LIKE? have you looked at the output of EXPLAIN? =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Chris Boget [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 1:53 PM To: [EMAIL PROTECTED] Subject: MyISAM v. InnoDB When dealing with a considerable number of records (10s of thousands) in a particular table, which is better to use? Currently we are using MyISAM and the queries on those tables are kind of slow. I've set the most frequently used (in a query) columns as keys and that isn't speeding it up any. I'm wondering if switching to InnoDB might solve my problem? Chris - 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
processors
we are considering moving to a 4-way system as the load average on our 2-way system is at 2. Does anyone have recommendations? Is the larger 1MB cache on some xeon chips worth the extra $$$? I'm thinking of 2 raid1 arrays, one for the os and logs, the other for the db. The data is relatively small, but gets much in the way of select statements. sql - 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: MyISAM v. InnoDB
On Mon, Jul 15, 2002 at 01:52:46PM -0500, Chris Boget wrote: When dealing with a considerable number of records (10s of thousands) in a particular table, which is better to use? Currently we are using MyISAM and the queries on those tables are kind of slow. Really? That's not a lot of data. Can you provide examples? I've set the most frequently used (in a query) columns as keys and that isn't speeding it up any. I'm wondering if switching to InnoDB might solve my problem? Probably won't help much at all. We need to find the root of the problem (why the queries are slow). Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 46 days, processed 1,002,132,916 queries (247/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: MyISAM v. InnoDB
When dealing with a considerable number of records (10s of thousands) in a particular table, which is better to use? Currently we are using MyISAM and the queries on those tables are kind of slow. Really? That's not a lot of data. Can you provide examples? Of the data? Or of the queries? I've set the most frequently used (in a query) columns as keys and that isn't speeding it up any. I'm wondering if switching to InnoDB might solve my problem? Probably won't help much at all. We need to find the root of the problem (why the queries are slow). Should I post some of my queries here? Those would be the root of the problem, yes? Chris - 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: MyISAM v. InnoDB
When dealing with a considerable number of records (10s of thousands) in a particular table, which is better to use? Currently we are using MyISAM and the queries on those tables are kind of slow. Really? That's not a lot of data. Can you provide examples? Of the data? Or of the queries? I've set the most frequently used (in a query) columns as keys and that isn't speeding it up any. I'm wondering if switching to InnoDB might solve my problem? Probably won't help much at all. We need to find the root of the problem (why the queries are slow). Should I post some of my queries here? Those would be the root of the problem, yes? Chris MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23.51 problems
Your /tmp directory is only accessible by root? What distribution are you using? /tmp is normally world readable/writeable. I would think you'd have problems with other software, too, if this wasn't setup correctly. --jeff - Original Message - From: Rob Lambden [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 3:57 AM Subject: 3.23.51 problems I have been using mysql for a while and I am now building two new servers to provide web/database services. I have had a number of problems with the mysql installation. BINARY DISTRIBUTION I have had very mixed success with the binary distribution. It assumes /tmp/mysql.sock for the socket but by default /tmp is only accessible by root. I can change the socket for the server and the client, but then I cannot for example install the Perl DBD modules as they always attempt to use /tmp/mysql.sock Changing the rights to /tmp does not always fix the problem - sometimes the mysqld processes do not start (I get nothing logged) and sometimes the system reboots. I have found that after the system has rebooted itself mysql seems to come up correctly if it is using /tmp/mysql.sock I decided to try the source distribution SOURCE DISTRIBUITON When I run the ./configure script I get the message: configure: error: cannot find sources in . or .. I get the same message with all of the options I have tried passing. As it seems I cannot compile the software I am left with the apparently unstable binary as my only option. Where should I go from here ? - 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: ODBC and TimeStamp
I believe the timestamp field needs to be nullable, but you shouldn't have any issue selecting a char. Can you run the query from mysql interface? If you can, than I would suspect that ADO is the culprit, try using straight ODBC instead. I hope this helps... Pat... - Original Message - From: Todd Cary [EMAIL PROTECTED] To: Jay Blanchard [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 2:15 PM Subject: Re: ODBC and TimeStamp Jay - Here is the table structure. If I include the MEM_Code field, the ODBC SELECT throws and exception, but I can understand that since CHAR is usually a one character field. The MEM_Change_Date field is another story. When it is included, the result is empty. Strange?!? Todd # # Table structure for table 'MEMBERS' # CREATE TABLE MEMBERS ( MEM_NUM int(11) NOT NULL default '0', MEM_LINK int(11) default NULL, MEM_NAME varchar(70) default NULL, MEM_FIRSTNAME varchar(30) default NULL, MEM_LASTNAME varchar(30) default NULL, MEM_SPOUSE varchar(30) default NULL, MEM_CHILDREN varchar(70) default NULL, MEM_ADDRESS1 varchar(30) default NULL, MEM_CITY1 varchar(30) default NULL, MEM_STATE1 varchar(30) default NULL, MEM_ZIP1 varchar(10) default NULL, MEM_PHONE1 varchar(14) default NULL, MEM_ADDRESS2 varchar(30) default NULL, MEM_CITY2 varchar(30) default NULL, MEM_STATE2 varchar(30) default NULL, MEM_ZIP2 varchar(10) default NULL, MEM_PHONE2 varchar(14) default NULL, MEM_CODE char(2) default NULL, problem MEM_CLASS int(11) default NULL, MEM_BOATS varchar(70) default NULL, MEM_INTERNET varchar(50) default NULL, MEM_PW varchar(15) default NULL, MEM_CHANGE_DATE timestamp(14) NOT NULL, problem MEM_UP_FNAME varchar(30) default NULL, MEM_UP_LNAME varchar(30) default NULL, MEM_PW_CHG char(1) default 'N', PRIMARY KEY (MEM_NUM), KEY IDX_MEM_FNAME (MEM_FIRSTNAME), KEY IDX_MEM_LNAME (MEM_LASTNAME), KEY IDX_MEM_NUM (MEM_NUM) ) TYPE=MyISAM; Jay Blanchard wrote: [snip] I have the following Sql SELECT statement: SELECT FirstName, LastName, ChangeTime FROM Members; If I execute the statement using ADO (uses a ODBC connection), the result set is empty. When the ChangeTime is removed, I get a result set. [/snip] Can you show us a DESCRIBE TABLE for Members? Thanks! Jay sql, mysql, query Auntie Em: Hate you, hate Kansas, taking the dog. -Dorothy * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [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 - 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: Problem using JDBC to obtain metadata
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tam, Michael wrote: | Hi all, | | I tried to use mm.mysql (2.0.14) to access the tables in mysql | (3.23.51). I can't retrieve metadata like tables' comment through the | following code: | | // get connection | conn=ds.getConnection(); | | // get metadata | dbmd=conn.getMetaData(); | | //get resultset for all tables metadata | rs = dbmd.getTables(null,null,%,null); | | //print out the info through a jsp | while (rs.next()){ | out.println(rs.getString(TABLE_NAME) + - + | rs.getString(REMARKS)); | } | | RESULTS: | | tablename_1 - | tablename_2 - | tablename_3 - | tablename_4 - | | | Would someone kindly show me if there is anything I did wrong in the code | or show me the right way to obtain tables' comment through JDBC? Greatly | appreciated. | | Best regards, | Michael | - | --- | Michael Tam - NFI Database Developer Natural | Resources Canada | [EMAIL PROTECTED] Pacific Forestry | Center | Phone: (250) 383-8074 506 West Burnside | Road | Fax: (250) 363-0775 Victoria, BC | V8Z 1M5 | | | | | - | 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 | JDBC doesn't retrieve the remarks, so you can't get to them. I'll add it into my features-request list, or if you'd like it to be tracked, add it to feature-requests at the sourceforge site (http://sourceforge.net/projects/mmmysql/) -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6-2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9MzsSlyjUJM+7nP4RAq/VAKCMK2o0FF7ajiVZ/s9MlQe4MYVdHwCfZsJt jIFjiF+N5BHEIBDqIwfFqco= =BI8w -END PGP SIGNATURE- - 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: Calculating weeks
On Monday, 15. July 2002 20:28, Paul W. Reilly wrote: Hello Paul, maybe http://www.mysql.com/doc/D/a/Date_and_time_functions.html could answer your question (Function week() ) Regards Georg Trying to count weeks! I am doing a personal accounting system in php/mysql. I have a report section that groups and calculates expenses into running totals, so that I can see total amount spent in each category. I would like to add a break down to this that will show me the average weekly amount. The calculation is easy enough, but I can not figure out how to get the number of weeks being calculated. Any ideas would be greatly appreciated. Right now I am putting in the number of weeks in manually. - 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
Setting a user to only see certain databases?
Hello, Here's what I'd like to do... * Create a new user in mysql. * Let them login and create a new database. * I want this database to be only viewable by the user that created it and also the mysql administrator. * I would like that user to be able to create users as well but they can only see databases created by the original user. Is this at all possible? How do ISPs offer MySQL databases? Surely they don't have an instance of mysql per web site hosted!? Maybe they've done it through their own interfaces to mysql but can't that be hacked? Thanks for any help with this, Nick - 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: Problem using JDBC to obtain metadata
Hi Mark, Thank you for your speedy response. I'd appreciate if you can put this feature the request list. Many thanks. By the way, is there a way I can access this metadata through sql ?? Best regards, Michael -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 2:14 PM To: Tam, Michael Cc: '[EMAIL PROTECTED]' Subject: Re: Problem using JDBC to obtain metadata -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tam, Michael wrote: | Hi all, | | I tried to use mm.mysql (2.0.14) to access the tables in mysql | (3.23.51). I can't retrieve metadata like tables' comment through the | following code: | | // get connection | conn=ds.getConnection(); | | // get metadata | dbmd=conn.getMetaData(); | | //get resultset for all tables metadata | rs = dbmd.getTables(null,null,%,null); | | //print out the info through a jsp | while (rs.next()){ | out.println(rs.getString(TABLE_NAME) + - + | rs.getString(REMARKS)); | } | | RESULTS: | | tablename_1 - | tablename_2 - | tablename_3 - | tablename_4 - | | | Would someone kindly show me if there is anything I did wrong in the code | or show me the right way to obtain tables' comment through JDBC? Greatly | appreciated. | | Best regards, | Michael | - | --- | Michael Tam - NFI Database Developer Natural | Resources Canada | [EMAIL PROTECTED] Pacific Forestry | Center | Phone: (250) 383-8074 506 West Burnside | Road | Fax: (250) 363-0775 Victoria, BC | V8Z 1M5 | | | | | - | 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 | JDBC doesn't retrieve the remarks, so you can't get to them. I'll add it into my features-request list, or if you'd like it to be tracked, add it to feature-requests at the sourceforge site (http://sourceforge.net/projects/mmmysql/) -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6-2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9MzsSlyjUJM+7nP4RAq/VAKCMK2o0FF7ajiVZ/s9MlQe4MYVdHwCfZsJt jIFjiF+N5BHEIBDqIwfFqco= =BI8w -END PGP SIGNATURE- - 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: auto_increment
On Sun, Jul 14, 2002 at 07:21:59PM +0200, Raymond Hamaker wrote: Hi all, When i use MS access 2000 with odbc to fill my MySQL db(running on linux) auto_increment seems not to work . Does not seem to work doesn't help us help you. What did you try to do? What did you expect to happen? What did happen? Is that normal? Or am i missing something? TIA Raymond -- 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