mysqlcheck (not myisamchk) harmful?
Hello A user of the Debian packages that I maintain reported a problem with the mysqlcheck --all-databases --fast --quiet command that is run in the /etc/init.d/mysql script just after the server is up and running as reported by mysqladmin ping. According to all documentation that I found and the source code, mysqlcheck in opposide to myisamchk does not deal with the tables files itself which could be suspicious (e.g. with skip-locking) but just issue a CHECK TABLE xyz FAST command to the server which should be safe regardless who else does whatever to the table. Can somebody acknowledge that? Or give me a pointer what could get wrong with this approach? (BTW: I am right that as long as there are no not properly closed tables, this command should only take a few seconds even with some thousand tables?) thanks, -christian- pgpB02TTw3dZ1.pgp Description: PGP signature
Re: slave should not stop
put the following option in your my.cnf on the slave in order to ignore errors. Just use the error-numbers you'd like to ignore: slave-skip-error=1053 Jan Jim Nachlin wrote: Is there any way within mysql to have the slaves not stop replicating on an error. For some reason, my application is trying to insert duplicate keys. This fails and the slaves stop replicating from the master. Ideally, the command that failed would just be skipped automatically and the whole system keeps going. If there's no way to fix this problem, has anyone come up with a workaround, like some sort of clever script that will keep things replicating? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump case sensitive in windows
is there an option to perform mysqldump to a file and to have the case sensitiveness of the tables saved as well in windows os? (I had all tables in lower case)... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connection time is slow
[Could not convert from 'ks_c_5601-1987'] Hello. I'm using MySQL 4.1.2 on Linux(Fedora Core 2) and Window 2003 Server. When connect from Linux to Windows, or from Window to Linux connection time is very slow. ( it takes about 6 seconds) But Linux to Linux or Windows to Windows Fast. Anybody has an Idea? Thank you for advanced answer! == (ÁÖ)À¯ºñÄõÅͽº Æйи® °³¹ßÆÀ Çã Á¤ ¼ö ÁÖÀÓ email : [EMAIL PROTECTED] tel : 02-798-6822 fax : 02-749-9632 Web : http://www.ufamily.co.kr ¼¿ï½Ã ¿ë»ê±¸ ³²¿µµ¿ 104 û¼Ûºôµù 3Ãþ == ¿ï ÂÄ, ¿ï ¾¥ We win! This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain private, confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, employee or agent responsible for delivering this message, please contact the sender by reply e-mail and destroy all copies of the original e-mail message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: connection time is slow
IMHO You have problems on the DNS server. _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: Heo, Jungsu [mailto:[EMAIL PROTECTED] Sent: Thursday, 29 July, 2004 3:17 PM To: [EMAIL PROTECTED] Subject: connection time is slow [Could not convert from 'ks_c_5601-1987'] Hello. I'm using MySQL 4.1.2 on Linux(Fedora Core 2) and Window 2003 Server. When connect from Linux to Windows, or from Window to Linux connection time is very slow. ( it takes about 6 seconds) But Linux to Linux or Windows to Windows Fast. Anybody has an Idea? Thank you for advanced answer! == (ÁÖ)À¯ºñÄõÅͽº Æйи® °³¹ßÆÀ Çã Á¤ ¼ö ÁÖÀÓ email : [EMAIL PROTECTED] tel : 02-798-6822 fax : 02-749-9632 Web : http://www.ufamily.co.kr ¼¿ï½Ã ¿ë»ê±¸ ³²¿µµ¿ 104 û¼Ûºôµù 3Ãþ == ¿ï ÂÄ, ¿ï ¾¥ We win! This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain private, confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, employee or agent responsible for delivering this message, please contact the sender by reply e-mail and destroy all copies of the original e-mail message. -- 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: Using SQL's JOIN to return all rows regardless of non-exist rows in other tables....
The syntax for a LEFT JOIN is identical to the syntax for the INNER JOIN. All you need to do is replace the word INNER with the word LEFT and your query should work as you want it to work. :-) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED] wrote on 07/28/2004 04:55:06 PM: I'm trying to figure out how to get the table FUNDED_INFO to return all rows, even if there is no row(s) in the two other tables, STOCK CUSTOMERS. This one doesn't really work 'cause either one of the two tables, STOCK and CUSTOMERS doesn't have a row which would cause a row from FUNDED_INFO not to be returned at all... --snip-- SELECT FUNDED_INFO.TIMESTAMP, FUNDED_INFO.ACCT_NUMBER FROM FUNDED_INFO INNER JOIN STOCK ON FUNDED_INFO.ACCT_NUMBER = STOCK.ACCT_NUMBER INNER JOIN CUSTOMERS ON FUNDED_INFO.ACCT_NUMBER = CUSTOMERS.ACCT_NUMBER --snip-- So, I believe that a LEFT JOIN would be the answer, if so what would be the appropriate syntax exactly to reflect that?? Thanks, FletchSOD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN/WHERE and index confusion
Why should it use any other? Timestamp is the only field in the where clause, and you are selecting all fields. The second index would require more index reads, and the third can't be used to satisfy the where clause. Eamon Daly wrote: Okay, now I'm even /more/ confused. I whittled everything down like so: CREATE INDEX reporting_t ON a (timestamp); CREATE INDEX reporting_t_id ON a (timestamp, a_id); CREATE INDEX reporting_id_t ON a (a_id, timestamp); EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 2004010100 AND 20040101235959 and it /still/ only uses reporting_t! What the heck am I missing? Eamon Daly - Original Message - From: Eamon Daly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 10:58 AM Subject: JOIN/WHERE and index confusion Hi, all. I think I'm misunderstanding something basic about indexes. I have a SELECT like so: SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id All of the id fields are primary indexes. I've already created an index on a.timestamp, and that works all right. I tried creating an index on a for the SELECT: KEY `reporting` (`a_id`,`c_id`,`timestamp`) and an index on c for the GROUP BY: KEY `reporting` (`c_id`,`d_id`,`e_id`) But EXPLAIN shows that MySQL isn't even considering the key on a, and chooses the primary key on c over my index. Clearly I'm confused about how indexes are used in a JOIN/WHERE situation: can anyone enlighten me? Eamon Daly -- 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: mysqlcheck (not myisamchk) harmful?
At 0:54 +0200 7/29/04, Christian Hammers wrote: Hello A user of the Debian packages that I maintain reported a problem with the mysqlcheck --all-databases --fast --quiet command that is run in the /etc/init.d/mysql script just after the server is up and running as reported by mysqladmin ping. According to all documentation that I found and the source code, mysqlcheck in opposide to myisamchk does not deal with the tables files itself which could be suspicious (e.g. with skip-locking) but just issue a CHECK TABLE xyz FAST command to the server which should be safe regardless who else does whatever to the table. It does indeed send CHECK TABLE statements to the server. It should be safe *unless* someone other than the server is messing with theh table files directly (e.g., someone running myisamchk at the same time, or renaming files, etc.) Can somebody acknowledge that? Or give me a pointer what could get wrong with this approach? (BTW: I am right that as long as there are no not properly closed tables, this command should only take a few seconds even with some thousand tables?) Dunno, never tried it with that many tables. :-) Should be reasonably quick, though, unless your directory access routines are dreadfully slow. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query for room reservations question
Hi all, I'm using two tables for handling a little room reservations system: a rooms table: CREATE TABLE `rooms` ( `id` int(11) NOT NULL auto_increment, `room_number` int(3) default NULL, `room_type` char(1) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; a reservations table: CREATE TABLE `reservations` ( `id` int(11) NOT NULL auto_increment, `room_id` int(11) default NULL, `date_from` date default NULL, `date_to` date default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; I have quite clear the join syntax (using for the the ON clause rooms.id and reservations.room_id fields ) to list all the rooms reserved on a given date interval, but I can't figure out the query to list all the rooms of a given type (room_type) that results FREE ( not reserved ) on the given date interval. thanx for your help, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL over Raid Mirror
The problem with putting the OS on the same drive as the database is that they will be competing for the drive resources. On the flip side, following the safety first rule will make sure your system will stay up if a drive fails. I always go for safety first. If you are going to put everything on the mirrored drives, I would make sure you have plenty of RAM. Your worst case scenario is having the OS doing a lot of paging, due too lack of RAM, while your database is very active. The OS will always use the disk (i.e. writing logs), but if you can keep the OS activity to minimum, your performance hit should be negligible. Get all the performance you can out of tweaking the MySQL and other settings first, then you can resort to the hardware. I assume you are using software mirroring and perhaps even just one IDE controller for both drives (master/slave setup). Both give you performance hits, especially having one controller. But it's definitely a workable setup that still should give you decent performance. On Jul 28, 2004, at 3:30 PM, Rick Dwyer wrote: Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) My config is a Mac Dual Processor G4 1.25 Ghtz running 10.3 with MysQL 4.0.15. both drives are 120GB 7200RM IDE Hitachi Deskstar's. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up MySQL on Raid Mirror
Years ago I designed a system using Lasso (3.5 I think) with FileMaker running on OS 9 on a 300Mhz G3 that handled about 4K hits per day, most hits during typical works hours. Your system is definitely not in the high-performance realm (no offense) and your hardware should handle the load quite well. Of course, that's a bold statement not knowing the size of your data or complexity of your queries. On Jul 28, 2004, at 3:59 PM, Rick Dwyer wrote: Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.x charset
Hello, How can I see what char set is being used as the default char set on the server or database? Also, is there a way to change the default setting? Thanks, Yves __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.17 to 4.1.3 connection problem
Wes, I don't think this is the issue as my passwords are all still in the old format. Plus, the 4.0.16 client has no problem connecting. Thanks -keith Keith: I don't know if it's the same problem, but I recently had issues where I had a similar setup with only two MySQL servers, one 4.0.20, the other 4.1.3, same usernames/passwords on each. My solution, and I don't remember where in the manual I saw this (possibly the FAQ), was to do an update to the 4.1.3 mysql user table: UPDATE user SET Password = OLD_PASSWORD( Password ) WHERE ... fill in with username/host/whatever I believe this was because I was trying to connect to MySQL 4.1.x with a 4.0.x client. I don't know if that's your case as well. http://dev.mysql.com/doc/mysql/en/Old_client.html Wes On Jul 28, 2004, at 6:21 PM, Keith Thompson wrote: I have a mysql connection problem that I'm trying to understand. The three servers and the version of mysql they are running (all under Solaris9) are: db1 - 4.0.16 db2 - 4.0.17 db3 - 4.1.3 All three systems have the same mysql user and passwords setup. I'll use the mythical user xx with password yy to explain here. Connections with the mysql client (using -uxx -pyy) from system to system all work except this one on db2: mysql -hdb3 -uxx -pyy ERROR 1045: Access denied for user 'xx'@'db2' (using password: YES) The mysql.user table entry has host=% and user=xx, so it's not simply an issue of a system-specific entry allowing one and not the other. Since db1 has no problem getting to db3, I wouldn't expect db2 to struggle. This same problem occurs with all users, so it' is also not something specific to how this user is setup. Does anyone know why this would be happening? Is there something different in 4.0.17 (compared to 4.0.16) that prevents it from connecting to the 4.1.3 server? I don't see anything in the 4.0.17 change list specific to this. Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query for room reservations question
Assume you want to check the availability of a room between two dates. You only want to get the rooms that are available for the entire time from @startdate to @enddate set @startdate='some_start_date', @enddate='some_end_date' SELECT r.room_number, r.room_type FROM rooms r LEFT JOIN reservations rv on rv.room_ID = r.id AND rv.startdate = @enddate AND rv.enddate = @startdate WHERE rv.id is null NOTE: the time portion of @startdate should be 00:00:00 on the first day and the time portion of @enddate should be set to 23:59:59 for the last day of the window you are interested in. How this works: The ON conditions of the JOIN of the table rooms to the table reservations identifies any reservation that covers any portion of the window you are interested in (draw it out with a number line, you will see the logic works) But because it's a LEFT JOIN (not an INNER JOIN) it lists all of the rooms regardless of whether or not there is a reservation. So, for those rows with room information but without reservation information to match our ON conditions, all of the columns that would normally have reservation data in them will have null values. We only want those unmatched rows so that's why we wrote the WHERE to return only those where the rv.id is null but we could have checked for NULL in any reservation column. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Giulio [EMAIL PROTECTED] wrote on 07/29/2004 09:18:50 AM: Hi all, I'm using two tables for handling a little room reservations system: a rooms table: CREATE TABLE `rooms` ( `id` int(11) NOT NULL auto_increment, `room_number` int(3) default NULL, `room_type` char(1) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; a reservations table: CREATE TABLE `reservations` ( `id` int(11) NOT NULL auto_increment, `room_id` int(11) default NULL, `date_from` date default NULL, `date_to` date default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; I have quite clear the join syntax (using for the the ON clause rooms.id and reservations.room_id fields ) to list all the rooms reserved on a given date interval, but I can't figure out the query to list all the rooms of a given type (room_type) that results FREE ( not reserved ) on the given date interval. thanx for your help, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.x charset
You can see it by executing the SHOW VARIABLES query on your server. You will find the used charset in the 'character_set' variable. You can also use the query SHOW VARIABLES LIKE 'character_set' which will directly match what you want. Yves wrote: Hello, How can I see what char set is being used as the default char set on the server or database? Also, is there a way to change the default setting? Thanks, Yves __ Post your free ad now! http://personals.yahoo.ca -- --- ___ _ __ / __\ ___ ___ _ __ | |_ _ _ (__) /__\/// _ \ / _ \| '_ \| __| | | | || / \/ \ (_) | (_) | | | | |_| |_| | || \_/\___/ \___/|_| |_|\__|\__, | ___||__.._ |___/ /\ \/~~~ Jean-Marc PULVAR (Web Programmer) Boonty SA 4 bis villa du mont tonnerre 75015 PARIS --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.x charset
Thanks, As it turns out, I was trying show variables like -- And had a syntax mistake Does SHOW VARIABLES only show a certain number of lines? When I ran this command, character_set was not listed... the list seemed cut off just before it. Unless it is an issue with SSH only showing x amount of lines.. thanks, Yves A --- Jean-Marc PULVAR [EMAIL PROTECTED] wrote: You can see it by executing the SHOW VARIABLES query on your server. You will find the used charset in the 'character_set' variable. You can also use the query SHOW VARIABLES LIKE 'character_set' which will directly match what you want. Yves wrote: Hello, How can I see what char set is being used as the default char set on the server or database? Also, is there a way to change the default setting? Thanks, Yves __ Post your free ad now! http://personals.yahoo.ca -- --- ___ _ __ / __\ ___ ___ _ __ | |_ _ _ (__) /__\/// _ \ / _ \| '_ \| __| | | | || / \/ \ (_) | (_) | | | | |_| |_| | || \_/\___/ \___/|_| |_|\__|\__, | ___||__.._ |___/ /\ \/~~~ Jean-Marc PULVAR (Web Programmer) Boonty SA 4 bis villa du mont tonnerre 75015 PARIS --- __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN/WHERE and index confusion
I would assume it would use reporting_id_t, since the WHERE clause has both a.a_id and a.timestamp in it. Eamon Daly - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 29, 2004 8:04 AM Subject: Re: JOIN/WHERE and index confusion Why should it use any other? Timestamp is the only field in the where clause, and you are selecting all fields. The second index would require more index reads, and the third can't be used to satisfy the where clause. Eamon Daly wrote: Okay, now I'm even /more/ confused. I whittled everything down like so: CREATE INDEX reporting_t ON a (timestamp); CREATE INDEX reporting_t_id ON a (timestamp, a_id); CREATE INDEX reporting_id_t ON a (a_id, timestamp); EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 2004010100 AND 20040101235959 and it /still/ only uses reporting_t! What the heck am I missing? Eamon Daly - Original Message - From: Eamon Daly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 10:58 AM Subject: JOIN/WHERE and index confusion Hi, all. I think I'm misunderstanding something basic about indexes. I have a SELECT like so: SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id All of the id fields are primary indexes. I've already created an index on a.timestamp, and that works all right. I tried creating an index on a for the SELECT: KEY `reporting` (`a_id`,`c_id`,`timestamp`) and an index on c for the GROUP BY: KEY `reporting` (`c_id`,`d_id`,`e_id`) But EXPLAIN shows that MySQL isn't even considering the key on a, and chooses the primary key on c over my index. Clearly I'm confused about how indexes are used in a JOIN/WHERE situation: can anyone enlighten me? Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.x charset
It's possibly due to your ssh connection but I couldn't answer you about this because I only use mysql with phpmyadmin. Regards Yves wrote: Thanks, As it turns out, I was trying show variables like -- And had a syntax mistake Does SHOW VARIABLES only show a certain number of lines? When I ran this command, character_set was not listed... the list seemed cut off just before it. Unless it is an issue with SSH only showing x amount of lines.. thanks, Yves A --- Jean-Marc PULVAR [EMAIL PROTECTED] wrote: You can see it by executing the SHOW VARIABLES query on your server. You will find the used charset in the 'character_set' variable. You can also use the query SHOW VARIABLES LIKE 'character_set' which will directly match what you want. Yves wrote: Hello, How can I see what char set is being used as the default char set on the server or database? Also, is there a way to change the default setting? Thanks, Yves __ Post your free ad now! http://personals.yahoo.ca -- --- ___ _ __ / __\ ___ ___ _ __ | |_ _ _ (__) /__\/// _ \ / _ \| '_ \| __| | | | || / \/ \ (_) | (_) | | | | |_| |_| | || \_/\___/ \___/|_| |_|\__|\__, | ___||__.._ |___/ /\ \/~~~ Jean-Marc PULVAR (Web Programmer) Boonty SA 4 bis villa du mont tonnerre 75015 PARIS --- __ Post your free ad now! http://personals.yahoo.ca -- --- ___ _ __ / __\ ___ ___ _ __ | |_ _ _ (__) /__\/// _ \ / _ \| '_ \| __| | | | || / \/ \ (_) | (_) | | | | |_| |_| | || \_/\___/ \___/|_| |_|\__|\__, | ___||__.._ |___/ /\ \/~~~ Jean-Marc PULVAR (Web Programmer) Boonty SA 4 bis villa du mont tonnerre 75015 PARIS --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql_safe just ends
So it turns out that the RPM install didn't work nor did the binary version but by rebuilding the source with a configure --prefix=/usr/local/mysql --- Well, that worked in the end. I still don't know why but there it is. :) Just thought I'd post my experience. Cheers -Original Message- From: Cam [mailto:[EMAIL PROTECTED] Sent: July 28, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: mysql_safe just ends So I'm a little confused here I've installed mysql-standard-4.0.20-pc-linux-i686.tar to /usr/local/mysql and then ran the scripts/mysql_install_db with seemingly no errors. After reading section 5.1 'the MySQL Server and Server Startup Scripts' I figured that cd /usr/local/mysql bin/mysqld_safe would simply work but no. I get [EMAIL PROTECTED] mysql]# bin/mysqld_safe [2] 11616 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:50:16 mysqld ended [2]+ Donebin/mysqld_safe [EMAIL PROTECTED] mysql]# OK - so I thought well I'll just specify what I want. [EMAIL PROTECTED] mysql]# bin/mysqld_safe --ledir=/usr/local/mysql/bin --data=/usr/local/mysql/data --user=mysql [2] 11664 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:52:34 mysqld ended [2]+ Donebin/mysqld_safe --ledir=/usr/local/mysql/bin --data=/usr/local/mysql/data --user=mysql So now in the end I'm confused. 1. Why does mysqld_safe simply start then immediately end? How do I start mysql? 2. Why does it go to /var/lib/mysql for the data even when I specify another location? For that matter where did it get that location? did mysql_install_db do that? Any help or direction would be greatly appreciated Cheers Cam -- 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: what os to use for mysql on amd64?
mc [EMAIL PROTECTED] wrote: Just curious if I have got something wrong with my eyes or fingers: [EMAIL PROTECTED] mysql-max-4.0.20-unknown-linux-x86_64]# ldd bin/mysqld librt.so.1 = /lib64/tls/librt.so.1 (0x003c71f0) libdl.so.2 = /lib64/libdl.so.2 (0x003c7190) libpthread.so.0 = /lib64/tls/libpthread.so.0 (0x003c7210) libz.so.1 = /usr/lib64/libz.so.1 (0x003c71d0) libcrypt.so.1 = /lib64/libcrypt.so.1 (0x003c71b0) libnsl.so.1 = /lib64/libnsl.so.1 (0x003c7250) libm.so.6 = /lib64/tls/libm.so.6 (0x003c7170) libc.so.6 = /lib64/tls/libc.so.6 (0x003c7140) /lib64/ld-linux-x86-64.so.2 = /lib64/ld-linux-x86-64.so.2 (0x003c7120) and this (from a 32bit installation) neon:/usr/local/mysql# ldd bin/mysqld not a dynamic executable SODIUM is from mysql amd64 tarball. Did I download the wrong tarball or do I need to hack libc and link them by myself? No. I've forgot to tell that the -Max binary is linked dynamically because it uses SSL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.20 and UTF-8?
Yves Goergen [EMAIL PROTECTED] wrote: I'm using UTF-8 in my newsboard, too, and I have no major problems with it. UTF-8 doesn't need to be handled as binary, I believe, since all characters should be in a range over the control characters. Anyone please correct me, if that's wrong... Tris is true for latin1 characters in UTF8. When you are working with national characters in UTF8, you can easily hit in control characters ranges. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and SSL
Michael Dykman [EMAIL PROTECTED] wrote: could someone please tell me which versions of mysql support SSL connections for both clients and replication slaves? The -Max build. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query for room reservations question
Brilliant ! thank you so much! only one more question: INNER JOIN is not a good choose because it lists only the record with a matching ON clause, and this is the only difference between LEFT and INNER, correct? thanks again, Giulio Il giorno 29/lug/04, alle 16:03, [EMAIL PROTECTED] ha scritto: Assume you want to check the availability of a room between two dates. You only want to get the rooms that are available for the entire time from @startdate to @enddate set @startdate='some_start_date', @enddate='some_end_date' SELECT r.room_number, r.room_type FROM rooms r LEFT JOIN reservations rv on rv.room_ID = r.id AND rv.startdate = @enddate AND rv.enddate = @startdate WHERE rv.id is null NOTE: the time portion of @startdate should be 00:00:00 on the first day and the time portion of @enddate should be set to 23:59:59 for the last day of the window you are interested in. How this works: The ON conditions of the JOIN of the table rooms to the table reservations identifies any reservation that covers any portion of the window you are interested in (draw it out with a number line, you will see the logic works) But because it's a LEFT JOIN (not an INNER JOIN) it lists all of the rooms regardless of whether or not there is a reservation. So, for those rows with room information but without reservation information to match our ON conditions, all of the columns that would normally have reservation data in them will have null values. We only want those unmatched rows so that's why we wrote the WHERE to return only those where the rv.id is null but we could have checked for NULL in any reservation column. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Giulio [EMAIL PROTECTED] wrote on 07/29/2004 09:18:50 AM: Hi all, I'm using two tables for handling a little room reservations system: a rooms table: CREATE TABLE `rooms` ( `id` int(11) NOT NULL auto_increment, `room_number` int(3) default NULL, `room_type` char(1) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; a reservations table: CREATE TABLE `reservations` ( `id` int(11) NOT NULL auto_increment, `room_id` int(11) default NULL, `date_from` date default NULL, `date_to` date default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; I have quite clear the join syntax (using for the the ON clause rooms.id and reservations.room_id fields ) to list all the rooms reserved on a given date interval, but I can't figure out the query to list all the rooms of a given type (room_type) that results FREE ( not reserved ) on the given date interval. thanx for your help, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kernel 2.4 and 2.6
Batara Kesuma [EMAIL PROTECTED] wrote: I use MySQL 4.0.20 on my replication (slave) server. I noticed that when I use kernel 2.4.26 SMP, the slave runs very slow (it can't catch up with master). My machine has dual CPUs with HT. But if I change to kernel 2.6.6 SMP, everything just runs fine, the slave can catch up easily with master. Both of the kernels are from Debian. Anyone has same experience? I just feel curious. AFAIR (correct me if I'm wrong) the HT optimization is found only in 2.6 kernels. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed Database still slow
[EMAIL PROTECTED] wrote: Ok, I will be the first to say that I am learning about indexes, however it is my understanding that if I have a database with MANY rows and I wish my queries to be faster I should index my database. With that being said, I have 2 tables in my database that are being queried with a single query using a UNION these 2 tables combined are about 9 Million records (yes I said million). My query which is below takes about 1 minute to run, now some people would say that this isn't long, however when the 2 tables were sub 5 million it only took a matter of about 20 seconds to run, so I figure I need an index. So I have created an index called Main within both tables and added 6 columns to that index, most of the columns that are used in my query. Sorry for the long background, but here is the problem, my query DID NOT speed up at all. It still takes right at 1 minute per query, so indexing didn't buy me anything as far as I can tell. Can someone tell me how the indexes are supposed to be done ( to ensure that I did it correctly) and tell me if they think that it should have sped up or if there is a more efficient way to do my query. You should debug (read = investigate) your query using EXPLAIN SELECT statement. Read http://dev.mysql.com/doc/mysql/en/EXPLAIN.html and perhaps http://dev.mysql.com/doc/mysql/en/Query_Speed.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large 30 GB Database
Robert Harrison [EMAIL PROTECTED] wrote: I have been asked to provide a searchable electoral roll. This data comprises of first name, surname, address for every person over 18 in the UK. Approximately 30 GB The data is to be used to find people in a certain area. EG All John Smith in London. All Sarah Dean in St Johns Road, Manchester. Should I even attempt this using mysql? Has anyone played with this much data in mysql? 30GB is not an issue for MySQL. It's not that big. Of course if you do table scans (queries on non-indexed columns) they will be as fast as the disks are (read - slow). But indexed 30GB is absolutely ok for MySQL. You may also consider to bear in mind the SOUNDEX function of MySQL: http://dev.mysql.com/doc/mysql/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: License info
Santino [EMAIL PROTECTED] wrote: The question is very simple: Your question was forwarded to MySQL licensing people. You will receive an answer shortly. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN/WHERE and index confusion
No. a.a_id is used in the join to b, not for selecting records in a. Eamon Daly wrote: I would assume it would use reporting_id_t, since the WHERE clause has both a.a_id and a.timestamp in it. Eamon Daly - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 29, 2004 8:04 AM Subject: Re: JOIN/WHERE and index confusion Why should it use any other? Timestamp is the only field in the where clause, and you are selecting all fields. The second index would require more index reads, and the third can't be used to satisfy the where clause. Eamon Daly wrote: Okay, now I'm even /more/ confused. I whittled everything down like so: CREATE INDEX reporting_t ON a (timestamp); CREATE INDEX reporting_t_id ON a (timestamp, a_id); CREATE INDEX reporting_id_t ON a (a_id, timestamp); EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 2004010100 AND 20040101235959 and it /still/ only uses reporting_t! What the heck am I missing? Eamon Daly - Original Message - From: Eamon Daly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 10:58 AM Subject: JOIN/WHERE and index confusion Hi, all. I think I'm misunderstanding something basic about indexes. I have a SELECT like so: SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id All of the id fields are primary indexes. I've already created an index on a.timestamp, and that works all right. I tried creating an index on a for the SELECT: KEY `reporting` (`a_id`,`c_id`,`timestamp`) and an index on c for the GROUP BY: KEY `reporting` (`c_id`,`d_id`,`e_id`) But EXPLAIN shows that MySQL isn't even considering the key on a, and chooses the primary key on c over my index. Clearly I'm confused about how indexes are used in a JOIN/WHERE situation: can anyone enlighten me? Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing MySQL Databases on RAM Drive
Stephen Rasku [EMAIL PROTECTED] wrote: We are currently using a flash drive to store our database but we want to store it in a RAM disk to prolong the life of the drive. We want to install the database on the RAM disk on startup and save it to flash on shutdown. There are two databases that we are using. Neither is very big but one is updated very frequently and the other one is not. However, even though the databases are small, the ibdata1 file is 136M. Is there a way to prevent this file from growing too big? The InnoDB needs more disk space than MyISAM. Perhaps, you could use MyISAM? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up MySQL on Raid Mirror
Rick Dwyer [EMAIL PROTECTED] wrote: I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Speed is always a drawback. We, at Ensita.NET, install all our servers completely on RAID1 (slow but reliable), RAID1+0 (fast and reliable), or RAID5 (something in between) arrays, including OS, excluding /tmp and swap :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.20 and UTF-8?
On 29.07.2004 17:33 (+0200), Egor Egorov wrote: Yves Goergen [EMAIL PROTECTED] wrote: I'm using UTF-8 in my newsboard, too, and I have no major problems with it. UTF-8 doesn't need to be handled as binary, I believe, since all characters should be in a range over the control characters. Anyone please correct me, if that's wrong... Tris is true for latin1 characters in UTF8. When you are working with national characters in UTF8, you can easily hit in control characters ranges. But what is this binary good for? Can't I just insert those control characters into a normal string/text field? -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving database from windows 3.x to unix 4.x
Hello all Can any one give some advise on moving mysql databases from windows 3.x to unix 4.x I have created dump files using: Mysqldump --user=myuser --password=mypassword --opt database database.dump Are there any issues I shoud be looking out for? Thanks, Bert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Conversion Function
M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion Function
Not being an MSSQL expert, I don't know what MM does, and since you did not specify what type of conversions you wish to perform, I can't say exactly which function would suit your needs, but here is a link to the manual page that lists all date and time functions -- http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- Original message from [EMAIL PROTECTED]: -- M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query for room reservations question
When it comes to JOINS, there are 3 flavors in MySQL: LEFT, RIGHT, and INNER. This is an example of how to join two tables a and b: SELECT (some columns) FROM a XXX JOIN b ON (some logical comparison) WHERE (some overall condition) Behind the scenes, the data engine creates a virtual table that consists of every column from each table that participates in the query. In this case it will have all of the columns of a and all of the columns of b. Which columns in that virtual table will be full of data and which columns will be full of NULLs or data depends on what you put in for XXX. If XXX were LEFT, all of the a columns would be full of a data (every row in the a table would be listed) but the b columns would only have data where the ON condition was satisfied. All of the b columns in all of the other rows where the ON condition was not satisfied would be full of NULL values. The opposite occurs with a RIGHT JOIN. All of the b columns would be full of data (every row in the table would be listed) but the a columns would either have NULLs or data depending on if those rows satisfied the ON clause. That's why using a LEFT JOIN or a RIGHT JOIN and looking for null values in the WHERE clause can find rows between tables that DO NOT match certain criteria (as specified in the ON clause) Yes, you were right when you said that INNER JOIN will ONLY return rows from either table if those rows satisfy the ON condition. One thing that surprises some people is that joining tables can (and often does) create more records than either table contains. By example: table a has 100 record, table b has 10 records but you get back 106 records from this query: SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id What happened was that there was at least 1 record in a with multiple matches to records in b. When the database engine builds its virtual table, each combination of a data and b data that satisfy the ON condition will be listed in that table. That's why this query (keeping table sizes the same) will return 1000 rows of data: SELECT a.*, b.* FROM a INNER JOIN b (what kind of join you use in this case is irrelevant as there is no ON clause to make a difference) If you actually ran this query you would see each row of a paired up with each row of b. This is what is known as a Cartesian product or cross product of your two tables. Cartesian products are not something I work with often (I actually try my best to avoid them through well crafted ON clauses), but it does have its uses at times. The best way to get a good grip on how all the different JOIN clauses work is to just try them all out and notice the differences. Have Fun! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Giulio [EMAIL PROTECTED] wrote on 07/29/2004 11:39:10 AM: Brilliant ! thank you so much! only one more question: INNER JOIN is not a good choose because it lists only the record with a matching ON clause, and this is the only difference between LEFT and INNER, correct? thanks again, Giulio Il giorno 29/lug/04, alle 16:03, [EMAIL PROTECTED] ha scritto: Assume you want to check the availability of a room between two dates. You only want to get the rooms that are available for the entire time from @startdate to @enddate set @startdate='some_start_date', @enddate='some_end_date' SELECT r.room_number, r.room_type FROM rooms r LEFT JOIN reservations rv on rv.room_ID = r.id AND rv.startdate = @enddate AND rv.enddate = @startdate WHERE rv.id is null NOTE: the time portion of @startdate should be 00:00:00 on the first day and the time portion of @enddate should be set to 23:59:59 for the last day of the window you are interested in. How this works: The ON conditions of the JOIN of the table rooms to the table reservations identifies any reservation that covers any portion of the window you are interested in (draw it out with a number line, you will see the logic works) But because it's a LEFT JOIN (not an INNER JOIN) it lists all of the rooms regardless of whether or not there is a reservation. So, for those rows with room information but without reservation information to match our ON conditions, all of the columns that would normally have reservation data in them will have null values. We only want those unmatched rows so that's why we wrote the WHERE to return only those where the rv.id is null but we could have checked for NULL in any reservation column. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Giulio [EMAIL PROTECTED] wrote on 07/29/2004 09:18:50 AM: Hi all, I'm using two tables for handling a little room reservations system: a rooms table: CREATE TABLE `rooms` ( `id` int(11) NOT NULL auto_increment, `room_number` int(3) default NULL, `room_type` char(1)
RE: what os to use for mysql on amd64?
[snip] No. I've forgot to tell that the -Max binary is linked dynamically because it uses SSL. Here are the results from my installation... in case you may find them useful :) [EMAIL PROTECTED] root]# file mysql-standard-4.0.20-unknown-linux-x86_64/bin/mysqld mysql-standard-4.0.20-unknown-linux-x86_64/bin/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), stripped [EMAIL PROTECTED] root]# file mysql-standard-4.0.20-pc-linux-i686/bin/mysqld mysql-standard-4.0.20-pc-linux-i686/bin/mysqld: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.0.0, statically linked, stripped [EMAIL PROTECTED] root]# file mysql-max-4.0.20-unknown-linux-x86_64/bin/mysqld mysql-max-4.0.20-unknown-linux-x86_64/bin/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), stripped [EMAIL PROTECTED] root]# file mysql-max-4.0.20-pc-linux-i686/bin/mysqld mysql-max-4.0.20-pc-linux-i686/bin/mysqld: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.0.0, dynamically linked (uses shared libs), stripped [EMAIL PROTECTED] root]# md5sum mysql-standard-4.0.20-unknown-linux-x86_64.tar.gz eac7467f1a21a60ea9433e5a2e36b01a mysql-standard-4.0.20-unknown-linux-x86_64.tar.gz [EMAIL PROTECTED] root]# md5sum mysql-standard-4.0.20-pc-linux-i686.tar.gz ae1d47f98a1e5af84c09bd7544bb7dc5 mysql-standard-4.0.20-pc-linux-i686.tar.gz [EMAIL PROTECTED] root]# md5sum mysql-max-4.0.20-unknown-linux-x86_64.tar.gz f398a4a40602687ebcf3f84ae42eb53b mysql-max-4.0.20-unknown-linux-x86_64.tar.gz [EMAIL PROTECTED] root]# md5sum mysql-max-4.0.20-pc-linux-i686.tar.gz 26839776a8c09143ab01f1b797b0f448 mysql-max-4.0.20-pc-linux-i686.tar.gz [EMAIL PROTECTED] root]# ldd mysql-standard-4.0.20-unknown-linux-x86_64/bin/mysqld librt.so.1 = /lib64/tls/librt.so.1 (0x003c71f0) libdl.so.2 = /lib64/libdl.so.2 (0x003c7190) libpthread.so.0 = /lib64/tls/libpthread.so.0 (0x003c7210) libz.so.1 = /usr/lib64/libz.so.1 (0x003c71d0) libcrypt.so.1 = /lib64/libcrypt.so.1 (0x003c71b0) libnsl.so.1 = /lib64/libnsl.so.1 (0x003c7250) libm.so.6 = /lib64/tls/libm.so.6 (0x003c7170) libc.so.6 = /lib64/tls/libc.so.6 (0x003c7140) /lib64/ld-linux-x86-64.so.2 = /lib64/ld-linux-x86-64.so.2 (0x003c7120) [EMAIL PROTECTED] root]# ldd mysql-standard-4.0.20-pc-linux-i686/bin/mysqld not a dynamic executable [EMAIL PROTECTED] root]# ldd mysql-max-4.0.20-unknown-linux-x86_64/bin/mysqld librt.so.1 = /lib64/tls/librt.so.1 (0x003c71f0) libdl.so.2 = /lib64/libdl.so.2 (0x003c7190) libpthread.so.0 = /lib64/tls/libpthread.so.0 (0x003c7210) libz.so.1 = /usr/lib64/libz.so.1 (0x003c71d0) libcrypt.so.1 = /lib64/libcrypt.so.1 (0x003c71b0) libnsl.so.1 = /lib64/libnsl.so.1 (0x003c7250) libm.so.6 = /lib64/tls/libm.so.6 (0x003c7170) libc.so.6 = /lib64/tls/libc.so.6 (0x003c7140) /lib64/ld-linux-x86-64.so.2 = /lib64/ld-linux-x86-64.so.2 (0x003c7120) [EMAIL PROTECTED] root]# ldd mysql-standard-4.0.20-unknown-linux-x86_64/bin/mysqld librt.so.1 = /lib64/tls/librt.so.1 (0x003c71f0) libdl.so.2 = /lib64/libdl.so.2 (0x003c7190) libpthread.so.0 = /lib64/tls/libpthread.so.0 (0x003c7210) libz.so.1 = /usr/lib64/libz.so.1 (0x003c71d0) libcrypt.so.1 = /lib64/libcrypt.so.1 (0x003c71b0) libnsl.so.1 = /lib64/libnsl.so.1 (0x003c7250) libm.so.6 = /lib64/tls/libm.so.6 (0x003c7170) libc.so.6 = /lib64/tls/libc.so.6 (0x003c7140) /lib64/ld-linux-x86-64.so.2 = /lib64/ld-linux-x86-64.so.2 (0x003c7120) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL auto-start failing
Hi, I configured MySQL to compile as a 64bit executable on a Sun Ultra10. I set all up as per the installation instructions and created/installed the startup scripts: - /etc/init.d/mysql - /etc/rc3.d/S99mysql - /etc/rc0.d/K01mysql The MySQL daemon failed to start at system startup. The log file contained the following... 040725 21:27:55 mysqld started ld.so.1: /usr/local/mysql/libexec/mysqld: fatal: libstdc++.so.5: open failed: No such file or directory 040725 21:27:56 mysqld ended Using Webmin (browser sys admin - www.webmin.com) in an attempt to start MySQL resulted in the following error: The MySQL client program /usr/local/mysql/bin/mysql could not be run because it could not find the MySQL shared libraries. Check the module configuration and make sure the Path to MySQL shared libraries is set. The command /usr/local/mysql/bin/mysql -V returned : ld.so.1: /usr/local/mysql/bin/mysql: fatal: /usr/local/lib/libstdc++.so.5: wrong ELF class: ELFCLASS32 The library path is correctly set. I can however start/stop and successfully interact with MySQL from the command line! I know what the error indicates, but I don't know how to correct this so that the server starts/stops automatically. Please help. Thanks Graeme
Re: Python library for cluster support?
the fact that data is stored in Cluster/NDB instead of one of the more conventional engines is pretty much transparent to the client application: it just connects to mysql server as per usual and the server does all that work. What you will have to worry about is the incompatibility between mysql client libraries built for 3.x and the 4.1 servers which support clusters. you might have to rebuild python against a 4.1 library On Wed, 2004-07-28 at 00:32, Joe Wong wrote: Hi, may I know of the current MySQL python library supports MySQL cluster or not? Regards, - Wong -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving database from windows 3.x to unix 4.x
Windows does not preserve the case sensitivity of the table names. -Original Message- From: Bert Beaudin To: [EMAIL PROTECTED] Sent: 7/29/04 11:02 AM Subject: Moving database from windows 3.x to unix 4.x Hello all Can any one give some advise on moving mysql databases from windows 3.x to unix 4.x I have created dump files using: Mysqldump --user=myuser --password=mypassword --opt database database.dump Are there any issues I shoud be looking out for? Thanks, Bert -- 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: Date Conversion Function
I do not know of an MM() date function in MS SQL, only mm used for the date part. What are you attempting to accomplish? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 7/29/04 11:17 AM Subject: Date Conversion Function M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- 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]
Lucene with MYSQL
Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TOP
Shawn, Thanks for your help, I was wondering, if you can make any sense out of WHERE should I insert the TOP Commands, in the snippet below. Although this code is specific to our application, but maybe you can help me with this. Thanks, and I really appreciate your help. -Kamal. /* Build the Table Name(s) Microsoft SQL command - SELECT iv_sensor_names.name, iv_attack_0.name, iv_alert_type_0.name, iv_alert_severity_0.name, iv_categories_0.displayableName, iv_subcategories_0.display_name, iv_detection_0.displayableName, iv_direction_0.displayableName, iv_result_set_0.displayableName, iv_alert_0.creationTime FROM iv_alert iv_alert_0, iv_alert_severity iv_alert_severity_0, iv_alert_type iv_alert_type_0, iv_attack iv_attack_0, iv_categories iv_categories_0, iv_detection iv_detection_0, iv_direction iv_direction_0, iv_result_set iv_result_set_0, iv_sensor_names iv_sensor_names_0, iv_subcategories iv_subcategories_0 WHERE iv_alert_0.sensorId = iv_sensor_names_0.sensor_id AND iv_alert_0.alertType = iv_alert_type_0.id AND iv_alert_0.severity = iv_alert_severity_0.id AND iv_alert_0.attackIdRef = iv_attack_0.id AND iv_alert_0.categoryId = iv_categories_0.categoryId AND iv_alert_0.subCategoryId = iv_subcategories_0.idnum AND iv_alert_0.detectionMechanism = iv_detection_0.detectionMechanism AND iv_alert_0.resultSetValue = iv_result_set_0.resultSetValue AND iv_alert_0.direction = iv_direction_0.direction Table Joins - iv_alert LEFT JOIN iv_sensor_names ON iv_alert.sensorId = iv_sensor_names.sensor_id iv_alert LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id iv_alert LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id iv_alert LEFT JOIN iv_attack ON iv_attack.attackIdRef = iv_attack.id iv_alert LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId iv_alert LEFT JOIN iv_subcategories ON iv_alert.subCategoryId = iv_subcategories.idnum iv_alert LEFT JOIN iv_detection ON iv_alert.detectionMechanism = iv_detection.detectionMechanism iv_alert LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue iv_alert LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction Need LIMIT i_Max_Rows_To_Return */ BREAKPOINT() PRINTF(s_Table, iv_alert LEFT JOIN iv_sensor_names ON iv_alert.sensorid = iv_sensor_names.sensor_id ) APPEND(s_Table: LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id ) APPEND(s_Table: LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id ) APPEND(s_Table: LEFT JOIN iv_attack ON iv_alert.attackIdRef = iv_attack.id ) APPEND(s_Table: LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId ) APPEND(s_Table: LEFT JOIN iv_subcategories ON iv_alert.subCategoryId = iv_subcategories.idnum ) APPEND(s_Table: LEFT JOIN iv_detection ON iv_alert.detectionMechanism = iv_detection.detectionMechanism ) APPEND(s_Table: LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue ) APPEND(s_Table: LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction) /* Build the Where line */ CLEAR(s_Where) /* Logic goes here for Offset parsing */ PRINTF (s_Where, WHERE iv_alert.uuid %d ORDER BY iv_alert.uuid ASC, i_Offset ) /* Build the Column Names list */ PRINTF(s_Columns0, iv_alert.uuid, iv_sensor_names.name, iv_attack.name, iv_alert_type.name, iv_alert_severity.name, iv_categories.displayableName, ) PRINTF(s_Columns1, iv_subcategories.display_name, iv_detection.displayableName, iv_direction.displayableName, iv_result_set.displayableName, iv_alert.creationTime, ) PRINTF(s_Columns2, iv_alert.targetIPAddr, iv_alert.targetPort, iv_alert.sourceIPAddr, iv_alert.sourcePort, iv_alert.networkProtocolId ) PRINTF(s_Columns, %s%s%s, s_Columns0, s_Columns1, s_Columns2) BREAKPOINT() /* - DO NOT CHANGE THE LINES BELOW - */ LOOKUP(SelectDB_CheckStatus,eSecurity_Actions) SET(i_Record_Counter = 0) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 2:44 PM To: Kamal Ahmed Cc: [EMAIL PROTECTED] Subject: Re: TOP In MS SQL Server (T-SQL) you say SELECT TOP n In MySQL you use: SELECT LIMIT n (http://dev.mysql.com/doc/mysql/en/SELECT.html) There is no direct equivalent to SELECT TOP n PERCENT Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 07/28/2004 02:39:11 PM: Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- MySQL General Mailing List For list archives:
RE: TOP
In MySQL, the LIMIT clause is usually the very LAST part of any query. I would put it here: /* Logic goes here for Offset parsing */ PRINTF (s_Where, WHERE iv_alert.uuid %d ORDER BY iv_alert.uuid ASC, i_Offset ) Right after the ASC. This should limit you to only 1 responses: ...ORDER BY iv_alert.uuid ASC LIMIT 1, i_Offset) If you read about it (http://dev.mysql.com/doc/mysql/en/SELECT.html) you see that it can help you with results paging as well ...ORDER BY iv_alert.uuid ASC LIMIT 1, 1000, i_Offset) That will give you the next 1000 records starting from record # 10001 (it's a zero-based number. First record = 0) . Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 07/29/2004 02:16:12 PM: Shawn, Thanks for your help, I was wondering, if you can make any sense out of WHERE should I insert the TOP Commands, in the snippet below. Although this code is specific to our application, but maybe you can help me with this. Thanks, and I really appreciate your help. -Kamal. /* Build the Table Name(s) Microsoft SQL command - SELECT iv_sensor_names.name, iv_attack_0.name, iv_alert_type_0.name, iv_alert_severity_0.name, iv_categories_0.displayableName, iv_subcategories_0.display_name, iv_detection_0.displayableName, iv_direction_0.displayableName, iv_result_set_0.displayableName, iv_alert_0.creationTime FROM iv_alert iv_alert_0, iv_alert_severity iv_alert_severity_0, iv_alert_type iv_alert_type_0, iv_attack iv_attack_0, iv_categories iv_categories_0, iv_detection iv_detection_0, iv_direction iv_direction_0, iv_result_set iv_result_set_0, iv_sensor_names iv_sensor_names_0, iv_subcategories iv_subcategories_0 WHERE iv_alert_0.sensorId = iv_sensor_names_0.sensor_id AND iv_alert_0.alertType = iv_alert_type_0.id AND iv_alert_0.severity = iv_alert_severity_0.id AND iv_alert_0.attackIdRef = iv_attack_0.id AND iv_alert_0.categoryId = iv_categories_0.categoryId AND iv_alert_0.subCategoryId = iv_subcategories_0.idnum AND iv_alert_0. detectionMechanism = iv_detection_0.detectionMechanism AND iv_alert_0.resultSetValue = iv_result_set_0.resultSetValue AND iv_alert_0.direction = iv_direction_0.direction Table Joins - iv_alert LEFT JOIN iv_sensor_names ON iv_alert.sensorId = iv_sensor_names.sensor_id iv_alert LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id iv_alert LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id iv_alert LEFT JOIN iv_attack ON iv_attack.attackIdRef = iv_attack.id iv_alert LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId iv_alert LEFT JOIN iv_subcategories ON iv_alert.subCategoryId = iv_subcategories.idnum iv_alert LEFT JOIN iv_detection ON iv_alert.detectionMechanism = iv_detection.detectionMechanism iv_alert LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue iv_alert LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction Need LIMIT i_Max_Rows_To_Return */ BREAKPOINT() PRINTF(s_Table, iv_alert LEFT JOIN iv_sensor_names ON iv_alert. sensorid = iv_sensor_names.sensor_id ) APPEND(s_Table: LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id ) APPEND(s_Table: LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id ) APPEND(s_Table: LEFT JOIN iv_attack ON iv_alert.attackIdRef = iv_attack.id ) APPEND(s_Table: LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId ) APPEND(s_Table: LEFT JOIN iv_subcategories ON iv_alert. subCategoryId = iv_subcategories.idnum ) APPEND(s_Table: LEFT JOIN iv_detection ON iv_alert. detectionMechanism = iv_detection.detectionMechanism ) APPEND(s_Table: LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue ) APPEND(s_Table: LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction) /* Build the Where line */ CLEAR(s_Where) /* Logic goes here for Offset parsing */ PRINTF (s_Where, WHERE iv_alert.uuid %d ORDER BY iv_alert.uuid ASC, i_Offset ) /* Build the Column Names list */ PRINTF(s_Columns0, iv_alert.uuid, iv_sensor_names.name, iv_attack. name, iv_alert_type.name, iv_alert_severity.name, iv_categories. displayableName, ) PRINTF(s_Columns1, iv_subcategories.display_name, iv_detection. displayableName, iv_direction.displayableName, iv_result_set. displayableName, iv_alert.creationTime, ) PRINTF(s_Columns2, iv_alert.targetIPAddr, iv_alert.targetPort, iv_alert.sourceIPAddr, iv_alert.sourcePort, iv_alert.networkProtocolId ) PRINTF(s_Columns, %s%s%s, s_Columns0, s_Columns1, s_Columns2) BREAKPOINT() /* - DO NOT CHANGE THE LINES BELOW - */ LOOKUP(SelectDB_CheckStatus,eSecurity_Actions) SET(i_Record_Counter = 0) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
RE: Lucene with MYSQL
Hi, i have been using Lucene MySQL but separately, MySQL is written in C/C++ with a JDBC driver available and Lucene is coded in Java, so (besides the mysql team opinion) you can mix searches throught both apis and referring to each other with the PrimaryKey. This combination works fine for some scenarios but it doesnt for others, in some cases the best choice is to use the FullText feature available in MySQL which employs the same Vectorial Model that Lucene's do. One advantage of Lucene is that is composed of layers and you can modify it to use different models instead of uniquely the Vectorial; on the other hand Lucene doesnt support concurrency which can be a serious problem in your application or maybe not. Hope my 5 cents works to you :) Carlos Proal Original Message Follows From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Lucene with MYSQL Date: Thu, 29 Jul 2004 13:36:56 -0400 Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? _ MSN 8 helps eliminate e-mail viruses. Get 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: Lucene with MYSQL
Carlos Proal wrote: Hi, i have been using Lucene MySQL but separately, MySQL is written in C/C++ with a JDBC driver available and Lucene is coded in Java, so (besides the mysql team opinion) you can mix searches throught both apis and referring to each other with the PrimaryKey. Does anyone know if/how a JVM using Lucene could be used in the MySQL 5 Stored Procedure architecture (or maybe as Functions in the 4.0 architecture). If the architecture (MySQL 5) would allow for a continuous running JVM with an entry point into the Java search code then could you use Lucene code against the MySQL DB tables directly? There are obvious places where too much data marshalling between C and Java may make the solution too slow, but I would like to investigate the possibilities. thanks, Jon This combination works fine for some scenarios but it doesnt for others, in some cases the best choice is to use the FullText feature available in MySQL which employs the same Vectorial Model that Lucene's do. One advantage of Lucene is that is composed of layers and you can modify it to use different models instead of uniquely the Vectorial; on the other hand Lucene doesnt support concurrency which can be a serious problem in your application or maybe not. Hope my 5 cents works to you :) Carlos Proal Original Message Follows From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Lucene with MYSQL Date: Thu, 29 Jul 2004 13:36:56 -0400 Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? _ MSN 8 helps eliminate e-mail viruses. Get 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: Fw: Tuning MySQL for Large Database
matt ryan wrote: There is a perl script that comes with MySQL called mysqldumpslow. You can just run it on your slow log and it will output summary statistics about the slow log. I saw that in the docs, but I definitly dont want to install perl on a production server, I never looked to see if I could do it offline, I only have ms boxes, no linux here, none of our techs know linux well enough to move to it. Matt copy the slow log from the windows box to a linux box and run the script there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lucene with MYSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jon Hancock wrote: | Carlos Proal wrote: | | |Hi, i have been using Lucene MySQL but separately, MySQL is written |in C/C++ with a JDBC driver available and Lucene is coded in Java, so |(besides the mysql team opinion) you can mix searches throught both |apis and referring to each other with the PrimaryKey. | | | Does anyone know if/how a JVM using Lucene could be used in the MySQL 5 | Stored Procedure architecture (or maybe as Functions in the 4.0 | architecture). If the architecture (MySQL 5) would allow for a | continuous running JVM with an entry point into the Java search code | then could you use Lucene code against the MySQL DB tables directly? | There are obvious places where too much data marshalling between C and | Java may make the solution too slow, but I would like to investigate the | possibilities. | thanks, Jon Jon, 5.0 most likely won't ship with 'external' stored procedures. However, even today, it is straightforward to write user-defined functions in any language you wish. One of our employees has even posted a UDF which allows you to call any arbitrary Java code, so it should be pretty straightforward to automatically update your Lucene index and query it inside MySQL queries: http://freshmeat.net/projects/judf/?topic_id=66 -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBCWD8tvXNTca6JD8RAsjoAJ0Z+z5Ac/LE8LI50OieQWb425jzegCfVONa 0wn9ay0mGKDvcfZ214oSZRU= =xqTw -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lucene with MYSQL
...snip... This combination works fine for some scenarios but it doesnt for others, in some cases the best choice is to use the FullText feature available in MySQL which employs the same Vectorial Model that Lucene's do. One advantage of Lucene is that is composed of layers and you can modify it to use different models instead of uniquely the Vectorial; on the other hand Lucene doesnt support concurrency which can be a serious problem in your application or maybe not. Could you just briefly explain layers and concurrency maybe w/a simple real-life example so I can see the contrast between the two searchs? Thanks very much, Lee G. Hope my 5 cents works to you :) Carlos Proal Original Message Follows From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Lucene with MYSQL Date: Thu, 29 Jul 2004 13:36:56 -0400 Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? _ MSN 8 helps eliminate e-mail viruses. Get 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]
Inserting records from one table to another respecting a condition
Hello all, I have two mysql tables. They have a slightly different structure but share comom columns. I need to get the top 100 sold parts ( partnumbers and quantities) from table Sales and insert into table Suggestion if I issue a query : SELECT partnumber,qty from Sales order by qty desc limit 1,100 I would satisfy my need of retrieving the top 100 sold Items from Sales. Then, I need to insert into Suggestion so that I can have an estimate suggestion of purchase. How can I achieve it? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lucene with MYSQL
From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Lucene with MYSQL Date: Thu, 29 Jul 2004 16:56:23 -0400 ...snip... This combination works fine for some scenarios but it doesnt for others, in some cases the best choice is to use the FullText feature available in MySQL which employs the same Vectorial Model that Lucene's do. One advantage of Lucene is that is composed of layers and you can modify it to use different models instead of uniquely the Vectorial; on the other hand Lucene doesnt support concurrency which can be a serious problem in your application or maybe not. Could you just briefly explain layers and concurrency maybe w/a simple real-life example so I can see the contrast between the two searchs? Thanks very much, Lee G. Sure, i mean layers relating about java packages, Lucene have different packages to store, index, retrieve, apply IR model, etc, so for example if you want to use a different index schema you can modify that package and improve or customize that layer, the same applies to IR models, most models are based on frequency, collection size, etc those methods are available so you can build an entirely new algorithm (ie. Latent Semantic) with the core indexing of Lucene. About concurrency, MySQL is a engine that supports thousand connections at the same time and mantains some indexes in memory (even Full Text indexes i think, someone at MySQL can confirm this ?) so every operation is very fast; Lucene is not an engine its an API, so every add/update must check if the index file is locked, wait and so on. If you need something more robust in this way MySQL is the best choice; if you are thinking in something more static than doesnt update frequently then Lucene can be a good one. Lucene's faq and http://today.java.net/pub/a/today/2003/07/30/LuceneIntro.html are good references for more details. regards Carlos Proal Hope my 5 cents works to you :) Carlos Proal Original Message Follows From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Lucene with MYSQL Date: Thu, 29 Jul 2004 13:36:56 -0400 Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you archive db daily?
I am running MySql 3.23.58-1 on a snort database containing IDS alerts. At 12:00am I would like move the previous day's alerts from four tables to a backup database named for the previous day. Has anyone implemented such a backup schedule? And if so can someone send me a script? I had the following ideas on the subject: 1.a. mysql shutdown. b. cp -r database //2004-07-29 c. mysqlstart /* I need a single user mode for the delete to work */ d. echo Delete iphdr; Delete tcphdr;Delete acid_event; Delete event | mysql -p xxx -u e. go to multiuser mode. 2. a. Assuming logging turned on mysqlhotcopy snortdb ( echo Delete iphdr; Delete tcphdr;Delete acid_event; Delete event ; mysqlbinlog snort.log ) | mysql -p xxx -u yyy 3. a. $ mysql -p xxx -u yyy 1. if a week then purge tables: $mysql Delete iphdr; ( repeat for the rest of the tables.) 2. mysql -p xxx -u yyy mysql Select iphdr.* from iphdr, event_id into outfile /.../backup/2004-07-29/iphdr.txt where timestamp.event_id 2004-07-29; mysql Delete iphdr; ( repeat for the rest of the tables.) mysql use backsnort_db Select iphdr.* from iphdr, event_id Load infile /.../backup/2004-07-29/iphdr.txt ( repeat for the rest of the tables.) mysql exit b. tar cvf backsnort_db That is my best shot if anyone has a more elegant solution I would appreciate hearing about it. Thank you, Raymond
RE: Inserting records from one table to another respecting a condition
Hi, You can use Insert into Suggestion (column 1, column 2, column 3, ..) SELECT partnumber,qty from Sales order by qty desc limit 1,100 Regards, Laercio. -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 29 de julho de 2004 19:01 To: [EMAIL PROTECTED] Subject: Inserting records from one table to another respecting a condition Hello all, I have two mysql tables. They have a slightly different structure but share comom columns. I need to get the top 100 sold parts ( partnumbers and quantities) from table Sales and insert into table Suggestion if I issue a query : SELECT partnumber,qty from Sales order by qty desc limit 1,100 I would satisfy my need of retrieving the top 100 sold Items from Sales. Then, I need to insert into Suggestion so that I can have an estimate suggestion of purchase. How can I achieve it? Thanks in advance. -- 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: Confusion about various users, starting over from scratch
I'm reading through the doc (gasp!) on the mysql.com site, specifically, 2.4 Unix Post Installation Procedures: http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html The first sentence in the last paragraph before the numbered steps says: In the examples shown here, the server runs under the user ID of the mysql login account. 1. I'm confused what the 'mysql login account' is. Does this mean the user who has logged into the Linux box, or is this a mysql-specific user (a user account that is in the mysql databases.) It's a Linux login account with a name of mysql. Like your login account that you use to log in on your Linux box, but with a different name. Other names for this might be shell account or system account. It's not a MySQL user account. Got it. I see where I went wrong - using the binary distr doc after I did a source installation. If it's a separate (new) user on the Linux box, where in the doc does it say to set this guy up before running the post-install stuff? In the two sentences following the sentence that you quote above. The full paragraph is: In the examples shown here, the server runs under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server. Got it. One of those 'had to read and re-read' multiple times things. If you install using an RPM, the account should be created for you. I've done this now. I ripped out all the old files, did a complete RPM install and up to testing, everything ran fine. In order to see if the server was running, I did a ps-aux to see if I had any mysql processes running, and there they were, about a half-dozen of them. Then, on step 4 of post-installation, it says to bin/mysqladmin version This command fails regardless of what permutation I try. I can't find mysqladmin anywehre on my system, either as a regular user or as root. I've been through all of the steps in the 2.4 troubleshooting guide: 1. I'm using MySQL out of the 'box' - no special options at all. 2. The data dirs (and files) are in /var/lib/mysql 3. The data dirs and files are owned by user mysql, group root. (mysqld --help works fine) 4. The server started fine. It's just mysqladmin that doesn't work, because it doesn't exist anywhere. Doesn't mysqladmin work when the daemon is running? (stupid question, I know, but I've been through this troubleshooting guide paragraph by paragraph multiple times and this one isn't covered.) -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confusion about various users, starting over from scratch
At 17:40 -0500 7/29/04, Whil Hentzen wrote: I'm reading through the doc (gasp!) on the mysql.com site, specifically, 2.4 Unix Post Installation Procedures: http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html The first sentence in the last paragraph before the numbered steps says: In the examples shown here, the server runs under the user ID of the mysql login account. 1. I'm confused what the 'mysql login account' is. Does this mean the user who has logged into the Linux box, or is this a mysql-specific user (a user account that is in the mysql databases.) It's a Linux login account with a name of mysql. Like your login account that you use to log in on your Linux box, but with a different name. Other names for this might be shell account or system account. It's not a MySQL user account. Got it. I see where I went wrong - using the binary distr doc after I did a source installation. If it's a separate (new) user on the Linux box, where in the doc does it say to set this guy up before running the post-install stuff? In the two sentences following the sentence that you quote above. The full paragraph is: In the examples shown here, the server runs under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server. Got it. One of those 'had to read and re-read' multiple times things. If you install using an RPM, the account should be created for you. I've done this now. I ripped out all the old files, did a complete RPM install and up to testing, everything ran fine. In order to see if the server was running, I did a ps-aux to see if I had any mysql processes running, and there they were, about a half-dozen of them. Then, on step 4 of post-installation, it says to bin/mysqladmin version This command fails regardless of what permutation I try. I can't find mysqladmin anywehre on my system, either as a regular user or as root. Hm, I don't think I understand what you mean. Do you mean you that when you execute the program, it runs but fails to connect to the server, or that you cannot even find the program to try to run it? If you installed using the server RPM, that doesn't include any of the client programs. There is a separate client RPM that you must install. Or is your (current) installation installed from a .tar.gz file, or did you build it from source? I've been through all of the steps in the 2.4 troubleshooting guide: 1. I'm using MySQL out of the 'box' - no special options at all. 2. The data dirs (and files) are in /var/lib/mysql 3. The data dirs and files are owned by user mysql, group root. (mysqld --help works fine) 4. The server started fine. It's just mysqladmin that doesn't work, because it doesn't exist anywhere. Doesn't mysqladmin work when the daemon is running? (stupid question, I know, but I've been through this troubleshooting guide paragraph by paragraph multiple times and this one isn't covered.) -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confusion about various users, starting over from scratch
Got it. One of those 'had to read and re-read' multiple times things. If you install using an RPM, the account should be created for you. I've done this now. I ripped out all the old files, did a complete RPM install and up to testing, everything ran fine. In order to see if the server was running, I did a ps-aux to see if I had any mysql processes running, and there they were, about a half-dozen of them. Then, on step 4 of post-installation, it says to bin/mysqladmin version This command fails regardless of what permutation I try. I can't find mysqladmin anywehre on my system, either as a regular user or as root. Hm, I don't think I understand what you mean. Do you mean you that when you execute the program, it runs but fails to connect to the server, or that you cannot even find the program to try to run it? I'm sorry, I wasn't very clear. (Jessh, Whil!) Door #2 - the system responds with cannot find program. If you installed using the server RPM, that doesn't include any of the client programs. There is a separate client RPM that you must install. Ah, I see; I didn't realize that mysqladmin was a _client_ program. I d/l'd that but neglected to install it. I was still working on the server part. Or is your (current) installation installed from a .tar.gz file, or did you build it from source? Nope, RPMs, but you answered my question. I've just installed the client stuff and mysqladmin runs 'version' and ' -u root shutdown' fine. It took me a while to get to restart the server. I'm still having a bit of trouble understanding all the various alternatives. 1. As Linux user 'root', /usr/bin/mysqld_safe --user=mysql Done this. Works fine. 2. As Linux user 'mysql, /usr/bin/mysqld_safe But what is the password of Linux user 'mysql'? This user was created automatically by the RPM script. 3. As Linux user 'ordinary_joe', /usr/bin/mysql_safe --user=mysql this fails. Is there a way to make this work? I'm getting there -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Kernel 2.4 and 2.6
2.6 - IO scheduler has some major problems. The DeadLine and Anticipatory schedulers underperforms compared to 2.4 Linus scheduler. In theory they should rock, but there are some major problems with it. Currently the 2.6 VM rocks and is way to fast for the 2.6 IO schedulers. On top of that they are buggy. Use SUSE 2.4 or RedHat 7.3 2.4.23aa_vm DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 9:39 PM To: [EMAIL PROTECTED] Subject: Kernel 2.4 and 2.6 Hi, I use MySQL 4.0.20 on my replication (slave) server. I noticed that when I use kernel 2.4.26 SMP, the slave runs very slow (it can't catch up with master). My machine has dual CPUs with HT. But if I change to kernel 2.6.6 SMP, everything just runs fine, the slave can catch up easily with master. Both of the kernels are from Debian. Anyone has same experience? I just feel curious. Regards, Batara -- 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: Inserting records from one table to another respecting a condition
INSERT INTO Suggestion SELECT partnumber,qty from Sales Limit 1,100; DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: Thursday, July 29, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Inserting records from one table to another respecting a condition Hello all, I have two mysql tables. They have a slightly different structure but share comom columns. I need to get the top 100 sold parts ( partnumbers and quantities) from table Sales and insert into table Suggestion if I issue a query : SELECT partnumber,qty from Sales order by qty desc limit 1,100 I would satisfy my need of retrieving the top 100 sold Items from Sales. Then, I need to insert into Suggestion so that I can have an estimate suggestion of purchase. How can I achieve it? Thanks in advance. -- 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]
soft link mysql socket?
Hello, mysql Gurus, Because of disk space issues, the data dir of my mysql DB is somewhere else other than /var/lib/mysql. I did not link /var/lib/mysql to the real data dir though. It works OK until I want to use perl DBI which complains can't connect to mysql thru '/var/lib/mysql/mysql.sock'. So I just create a soft link of '/var/lib/mysql/mysql.sock' pointing to the real socket and it works. I am wondering if this is gonna give me any problem in the future. Or i should just soft link the whole '/var/lib/mysql' instead of a single file? THank you very much for help. All the best ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
prepared statements in C API not working...headers needed!!
people actually this is a stupid question i suppose..but.. i tried out to run a query from my C program using prepared statements.. It is giving ..undefined reference so anyone who worked on prepared statements can give me the headers that i should include to run them.. Thanx in advance sujay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: soft link mysql socket?
On Thu, Jul 29, 2004 at 08:10:42PM -0700, Ginger Cheng wrote: Hello, mysql Gurus, Because of disk space issues, the data dir of my mysql DB is somewhere else other than /var/lib/mysql. I did not link /var/lib/mysql to the real data dir though. It works OK until I want to use perl DBI which complains can't connect to mysql thru '/var/lib/mysql/mysql.sock'. So I just create a soft link of '/var/lib/mysql/mysql.sock' pointing to the real socket and it works. I am wondering if this is gonna give me any problem in the future. It shouldn't be a problem. -- 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]
