No Response from Server
Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Response from Server
Can you give us more insight into your database layout? Are you using indexes? How many clients are accessing it? What kind of queries? Are those queries written to take advantage of the indexes? j- k- On Monday 12 July 2004 10:28 pm, s.ahmad said something like: Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Response from Server
thankyou for your kind attention. there are 50+ Reservation stations in the country. Where multiple operators are sitting and reserving seats. Indexes are being used in the DB. as i am related to hosting company, i havent seen if they are taking advantage of indexes. regards, s.ahmad On Mon, 12 Jul 2004 22:36:10 -0800, Joshua J. Kugler [EMAIL PROTECTED] wrote: Can you give us more insight into your database layout? Are you using indexes? How many clients are accessing it? What kind of queries? Are those queries written to take advantage of the indexes? j- k- On Monday 12 July 2004 10:28 pm, s.ahmad said something like: Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- s.ahmad [EMAIL PROTECTED] www.shakeelahmad.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running mysql-administrator and mysqlcc graphical clients
Hi list, i've installed mysql-administrator and mysqlcc graphical clients downloaded from mysql.com. As i read in doc from the site, i unzipped the downloaded files in /usr/local. It seems to be ok, but i cannot execute the apps. [EMAIL PROTECTED] bin]$ ls mysql-administrator mysql-administrator-bin [EMAIL PROTECTED] bin]$ mysql-administrator bash: mysql-administrator: command not found [EMAIL PROTECTED] bin]$ The system shows mysql-administrator file in green and it looks like the bash shell cannot execute it. I have the same problems when trying to run mysqlcc. Any help? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Response from Server
50 clients accessing the database should not overload a system like that. I would recommend posting your database layout. That might help in diagnosing the problem. j- k- On Monday 12 July 2004 11:08 pm, s.ahmad said something like: thankyou for your kind attention. there are 50+ Reservation stations in the country. Where multiple operators are sitting and reserving seats. Indexes are being used in the DB. as i am related to hosting company, i havent seen if they are taking advantage of indexes. regards, s.ahmad On Mon, 12 Jul 2004 22:36:10 -0800, Joshua J. Kugler [EMAIL PROTECTED] wrote: Can you give us more insight into your database layout? Are you using indexes? How many clients are accessing it? What kind of queries? Are those queries written to take advantage of the indexes? j- k- On Monday 12 July 2004 10:28 pm, s.ahmad said something like: Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- s.ahmad [EMAIL PROTECTED] www.shakeelahmad.net -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running mysql-administrator and mysqlcc graphical clients
On Tuesday 13 July 2004 08:12, A. Lanza might have typed: Hi list, i've installed mysql-administrator and mysqlcc graphical clients downloaded from mysql.com. As i read in doc from the site, i unzipped the downloaded files in /usr/local. It seems to be ok, but i cannot execute the apps. [EMAIL PROTECTED] bin]$ ls mysql-administrator mysql-administrator-bin [EMAIL PROTECTED] bin]$ mysql-administrator bash: mysql-administrator: command not found You don't have . in your path most likely. ./mysql-administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Generation Of Passwords
think this will work, have not tried it: char(int) returns a character rand() returns a random number between 0 and 1 (with loads of decimal places) round(int) rounds a number to a whole number. so a random password generator could be SELECT char(round(rand()*100), round(rand()*100), round(rand()*100), round(rand()*100), round(rand()*100)) maybe stick some round(rand()*100)) at the end to add some numbers to your password. the char() will return some special characters ;'£$ etc... so you will h ave to find what range they are in and adjust the rand()*100 part maybe if they are all in the low range you can do (rand()*100+10) I bet someone says there is a function like randompassword() now :| -- Dave - Original Message - From: Michael Mason To: 'MySQL Mailing List' Sent: Tuesday, July 13, 2004 1:25 AM Subject: Auto Generation Of Passwords I was wondering if it's possible to create a field in a MySQL Databse that automatically creates unique field values for use as a password for example Michael Mason Business Support Services Arras® People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt.
LOAD DATA LOCAL INFILE doesn't show in binlog
Hi, I'm running MySQL 3.23.52 with binary logging enabled. All commands seems to be written to the log except for LOAD DATA LOCAL INFILE. Is there a setting that needs to be tweaked for this to work? Thanks for your help, David _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
Joshua, - Original Message - From: Joshua Chamas [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, July 12, 2004 11:33 PM Subject: RE: Concurrency Question Quoting Marvin Wright [EMAIL PROTECTED]: Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ? For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? I would not think this would make a difference, in fact you should test this, as it may be that in fact bundling 24,000 at a time is slower than 12,000 at a time. I know that things can slow down when doing too much in one transaction in Oracle for example, and InnoDB/MySQL might be similar. big transactions do not slow down InnoDB. But, of course, if the application is multithreaded and the clients compete for row locks, then having big transactions will increase the risk of deadlocks and lock waits. Regards, Josh please address these general MySQL questions to [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA LOCAL INFILE doesn't show in binlog
Not sure about the 3.x series, but in 4.x, if you build your own mysql, you need to explicitly enable that feature via a configure option. I think binary builds all have it enabled though. Issac - Original Message - From: David Brännlund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 11:14 AM Subject: LOAD DATA LOCAL INFILE doesn't show in binlog Hi, I'm running MySQL 3.23.52 with binary logging enabled. All commands seems to be written to the log except for LOAD DATA LOCAL INFILE. Is there a setting that needs to be tweaked for this to work? Thanks for your help, David _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot do join on varchar
Hi, I found out that when joining varchar column, you have to trim down the left column in order for MYSQL to match the row, is it a known issue? Or join only works fine in number column? CREATE TABLE `tb_test1` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; CREATE TABLE `tb_test2` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; INSERT INTO tb_test1(id, name) VALUES('abcde', 'NAME1') INSERT INTO tb_test2(id, name) VALUES('abcde', 'NAME2') /* The following display zero row */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id /* The following will display correct result */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id Redhat 9, with MYSQL 4.1.1, using UTF8 as default charset. Any idea?? Thank you. Francis Mak
SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension
I am using MySQL 4.0.18 on Windows. I want to give my users the possibility to change their MySQL password after they log in. The command SET PASSWORD = PASSWORD('newpassword') works fine as long as the user has been created with no specific host extension in the mysql database (corresponding to e.g. [EMAIL PROTECTED]). Now if the user is defined in the mysql database with a host extension ([EMAIL PROTECTED], for example), the SET PASSWORD command fails with the message Can't find any matching row in the user table. For security reasons, I need the users to be defined with a host extension. Is this a bug, or is there a way around this? (Please do not suggest to use SET PASSWORD FOR [EMAIL PROTECTED] = PASSWORD ('newpassword') as this is only allowed for users that have access to the mysql database!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 performance
Hi! On Jul 12, Hickey,Thom wrote: I've been comparing the performance of 4.1 with the MySQL 3.23.58 that came with our Rocks cluster software. I'm finding that 4.1 is running approximately 1/3 slower (e.g. a 0.075 second query goes to 0.100 seconds) than 3.23.58. Size of buffers, etc. seems to have little effect. The database is fairly large with about 3 gig spread over a half-dozen tables. The largest table has 62 million rows. What kind of queires do you run ? What does EXPLAIN show ? Are all tables ANALYZE'd ? 4.1 can use more complex query transformations and join methods that should bring more performance. Of course if the statistical data are incorrect, the optimizer cannot jugde what is more and what it less, so new features may expose problems that were hidden in the old version. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server fails no mysql.sock
Description: Installed MySQL Binary, previous attempt with 'RPM' failed. From the Binary install everything seems ok, however when I attempt to start the mysqld, and run test to check it, I get a failure message as follows: [EMAIL PROTECTED] mysql-standard-4.0.13-pc-linux-i686 ./bin/mysqladmin version ./bin/mysqladmin: connect to server at 'localhost' failed. error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! There is in fact no socket in the /tmp directory, and I can not find a way to create this socket. How-To-Repeat: I have removed the program and all directories/subdirectories several times and keep coming up with the same problem. Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id:[EMAIL PROTECTED] Originator:root Organization: charles coffee MySQL support: none Synopsis:mysqld server will not run - no mysql socket Severity:serious Priority:medium Category:mysql Class:support Release:mysql-4.0.13-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: IBM Thinkpad T23, Red Hat 8.0, target, libraries (multiple lines) System: Linux TPT23 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 May 18 2003 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x2 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot do join on varchar
Have you tried this with the latest MySQL 4.1.x build? Does it not work in this version? What does the explain plan show? -Original Message- From: Francis Mak To: [EMAIL PROTECTED] Sent: 7/13/04 4:01 AM Subject: Cannot do join on varchar Hi, I found out that when joining varchar column, you have to trim down the left column in order for MYSQL to match the row, is it a known issue? Or join only works fine in number column? CREATE TABLE `tb_test1` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; CREATE TABLE `tb_test2` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; INSERT INTO tb_test1(id, name) VALUES('abcde', 'NAME1') INSERT INTO tb_test2(id, name) VALUES('abcde', 'NAME2') /* The following display zero row */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id /* The following will display correct result */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id Redhat 9, with MYSQL 4.1.1, using UTF8 as default charset. Any idea?? Thank you. Francis Mak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] SQL Maestro 1.5 released
Hello, We are happy to announce the release of SQL Maestro 1.5. The trial version of the software is always available at http://www.sqlmaestro.com/products/download.html. In this version we have implemented a possibility to connect to a remote MySQL server using a secure protocol. The secure connection is established through the Secure SHell (SSH) tunnel, which guarantees a high security for the data transmission between a local PC and remote MySQL server. All the parameters for the SSH connection are stored in database profiles as extra properties, which can be specified during database creation/registration process. In addition to this feature, we have made some small improvements and several minor bug fixes. The full news article can be read at http://www.sqlmaestro.com/news.html?id=14. SQL Maestro is a powerful Win32 GUI solution for MySQL server administration and database development. This product supports all the latest versions of MySQL, including MySQL 4.1/5.0, and all of the most important MySQL features, including stored procedures and functions, InnoDB foreign keys, user-definable functions, transaction-safe tables, BLOB and TEXT field types, MySQL 4.x user privilege extensions and many more. Sincerely yours, SQL Maestro Group http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem getting innodb enabled ...
Sorry, I should have included it originally. These are the only lines referencing innodb that I can find. # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 This is how it looks when I try to enable innodb, however, MySQL won't start with them uncommented, but it runs fine when I comment them out. -- Hugh mailto: [EMAIL PROTECTED] Victor Pendleton wrote: Can you post the exact syntax from your my.cnf file? -Original Message- From: Hugh Taylor To: [EMAIL PROTECTED] Sent: 7/12/04 3:17 PM Subject: Problem getting innodb enabled ... Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension
When that user does a select user() are they receiving the numeric ip address or the dns resolved name? If you set up the privilege with the numeric ip and dns is resolving this the two values will not match. If necessary you can use --skip-name-resolve. -Original Message- From: Stephane Carre To: [EMAIL PROTECTED] Sent: 7/13/04 5:01 AM Subject: SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension I am using MySQL 4.0.18 on Windows. I want to give my users the possibility to change their MySQL password after they log in. The command SET PASSWORD = PASSWORD('newpassword') works fine as long as the user has been created with no specific host extension in the mysql database (corresponding to e.g. [EMAIL PROTECTED]). Now if the user is defined in the mysql database with a host extension ([EMAIL PROTECTED], for example), the SET PASSWORD command fails with the message Can't find any matching row in the user table. For security reasons, I need the users to be defined with a host extension. Is this a bug, or is there a way around this? (Please do not suggest to use SET PASSWORD FOR [EMAIL PROTECTED] = PASSWORD ('newpassword') as this is only allowed for users that have access to the mysql database!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Response from Server
On Tue, 13 Jul 2004, s.ahmad wrote: thankyou for your kind attention. there are 50+ Reservation stations in the country. Where multiple operators are sitting and reserving seats. Indexes are being used in the DB. as i am related to hosting company, i havent seen if they are taking advantage of indexes. It could be a PHP problem rather than a MySQL problem. A PHP script error can cause the web server (apache, IIS, or whatever) to go to 99-100% CPU utilisation on a server that isn't running MySQL. Typical of this is where a PHP script tries to access or serve up a zero length plain text ASCII file - it will find it OK but as it has no bytes in it and, most importantly, no EOF character the script will hang and take the httpd server to 99% or 100% CPU unless it has been written to check for conditions like these. This will happen with just 1 single access to the page. Hope this helps, Andy regards, s.ahmad On Mon, 12 Jul 2004 22:36:10 -0800, Joshua J. Kugler [EMAIL PROTECTED] wrote: Can you give us more insight into your database layout? Are you using indexes? How many clients are accessing it? What kind of queries? Are those queries written to take advantage of the indexes? j- k- On Monday 12 July 2004 10:28 pm, s.ahmad said something like: Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- s.ahmad [EMAIL PROTECTED] www.shakeelahmad.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Implementing full text searching - need recommendations
Hi, you can try to add your headings to the stopword list, but remember: - A word is any sequence of characters consisting of letters, digits, `'', and `_'. Any word that is present in the stopword list or is just too short is ignored. - The stopword list is global and will affect all tables. As for me, in your situation I would prefer another aproach distributing your text to several TEXT columns. Then your headers become column names. Cheers, Thomas Spahni On Mon, 12 Jul 2004, leegold wrote: Please let me show you one (TEXT data-type) item example. You will see Title_[ ... Author[ ...Resp_Org__[... Obviously, I don't want those strings searchable, they are headings, but I do want the content to the right of them indexed and searchable. So looking at it - what should I put in my stop words list...eg. Author[ ? This would be a typical text field indexed by fulltext. I have never used it before - any recomendations appreciated - it's formatted below for readability. I have the actual field item below this formatted item complete with '\r\n'...it's text afterall. Thanks for the help. Title_[ Balloon Capabilities and Futures] Author[ Thomas W. Kelly Resp_Org__[ Air Force Cambridge Research Labs. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 25 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.] Subj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation] Content___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longer duration.] ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf Avail_From[ WFF/BTL Title_[ Balloon Capabilities and Futures]\r\nAuthor[ Thomas W. Kelly\r\nResp_Org__[ Air Force Cambridge Research Labs.\r\nFundingOrg[\r\nDate__[ Dec 1963\r\nReport_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154\r\nReposit_No[ Found in: AD-614 065\r\nContractNo[\r\nDescript__[ Conference Paper, 25 p\r\nNotes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.]\r\nSubj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation]\r\nContent___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longer duration.]\r\nElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf\r\nAvail_From[ WFF/BTL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql Query Issue
I suggest that you add more indexes to your tables. If you run an EXPLAIN on your query, you will see that you are doing WAY too many table scans and that is what is slowing you down. Index the columns in each table that reference the ID values of another table. Then run your EXPLAIN again and you should see a major difference. Use the manual, it has great advice on optimizing queries and full documentation of the EXPLAIN command. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeyabalan MurugesanTo: [EMAIL PROTECTED] Sankarasubramaniacc: n Fax to: [EMAIL PROTECTED]Subject: Sql Query Issue xis.com 07/12/2004 03:55 AM Hi All, I migrated the data from Oracle to MySQL.The following query works fine with Oracle whereas in MySql its hanging. SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, st_caty.notes order by st_caty.name Following are the table structures with Row count in each table. CREATE TABLE segs( s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, c_idINT(12) NULL , textVARCHAR(255) NULL, lookupVARCHAR(255) NULL, in_id INT(12) NULL, prr_d VARCHAR(12) NULL, nxt_d VARCHAR(12) NULL, descn VARCHAR(255) NULL, notes VARCHAR(255) NULL, s_st_id INT(12) NULL, versn FLOAT(10,4) NULL, mesg_type VARCHAR(50) NULL ); Row count 34700 CREATE TABLE caty( c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, nameVARCHAR(255) NULL, notes VARCHAR(255) NULL, version FLOAT(10,4) ); Row count 281 CREATE TABLE st_mbers( st_idINT(12) NULL, c_idINT(12) NULL, versionFLOAT(10,4) NULL, st_mber_id INT(12) NOT NULL ); Row count 1362 CREATE TABLE t_mbers( t_id INT(12) NOT NULL, st_id INT(12) NULL, seq_nbr INT(12) NULL, version FLOAT(10,4) NULL, t_mber_id INT(12) NOT NULL ); Row count 1260 CREATE TABLE p_mbers ( p_id INT(12) NOT NULL , t_id INT(12) NULL , seq_nbr INT(12) NULL, notes TEXT NULL, versionFLOAT(10,4) NULL, p_mber_id INT(12) NOT NULL ); Row Count 2198 CREATE TABLE pr_mbers( pr_idINT(12) NULL, p_idINT(12) NULL, seq_nbr INT(12) NULL, pr_mbr_id INT(12) NOT NULL ); Row Count 294 Help me in solving this hanging issue. I tried the same with both Linux and Windows XP but without any success. I tried with one record in each table mentioned above and its working fine. Kindly guide me in this regard. Thanks in advance. regards msjeyabalan ** CONFIDENTIAL INFORMATION
Into outfile
Is there a way to get the output of a select into outfile statement to have the column headers in it and not just the data? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why this query doesn't group the email addresses?
Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why this query doesn't group the email addresses?
Were the records exactly the same? -Original Message- From: Aaron Wolski To: [EMAIL PROTECTED] Sent: 7/13/04 10:04 AM Subject: Why this query doesn't group the email addresses? Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why this query doesn't group the email addresses?
Yes sir. Exactly! A -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: July 13, 2004 11:15 AM To: 'Aaron Wolski '; '[EMAIL PROTECTED] ' Subject: RE: Why this query doesn't group the email addresses? Were the records exactly the same? -Original Message- From: Aaron Wolski To: [EMAIL PROTECTED] Sent: 7/13/04 10:04 AM Subject: Why this query doesn't group the email addresses? Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension
Right on! Effectively, user() returns the dns resolved name. So obviously this will not match the mysql user table entry, where the ip range is specified. I will try the --skip-name-resolve option, but I have to wait for a scheduled down time of the server. Thanks for the expert advice! At 14:29 13/07/2004, you wrote: When that user does a select user() are they receiving the numeric ip address or the dns resolved name? If you set up the privilege with the numeric ip and dns is resolving this the two values will not match. If necessary you can use --skip-name-resolve. -Original Message- From: Stephane Carre To: [EMAIL PROTECTED] Sent: 7/13/04 5:01 AM Subject: SET PASSWORD = PASSWORD('newpassword') does not work if user has host extension I am using MySQL 4.0.18 on Windows. I want to give my users the possibility to change their MySQL password after they log in. The command SET PASSWORD = PASSWORD('newpassword') works fine as long as the user has been created with no specific host extension in the mysql database (corresponding to e.g. [EMAIL PROTECTED]). Now if the user is defined in the mysql database with a host extension ([EMAIL PROTECTED], for example), the SET PASSWORD command fails with the message Can't find any matching row in the user table. For security reasons, I need the users to be defined with a host extension. Is this a bug, or is there a way around this? (Please do not suggest to use SET PASSWORD FOR [EMAIL PROTECTED] = PASSWORD ('newpassword') as this is only allowed for users that have access to the mysql database!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
Having that many instances on one box is going to be a management nightmare. I can only imagine the recovery scenarios should you have a hardware problem. Perhaps you may want to think about writing your metric data to a local mysql instance then pulling the data from each instance into the depot database with a script that runs every few minutes. Using this pull methodology you don't have to worry about losing metrics if your depot is down but you don't have the burden of up to tweleve databases to manage on one machine (twenty four databases total). Another option would be to write the metrics to a flat file on the web server, then simply serve that file up via HTTP. You then have only one database to manage, you can pull the metrics into it very easily and you still don't have to worry about a down depot server. In your depot you then have a couple options. You could have one master table that contains metrics from all the machines and contains a column for the machine name or you could have a seperate table for each machine, and if you want to examine metrics for all machines you could use a merge table. --- Marc Knoop [EMAIL PROTECTED] wrote: Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
On Tue, Jul 13, 2004 at 11:23:03AM -0400, Marc Knoop wrote: Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? That should be a problem at all. I know of much larger instances (millions of records) doing the same on similar (or less) hardware. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
Jeremy Zawodny writes: Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. That should be a problem at all. I know of much larger instances (millions of records) doing the same on similar (or less) hardware. Jeremy - good to hear. Now that I know this is technically possible, which of the following possible solutions would be the cleanest or most efficient from a management perspective: 1) Use mysql replication to have mirror dbs on the DEPOT server. A job would regularly run on DEPOT to consolidate all data into one db so that an external system can query/report on. 2) Do not use mysql replication and instead have a job on DEPOT regularly pull from each webserver and consolidate all data into one db so that an external system can query/report on. 3) Same as #2, except the web servers would *push* to DEPOT instead of being *pulled* from. As another reader commented, #1 could be difficult to manage because of the number of DBs (N*2). Plus, DEPOT is already a master to all web servers for read only data. #2 and #3 seem to be more appropriate, as long as the jobs are FAST and can be managed. Would Perl be the ideal candidate for this? Since the web servers are remote, performance of DEPOT updates is important - something replication was good at. It's nice to have different solutions to this puzzle. Choosing the most elegant solution is tricky! -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
all upper case records.. Keeping first char upper and rest lower?
Hey guys, I have a column in a table called 'first'. Currently all records are upper case. Is it possible for me to do a select statement that makes all chars after the first char lower case? Example: Current: AARON After: Aaron I think this is possible.. just don't know how to execute the functions together to make it happen. Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SCO Openserver Install
Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld And I have FSU threads installed ... Any help would be appreciated Thanks! Jim Ginn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: all upper case records.. Keeping first char upper and rest lower?
SELECT CONCAT( UPPER( LEFT( first, 1 ) ), LOWER( RIGHT( first, LENGTH( first ) - 1 ) ) ) AS `first` FROM table On Jul 13, 2004, at 12:51 PM, Aaron Wolski wrote: Hey guys, I have a column in a table called 'first'. Currently all records are upper case. Is it possible for me to do a select statement that makes all chars after the first char lower case? Example: Current: AARON After: Aaron I think this is possible.. just don't know how to execute the functions together to make it happen. Thanks! Aaron -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure Limitation
Dear All, On executing a Stored Procedure (in MySQL 5.0.0-alpha) containing multiple Select statements, I get the error SELECT in a stored procedure must have INTO. I found that this is a MyODBC bug. http://bugs.mysql.com/bug.php?id=2273 and http://bugs.mysql.com/bug.php?id=2658 Mark Matthews has suggested to use the client flag 'CLIENT_MULTI_RESULTS' until we get the 'MyODBC 3.53' Release. Can someone help me in using it? I wish to Call the Stored Procedure from my C++ as well as VB code through ADO. Any input would be of immense help. Thanks, Nawal Lodha.
Re: Why this query doesn't group the email addresses?
What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: all upper case records.. Keeping first char upper and rest lower?
Hi there, Thanks for the help! Someone else also gave me a similar query which did exactly as yours did. But with the same minor problem In this table.. I also have columns for last and email. So the columns are first,last,email. The problem with both yours and this other person's query is that is groups all the columns (first,last,email) into one column. What I would like is just the 'first' column. Something tells me this isn't possible? Thanks to you as well for the help. I wouldn't have figured it out for myself that's for sure. Aaron -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: July 13, 2004 1:10 PM To: Aaron Wolski Cc: [EMAIL PROTECTED] Subject: Re: all upper case records.. Keeping first char upper and rest lower? SELECT CONCAT( UPPER( LEFT( first, 1 ) ), LOWER( RIGHT( first, LENGTH( first ) - 1 ) ) ) AS `first` FROM table On Jul 13, 2004, at 12:51 PM, Aaron Wolski wrote: Hey guys, I have a column in a table called 'first'. Currently all records are upper case. Is it possible for me to do a select statement that makes all chars after the first char lower case? Example: Current: AARON After: Aaron I think this is possible.. just don't know how to execute the functions together to make it happen. Thanks! Aaron -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: all upper case records.. Keeping first char upper and rest lower?
It's ulgy, and I'm not sure how efficient it is, but this will do the trick: select concat(left(first,1),substring(lower(first) from 2)) as first; Aaron Wolski wrote: Hey guys, I have a column in a table called 'first'. Currently all records are upper case. Is it possible for me to do a select statement that makes all chars after the first char lower case? Example: Current: AARON After: Aaron I think this is possible.. just don't know how to execute the functions together to make it happen. Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why this query doesn't group the email addresses?
varchar, sir. -Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: July 13, 2004 1:14 PM To: Aaron Wolski Cc: [EMAIL PROTECTED] Subject: Re: Why this query doesn't group the email addresses? What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB : FOR UPDATE who is locking ?
Hi all, I'm using SELECT ... FOR UPDATE on InnoDB Table, i would like to know which CONNECTION_ID() is locking the row selected if it's the case ? To know which CONNECTION_ID() is locking row i've think to implement a table LOCK but if i insert lock info after a FOR UPDATE into it, it releases the lock...I've tried with Multi Level Transactions, but BEGIN TRANSACTION release lock...I've tried whith SAVEPOINT, but it isn't possible to COMMIT TO a SAVEPOINT... Anyone have solution(s) ? Thanks. Best regards.
Small Bug in 4.1.3 beta ?
I have been trying out 4.1.3 beta on XP, and note that in SQLyog, Maestro, and even MYSQL Administrator, the number of records shown is always one more than actual - in InnoDB tables only. Even an empty table still shows as 1. This is different than the count shown in these GUIs with 4.1.1 alpha. SELECT COUNT(*) retrieves the correct number, however. This is not the case for the MyISAM tables, which show the correct numbers at all times. Is this supposed to happen? Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB : FOR UPDATE who is locking ?
Hi all, I'm using SELECT ... FOR UPDATE on InnoDB Table, i would like to know which CONNECTION_ID() is locking the row selected if it's the case ? To know which CONNECTION_ID() is locking row i've think to implement a table LOCK but if i insert lock info after a FOR UPDATE into it, it releases the lock...I've tried with Multi Level Transactions, but BEGIN TRANSACTION release lock...I've tried whith SAVEPOINT, but it isn't possible to COMMIT TO a SAVEPOINT... Anyone have solution(s) ? Thanks. Best regards Jacky
Moving InnoDB database from Linux to Windows
How can I copy the data directory from linux to windows, with the same mysql version (4.0.16) in both OS and innodb tables? I try simple copy all files (iblogs, ibdata, mysql dir, database dir) but I get this error when open a table: ERROR 1016: Can't open file: 'advogado.InnoDB'. (errno: 1) -- Mauricio Bruns - ASV Consultoria [EMAIL PROTECTED] - www.asv.com.br Rua Felipe Schmidt, 31 - Sala 904 88350-075 - Brusque - SC - Brasil tel/fax: (47) 351-3901 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
You are doing an implicit group by of first, last as well as your explicit group by of email. So you could have two records with the same e-mail address generate two records with your group by Justin Time [EMAIL PROTECTED] Justin Credible [EMAIL PROTECTED] --DUPE-- Case differences between the records could also cause dupes. If case differences are causing it then do select lower(first), lower(last), lower(email) ... group by lower(first), lower(last), lower(email) --- Wesley Furgiuele [EMAIL PROTECTED] wrote: What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access hosts wildcard.
Hi, I'm setting up MySQL databases on 'shared server' space I've rented using cPanel's 'MySQL Database' interface. I can add 'access hosts' (it's not limited to localhost) and it says the % wildcard is allowed. Forgive me if this is a trivial or annoying question (I can't find the answer anywhere) but how do I just 'allow any host'? Presumably I use % in some way maybe '%.%.%.%' (an all encompassing IP address with wildcards instead of numbers) or maybe it's just '%'? Thanks, ..matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving InnoDB database from Linux to Windows
Try using mysqldump for the innodb files. -Original Message- From: Mauricio Bruns To: MySQL List Sent: 7/13/04 12:41 PM Subject: Moving InnoDB database from Linux to Windows How can I copy the data directory from linux to windows, with the same mysql version (4.0.16) in both OS and innodb tables? I try simple copy all files (iblogs, ibdata, mysql dir, database dir) but I get this error when open a table: ERROR 1016: Can't open file: 'advogado.InnoDB'. (errno: 1) -- Mauricio Bruns - ASV Consultoria [EMAIL PROTECTED] - www.asv.com.br Rua Felipe Schmidt, 31 - Sala 904 88350-075 - Brusque - SC - Brasil tel/fax: (47) 351-3901 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
Justin: Interesting. So, if GROUP BY t1.email doesn't group simply by t1.email, then how would the query be written to accomplish that? And how is it occurring that Aaron is implicitly grouping by email, first, last instead of just grouping by email? Is it because of the ORDER BY clause? Wes On Jul 13, 2004, at 2:13 PM, Justin Swanhart wrote: You are doing an implicit group by of first, last as well as your explicit group by of email. So you could have two records with the same e-mail address generate two records with your group by Justin Time [EMAIL PROTECTED] Justin Credible [EMAIL PROTECTED] --DUPE-- Case differences between the records could also cause dupes. If case differences are causing it then do select lower(first), lower(last), lower(email) ... group by lower(first), lower(last), lower(email) --- Wesley Furgiuele [EMAIL PROTECTED] wrote: What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem getting innodb enabled ...
After much searching (and trying to read non-English posts), I've stumbled across the solution. I installed the mysql-MAX rpm, changed the my.cnf file and Viola! it works! -- Hugh mailto: [EMAIL PROTECTED] Hugh Taylor wrote: Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table scope
If I have tables within 3 different mysql dbs, is there a way for me to combine all 3 tables into a scope so that I may run a query accessing information from those 3 tables within 3 mysql dbs? In MS Access/MSSQL, it would be called linking, but I couldn't find out whether mysql has that or not. If not, are there any alternatives anyone can suggest? Thanks Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about using mysqldump file
Hello, mysql gurus, I have been trying to transfer some huge databases (20G) to another server using mysqldump. Here is my command mysqldump --add-locks --allow-keywords -q -p db db.dump When I tried 'mysql -p db db.dump' in another server, everything goes all right until at some point it lost the connection to mysql server. My db is partially recovered in the 2nd server then. I am wondering how I can only restore the part of db that is not recovered the first time instead of doing 'mysql -p db db.dump' for the whole db again. I don't want to open the db.dump file and pick the paragraphs containing tables not in the 2nd server yet as the dump file is huge. Is there any other easy way to do it? Hope I am clear. Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table scope
If the db's are on the same server and you have the needed permissions for all of them, just prefix the table names with the db names like so: select dbname.tablename.field1, dbname2.tablename2.field2. and so on. Your FROM will need similar qualifiers. j- k- On Tuesday 13 July 2004 11:28 am, Alex said something like: If I have tables within 3 different mysql dbs, is there a way for me to combine all 3 tables into a scope so that I may run a query accessing information from those 3 tables within 3 mysql dbs? In MS Access/MSSQL, it would be called linking, but I couldn't find out whether mysql has that or not. If not, are there any alternatives anyone can suggest? Thanks Alex -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: question about using mysqldump file
What are the table types being moved? What is the max packet size? -Original Message- From: ginger cheng To: [EMAIL PROTECTED] Sent: 7/13/04 2:42 PM Subject: question about using mysqldump file Hello, mysql gurus, I have been trying to transfer some huge databases (20G) to another server using mysqldump. Here is my command mysqldump --add-locks --allow-keywords -q -p db db.dump When I tried 'mysql -p db db.dump' in another server, everything goes all right until at some point it lost the connection to mysql server. My db is partially recovered in the 2nd server then. I am wondering how I can only restore the part of db that is not recovered the first time instead of doing 'mysql -p db db.dump' for the whole db again. I don't want to open the db.dump file and pick the paragraphs containing tables not in the 2nd server yet as the dump file is huge. Is there any other easy way to do it? Hope I am clear. Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
Boyd Lynn Gerber: I'll give the newer version a try as I was using: /usr/local/mysql-4.0.18-pc-sco3.2v5.0.7-i386 build from your site ... Thanks! Jim On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
On Tue, 2004-07-13 at 11:13, Justin Swanhart wrote: You are doing an implicit group by of first, last as well as your explicit group by of email. So you could have two records with the same e-mail address generate two records with your group by Justin Time [EMAIL PROTECTED] Justin Credible [EMAIL PROTECTED] --DUPE-- Where is the implicit group? The 'order by' shouldn't effect how things are grouped. On MySQL 4.0.17: create table bar ( email varchar(64), first varchar(64), last varchar(64) ); insert into bar values ('[EMAIL PROTECTED]', 'a', 'smith'), ('[EMAIL PROTECTED]', 'a', 'williams'), ('[EMAIL PROTECTED]', 'b', 'webb'), ('[EMAIL PROTECTED]', 'c', 'oconner'); mysql select * from bar; +-+---+--+ | email | first | last | +-+---+--+ | [EMAIL PROTECTED] | a | smith| | [EMAIL PROTECTED] | a | williams | | [EMAIL PROTECTED] | b | webb | | [EMAIL PROTECTED] | c | oconner | +-+---+--+ mysql select first,last,email from bar b group by b.email order by b.first, b.last; +---+---+-+ | first | last | email | +---+---+-+ | a | smith | [EMAIL PROTECTED] | | b | webb | [EMAIL PROTECTED] | +---+---+-+ Case differences between the records could also cause dupes. If case differences are causing it then do select lower(first), lower(last), lower(email) ... group by lower(first), lower(last), lower(email) Case or extra whitespace is a definite possibility. Aaron, try to find at least one occurrence of duplicate email addresses and then post a small (5 row) dataset that exhibits the problem you are having. Garth --- Wesley Furgiuele [EMAIL PROTECTED] wrote: What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
Where is the implicit group? The 'order by' shouldn't effect how things are grouped. On MySQL 4.0.17: Wow. I wasn't aware. Is that expected behavior? Other databases (Oracle) generate an error when you include columns in the select list that aren't in a group by, or they do an implicit group by (postgres) on the columns. I [wrongly] assumed an implicit group by was going on because the query hadn't generated an error. I guess mysql just takes the values from the first record that matches the group expression. That creates confusion when you do: select first,last,email, count(*) from foobar group by email Because the query will report a count of two (given your data) when there really is only one row with that first,last,email combination. Oracle would require you to do: select first,last,email, count(*) from foobar group by first,last,email otherwise you would get an error that first is not a GROUP BY expression. That query would return four rows on your data, each with a count of 1. My apologies, Justin create table bar ( email varchar(64), first varchar(64), last varchar(64) ); insert into bar values ('[EMAIL PROTECTED]', 'a', 'smith'), ('[EMAIL PROTECTED]', 'a', 'williams'), ('[EMAIL PROTECTED]', 'b', 'webb'), ('[EMAIL PROTECTED]', 'c', 'oconner'); mysql select * from bar; +-+---+--+ | email | first | last | +-+---+--+ | [EMAIL PROTECTED] | a | smith| | [EMAIL PROTECTED] | a | williams | | [EMAIL PROTECTED] | b | webb | | [EMAIL PROTECTED] | c | oconner | +-+---+--+ mysql select first,last,email from bar b group by b.email order by b.first, b.last; +---+---+-+ | first | last | email | +---+---+-+ | a | smith | [EMAIL PROTECTED] | | b | webb | [EMAIL PROTECTED] | +---+---+-+ Case differences between the records could also cause dupes. If case differences are causing it then do select lower(first), lower(last), lower(email) ... group by lower(first), lower(last), lower(email) Case or extra whitespace is a definite possibility. Aaron, try to find at least one occurrence of duplicate email addresses and then post a small (5 row) dataset that exhibits the problem you are having. Garth --- Wesley Furgiuele [EMAIL PROTECTED] wrote: What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- . Garth Webb . [EMAIL PROTECTED] . . shoes * 鞋子 * schoenen * 단화 * chaussures * zapatos . Schuhe * παπούτσια * pattini * 靴 * sapatas * ботинки -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to create an Installation Package using VB.NET?
Hi, I am very new to MySQL and using it as the backend of my VB.NET application. Now, I am trying to create an installation package from within VB and don't know what files from MySQL MyODBC 3.51 I need to add to my installation package. Can anyone provide some help in this regards? Also, I will need to know how to start mysql as a service, setup users password, all through the installation package. I would appreciate any help that anyone can provide. Sincerely, Roy.
Re: Access hosts wildcard.
[...] how do I just 'allow any host'? [...] maybe it's just '%'? AFAIK, that's it. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
Boyd Lynn Gerber: Updated to (your) 4.1.3-beta and now get: ./bin/mysqld --skip-grant dynamic linker : ./bin/mysqld : could not open libssl.so.0.9.6m Check in /usr/lib and see: l /usr/lib/libssl* -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0.9.6@ -r--r--r-- 1 bin bin38044 Sep 20 2003 /usr/lib/libsso.a@ Is there a 'trick' (ie. symbolic links) or some other library I'm looking for with 'm' suffix? Thanks! Jim On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
On Tue, 2004-07-13 at 13:51, Justin Swanhart wrote: Where is the implicit group? The 'order by' shouldn't effect how things are grouped. On MySQL 4.0.17: Wow. I wasn't aware. Is that expected behavior? Yup, check out: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html Other databases (Oracle) generate an error when you include columns in the select list that aren't in a group by, or they do an implicit group by (postgres) on the columns. I [wrongly] assumed an implicit group by was going on because the query hadn't generated an error. I guess mysql just takes the values from the first record that matches the group expression. Sorta, but its probably better to assume that its random. That creates confusion when you do: select first,last,email, count(*) from foobar group by email Because the query will report a count of two (given your data) when there really is only one row with that first,last,email combination. But there are 2 rows with that email which, considering 'email' is the only thing group'ed on, makes sense. From the link above, one would only select 'first' and 'last' if they knew they were unique as well and wanted to save on typing. Probably the only way to trust that is to have a unique constraint on those three columns. Oracle would require you to do: select first,last,email, count(*) from foobar group by first,last,email otherwise you would get an error that first is not a GROUP BY expression. Yah, and Oracle actually enforces foreign keys and won't insert guessed default values for 'NOT NULL' fields when you try to insert a null value into them. MySQL keeps you on your toes! Garth That query would return four rows on your data, each with a count of 1. My apologies, Justin create table bar ( email varchar(64), first varchar(64), last varchar(64) ); insert into bar values ('[EMAIL PROTECTED]', 'a', 'smith'), ('[EMAIL PROTECTED]', 'a', 'williams'), ('[EMAIL PROTECTED]', 'b', 'webb'), ('[EMAIL PROTECTED]', 'c', 'oconner'); mysql select * from bar; +-+---+--+ | email | first | last | +-+---+--+ | [EMAIL PROTECTED] | a | smith| | [EMAIL PROTECTED] | a | williams | | [EMAIL PROTECTED] | b | webb | | [EMAIL PROTECTED] | c | oconner | +-+---+--+ mysql select first,last,email from bar b group by b.email order by b.first, b.last; +---+---+-+ | first | last | email | +---+---+-+ | a | smith | [EMAIL PROTECTED] | | b | webb | [EMAIL PROTECTED] | +---+---+-+ Case differences between the records could also cause dupes. If case differences are causing it then do select lower(first), lower(last), lower(email) ... group by lower(first), lower(last), lower(email) Case or extra whitespace is a definite possibility. Aaron, try to find at least one occurrence of duplicate email addresses and then post a small (5 row) dataset that exhibits the problem you are having. Garth --- Wesley Furgiuele [EMAIL PROTECTED] wrote: What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
On Tue, 13 Jul 2004, Jim Ginn wrote: Updated to (your) 4.1.3-beta and now get: ./bin/mysqld --skip-grant dynamic linker : ./bin/mysqld : could not open libssl.so.0.9.6m Check in /usr/lib and see: l /usr/lib/libssl* -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0.9.6@ -r--r--r-- 1 bin bin38044 Sep 20 2003 /usr/lib/libsso.a@ I am currently using openssl-0.9.6m. You will find a complete static and binary of what I am using in openssl-0.9.6m-osr5.tar.gz ftp://ftp.zenez.com/pub/zenez/prgms/openssl-0.9.6m-osr5.tar.gz I usually install it in /usr/local/ssl. Most people install it in /usr/lib. You can also put links from /usr/local/ssl/lib to /usr/lib/ On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
Boyd Lynn gerber: Much better but it complains about max_allowed_packet? It is set to the default 1M ... Thanks! Jim Installing all prepared tables Fill help tables ERROR: 1153 Got a packet bigger than 'max_allowed_packet' bytes 040713 17:43:07 Aborting 040713 17:43:07 ./bin/mysqld: Shutdown complete On Tue, 13 Jul 2004, Jim Ginn wrote: Updated to (your) 4.1.3-beta and now get: ./bin/mysqld --skip-grant dynamic linker : ./bin/mysqld : could not open libssl.so.0.9.6m Check in /usr/lib and see: l /usr/lib/libssl* -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0.9.6@ -r--r--r-- 1 bin bin38044 Sep 20 2003 /usr/lib/libsso.a@ I am currently using openssl-0.9.6m. You will find a complete static and binary of what I am using in openssl-0.9.6m-osr5.tar.gz ftp://ftp.zenez.com/pub/zenez/prgms/openssl-0.9.6m-osr5.tar.gz I usually install it in /usr/local/ssl. Most people install it in /usr/lib. You can also put links from /usr/local/ssl/lib to /usr/lib/ On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB : FOR UPDATE who is locking ?
Hi all, I'm using SELECT ... FOR UPDATE on InnoDB Table, i would like to know which CONNECTION_ID() is locking the row selected if it's the case ? To know which CONNECTION_ID() is locking row i've think to implement a table LOCK but if i insert lock info after a FOR UPDATE into it, it releases the lock...I've tried with Multi Level Transactions, but BEGIN TRANSACTION release lock...I've tried whith SAVEPOINT, but it isn't possible to COMMIT TO a SAVEPOINT... Anyone have solution(s) ? Thanks. Best regards Jacky
Error 1146
I am trying to do an INSERT ... SELECT statement. When using MySQLcc everything is fine. However when I try to execute the command using PHP I get error 1146: Table 'caldata.resource' doesn't exist. Following is my query statement. I am trying to combine a bunch of tables together and I don't think the MERGE method will work very well for me. The table 'job' already exists with all of the necessary fields. There is a primary key setup in 'job' called 'id'. I appreciate any help. INSERT INTO job (CustomerName, ModelNumber, SerialNumber, CertificateNumber, AverageFluidViscosity, JobNumber, SystemID) SELECT CustomerName, ModelNumber, SerialNumber, CertificateNumber, AverageFluidViscosity, JobNumber, SystemID FROM 50041_job Jeff Blasius mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Re: Access hosts wildcard.
Matthew Stanfield wrote: I'm setting up MySQL databases on 'shared server' space I've rented using cPanel's 'MySQL Database' interface. I can add 'access hosts' (it's not limited to localhost) and it says the % wildcard is allowed. Forgive me if this is a trivial or annoying question (I can't find the answer anywhere) but how do I just 'allow any host'? Presumably I use % in some way maybe '%.%.%.%' (an all encompassing IP address with wildcards instead of numbers) or maybe it's just '%'? Frederic Wenzel wrote: [...] how do I just 'allow any host'? [...] maybe it's just '%'? AFAIK, that's it. Thanks Frederic. Does anyone know any different as this doesn't seem to be working? --But maybe the problem lies elsewhere. :( ..matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux 2GB Memory Limit
Hi, I'm now running redhat AS 3.0 with kernel version 2.4 and have 8GB of RAM. If I set my innodb_buffer_pool to 2048M, it just will not start, I get this error. 040713 22:10:24 mysqld started 040713 22:10:24 Warning: Asked for 196608 thread stack, but got 126976 InnoDB: Fatal error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 37066436 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=1044480 max_used_connections=0 max_connections=800 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1643385 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x83f4800 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe368, stack_bottom=0x69726575, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x712d776f is invalid pointer thd-thread_id=925983092 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040713 22:10:24 mysqld ended My ulimit is ulimit -a core file size(blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) 4 max memory size (kbytes, -m) unlimited open files(-n) 1024 pipe size (512 bytes, -p) 8 stack size(kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes(-u) 7168 virtual memory(kbytes, -v) unlimited What do I have to do to the OS so that it will let me have an innodb buffer pool of 2GB ?? Regards, Marvin. -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: 09 July 2004 20:15 To: Marvin Wright Cc: [EMAIL PROTECTED] Subject: Re: Linux 2GB Memory Limit On Fri, 9 Jul 2004 15:46:37 +0100 , Marvin Wright [EMAIL PROTECTED] wrote: Hi, Current Platform RH version is 7.3 IBM Blade Server - 2 x Intel(R) Xeon(TM) CPU 3.20GHz 32 GB SCSI 4 GB Ram This is the platform we are moving to in a week or so RH Enterprise AS 2.1 or 3.0 4 x Intel(R) Xeon(TM) MP CPU 2.70GHz 128 GB SCSI Raid 16 GB Ram So with the new platform I'll be able to have a much bigger InnoDB buffer Note it will still be limited to something that is definitely no bigger than 4 gigs, and may be smaller... I haven't had any luck with ~2 gig innodb buffer sizes even on systems with 3 or 3.5 gigs of addess space available per process, but I never looked into that too deeply so it may work fine with the right setup. This is probably a bit late, but I would have definitely recommended running 64-bit opterons in your configuration since then you could have a larger innodb buffer. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit:
Re: SCO Openserver Install
On Tue, 13 Jul 2004, Jim Ginn wrote: Much better but it complains about max_allowed_packet? It is set to the default 1M ... I have my stune set as follows. NODEosr507 EVDEVS 96 EVQUEUES88 NSPTTYS 64 NUMSP 256 NSTREAM 4352 NHINODE 1024 GPGSLO 1000 GPGSHI 3000 NSTRPAGES 4000 NAIOPROC50 NAIOREQ 400 NAIOBUF 400 NAIOHBUF100 NAIOREQPP 400 NAIOLOCKTBL 50 MAX_PROC1 MAXUMEM 1048576 NCALL 256 NCLIST 512 NSTREVENT 14848 NUMTIM 1888 NUMTRW 1888 SDSKOUT 64 SEMMAP 8192 SEMMNI 8192 SEMMNS 8192 SEMMSL 150 SEMMNU 150 SHMMAX 2147483647 TTHOG 4096 SECLUID 0 SECSTOPIO 1 SECCLEARID 1 MAXUP 4096 NOFILES 4096 SHMMNI 200 Installing all prepared tables Fill help tables ERROR: 1153 Got a packet bigger than 'max_allowed_packet' bytes 040713 17:43:07 Aborting 040713 17:43:07 ./bin/mysqld: Shutdown complete On Tue, 13 Jul 2004, Jim Ginn wrote: Updated to (your) 4.1.3-beta and now get: ./bin/mysqld --skip-grant dynamic linker : ./bin/mysqld : could not open libssl.so.0.9.6m Check in /usr/lib and see: l /usr/lib/libssl* -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0.9.6@ -r--r--r-- 1 bin bin38044 Sep 20 2003 /usr/lib/libsso.a@ I am currently using openssl-0.9.6m. You will find a complete static and binary of what I am using in openssl-0.9.6m-osr5.tar.gz ftp://ftp.zenez.com/pub/zenez/prgms/openssl-0.9.6m-osr5.tar.gz I usually install it in /usr/local/ssl. Most people install it in /usr/lib. You can also put links from /usr/local/ssl/lib to /usr/lib/ On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL on MAC
Astrum Et Securis We've just bought a new Power Mac G5 Macintosh computer for the office and I'll be needing to do some database engineering work using it. I've never worked with mySQL on MAC, so I really have no experience with what happens on a MAC.. Anyone here who works with mySQL on a MAC? Any helpful insights? Thanks. RSJ
Re: Stored Procedure Limitation
Nawal Lodha wrote: Dear All, On executing a Stored Procedure (in MySQL 5.0.0-alpha) containing multiple Select statements, I get the error "SELECT in a stored procedure must have INTO". I found that this is a MyODBC bug. http://bugs.mysql.com/bug.php?id=2273 and http://bugs.mysql.com/bug.php?id=2658 Mark Matthews has suggested to use the client flag 'CLIENT_MULTI_RESULTS' until we get the 'MyODBC 3.53' Release. Can someone help me in using it? I wish to Call the Stored Procedure from my C++ as well as VB code through ADO. Any input would be of immense help. Thanks, Nawal Lodha. Thanks for noticing my bug :) My interpretation of the response given to both bugs is that the current driver ( MyODBC-3.51.x ) will not work AT ALL with MySQL stored procedures that return a result set. The 'CLIENT_MULTI_RESULTS' is only available in client libraries that are compiled against MySQL-4.1 or newer, and MyODBC-3.51.x is not. According to the comments at the bottom of my bug: MyODBC 3.53 is near completion but no date has been set yet. ie you will be waiting for quite some time. -- sig Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL on MAC
I gather you have used MySQL on other platforms? Won't be much different. OS X is BSD Unix-based, so it will be very similar (if not identical) to using MySQL on a Unix box. Have fun. Or were you asking about compiling, etc? j- k- On Tuesday 13 July 2004 03:21 pm, MySQL Junkie said something like: We've just bought a new Power Mac G5 Macintosh computer for the office and I'll be needing to do some database engineering work using it. I've never worked with mySQL on MAC, so I really have no experience with what happens on a MAC.. Anyone here who works with mySQL on a MAC? Any helpful insights? -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to create an Installation Package using VB.NET?
Don't know much about creating a package in VB.NET but why not just the windows installer from the web page instead, its always worked piece of cake on the windows boxes if worked with. Can you call it from and then add in some programming in VB to add the users? -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 4:54 PM To: [EMAIL PROTECTED] Subject: How to create an Installation Package using VB.NET? Hi, I am very new to MySQL and using it as the backend of my VB.NET application. Now, I am trying to create an installation package from within VB and don't know what files from MySQL MyODBC 3.51 I need to add to my installation package. Can anyone provide some help in this regards? Also, I will need to know how to start mysql as a service, setup users password, all through the installation package. I would appreciate any help that anyone can provide. Sincerely, Roy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL on MAC
Astrum Et Securis Josh, Thanks for writing in... Yes in fact I have worked with MySQL on other platforms. And am glad to hear it wont' be a problem or much different. Thanks a lot. Dominor, RSJ -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 7:34 PM To: [EMAIL PROTECTED] Subject: Re: mySQL on MAC I gather you have used MySQL on other platforms? Won't be much different. OS X is BSD Unix-based, so it will be very similar (if not identical) to using MySQL on a Unix box. Have fun. Or were you asking about compiling, etc? j- k- On Tuesday 13 July 2004 03:21 pm, MySQL Junkie said something like: We've just bought a new Power Mac G5 Macintosh computer for the office and I'll be needing to do some database engineering work using it. I've never worked with mySQL on MAC, so I really have no experience with what happens on a MAC.. Anyone here who works with mySQL on a MAC? Any helpful insights? -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle 2 MySQL updates/replication?
Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Thanks for any ideas, -Carl Edwards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'x' on blob field when retrieving records
I have a query like this select hex(blob_field) as myField from table When the blob field is empty ('') it returns 'x'. i'm using ADO with Visual Basic and MyODBC 3.51. MySQL Version is 4.0.18 and SO is Win XP Pro (also happens on Windows 2K) Felix Beltran.
Re: Oracle 2 MySQL updates/replication?
On Tue, Jul 13, 2004 at 06:11:22PM -0700, Carl Edwards wrote: Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Golden Gate Software makes a product that does this. I'd have a look at what they offer. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key
Hi all, this is my first post, and I whould like to know if there is a way to create Foreign Key´s between Parent and Child table, I realy dont know if is it possible to do it and if OK, how will be a simple SQL code of it. I use PowerDesigner 10.0 and Erwin 4.0 Tks for all Everton Sanga
Re: Oracle 2 MySQL updates/replication?
An option would be a log reader program that uses Oracle log miner to only show commited transactions from the redo logs. You could then replay the SQL that is being executed on the oracle box on the mysql server as long as the tables are defined the same. 9i has an enhanced log miner that can be used to read 8i redo logs as well, so you might want to use the newer 9i client if you go this way. Updates to the oracle database could be processed the same way using the mysql binary log as long as no mysql extensions were used like inserting multiple rows with a single insert statement. --- Jeremy Zawodny [EMAIL PROTECTED] wrote: On Tue, Jul 13, 2004 at 06:11:22PM -0700, Carl Edwards wrote: Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Golden Gate Software makes a product that does this. I'd have a look at what they offer. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1 performance
Sergei, Thom.. I am interested in seeing this thread followed through. As developers at my work have experienced similar performance issues between 3.23.x and 4. Our database is also of similar size and a full optimize has been run. Regards, Lachlan -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Tuesday, 13 July 2004 8:11 PM To: Hickey,Thom Cc: [EMAIL PROTECTED] Subject: Re: 4.1 performance Hi! On Jul 12, Hickey,Thom wrote: I've been comparing the performance of 4.1 with the MySQL 3.23.58 that came with our Rocks cluster software. I'm finding that 4.1 is running approximately 1/3 slower (e.g. a 0.075 second query goes to 0.100 seconds) than 3.23.58. Size of buffers, etc. seems to have little effect. The database is fairly large with about 3 gig spread over a half-dozen tables. The largest table has 62 million rows. What kind of queires do you run ? What does EXPLAIN show ? Are all tables ANALYZE'd ? 4.1 can use more complex query transformations and join methods that should bring more performance. Of course if the statistical data are incorrect, the optimizer cannot jugde what is more and what it less, so new features may expose problems that were hidden in the old version. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign Key
You should take a look at using InnoDb table types. -Original Message- From: Everton Sanga To: [EMAIL PROTECTED] Sent: 7/13/04 8:26 PM Subject: Foreign Key Hi all, this is my first post, and I whould like to know if there is a way to create Foreign Key´s between Parent and Child table, I realy dont know if is it possible to do it and if OK, how will be a simple SQL code of it. I use PowerDesigner 10.0 and Erwin 4.0 Tks for all Everton Sanga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to create an Installation Package using VB.NET?
Roy I don't think we provide any merge module for our ODBC installation yet, but the changes that the odbc installer makes are very trivial so you could make them yourself. Are you going to run the mysql installer or perform the install yourself? If you are going to perform the install yourself, you could install the service by simply running the mysql binary with the --install option. [check the command line help for syntax] Once you get the mysql service installed, you could start the service and then add users using some vb.net ado.net code you write. Or you could just create a text file in temp and feed that to the server. Are you using the Vs.net installer stuff or wix? -reggie -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 3:54 PM To: [EMAIL PROTECTED] Subject: How to create an Installation Package using VB.NET? Hi, I am very new to MySQL and using it as the backend of my VB.NET application. Now, I am trying to create an installation package from within VB and don't know what files from MySQL MyODBC 3.51 I need to add to my installation package. Can anyone provide some help in this regards? Also, I will need to know how to start mysql as a service, setup users password, all through the installation package. I would appreciate any help that anyone can provide. Sincerely, Roy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade 3.23 to 4.1.x
Dear all: Do any one try to upgrade the mysql from 3.23 to 4.1? In doc,mysql doesn't recommend update the mysql from diff series.Do any one try it successfully? Do I just need to copy the datafile to the new mysql ? Best regards. MaFai [EMAIL PROTECTED] 2004-07-14
RE: Cannot do join on varchar
Since I don't have control on this server(cannot upgrade), so I cannot tell if same happen in 4.1.3. However, I would like to know the result in 4.0 as well. Anyone test this script in 4.0/3.X server? I suspect this could be a unicode problem in 4.1? If 4.1.3 could fix the problem I will suggest my admin to do an upgrade. Luckly this is not a production server... EXPLAIN SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id In Extra show: Impossible WHERE noticed after reading const tables I also found out that the trim should actually go to the 'matching' id: /* Previously I suggest trim on the left column: */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id /* However, this works as well, and it seems more logical? */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE TRIM(tb_test1.id)='abcde' AND tb_test1.id = tb_test2.id Anybody could explain this? Thank you. Francis -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 8:19 PM To: 'Francis Mak '; '[EMAIL PROTECTED] ' Subject: RE: Cannot do join on varchar Have you tried this with the latest MySQL 4.1.x build? Does it not work in this version? What does the explain plan show? -Original Message- From: Francis Mak To: [EMAIL PROTECTED] Sent: 7/13/04 4:01 AM Subject: Cannot do join on varchar Hi, I found out that when joining varchar column, you have to trim down the left column in order for MYSQL to match the row, is it a known issue? Or join only works fine in number column? CREATE TABLE `tb_test1` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; CREATE TABLE `tb_test2` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; INSERT INTO tb_test1(id, name) VALUES('abcde', 'NAME1') INSERT INTO tb_test2(id, name) VALUES('abcde', 'NAME2') /* The following display zero row */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id /* The following will display correct result */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id Redhat 9, with MYSQL 4.1.1, using UTF8 as default charset. Any idea?? Thank you. Francis Mak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
Boyd Lynn Gerber: I have (think) the MySQL dataserver running. You wouldn't know where I could find a compiled: mysql.so module for apache/php data access? Thanks! Jim On Tue, 13 Jul 2004, Jim Ginn wrote: Much better but it complains about max_allowed_packet? It is set to the default 1M ... I have my stune set as follows. NODEosr507 EVDEVS 96 EVQUEUES88 NSPTTYS 64 NUMSP 256 NSTREAM 4352 NHINODE 1024 GPGSLO 1000 GPGSHI 3000 NSTRPAGES 4000 NAIOPROC50 NAIOREQ 400 NAIOBUF 400 NAIOHBUF100 NAIOREQPP 400 NAIOLOCKTBL 50 MAX_PROC1 MAXUMEM 1048576 NCALL 256 NCLIST 512 NSTREVENT 14848 NUMTIM 1888 NUMTRW 1888 SDSKOUT 64 SEMMAP 8192 SEMMNI 8192 SEMMNS 8192 SEMMSL 150 SEMMNU 150 SHMMAX 2147483647 TTHOG 4096 SECLUID 0 SECSTOPIO 1 SECCLEARID 1 MAXUP 4096 NOFILES 4096 SHMMNI 200 Installing all prepared tables Fill help tables ERROR: 1153 Got a packet bigger than 'max_allowed_packet' bytes 040713 17:43:07 Aborting 040713 17:43:07 ./bin/mysqld: Shutdown complete On Tue, 13 Jul 2004, Jim Ginn wrote: Updated to (your) 4.1.3-beta and now get: ./bin/mysqld --skip-grant dynamic linker : ./bin/mysqld : could not open libssl.so.0.9.6m Check in /usr/lib and see: l /usr/lib/libssl* -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0.9.6@ -r--r--r-- 1 bin bin38044 Sep 20 2003 /usr/lib/libsso.a@ I am currently using openssl-0.9.6m. You will find a complete static and binary of what I am using in openssl-0.9.6m-osr5.tar.gz ftp://ftp.zenez.com/pub/zenez/prgms/openssl-0.9.6m-osr5.tar.gz I usually install it in /usr/local/ssl. Most people install it in /usr/lib. You can also put links from /usr/local/ssl/lib to /usr/lib/ On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Upgrade 3.23 to 4.1.x
What table types are you currently using? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 7/13/04 10:09 PM Subject: Upgrade 3.23 to 4.1.x Dear all: Do any one try to upgrade the mysql from 3.23 to 4.1? In doc,mysql doesn't recommend update the mysql from diff series.Do any one try it successfully? Do I just need to copy the datafile to the new mysql ? Best regards. MaFai [EMAIL PROTECTED] 2004-07-14 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO Openserver Install
On Wed, 14 Jul 2004, Jim Ginn wrote: I have (think) the MySQL dataserver running. You wouldn't know where I could find a compiled: mysql.so module for apache/php data access? Yes there is one in ftp.sco.com ftp://ftp.sco.com/pub/openserver5/507/iso/suppcd3/osr407suppcd3.iso and in MP3 which is the mp3 OpenServer 5 patch supplement. ftp://ftp.sco.com/pub/openserver5/507/mp/mp3/507mp3_vol.tar On Tue, 13 Jul 2004, Jim Ginn wrote: Much better but it complains about max_allowed_packet? It is set to the default 1M ... I have my stune set as follows. NODEosr507 EVDEVS 96 EVQUEUES88 NSPTTYS 64 NUMSP 256 NSTREAM 4352 NHINODE 1024 GPGSLO 1000 GPGSHI 3000 NSTRPAGES 4000 NAIOPROC50 NAIOREQ 400 NAIOBUF 400 NAIOHBUF100 NAIOREQPP 400 NAIOLOCKTBL 50 MAX_PROC1 MAXUMEM 1048576 NCALL 256 NCLIST 512 NSTREVENT 14848 NUMTIM 1888 NUMTRW 1888 SDSKOUT 64 SEMMAP 8192 SEMMNI 8192 SEMMNS 8192 SEMMSL 150 SEMMNU 150 SHMMAX 2147483647 TTHOG 4096 SECLUID 0 SECSTOPIO 1 SECCLEARID 1 MAXUP 4096 NOFILES 4096 SHMMNI 200 Installing all prepared tables Fill help tables ERROR: 1153 Got a packet bigger than 'max_allowed_packet' bytes 040713 17:43:07 Aborting 040713 17:43:07 ./bin/mysqld: Shutdown complete On Tue, 13 Jul 2004, Jim Ginn wrote: Updated to (your) 4.1.3-beta and now get: ./bin/mysqld --skip-grant dynamic linker : ./bin/mysqld : could not open libssl.so.0.9.6m Check in /usr/lib and see: l /usr/lib/libssl* -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0@ -r-xr-xr-x 3 bin bin 191812 Sep 20 2003 /usr/lib/libssl.so.0.9.6@ -r--r--r-- 1 bin bin38044 Sep 20 2003 /usr/lib/libsso.a@ I am currently using openssl-0.9.6m. You will find a complete static and binary of what I am using in openssl-0.9.6m-osr5.tar.gz ftp://ftp.zenez.com/pub/zenez/prgms/openssl-0.9.6m-osr5.tar.gz I usually install it in /usr/local/ssl. Most people install it in /usr/lib. You can also put links from /usr/local/ssl/lib to /usr/lib/ On Tue, 13 Jul 2004, Jim Ginn wrote: Trying to get MySQL running on SCO Openserver 5.0.7 and get this message: dynamic linker: ./bin/mysqld: binder error: symbol not found: pthread_key_delete; referenced from: ./bin/mysqld This was an error for some versions of MySQL. This is fixed in MySQL-4.0.19 forward. You will need FSU-threads-3.14 or new. And I have FSU threads installed ... I made a special version of FSU-threads for this but then worked with MySQL on a better solution as the creator of FSU-threads did not want to make changes and release a new version. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'x' on blob field when retrieving records
Hi list... I found that someone else had this problem: http://lists.mysql.com/mysql/161614 But the solution is missing... I couldn't find any other reference to this problem, does any one know the answer? Regards FBR [EMAIL PROTECTED] 13/07/2004 07:15 p.m. To [EMAIL PROTECTED], [EMAIL PROTECTED] cc Subject 'x' on blob field when retrieving records I have a query like this select hex(blob_field) as myField from table When the blob field is empty ('') it returns 'x'. i'm using ADO with Visual Basic and MyODBC 3.51. MySQL Version is 4.0.18 and SO is Win XP Pro (also happens on Windows 2K) Felix Beltran.
How to fix a connection of replication
Hello, Is there any way to fix a connection of replication from slave host when user on slave host noticed that the connection of replication had been lost? Now I have a following environment. MySQL Master server == replication == MySQL Slave server Both server has Turbo Linux 8.0 WS MySQL-4.0.18. Master has a global IP address and has good network performance. Slave is connected to Internet with 64kbps. By a network problem, the connection between slave server and Internet often drops. So I want to make a command like mysql_synchronize which tries to fix the connection of replication immediately. I think that a possible script for the command is like mysql --host=master_host -e show master status # memory master_log_pos and master_log_file mysql --host=slave_host -e \ stop slave; start slave; select master_pos_wait('master_log_file',master_log_pos); Are there any better script? Now I am using following parameters (these are default values). slave_net_timeout=3600 master-connect-retry=60 I think these values should be decreased in my environment. slave_net_timeout=30 master-connect-retry=10 If I use above values, it will not be necessary to use mysql_synchronize. But I think these values cause a wasting network traffic. Any ideas? Thanks, -- Yasumitsu Ito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]