Re: Re: Re: Re: Re: Re: Re: (ad infinitum)
Subject: Re: MySQL/InnoDB-4.0.4 is released ... Subject: Re: MySQL/InnoDB-4.0.4 is released ... Subject: Re: MySQL/InnoDB-4.0.4 is released ... Subject: Re: MySQL/InnoDB-4.0.4 is released ... Subject: Re: MySQL/InnoDB-4.0.4 is released ... Subject: Re: MySQL/InnoDB-4.0.4 is released ... Would it be too much to ask that people trim their quotes up a bit? This is a high-traffic list. The only way some of us can contribute is to get it in digest format, and the endless quotes make digest reading painful. Thanks in advance for your courtesy! filter fodder sql query -- : Jan Steinman -- nature photography: http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Join the forums at http://www.Bytesmiths.com/wiki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Unsort Single Column
Hi, My tables is something like this Value Count B 10 C 20 A 15 D 8 When I query using SELECT Value, Count FROM Table, the data is appearing exactly at the order above, however when I select only a single column using SELECT Value FROM Table, the data get sorted. Instead of Value B C A D It's now Value A B C D Can I get the order back to BCAD? Even the best Programming Language can't prevent Programmer from making mistakes, so if you want to become a Programmer, you just have to learn not to make mistakes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Unsort Single Column
Is there primary key on field Value? Or any index? Daniel At 14:37 2002.10.04._ +0800, you wrote: Hi, My tables is something like this Value Count B 10 C 20 A 15 D 8 When I query using SELECT Value, Count FROM Table, the data is appearing exactly at the order above, however when I select only a single column using SELECT Value FROM Table, the data get sorted. Instead of Value B C A D It's now Value A B C D Can I get the order back to BCAD? Even the best Programming Language can't prevent Programmer from making mistakes, so if you want to become a Programmer, you just have to learn not to make mistakes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERROR 1: Can't create/write to file tmpdirfile (Errcode: 13)
When trying to create an index on Table ANSCHLUSS: | ID | int(11) | | PRI | NULL| auto_increment | | TELEFON | varchar(20) | | MUL | || | REGION | int(11) | | | 0 || | LAND | int(11) | | | 0 || | NETZ | tinyint(4) | | | 0 || | PROTOKOLLTYP | tinyint(4) | | | 0 || | ANZAHLANRUFE | int(11) | | | 0 || | AUSCALLERID | tinyint(4) | | | 0 || containing 481484 records, mysql yields mysql CREATE INDEX INDX_ANSCHLUSS ON ANSCHLUSS(TELEFON, REGION, LAND, NETZ); ERROR 1: Can't create/write to file '/home/data/mysql/tmp/STabnagj' (Errcode: 13) Only 18% of the disk are in use, df shows that there are 28'103'092 KB available. root, the owner has rights enough, mysql is started from the root user. (drwxr-xr-x2 root root 35 Oct 4 08:08 tmp/) OS: Linux Thanks and regards for any comment. Stephan Gloor Gloor Informatik Zurlindenstr. 215 a 8003 Zürich Tel: 01 461 24 11 Fax: 01 461 22 39 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Create table if not exists from mysqldump?
Hi, Do u mean when u take a dump you need to add before each table drop table if exists then here is the command. mysqldump -uusername -p -hhostname --add-drop-table databasename tablename test.sql regards, Praveen --- John Kelly [EMAIL PROTECTED] wrote: Hi, I am trying to use mysqldump to dump the structure of a single database and want the output to make create table commands that include the IF NOT EXISTS switch so that when the file is imported into an existing database it does not write over tables with the same name. Does anyone know if this is possible with mysqldump and if so what option achieves this? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Create table if not exists from mysqldump?
Or your could ... mysqldump -uusername -p -hhostname --all --add-drop-table --add-locks -- complete-insert --compress --extended-insert --flush-logs --force --opt --ve rbose --databases databasename tablename test.sql - Original Message - From: swati sandhya [EMAIL PROTECTED] To: John Kelly [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 04, 2002 5:19 AM Subject: Re: Create table if not exists from mysqldump? Hi, Do u mean when u take a dump you need to add before each table drop table if exists then here is the command. mysqldump -uusername -p -hhostname --add-drop-table databasename tablename test.sql regards, Praveen --- John Kelly [EMAIL PROTECTED] wrote: Hi, I am trying to use mysqldump to dump the structure of a single database and want the output to make create table commands that include the IF NOT EXISTS switch so that when the file is imported into an existing database it does not write over tables with the same name. Does anyone know if this is possible with mysqldump and if so what option achieves this? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Create table if not exists from mysqldump?
John, Friday, October 04, 2002, 6:41:46 AM, you wrote: JK Hi, I am trying to use mysqldump to dump the structure of a single database JK and want the output to make create table commands that include the IF NOT JK EXISTS switch so that when the file is imported into an existing database it JK does not write over tables with the same name. Does anyone know if this is JK possible with mysqldump and if so what option achieves this? Thanks. You can't. You can add DROP TABLE before each CREATE table statement using --add-drop-table option. You can run mysql with --force option when you restore tables from dump file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Fw: Three Tables
William, Thursday, October 03, 2002, 10:14:04 AM, you wrote: I have three tables in my database t1, t2, and t3. Each table has records that are the same or similar. I WM am trying to SELECT part number, part description, location, part cost, sell price and on hand quantity from these three tables. Each part number is WM the same in all three tables. I am having trouble with my select statement. Please, be more detailed. What is the structure of your tables? What do you want to get? What SELECT statement did you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1: Can't create/write to file tmpdirfile (Errcode:13)
On Fri, 4 Oct 2002, Stephan Gloor wrote: When trying to create an index on Table ANSCHLUSS: | ID | int(11) | | PRI | NULL| auto_increment | | TELEFON | varchar(20) | | MUL | || | REGION | int(11) | | | 0 || | LAND | int(11) | | | 0 || | NETZ | tinyint(4) | | | 0 || | PROTOKOLLTYP | tinyint(4) | | | 0 || | ANZAHLANRUFE | int(11) | | | 0 || | AUSCALLERID | tinyint(4) | | | 0 || containing 481484 records, mysql yields mysql CREATE INDEX INDX_ANSCHLUSS ON ANSCHLUSS(TELEFON, REGION, LAND, NETZ); ERROR 1: Can't create/write to file '/home/data/mysql/tmp/STabnagj' (Errcode: 13) Stephan, tsp@host:~ perror 13 Error code 13: Permission denied It must have to do with permissions; whats the result of 'ls /home/data/mysql/tmp/' ? Thomas Only 18% of the disk are in use, df shows that there are 28'103'092 KB available. root, the owner has rights enough, mysql is started from the root user. (drwxr-xr-x2 root root 35 Oct 4 08:08 tmp/) OS: Linux Thanks and regards for any comment. Stephan Gloor filter: sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: To Duplicate a Row?
* Jan Steinman Is there a simple way to duplicate one or more rows in a table? I tried: INSERT INTO table SELECT * FROM table WHERE criteria but apparently INSERT...SELECT cannot function on the same table. Right, but you can use an intermediate temporary table: CREATE TEMPORARY TABLE tmp1 SELECT * FROM table WHERE criteria INSERT INTO table SELECT * from tmp1 -- Roger sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: [Q] Replication temporary tables (fwd)
Andrey, Thursday, October 03, 2002, 6:08:16 PM, you wrote: Andrey, Tuesday, October 01, 2002, 11:53:41 AM, you wrote: AK Why is temporary tables replecated? AK Is it true? Yes, temporary table are replicated properly since 3.23.29: http://www.mysql.com/doc/en/Replication_Features.html AK Hmm... What is the reason? AK If I create a temporary table in one process, I can't use it in another. AK And after close connection table will destroy. AK What is the reason to replicate it? Andrey, you can use temporary tables in INSERT ... SELECT statement, in multi-table deletes etc. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: unable to set root password
Palash, Thursday, October 03, 2002, 7:44:33 PM, you wrote: PMK i have just installed mysql.i was able to start the mysqld PMK process.howeveri am unable to set password for root using the following PMK command sequence : PMK ./bin/mysqladmin -u root -password 'abcd' This statement means that you connect to the MySQL server as user 'root' with password 'assword'. According to the MySQL manual: Another way to set the password is by using the mysqladmin command: shell mysqladmin -u root password new_password PMK I get the following output : PMK ./bin/mysqladmin: connect to server at 'localhost' failed PMK error: 'Access denied for user: 'root@localhost' (Using password: YES)' PMK I had read that there is a default userid called 'test'.i am unable to use PMK that as well. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: [Q] Replication temporary tables (fwd)
äÏÂÒÙÊ ÄÅÎØ. Yes, temporary table are replicated properly since 3.23.29: http://www.mysql.com/doc/en/Replication_Features.html AK Hmm... What is the reason? AK If I create a temporary table in one process, I can't use it in another. AK And after close connection table will destroy. AK What is the reason to replicate it? Andrey, you can use temporary tables in INSERT ... SELECT statement, in multi-table deletes etc. Thank you. I understand. Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport ÔÅÌ. +380 562 34-00-44 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with moving database using mysqldump
I have tried that with no success. A suggestion was made though of just tarring the data dir, I think I shall try that route. Mike -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 6:46 PM To: 'Mike Hillyer'; MySQL Mailing List Subject: RE: Problem with moving database using mysqldump Hi, Some of the old mysqldump apps have a bug where they're missing quotes on long rows. Try the dump the other way - i.e. use the mysqldump client on the new machine and pipe it from the old machine back to the new machine (make sure the new machine has permissions to grab from the old one...). With something like: mysqldump -h oldmachine -u username olddatabase | mysql newdatabasename Cheers, Andrew Sql,query -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED]] Sent: Friday, 04 October 2002 01:25 To: MySQL Mailing List Subject: Problem with moving database using mysqldump Hello All; I am having a problem using the mysqldump application. I have an old server that I am trying to move to a faster machine. No matter what I do, about 90 queries in the connection ceases and the old meachine gets a server has gone away error. I have tries runnig mysql dump piped to the mysql client on the old machine with the client connecting across the network and on the new machine with mysqldump connecting across the network. I have also tried producing a file and then piping the file into mysql client on the new machine, but no success, always the new machine stops processing requests around the 90-100 query mark. Does anyone have any idea why this is happening? The file is about 40-50MB and the largest table in the database is only 110,000 rows, so this should not be straining the system. Thanks, Mike Hillyer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP -- 4.04-beta does not compile
Using RedHat 7.2 and GCC 3.1 to compile the MySQL 4.04 beta, I get the following error: ./gen_lex_hash lex_hash.h ./gen_lex_hash: error while loading shared libraries: libstdc++.so.4: cannot open shared object file: No such file or directory Is this a bug or a problem with my configuration??? Robert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (ad infinitum)
Jan Steinman wrote: This is a high-traffic list. The only way some of us can contribute is to get it in digest format, and the endless quotes make digest reading painful. I read the list 'live' if you will (not pre-digested) and find it equally irritating. I'd appreciate everyone taking the extra 5-10 seconds it requires to delete the portions of the previous person's E-mail that isn't relevant to what you're writing. If I may digress for a moment (on topics not related to SQL), it seems that this relatively new (in Internet-age terms) trend of typing before the quoted previous message (brought on by MS and friends) is a large part of the problem; one can simply type away without realizing that there are ten pages of text below one's response. If one takes the time to scroll to below the text to which they are responding first, one realizes how much irrelevant quoted text there is. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reply-to munging [OT]
Niclas Hedhman wrote: I don't by the less functionality argument any more than if you have a good emailer. I think I have a good emailer, and on the munging lists, I press Reply-All, it will also send to the original author, but 95% of the case I want the list, and original author doesn't want two replies. You are the one who uses KMail, so you decide if it does what you think it ought to do for you. By way of comparison, MUTT 'knows' which mailing lists you are subscribed to and allows you to hit 'F' to follow-up to the mailing list, or 'r' to reply to the author. The 'less functionality' argument is subtle but very true; I'm on several lists that screw up the reply-to: header by modifying it to point to the list and when I want to actually reply to the author, I can't unless I take the time to cut and paste the author's E-mail address into my mailer. BTW, for some logical thought: if _you_ want the list, but someone else could conceivably want the author, then _you're_ mail program should be what is configured _not_ the mailing list, since the mailing list is a shared medium and should offer _both_ options but your mailer is _not_ shared and you should be able to set your preferences in your mailer. And this still has nothing to do with SQL. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: raid vs splitting the database
Well, there is the ideal setup, which requires intimate knowledge of the database, lots of disks and extra administration. And then there is the easy setup. Ideally you don't want to have any hot disks which will cause contention. This requires you to place your busy tables (read or write) on separate disks so that one drive doesn't get most of the activity. You really only need to do this for your large tables. But as your system grows, it can be difficult to do this manual tuning. I don't recommend this on smaller systems and would really only recommend it on systems where the OS allows you to expand file systems or migrate them while they are in use. I know AIX can do this and I'm sure Solaris (with Veritas) and a few others can. The easy option, and the one I would recommend, is setting up a RAID using something like RAID 0 (striping) which will give you good read and write performance but no safety. Or RAID 1 (mirrored), which will give you good read performance, but poor write performance. RAID 0 and 1 are the least expensive to implement and can be done in software. RAID 5 will give you good read and write as well as safety, but takes a minimum of three disks. If you are using a hardware based RAID, then there typically is no write performance hit for mirrored drives. When striping is used (RAID 0 and 5), your data is split up into small chunks and spread across disks, so it's unlikely that you would get a hot disk. More disks will give you better performance. For optimal RAID setup you want to set the optimal stripe size. If you are dealing with large files, like graphics, you want to setup a large stripe size so that you can take advantage of read ahead settings on the drive/os. For databases, you probably want to have a small stripe size, but not smaller than the size of your largest record size. The optimal setup would be to have a stripe size that is the same size as your database record. In real life this isn't really feasible though. Striping is the easiest way to go and will give you very good performance. The other thing to watch out for is the performance of the card you have the drives hooked up to. Just like you wouldn't want to have a hot drive, you don't want to have a hot card. If you are using SCSI, you really wouldn't want to have any more that 6 drives hooked up to one card. Even if the card could theoretically handled the max output of the combined drives. There is addressing overhead, and protocol overhead, in SCSI that becomes more significant with the more drives you add. If you really want to get technical, the SCSI ID of a drive also has an affect on performance. But this is pretty minimal. If you are doing strictly mirroring, you want to have at least two cards and separate your drives between your cards so that your mirrored drives are on separate cards. That also gives you safety if a card fails. This used to be called duplexing, but I haven't heard that term used for storage in a while. Some SCSI cards do have more than one independent bus, this would also work for mirroring. On Thursday, October 3, 2002, at 04:26 PM, Gary Traffanstedt wrote: sql, query I have a dilemma and maybe you can help. I'm wanting to improve my disk performance and I'm wondering if I should go with Raid 10 or if I should simply mirror the drives so that I have redundancy and then put some of my tables on one drive and some on the other. Or the third option is to put the tables on one drive and the logs on another drive. Ultimately, what is going to give me the best performance? Should I use 3 drives (mirrored so 6 actual drives) and put half of the tables on drive 1, half on drive 2, and logs on drive 3? TIA, Gary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multi-table Join (WAS: Is there Examples and Documents on joiningtables?)
Josh Trutwin wrote: The first query averages about 0.085 seconds from the mysql prompt, the second about 0.075 seconds and the 3rd 0.065 seconds. Thanks for some great advice, this has been bugging me for a while! Its well appreciated to see examples of what works and what doesn't too; its nice to know how to get one's queries in an order that makes the MySQL optimizer's life easier. I'm trying to decide if there's some logical way to inform MySQL that it can reorder a series of JOINs any way it likes for best performance. At any rate, try ordering your JOINs in such a way as to eliminate the most rows as soon as possible, and see if that makes a difference; if your 'smallest tables first' query does in fact do that, then great. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (ad infinitum)
. If I may beg the List's tolerance for a while - The practice of putting responses before the (often wildly varied and prolix) text prompting that response means that those whose eyesight is not what it used to be, if indeed it yet remains, do not have to wait for aeons as speech software grinds grimly through all the original texts for the umpteenth time. I agree that the situation is not all that likely in this List but I would ask all our Gentle Readers to consider the point. Yooors, Iain. If I may digress for a moment (on topics not related to SQL), it seems that this relatively new (in Internet-age terms) trend of typing before the quoted previous message (brought on by MS and friends) is a large part of the problem; one can simply type away without realizing that there are ten pages of text below one's response. If one takes the time to scroll to below the text to which they are responding first, one realizes how much irrelevant quoted text there is. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock Some things cannot be learned quickly and time, which is all we have, must be paid heavily for their acquiring. E.Hemingway. This post scanned by Norton AV for viruses before despatch. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sending data problem
Hello,
I have a very serious problem.
I hope you can give me some advice, that would be very appreciated.
I have a mysql query like this
SELECT PartnerID,CampaignID,BrowserID,count(DISTINCT IP)
FROM tblData20020930
WHERE EventType='Impression' AND
PartnerID!=0
GROUP BY PartnerID,CampaignID,BrowserID
that takes forever to run. When I check the processlist
of mysql threads that query has status Sending data.
It says like that for hours.
The tblData20020930 has about 2,500,000 rows in it.
It has keys(indexes) set on PartnerID,CampaignID, BrowserID,EventType.
mysql desc tblData20020930;
++---+--+-+-
---+---+
| Field | Type | Null | Key |
Default| Extra |
++---+--+-+-
---+---+
| PartnerID | int(10) unsigned | | MUL | 0
| |
| AdNetworkID| int(10) unsigned | | MUL | 0
| |
| EtimeStamp | char(20) | | |
| |
| ReferringURL | char(200) | YES | | NULL
| |
| IP | char(15) | | |
| |
| RemoteHost | char(200) | | |
| |
| CampaignID | int(10) unsigned | | MUL | 0
| |
| CountryID | int(10) unsigned | | MUL | 0
| |
| OSID | int(10) unsigned | | MUL | 0
| |
| BrowserID | int(10) unsigned | | MUL | 0
| |
| TopLevelID | int(10) unsigned | | MUL | 0
| |
| EventType | enum('Click','Impression','Sale') | | MUL |
Impression | |
| UserType | enum('Partner','AdNetwork') | | MUL |
Partner| |
| ISPID | int(10) unsigned | | | 0
| |
| HODID | int(10) unsigned | | | 0
| |
| BannerLocation | char(5) | YES | | NULL
| |
++---+--+-+-
---+---+
16 rows in set (0.00 sec)
I dont understand why is stays in Sending data mode for so long.
Nobody else is accessing that table, it is not locked, the database and
client are on the same machine. I tried using mysql_use_result()
instead of mysql_store_result() for getting the result set with no
improvements.
The situation is critical... I'd appreciate if you could
provide any type of help/advice.
Regards,
BK
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[Fwd: Re: Re: Fwd: Re: OT: Reply-Adress in this list]
I hate this spam filter with a passion. Original Message Subject: Re: Re: Fwd: Re: OT: Reply-Adress in this list Date: Fri, 4 Oct 2002 17:25:26 +0200 From: [EMAIL PROTECTED] To: Michael T. Babcock [EMAIL PROTECTED] sql,query Niclas Hedhman wrote: I don't care which becomes industry standard, as long as the behaviours converge, reducing what I have to remember. This is somewhat true ... but I still care; even if the standard sways to the other side. In fifteen years of mailing lists, this has come up more and more in recent history. I'd like to know how many people have sent E-mails to their E-mail software authors asking for a 'reply to list' option in their mailers ... There is actually one good thing about replying to the list, instead of the individual; The list can manage to remove invalid email addresses and out-of-office replies (not that all lists do that, but could). My E-mail software also does those things for me ... again, you might want different E-mail software. I'm sure many people on this list (yourself included?) remember the number of that's because you use AOL responses AOL users got to their E-mail problem requests in the last years ... the same applies to MS products now (although you use KMail, not Outlook and its ill-formed compatriates). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multi-table Join (WAS: Is there Examples and Documents on joining tables?)
* Michael T. Babcock Its well appreciated to see examples of what works and what doesn't too; its nice to know how to get one's queries in an order that makes the MySQL optimizer's life easier. I'm trying to decide if there's some logical way to inform MySQL that it can reorder a series of JOINs any way it likes for best performance. AFAIK, mysql will try to optimize by doing the joins in the fastest possible way, regardless of what order they are listed in the select statement. There are some exceptions to this: a LEFT JOIN b and a STRAIGHT_JOIN b will always read a before b. At any rate, try ordering your JOINs in such a way as to eliminate the most rows as soon as possible, and see if that makes a difference; if your 'smallest tables first' query does in fact do that, then great. In my experience, the only way to force the order of the joins is to use STRAIGHT_JOIN, and even when you use STRAIGHT_JOIN, you don't always get what you think: SELECT * FROM a STRAIGHT_JOIN b,c,d,e LEFT JOIN f ON f.id=e.id The join order of this select might become for instance c,d,e,a,b,f. In other words, you have only forced b to be read after a, not a to be the first table to read... only if you replace all commas with STRAIGHT_JOIN(1), you get the order you dictate. (Normally not a good idea, the optimizer usually finds the best join order.) Another thing to be aware of: The distribution of data in your tables affect the join optimizer. Consider a multi-table join with this condition: WHERE ... firstname.name = 'Roger' and lastname.name = 'Baklund' ...and the same statement with this condition: WHERE ... firstname.name = 'Roger' and lastname.name = 'Olsen' (Olsen is a very common last name in Norway, Baklund is not). For the first statement, the optimizer may decide to read the 'lastname' table first, because there are relatively few occurences of Baklund, while there are many occurences of Roger. For the second statement, the 'firstname' table is read first, because there are many more occurences of Olsen in the lastname table compared to Roger in the firstname table. This optimization is normally a good thing, and it is lost if you use STRAIGHT_JOIN. (1) or use the STRAIGHT_JOIN option for the SELECT statement -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What order is a mysql query executed?
Hello there!
If I have a table with 100,000 records in and I have two columns in the
table, one an ID field (int) numbered 1 to 100,000 that is indexed etc. The
other field is a text field with say 100 words in each row.
What would produce the fastest search if I wanted to search for all entries
that had a id50,000 and contained the word 'CAT'.
If this produced a result of 25,000 records, would it be quicker to do
either:
SELECT * FROM table WHERE idfield 5 AND MATCH textfield AGAINST('CAT'
IN BOOLEAN MODE}
or
SELECT * FROM table WHERE MATCH textfield AGAINST('CAT' IN BOOLEAN MODE} AND
idfield 5
The reason I ask this is because it would obviously be quicker to do a MATCH
over 50,000 records rather than the complete 100,000 records.
Thanks!
Chris..
[query]
http://www.exchangeandmart.co.uk
IMPORTANT. Any views or opinions are solely those of the author and do not
necessarily represent those of United Business Media. The information
transmitted is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. If you
are not the intended recipient of this message, please do not read, copy,
use or disclose this communication and notify the sender immediately. It
should be noted that any review, retransmission, dissemination or other use
of, or taking of any action in reliance upon, this information by persons
or entities other than the intended recipient is prohibited.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
right. corereader is designed to query all platforms from an ms. windows frontend. Thanks John, I looked through your web site, it seems to me that corereader is microsoft based product. I'm on Linux RedHat 7.2. thanks [EMAIL PROTECTED] wrote: corereader will help you do a pointclick join of up to four tables. you can operate corereader without it, but reading the documentation is strongly recommended. Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
right. it also won't run on mainframes. :) corereader is designed to query all platforms from an ms. windows frontend. Thanks John, I looked through your web site, it seems to me that corereader is microsoft based product. I'm on Linux RedHat 7.2. thanks [EMAIL PROTECTED] wrote: corereader will help you do a pointclick join of up to four tables. you can operate corereader without it, but reading the documentation is strongly recommended. Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT...LIKE... bug with cyrillic
Description: I have a table with columns containing keywords in cyrillic. If I write i.e. select * from imgs where kw_1 like '%xx%' (xx are two cyrillic letters and kw_1 is the first keyword-column. The table contains 2666 rows.) MySQL returns a lot of words(396 rows) and all of them do not contain xx. The same problem - when select * from imgs where kw_1 like '%xx_' returns 272 rows all of them not containing xx but!!! one and the same word select * from imgs where kw_1 like '_xx%' returns 15 rows all of them not containing xx if I make selection like select * from imgs where kw_1 like '_xx_' returns 0 rows (this is correct) select * from imgs where kw_1 like 'xx' returns 0 rows (this is correct) and finally select * from imgs where kw_1 like '%xx' returns 52 rows of correct data There is no problem with three or more letter search, BUT there is something more I found interesting If I make a surch query and instead of the cyrillic xx(in the above examples it was the cyrillic correspondence of the latin 'vo') we use doubled latin vowels - double a,e,i,o,u: select * from imgs where kw_1 like '%aa%' 115 rows select * from imgs where kw_1 like '%ee%' 288 rows select * from imgs where kw_1 like '%ii%' 413 rows select * from imgs where kw_1 like '%oo%' 277 rows select * from imgs where kw_1 like '%uu%' 6 rows (one and the same word) I'd like to remind you that all of the keywords are written in cyrillic and there is no latin letter in them! MySQL returns 0 rows if we make it with double english consonants, i.e 'ww','rr','ss' and so on. Such is the case if we try a combination of vowel and consonant, i.e. '%qa%' or '%aq%'. If we try a comnbination of two different vowels the bug works and here are following four examples: select * from imgs where kw_1 like '%ae%' 361 rows select * from imgs where kw_1 like '%ea%' 514 rows select * from imgs where kw_1 like '%ei%' 317 rows select * from imgs where kw_1 like '%ie%' 208 rows I tryed with three vowels search: select * from imgs where kw_1 like '%ieo%' 1 row select * from imgs where kw_1 like '%oeo%' 167 rows select * from imgs where kw_1 like '%oea%' 34 rows select * from imgs where kw_1 like '%oii%' 0 rows select * from imgs where kw_1 like '%oai%' 0 rows select * from imgs where kw_1 like '%eai%' 306 rows select * from imgs where kw_1 like '%iae%' 3 rows Four vowels search: select * from imgs where kw_1 like '%eaio%' --- 25 rows and I think these examples are enough All these bugs are working on the other keyword columns in the same table. The columns are declared as varchar(20) and null is default although there are no null values but empty strings when needed. The table is MyISAM. In my database I have another MyISAM table which contains all of the keywords from table 'imgs'. I found the same bugs. I suggest to make such a table like mine. How-To-Repeat: create table dumi (kw_id int unsigned auto_increment primary key, duma varchar(20) not null, index slovar(duma)); on http://212.91.166.133/fotoged.php is a menu where you could view all the keywords from this table and a number wich describes how many time it is repeated. From the generated HTML-source you could copy and paste the keywords. The activities are described above. Fix: I don't know but I think the problem is somewhere in the ASCII support. And something that could be important if I use ORDER BY clause on a column which contains cyrillic letters it is not performing very well, I mean MySQL is showing a strange alphabetical order! Submitter-Id: Originator:root Organization: GED Ltd. Bulgaria MySQL support: none Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.41 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux inter 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CX\ X='c++' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 11 13:27 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1282588 Sep 4 2001 /lib/libc-2.2.4.so -rw-r--r--1 root root 27304836 Sep 4 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 4 2001 /usr/lib/libc.so Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --s\ ysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib
Fw: Three Tables. Please help
Pass Filter: sql,query Hello All, I have three tables in my database t1, t2, and t3. Each table has records that are the same or similar. I am trying to SELECT part number, part description, location, part cost, sell price and on hand quantity from these three tables. Each part number is the same in all three tables. The other fields are different. I would like to display each tables data sorted by item number. I am having trouble with my select statement. Thank you for your time and assistance. Sincerely, William Martell - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Create table if not exists from mysqldump?
On Thursday, Oct 3, 2002, at 20:41 America/Phoenix, John Kelly wrote: Hi, I am trying to use mysqldump to dump the structure of a single database and want the output to make create table commands that include the IF NOT EXISTS switch so that when the file is imported into an existing database it does not write over tables with the same name. Does anyone know if this is possible with mysqldump and if so what option achieves this? Thanks. add the option -n or --no-create-db. This will add IF NOT CREATE to the sql. -- Clayburn W. Juniel, III -- Effective Software Solutions Phone: (602) 326-7707Mobile: (602)326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.com -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Memory Limit
Hi, I'm having trouble in configuring the my.cnf for optimal condition on my server. My MySQL server (3.23.52max) is running on Solaris 8 - Sun Sparc 4 processors and 4 GB memory. and my.cnf is : - keybuffer : 768M - sortbuffer : 8M - recordbuffer : 8M previously is : - keybuffer : 1024M - sortbuffer : 12M - recordbuffer : 12M and with this configuration my server can't handle more than 1200 concurrent threads. The error message said Can't create more threads ... error 11 ... memory limit And my database condition is : - average number of records : 2 million per table (1GB size per table) - a lot of query with order by (I'm aware it's related to sortbuffer) - index almost created properly :D I know if I set my configuration to lower value than I had slow query performance. And I found with sortbuffer : 12M and recordbuffer : 12M ... my query performance is good. but I can't have more than 1200 concurrent threads :(( Please help me on this. What should I do ? Which value should I set bigger or lower ? And what is the calculation for the memory also Thank you ... thank you very much if you would like to help me :D -- Write clearly - don't be too clever. - The Elements of Programming Style (Kernighan Plaugher) MySQL 3.23.51 : up 2 days, Queries : 340.607 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan 12790 Phone: +62 21 79199577 - HP: +62 8158787286 - Web: http://www.1rstwap.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Three Tables. Please help
In which tables of the three that you mention are the fields: part number, part description, location, part cost, sell price and on hand quantity What is the structure of each or the tables. -Original Message- From: William Martell [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 8:34 AM To: MySQL Main List Subject: Fw: Three Tables. Please help Pass Filter: sql,query Hello All, I have three tables in my database t1, t2, and t3. Each table has records that are the same or similar. I am trying to SELECT part number, part description, location, part cost, sell price and on hand quantity from these three tables. Each part number is the same in all three tables. The other fields are different. I would like to display each tables data sorted by item number. I am having trouble with my select statement. Thank you for your time and assistance. Sincerely, William Martell - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: raid vs splitting the database
Brent Baisley wrote: Well, there is the ideal setup, which requires intimate knowledge of the database, lots of disks and extra administration. And then there is the easy setup. Ideally you don't want to have any hot disks which will cause contention. This requires you to place your busy tables (read or write) on separate disks so that one drive doesn't get most of the activity. You really only need to do this for your large tables. But as your system grows, it can be difficult to do this manual tuning. I don't recommend this on smaller systems and would really only recommend it on systems where the OS allows you to expand file systems or migrate them while they are in use. I know AIX can do this and I'm sure Solaris (with Veritas) and a few others can. The easy option, and the one I would recommend, is setting up a RAID using something like RAID 0 (striping) which will give you good read and write performance but no safety. Or RAID 1 (mirrored), which will give you good read performance, but poor write performance. RAID 0 and 1 are the least expensive to implement and can be done in software. RAID 5 will give you good read and write as well as safety, but takes a minimum of three disks. If you are using a hardware based RAID, then there typically is no write performance hit for mirrored drives. When striping is used (RAID 0 and 5), your data is split up into small chunks and spread across disks, so it's unlikely that you would get a hot disk. More disks will give you better performance. For optimal RAID setup you want to set the optimal stripe size. If you are dealing with large files, like graphics, you want to setup a large stripe size so that you can take advantage of read ahead settings on the drive/os. For databases, you probably want to have a small stripe size, but not smaller than the size of your largest record size. The optimal setup would be to have a stripe size that is the same size as your database record. In real life this isn't really feasible though. Striping is the easiest way to go and will give you very good performance. The other thing to watch out for is the performance of the card you have the drives hooked up to. Just like you wouldn't want to have a hot drive, you don't want to have a hot card. If you are using SCSI, you really wouldn't want to have any more that 6 drives hooked up to one card. Even if the card could theoretically handled the max output of the combined drives. There is addressing overhead, and protocol overhead, in SCSI that becomes more significant with the more drives you add. If you really want to get technical, the SCSI ID of a drive also has an affect on performance. But this is pretty minimal. If you are doing strictly mirroring, you want to have at least two cards and separate your drives between your cards so that your mirrored drives are on separate cards. That also gives you safety if a card fails. This used to be called duplexing, but I haven't heard that term used for storage in a while. Some SCSI cards do have more than one independent bus, this would also work for mirroring. On Thursday, October 3, 2002, at 04:26 PM, Gary Traffanstedt wrote: sql, query I have a dilemma and maybe you can help. I'm wanting to improve my disk performance and I'm wondering if I should go with Raid 10 or if I should simply mirror the drives so that I have redundancy and then put some of my tables on one drive and some on the other. Or the third option is to put the tables on one drive and the logs on another drive. Ultimately, what is going to give me the best performance? Should I use 3 drives (mirrored so 6 actual drives) and put half of the tables on drive 1, half on drive 2, and logs on drive 3? TIA, Gary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Brent, You mentioned placing the busy tables on seperate disks. I didn't think in mysql that you could specify where the datafiles foreach tablelive. I know you could symlink (linux/unix) the files, but I remember seeing something about
recover innodb using slave datafiles
I have a few questions regarding recovering an innodb tablespace/datafile. Here's the situation. I lose a drive on my master database that contained an innodb datafile. I then shut the database down and umount that drive. What would I need to copy from the slave database in order to bring the master database backup? I know I'd need to copy all innodb datafiles from the slave to the master but what else would I have to do? Will the database complain about the new datafiles if things were not shutdown correctly? 99% of the transactions on the innodb tables are done by software which only inserts/updates one row at a time and they are immediate commits, so I'm not worried about losing transactions. I know I could run hot backups on the master for innodb tablespace, but I'd need to store them across the network so I'm guessing it would be quicker to just copy the slave datafiles and start the master database than it would be to copy the backups and then run the logs against them. mysql query Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Truncated Text data Type
Hi List, I am new to mysql so I ask for your patience... 1 - I am trying to load a text file (with mysql-front) to a table that contains a TEXTdata type field. Despite of the documentation (TEXT = 2^16 bytes), after the load of the file that field is truncate to 51 characters. On the text file I have some entries with more than 800 characters. 2 - I am trying to load the same file but now with LOAD DATA INFILE at a term session. I am connect as root, I call mysql but when I execute the command, I receive an Permission Denied Error (13) saying that I cannot stat the text file. I tried to change the owner and the permissions of the file but nothing works. Could someone help me? Thanks in advance Oswaldo Castro - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP -- 4.04-beta does not compile
With gcc 3.2, I get this: g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local\ -DDATADIR=\/usr/local/var\ -DSHAREDIR=\/usr/local/share/mysql\ -DHAVE_CONFIG_H -I../innobase/include -I./../include -I./../regex-I. -I../include -I. -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -c sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX*, unsigned int, bool)': sql_lex.cc:171: `get_hash_symbol' undeclared (first use this function) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: Three Tables. Please help
William Martell wrote: rice and on hand quantity from these three tables. Each part number is the same in all three tables. The other fields are different. I would like to display each tables data sorted by item number. I am having trouble with my select statement. SQL: select * from t1, t2, t3 where t1.partnum = t2.partnum and t1.partnum = t3.partnum; I highly suggest reworking your data structures at some point in the future though. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: Create table if not exists from mysqldump?
Begin forwarded message: From: Clayburn W. Juniel, III [EMAIL PROTECTED] Date: Fri Oct 4, 2002 10:24:06 America/Phoenix To: Clayburn W. Juniel, III [EMAIL PROTECTED] Subject: Re: Create table if not exists from mysqldump? On Friday, Oct 4, 2002, at 08:48 America/Phoenix, Clayburn W. Juniel, III wrote: On Thursday, Oct 3, 2002, at 20:41 America/Phoenix, John Kelly wrote: Hi, I am trying to use mysqldump to dump the structure of a single database and want the output to make create table commands that include the IF NOT EXISTS switch so that when the file is imported into an existing database it does not write over tables with the same name. Does anyone know if this is possible with mysqldump and if so what option achieves this? Thanks. add the option -n or --no-create-db. This will add IF NOT CREATE to the sql. Oops! That's not correct. That only works on creating the database not the table. But since this is only a text file, what you can do is use whatever program you have available and change every occurrence of CREATE TABLE to CREATE TABLE IF NOT EXISTS. If someone sees a reason why this won't work, please let me know. -- Clayburn W. Juniel, III -- Effective Software Solutions Phone: (602) 326-7707Mobile: (602)326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.com -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Search
Hi all, I'm fairly new to MySQL and PHP, but have been reading a few books and experimenting a bit. I've created a php application that will allow me to view all the fields in my Database, but have not been able to correctly build a search function in it. Here's a little background on what I am trying to do: Build a very simple script, that will allow me to serach a particular collum. I don't want it to display the collum if it comes back with a hit, all I want is something like hit = available no hit = not available If need be, I can paste some of the script I've been working on, but if someone's already created something of the sort, and would be willing to share, it would be greatly appriciated. Thanks, Scott Miller - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Search
Greetings... I am developing an application on MySQL using the MySQL API's. I have .sql file with 1 insert statement. I want to run all the queries in one shot. mysql_real_query() allows me to execute only one query at a time ? So what do you think is the best approach to execute all the queries in the most optimed way. Even the binary mysql allows to do that. How they do it? Sorry, I have not gone through the code of that software, due to constraint in time. Any help will be appreciated ? Greetings Insane - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update multiple data sets
Hi everyone! I need to run an update query on 400 sets of data. Being given a list of 400 names with 2 email addresses (company and private) each, the task is to update all people who have an old email address (either company or private). So here are my questions: 1.Is there any way I can load a text file containing the names into my my query? 2. How do I have to build the update statement itself? Can I use some kind of variable? Thanks for your help in advance SVen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Starting someone else's mysqld
I'm new to this list. I hope I don't break any rules, but here goes. I'm just getting on linux box that someone else set up with mysql 3.23. That person unfortunately is back in school and hasn't yet answered my questions. As if his exams are more important than my questions, imagine that. I don't see any daemons running nor can I seem to start any. If I try to run /usr/bin/safe_mysqld, I get the following: Starting mysqld daemon with databases from /var/lib/mysql mysqld ended. Doing 'ps -ef | grep mysql' only shows this grep. How can I either remove this installation so I can reinstall the latest version from scratch, or get mysqld started with this installation? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Starting someone else's mysqld
On Fri, 4 Oct 2002, Cohan, Sean wrote: How can I either remove this installation so I can reinstall the latest version from scratch, or get mysqld started with this installation? Find the var directory for the mysql installation. There will be log files, typically machine.name.err -- Sapere aude My mind not only wanders, it sometimes leaves completely. Never attribute to malice that which can be adequately explained by stupidity. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Additional Information: Replication issue in 4.0.4
On Thu, Oct 03, 2002 at 06:39:26PM -0500, Shane Allen wrote: We have a 4.0.4-beta master and slave. The slaving process starts correctly, but randomly (and frequently) has issues. When the server starts, it will slave, but eventually will hit one of two conditions: - Duplicate key insert error (we re-sync'd the slave by hand during the install, copying databases by hand, etc, so they're known good) - Signal 8. The database will hit Signal 8, die, and be restarted. Immediately upon restarting, it dies, and restarts, ad naseum. I upgraded in testing to 4.0.4 again, built with debugging enabled, and got a slightly different result. This time, it eventually hit a query and the slaving stopped. This time the error was: 021004 12:57:00 Slave: error 'Incorrect key file for table: 'listing_text'. Try to repair it' on query 'UPDATE sell.listing_text SET description='blah' WHERE listing_id=221112', error_code=1034 So, I issued `mysqlcheck -r sell listing_text`, and eventually Lost connection to MySQL server during query. Inspection of the error log yields the following: mysqld: mf_iocache.c:1106: _flush_io_cache: Assertion `info-end_of_file == my_tell(info-file,(myf) (0))' failed. Number of processes running now: 1 mysqld process hanging, pid 790 - killed presumably the debugging code that is built in is what is preventing the signal 8 behaviour that I experienced last night. On the master database I have been able to issue successful repair statements several timesi against this table, and the databases that I am attempting to repair have been copied over from the master, so I'm not sure what is causing the repairs to fail... -- Shane Allen [EMAIL PROTECTED] sell.com : Buy Sell Anything http://www.sell.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Create table if not exists from mysqldump?
Thanks for all the responses on how to import a dump file that will not write over existing tables and not stop with an error on existing tables - in other words just add missing tables. As Victoria pointed out, there is no option in mysqldump to add the appropriate create table option [IF NOT EXISTS] but one can add the --force option when importing the dump file back in with mysql. This causes mysql to ignore the error generated when it comes across a table that already exists and it will continue on adding any tables that are missing. Without it, mysql will stop on the first table that already exists with an error. An alternative solution is running a script on the dump file to replace all occurrences of CREATE TABLE with CREATE TABLE IF NOT EXISTS as suggested by Clayburn. Thanks again! John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Starting someone else's mysqld
Two things. I see the following in one of the mysqld.log files: 021004 14:41:05 mysqld started 021004 14:41:05 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021004 14:41:05 mysqld ended We have red hat 2.4.7-10. When I tried to install a later version of mysql, I get a bunch of messages like: ... conflicts with file from package mysql-3.23.41-1 So i'm not sure if that is succeeding. Thanks. -Original Message- From: Eric Lamendola [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 3:09 PM To: Cohan, Sean Subject: Re: Starting someone else's mysqld Hey, There are a lot of things you can do to try to get it started. What version of Linux are you using? If you are using Red Hat, you can simply download and install the latest RPMs. The reason that Mysql is ending is because usually you have to start MySQL as a user with permissions to start it. You can always look in the error log to check to see why it didnt start. It will generally give you information like, it couldnt find the databases, or it didnt have sufficient memory or permissions. Also, I prefer to use the /etc/rc.d/init.d/mysql start command to get things rolling. Ummm... Also make sure there isnt any information in the /etc/my.cnf or /%path/to/mysql/my.cnf files that would stop it from starting. Hope this helps At 02:39 PM 10/4/02 -0400, Cohan, Sean wrote: I'm new to this list. I hope I don't break any rules, but here goes. I'm just getting on linux box that someone else set up with mysql 3.23. That person unfortunately is back in school and hasn't yet answered my questions. As if his exams are more important than my questions, imagine that. I don't see any daemons running nor can I seem to start any. If I try to run /usr/bin/safe_mysqld, I get the following: Starting mysqld daemon with databases from /var/lib/mysql mysqld ended. Doing 'ps -ef | grep mysql' only shows this grep. How can I either remove this installation so I can reinstall the latest version from scratch, or get mysqld started with this installation? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Query Help
Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. Is this making sense? David McInnis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query help
David McInnis wrote: select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. Post the basic schema for the database tables in question and it would help. It doesn't sound like you need a subselect, but that may just be a misinterpretation. PS, I'd like some stats from the mysql list admins as to how many messages a day are blocked because of the requirement to include SQL or QUERY in the message and how many of those are _actually_ spam ... just watch for one day ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question Regarding Update Year statement
Hello All, I'm looking for a date function that will allow me to update only the year portion of a field to a particular year, in this case, 2002. I've tried UPDATE [table] SET YEAR([field]) = '2002' WHERE [field] 2002 but this gives me an error. The MySQL documentation only seems to cover using SELECT with the YEAR function. What is the correct syntax for doing this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query help
Here is my table schema. Thanks for your help. David ORDERS +--+--- | Field| Type +--+--- | id | int(10) unsigned zerofill | clientid | int(11) | refid| int(11) | sessionid| varchar(40) | staffid | int(11) | staffcomp| date | auth_code| varchar(6) | trans_id | varchar(30) | dt_trans | datetime | job_target | smallint(6) | exp_level| smallint(6) | careerchg| char(1) | subtotal | float(10,2) | tax | float(10,2) | rushfee | float(10,2) | total| float(10,2) | dt_create| datetime | dt_timestamp | datetime | dt_update| date +--+-- ORDERDETAIL ++--- | Field | Type ++--- | id | int(10) unsigned zerofill | orderid| int(11) | itemid | varchar(10) | qty| int(11) | unitcost | float(10,2) | extended | float(10,2) | grp_id | varchar(10) | grp_pwd| varchar(10) ++--- -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 1:00 PM To: '[EMAIL PROTECTED]' Subject: Re: Query help David McInnis wrote: select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. Post the basic schema for the database tables in question and it would help. It doesn't sound like you need a subselect, but that may just be a misinterpretation. PS, I'd like some stats from the mysql list admins as to how many messages a day are blocked because of the requirement to include SQL or QUERY in the message and how many of those are _actually_ spam ... just watch for one day ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Help
So you want a SQL QUERY something like: SELECT ORDERDETAIL.id as detailid, qty, unitcost, unitcost * qty as extended from ORDERDETAIL LEFT JOIN ORDERS ON orderid = ORDERS.id WHERE ... Right? What's the problem you have with tax requests that you hinted at last time? David McInnis wrote: ORDERS +--+--- | Field| Type +--+--- | id | int(10) unsigned zerofill | subtotal | float(10,2) | tax | float(10,2) | rushfee | float(10,2) | total| float(10,2) ORDERDETAIL ++--- | Field | Type ++--- | id | int(10) unsigned zerofill | orderid| int(11) | qty| int(11) | unitcost | float(10,2) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query help
Well, what I need to do is something like this: select sum(tax) from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) The problem is this that when I run this sql query: select orderdetail.itemid, orderdetail.id as odid, orders.id, orders.tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I get something like this: Itemid OdidOrderid Tax 1 13 1 16.71 2 14 1 16.71 3 15 1 16.71 1 16 2 10.00 1 17 3 15.00 Which leads me to believe that if I take a sum(tax) it will sum all three. I only want one record, not all three. David -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 2:19 PM To: David McInnis Cc: [EMAIL PROTECTED] Subject: Re: Query help So you want something like: SELECT ORDERDETAIL.id as detailid, qty, unitcost, unitcost * qty as extended from ORDERDETAIL LEFT JOIN ORDERS ON orderid = ORDERS.id; Right? What's the problem you have with tax requests that you hinted at last time? David McInnis wrote: ORDERS +--+--- | Field| Type +--+--- | id | int(10) unsigned zerofill | subtotal | float(10,2) | tax | float(10,2) | rushfee | float(10,2) | total| float(10,2) ORDERDETAIL ++--- | Field | Type ++--- | id | int(10) unsigned zerofill | orderid| int(11) | qty| int(11) | unitcost | float(10,2) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query Help
On Fri, Oct 04, 2002 at 12:36:30PM -0700, David McInnis wrote: Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. I'm not certain if I understand what you're after... If you want total tax per order, try select sum(tax) as tax, orders.id from orders, orderdetail where orders.id = orderdetail.orderid and productid in (1,2,3) group by orderid; Otherwise, please clarify what you want in your desired result set. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How inefficient are database sepcified selects? Ex: SELECT db.tablename.fieldname...
I have a Mysql DB schema which consists of some core tables in a database 'core'. Then I have about 22 separate databases which have between 20 - 150 tables a piece. I normally will stay within one of the daughter databases but occasionally have to mine data from one of its sisters. I would rather not change the currently selected database via a 'USE dbname' command but would rather use the dbname in the select. Example: SELECT alias.id FROM dbname.tablename AS alias WHERE... My question is how inefficient is that? I am concerned with switching databases frequently because I don't want to use hundreds of file descriptors - I have modified the default variables in my.cnf and increased the ulimit in safe_mysqd (using FreeBSD). However, I simply don't want to be using tons of file descriptors that will only be interfaced with once. -Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
myodbc prolbems between linux windows
I had gotten some feedback on this, but I still do not have enough information on this to resolve it. the initial thoughts comments I got from people the believed it was a connectivity problem. this is on an internal network. the linux box has shared drives I use all day everyday with no problems, telnet between the 2, ftp, web applications. there is no firewall or restrictions between the 2 computers, and I can connect to mysql on the localhost (which is the linux box). I am using the myodbc driver. I am completely stuck on this, and I cannot figure out how to resolve this. I have mysql on a linux box (IP 192.168.0.1), and I've got a windows 2000 box that I am trying to connect to with with odbc. I am getting the following error message [MySQL AB][MyODBC] ERROR : Can't connect to MySQL server on 192.168.0.1 (10061) Jeff Bluemel Jeff Bluemel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Linux GUIDs
Does MySQL support GUIDs? Is there any code around that is equivalent to the built-in M$-SQL function NewID(), which returns a GUID? TIA, Arthur - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Linux GUIDs
In the last episode (Oct 04), Arthur Fuller said: Does MySQL support GUIDs? Is there any code around that is equivalent to the built-in M$-SQL function NewID(), which returns a GUID? Providing GUIDs is usually the job of the OS, and there really isn't much of a standard for it yet. One solution would be to write a UDF that either calls a native uuidgen() function, or shells out and runs uuidgen/makeuuid, and returns the result. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Starting someone else's mysqld
I've uninstalled the previous version and installed the latest version 3.23.52.1. safe_mysqld is now running. I'm making progress but getting bogged down in the littlest things. I feel ignorant, but I know once I get over the first hump, I'm well on my way. I'm trying to change the password following the instruction given when running mysql_install_db: mysql_install_db -u root -h$hostname password 'newpassword', but I get the following. mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: root@localhost (Using password: NO)' help. Also, what is the mysql_installation_directory if I ran the rpm and didn't specify one? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A little hair-pulling can be a good thing (or not)
I'm encountering a situation with MySQL that's really driving me crazy and I have been unable to locate a solution. I'm hoping that someone on the mailing list can provide some guidance... I'm attempting to use a piece of php software (phpBB) which is talking to a dedicated MySQL server via TCP. The MySQL install is the Mandrake 8.2 default (3.23.47). Okay, here's the error I receive using their debug mode... Couldn't obtain forum list DEBUG MODE SQL Error : 1 Can't create/write to file '/root/tmp/#sql1f77_259_0.MYI' (Errcode: 13) SELECT f.* FROM phpbb_forums f, phpbb_categories c WHERE c.cat_id = f.cat_id ORDER BY c.cat_order ASC, f.forum_order ASC Line : 166 File : /var/www/html/phpBB2/admin/admin_forumauth.php Now, before ANYONE mentions rebuilding/repairing/re-installing, etc. as I've seen in posts on the phpBB site, here's the deal... 1. I have done all of the above; 2. Upon closer examination, I find that what is happening is that MySQL wants to write to /root and /root/tmp directories. Problem is, the permissions of the directories are set to root:root. Since MySQL isn't running as root, it is unable to write to those directories. It seemed an easy solution: chown /root to root:mysql -- and the same for /root/tmp -- then chmod the directories to 770. Woo Hoo, works! (temporarily - please drop the drumsticks at this point...) Seems that at unknown times of the day, RH/Mandrake comes through and cleans up any crap one might create - including changing the ownership of /root. Lo and behold, I come back to the development system and find that I can no longer access - again - since the system reset the permissions! Obviously, it is a bad idea to run MySQL as root. At the same time, one can't really (shouldn't?) change the /root ownership/permissions... Needless to say, each time I reset permissions on the directories, all works according to the plan. Each time the permissions are reset, the system churns out vomitus, like after eating stale nachos and cigarette butts. I've been told (we've all heard that before) that I should be looking at my.cfg - however, I can't find this file to save my life (does it really exist???) Needless to say, suggestions are more than welcome. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Question Regarding Update Year statement
Hi!, From: W [mailto:[EMAIL PROTECTED]] Sent: 04 October 2002 15:11 To: [EMAIL PROTECTED] Subject: Question Regarding Update Year statement I'm looking for a date function that will allow me to update only the year portion of a field to a particular year, in this case, 2002. I've tried UPDATE [table] SET YEAR([field]) = '2002' WHERE [field] 2002 but this gives me an error. The MySQL documentation only seems to cover using SELECT with the YEAR function. What is the correct syntax for doing this? You might have more luck with something like this... UPDATE [table] SET [field] = DATE_FORMAT([field],'2002-%m-%d') WHERE YEAR([field]) 2002; Of course I might be wrong, but it should work... HTH, William. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Memory Limit
-Original Message- From: Dicky Wahyu Purnomo [mailto:[EMAIL PROTECTED]] Subject: Memory Limit And what is the calculation for the memory also The formula you want is (this does not account for InnoDB buffers either): key_buffer_size + (record_buffer + sort_buffer)*max_connections 768M +16M * 1,200 = 19,968M That's way more than 4gb of ram. Do you mean 120 connections? That would set you right underneath the magic 2GB which has been plauging me as well on Intel. (4xXeon, 4gb ram) I know if I set my configuration to lower value than I had slow query performance. And I found with sortbuffer : 12M and recordbuffer : 12M ... my query performance is good. but I can't have more than 1200 concurrent threads :(( I've been able to increase our connections to above 600 by using 1M sort and record buffers and key_buffer sizes of only 256M. Of course, with settings like these, at 600 connections it's dog slow and useless to connect that many threads anyway. Under Linux, any maybe Solaris as well, the problem lies in the fact that MySQL is one application with multiple threads. Thus all threads share a single memory 'ceiling'. A fork-based model wouldn't have this problem. Or I don't think it would anyway. Not much help, I know, but if you do find a way around this let me know. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where are db files ??
HELP ! I'm a newbie to both Linux mySQL. I have a simple mySQL sample data base running on a Red Hat 7.3 machine. It was already setup when I got it. Now I want to find the data base files that mySQL creates so I can back them up... and for my education. I can't seem to get Linux to search the entire hard disk... and I'm really not sure of the data base file names... it comes up in a demo web page. 1 - Where does mySQL normally store it's data base files? 2 - Can you change the default directory for a data base? 3 - How do you get Linux to search the entire disk for a file? Thanks for any help... tmb __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Where are db files ??
On Fri, 2002-10-04 at 20:38, tmb wrote: HELP ! I'm a newbie to both Linux mySQL. I have a simple mySQL sample data base running on a Red Hat 7.3 machine. It was already setup when I got it. Now I want to find the data base files that mySQL creates so I can back them up... and for my education. I can't seem to get Linux to search the entire hard disk... and I'm really not sure of the data base file names... it comes up in a demo web page. 1 - Where does mySQL normally store it's data base files? 2 - Can you change the default directory for a data base? 3 - How do you get Linux to search the entire disk for a file? Thanks for any help... tmb look in /var/lib/mysql that's where they are placed usually by rpm. you have to be root to to this I think though. John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't start mysql on Solaris, can't find libstdc++.so.4
Installed mysql-3.23.43-pkg on Solaris Sparc 8. Get the missing library error on everything I try. Anyone know what to do? Here's the error log entry for the command ./usr/local/bin/safe_mysqld --user=mysql 021004 21:41:12 mysqld started ld.so.1: /usr/local/mysql/libexec/mysqld: fatal: libstdc++.so.4: open failed: No such file or directory 021004 21:41:12 mysqld ended -- Randy Perry sysTame Mac Consulting/Sales http://www.systame.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Where are db files ??
1 - Where does mySQL normally store it's data base files? * From what I have seen, it depends on how you install it. You can check in the my.ini file and that will let you know where it is. 2 - Can you change the default directory for a data base? * Yes, you can change this in the my.ini file. 3 - How do you get Linux to search the entire disk for a file? * Do you have any type of shell over linux? They usually offer some sort of search. You might want to look at installing webmin, it makes it easy to control a lot of things on your linux bot via a web based control center. -Original Message- From: tmb [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 7:39 PM To: [EMAIL PROTECTED] Subject: Where are db files ?? HELP ! I'm a newbie to both Linux mySQL. I have a simple mySQL sample data base running on a Red Hat 7.3 machine. It was already setup when I got it. Now I want to find the data base files that mySQL creates so I can back them up... and for my education. I can't seem to get Linux to search the entire hard disk... and I'm really not sure of the data base file names... it comes up in a demo web page. 1 - Where does mySQL normally store it's data base files? 2 - Can you change the default directory for a data base? 3 - How do you get Linux to search the entire disk for a file? Thanks for any help... tmb __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Where are db files ??
At 23:46 2002-10-04, Chris Couture wrote: 1 - Where does mySQL normally store it's data base files? * From what I have seen, it depends on how you install it. You can check in the my.ini file and that will let you know where it is. On Linux (which I believe is the one in question) it normally goes in /var/lib/mysql/, but... 2 - Can you change the default directory for a data base? * Yes, you can change this in the my.ini file. Yes 3 - How do you get Linux to search the entire disk for a file? * Do you have any type of shell over linux? They usually offer some sort of search. You might want to look at installing webmin, it makes it easy to control a lot of things on your linux bot via a web based control center. type man find (without the quotes!) and it will tell You more than You wish to know about finding files on Your system. The quick and dirty on finding a file named kkk.ttt is: prompt# find / -name kkk.ttt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with LOAD DATA LOCAL INFILE
1)Is it possible to enable LOAD DATA LOCAL INFILE from the configuration
file?
My.ini file is below:
[mysqld]
basedir=C:/mysql
datadir=C:/mysql/data
enable-local-infile
local_infile=1
When I perform the LOAD DATA LOCAL INFILE 'file_name.txt' INTO... command
from a client application using MyOBDC I receive the error message 1148
('The used command is not allowed with this MySQL version').
Where is the mistake?
The server version is 4.0.2.-alpha.
2)Other problems appears when I use the 3.23.47 My SQL version.
2.1.
In that case LOAD DATA LOCAL INFILE 'file_name.txt' INTO... works but
'file_name.txt' is
loaded just if it reside on the server. But notice that I want to load the
file ('file_name.txt') from the client machine.
Again, what is wrong?
2.2.
I tried another approach for this problem and I have performed from the
client machine a SELECT * FROM... INTO OUTFILE 'file_name.txt' command. Now,
everything goes well and LOAD DATA LOCAL INFILE 'file_name.txt' INTO...
works fine. It seems my troubles are finished, but... If I run again the
same command, another new file with the same name cannot be created (because
'file_name.txt' already exists). How can I remove the older 'file_name.txt',
doing this from the client side and using MyODBC?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql syntax help
I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
