Converting Column to SET
I'm converting a database where multiple selections from a valuelist were stored delimited within a single field with a \r. I imported this data into MySQL 3.23.46 and imported the valuelist selections as is into a VARCHAR field large enough to hold all selections. I'm fine storing selection values to the MySQL field using the existing middleware valuelist routines. However, I now find myself in a pickle when it comes to searching that field for combinations of values. The previous database compared each value in my search string with each value as an independent contains. So, if I had red, blue, green, black as selection choices, and a field had red \r green \r black, (spaces are for readability only) I could search for red \r black and get a list of found records. In MySQL I can search with a single selection red and specify a 'contains' operator with my middleware (Lasso Pro 5) and get a list of records. However, I cannot include more than one option in my search string. It's obvious (and understandable) that MySQL is searching with a literal red \r black string, and finds no matches. Questions: 1 - is there a way with some SQL expression to search the existing field and \r delimited data with a string such as red \r black to reproduce the same search I had before? (I know this will not be as fast as SET). 2 - if I convert this column to a SET and define my choices, will MySQL automatically recognize the existing \r delimited values in the fields and properly convert the field data to its own preferred format for SET fields? 2a - do I first have to manually convert all \r instances to commas or something else? 2b - do I have to export the data, redefine the column as SET, then reimport it into this column? 3 - what happens to field data when the SET selections are redefined? The reason behind the previous setup was to allow easy changes to the selection list and isolate the definition of the list from the database. I know SET is faster, but these are really small databases. Thanks. -- Greg Willits -- [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: memcpu usage (mysqld under Linux RH 7.2)
Mikhail V.Soloviev [EMAIL PROTECTED] wrote: Hello, I have a problem with mysqld (3.32.46 under Red Hat Linux 7.2, pIII-800 512K RAM). It starts and works fine until some time is passed, then it starts eating my memory and CPU. Try to run binaries from MySQL site - gcc 2.96 can be the problem here -- Dimitry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 about displaying of 'timestamp' field
I've added a timestamp field to a table and when viewing the data stored in the field it looks like this . 19970523091528 .. but when I view the data in Access or Macromedia Dreamweaver it looks like this 05/23/1997 09:15:28 .. is there any way to make Access/Dreamweaver display the timstamp field using the first format ?. Thanks. Clive Arnold - IT Co-ordinator White and Bowker Solicitors. *** Confidentiality: This e-mail message and any attachments may contain confidential and/or legally privileged information. It is intended for the addressee only and if you are not the intended recipient you should not copy or use the contents nor disclose them to anybody else. In such a case please notify the sender by return e-mail immediately and delete this message and its attachments together with all copies in whatever form. Security: In the case of a client contacting White and Bowker by e-mail, White and Bowker will assume that they have the clients' implied consent to communicate (with the client) using e-mail, in the clients full knowledge that e-mail is not a secure mode of communication. Business Use: Any views or opinions expressed in this message (and any attachments) that do not relate to the official business of White Bowker are neither given nor endorsed by it. Viruses: This e-mail and any attachments has been checked for viruses using Guinevere but White Bowker accepts no responsibility for any viruses not revealed by such check and in accordance with good computing practice recipients should ensure that they are actually virus free. In case of any query relating to this message or its content please contact the Sender or the System Manager by return e-mail or telephone +44 (0)1962 80 or by post at White Bowker 19 St. Peter Street Winchester SO23 8BU United Kingdom *** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question about displaying of 'timestamp' field
Clive, I've added a timestamp field to a table and when viewing the data stored in the field it looks like this . 19970523091528 .. but when I view the data in Access or Macromedia Dreamweaver it looks like this 05/23/1997 09:15:28 .. is there any way to make Access/Dreamweaver display the timstamp field using the first format ?. =Tell Access/Dreamweaver to display the timestamp field as an integer (not interpret it as a date). =Regards, =dn This msg will be considered spam until I add: MySQL!? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Windows installation
Hi Apols for being dense but I have basic problem with MySQL for Win98. I have downloaded and unzipped mysql-3.23.47-win-src.zip The next step is to do the setup.exe but there is no file called this. So how do I proceed? (There is a small zip program also on the download page, but this is 'not a valid archive' according to my WinZip). THANKS Geoff PS this is to run a small org site on multilingual web sites. __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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
mysql not using indexes if select * where a 5
Hi, I found that mysql doesn't used indexes if there is a in the where statement... for example mysql explain select * from testtable where id 5; +-+--+---+--+-+--+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+---++ | testtable | ALL | NULL | NULL |NULL | NULL | 13505 | where used | +-+--+---+--+-+--+---++ 1 row in set (0.00 sec) please note that id is the primary key for the table... any ideas on how to optimize this type of queries ? Nat -- Natalino Picone - [EMAIL PROTECTED] -- It's a horrible thing to be on top of the world and then to lose it and try to get it back. It's a whole lot harder the second time. -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Windows installation
Hi Geoff, Apols for being dense but I have basic problem with MySQL for Win98. Perhaps not dense, just baffled by 'new' terminology. You appear to have downloaded the source code. If you want to download MySQL and not have the extra, compile, step to negotiate, then find the *-win.zip option. In *nix-speak these downloadable/directly installable files are called binaries. Regards, =dn I have downloaded and unzipped mysql-3.23.47-win-src.zip The next step is to do the setup.exe but there is no file called this. So how do I proceed? (There is a small zip program also on the download page, but this is 'not a valid archive' according to my WinZip). THANKS Geoff PS this is to run a small org site on multilingual web sites. __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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: mysql not using indexes if select * where a 5
On Thursday, 21. February 2002 12:46, Natalino Picone wrote: Hi, I found that mysql doesn't used indexes if there is a in the where statement... for example mysql explain select * from testtable where id 5; It would only make sense to use an index, when you want to select the id field (SELECT id from testtable where id 5). Otherwise a table scan would be faster. Regards George - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrade problem from 3.22.27-2 - 3.23.37-8
Hi, I have the following problem: I had to install a complete new server with Suse 7.2 (MySql: 3.23.37-8). From the the old installation I only got the backup files (created with 'mysqldump database my_databases.sql') and the the database file (ISD, ISM, frm). My problem is: I can't read the backup file (mysql database backup-file.sql). Executing the command nothing happens. Using the restore table syntax only empty tables are created. Does anybody has an idea who to get the data from the old files in the database version. Greetings, Andreas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 PHP Development IDE
This off topic, but one of our programmers is interested in NuSphere's PHPEd development interface and I would liek some feed back from other users. His interest lies not so much in using for database table modeling, but for PHP and Perl development. I am well aware of the conflict a few months ago between MySQL and NuSphere, but I have never heard anything one way or the other about PHPEd. At nearly $500 per package, it is not a decision I wish to make without some feedback from the community. How does PHPEd stack up compared to other PHP IDE's? What experience (good or bad) has anyone had with it? Is there another product that is better? Any feed back (either to the list or me me privately) will be appreciated. Gerald Jensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Are there any other 'automatic' fields apart from timestamp ?.
Apart from timestamp are there any other fields that will automatically generate data for themselves when a new record is created ?. I'm having trouble using timestamp with Macromdia Dreamweaver so something that just generates a unique number for each record would be good e.g 1,2,3,4,5,6,7, ... Thanks. Clive Arnold - IT Co-ordinator White and Bowker Solicitors. *** Confidentiality: This e-mail message and any attachments may contain confidential and/or legally privileged information. It is intended for the addressee only and if you are not the intended recipient you should not copy or use the contents nor disclose them to anybody else. In such a case please notify the sender by return e-mail immediately and delete this message and its attachments together with all copies in whatever form. Security: In the case of a client contacting White and Bowker by e-mail, White and Bowker will assume that they have the clients' implied consent to communicate (with the client) using e-mail, in the clients full knowledge that e-mail is not a secure mode of communication. Business Use: Any views or opinions expressed in this message (and any attachments) that do not relate to the official business of White Bowker are neither given nor endorsed by it. Viruses: This e-mail and any attachments has been checked for viruses using Guinevere but White Bowker accepts no responsibility for any viruses not revealed by such check and in accordance with good computing practice recipients should ensure that they are actually virus free. In case of any query relating to this message or its content please contact the Sender or the System Manager by return e-mail or telephone +44 (0)1962 80 or by post at White Bowker 19 St. Peter Street Winchester SO23 8BU United Kingdom *** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 PHP Development IDE
His interest lies not so much in using for database table modeling, but for PHP and Perl development. How does PHPEd stack up compared to other PHP IDE's? What experience (good or bad) has anyone had with it? Is there another product that is better? I've just finished using Quanta+ for doing a fairly small PHP frontend to a MySQL database. I didn't choose this for any reason other than it got installed on the Suse-based laptop I was using. Got to say though, it's pretty slick (syntax highlighting, inbuilt docs, inbuilt preview, etc) - and best of all free from Sourceforge. However, since I'm now getting more into the web development, (heck I quite like PHP!), side of things I'm now thinking of spending the USD49.99 (CD version, download-only is USD10 cheaper) and getting the uprated version - Quanta Gold - from TheKompany. Certainly the feature list and screenshots look good. To me, USD500 sounds an awful lot of money for a 'mere' PHP/Perl editor. The cynic in me would wonder if it's not overkill. I used to do all my PHP and Perl stuff just using the appropriate modes in Xemacs which is free. Xemacs doesn't have the nice inbuilt docs and project handling that Quanta does though. Just my personal opinion above. Regards Bob Cross. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This message is confidential. It may also be privileged or protected by other legal rules. It is not an offer or acceptance of an offer, nor shall it form any part of a legally binding contract. If you have received this communication in error, please let us know by reply then destroy it. You should not use, print, copy the message or disclose its contents to anyone. E-mail is subject to possible data corruption, is not secure, and its content does not necessarily represent the opinion of this Company. No representation or warranty is made as to the accuracy or completeness of the information and no liability is accepted for any loss arising from its use. This e-mail and any attachments are not guaranteed to be free from so-called computer viruses. You should check for viruses before down-loading it to your computer equipment. This Company has no control over other websites to which there may be hypertext links and no liability is accepted in relation to those sites. This Company randomly monitors its e-mail system (including incoming e-mails) for operational purposes. Scottish Newcastle plc Registered in Scotland, Registered Number 16288 Registered Office: 33, Ellersly Road, Edinburgh, EH12 6HX * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Master/Slave reversal in Replication
Jorge, Wednesday, February 20, 2002, 7:44:50 PM, you wrote: JG Hello JG I'm new to MySQL, Unix and Replication. JG I would like to know if in a scenario where I have one master and JG several slaves I can convert one of the slaves to master in the event JG my master goes down. It will be added in v4.XX. Look at: http://www.mysql.com/doc/R/e/Replication_Features.html JG Once I can bring the master back up, how to I JG reverse the operation? What do you mean in reverse the operation? JG Does any one know how to make the switch or JG where I can read up on it? You can find more info in the manual at: http://www.mysql.com/doc/R/e/Replication.html JG I'm using FreeBSD with MySQL v4.0.1 alpha. JG Thanks. -- For technical support contracts, goto https://order.mysql.com/ 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
CHECK constraints
Neil, Thursday, February 21, 2002, 12:18:27 AM, you wrote: NZ Hello, NZ I searched the manual but it seems like MySQL does not support ANSI SQL NZ CHECK constraints. These are often used to check that rows inserted into NZ tables meet specified criteria. For example table T created as follows NZ CREATE TABLE T ( NZ X INT, NZ CHECK (X = 10) NZ ); Yes, you are right. CHECK clause do nothing. Look at: http://www.mysql.com/doc/C/R/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/ 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
mysql not using indexes if select * where a 5
Natalino, Thursday, February 21, 2002, 1:46:11 PM, you wrote: NP Hi, NP I found that mysql doesn't used indexes if there is a in the where NP statement... NP for example NP mysql explain select * from testtable where id 5; NP +-+--+---+--+-+--+---++ NP | table | type | possible_keys | key | key_len | ref | rows | Extra | NP +-+--+---+--+-+--+---++ NP | testtable | ALL | NULL | NULL |NULL | NULL | 13505 | where NP used | NP +-+--+---+--+-+--+---++ NP 1 row in set (0.00 sec) NP please note that id is the primary key for the table... It is not efficient in this case. You can read in the manual how MySQL uses indexes: http://www.mysql.com/doc/M/y/MySQL_indexes.html NP any ideas on how to optimize this type of queries ? NP Nat -- For technical support contracts, goto https://order.mysql.com/ 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
Windows installation
Geoff, Thursday, February 21, 2002, 1:33:47 PM, you wrote: GD Hi GD Apols for being dense but I have basic problem with GD MySQL for Win98. GD I have downloaded and unzipped GD mysql-3.23.47-win-src.zip GD The next step is to do the setup.exe but there is no GD file called this. So how do I proceed? It's a source distributive. You should compile it by yourself.:) If you want to have binary distributive, you should find mysql-3.23.47-win.zip or something like that at: http://www.mysql.com/downloads http://sourceforge.net/project/showfiles.php?group_id=47release_id=68039 GD (There is a small zip program also on the download GD page, but this is 'not a valid archive' according to GD my WinZip). GD THANKS GD Geoff -- For technical support contracts, goto https://order.mysql.com/ 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
Are there any other 'automatic' fields apart from timestamp ?.
Clive, Thursday, February 21, 2002, 3:22:47 PM, you wrote: CA Apart from timestamp are there any other fields that will CA automatically generate data for themselves when a new record is CA created ?. CA I'm having trouble using timestamp with Macromdia Dreamweaver so CA something that just generates a unique number for each record CA would be good e.g 1,2,3,4,5,6,7, ... What about AUTO_INCREMENT field or LAST_INSERT_ID() function? Look at: http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html http://www.mysql.com/doc/M/i/Miscellaneous_functions.html http://www.mysql.com/doc/G/e/Getting_unique_ID.html CA Thanks. CA Clive Arnold - IT Co-ordinator -- For technical support contracts, goto https://order.mysql.com/ 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
print out the datastructure of tables
Theo, Wednesday, February 20, 2002, 6:37:15 PM, you wrote: TVS Hello, TVS I am new with MySQL and have it running on Windws 2000 server and TVS programming with VC++ 6.0 TVS Now converting from SQL 7.0 to MySQL TVS I wonder if there is a way to print out the data structure of the TVS table in MySQL? You can put data structure into a text file. Then you can print it. Take a look at mysqldump: http://www.mysql.com/doc/m/y/mysqldump.html You can see table structure by using command: SHOW CREATE TABLE table_name; You can see table info by using command: DESCRIBE table_name; TVS Theo van Stratum TVS Albanet Ltd -- For technical support contracts, goto https://order.mysql.com/ 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: MySQL PHP Development IDE
This off topic, but one of our programmers is interested in NuSphere's PHPEd development interface and I would liek some feed back from other users. His interest lies not so much in using for database table modeling, but for PHP and Perl development. If he's not really that interested in the mysql integration than I would say that you could probably go with a different IDE. PHPed's db integration and built-in php debugger are about the only two things that set it apart from other IDE's. I am well aware of the conflict a few months ago between MySQL and NuSphere, but I have never heard anything one way or the other about PHPEd. At nearly $500 per package, it is not a decision I wish to make without some feedback from the community. How does PHPEd stack up compared to other PHP IDE's? What experience (good or bad) has anyone had with it? Is there another product that is better? Any feed back (either to the list or me me privately) will be appreciated. Before you make a commitment, you can apply for a trial version of PHPed. You have to give them real contact information, (I think I've gotten like 5 sales-pitch emails since I downloaded my free trial 3 months ago.) So you can give it a shot before you buy... and at $500 you should probably try it first. As for feedback on experience with the editor: When you try to install the editor (at least this was what it did three months ago) It wants to install it's own copies of Apache and MySQL. kind of un-handy if you've got current installs you want to use. I think it gave you the option to keep your own, but after I got everything installed, the database integration wouldn't work. So I decided that I had free time on my hands, I hosed my installs of apache and mysql and let it install it's own stuff. after that I still couldn't get it to work properly. I probably could have tweeked something here or there and gotten everything to go, but my reaction was kind of like.. I'm going to pay $300 to have to play with it this much to get it to work? It just didn't seem worth it Given that I currently used allaire's homesite and the only things that PHPed had over that in features was the db integration and php debugger, and I had already worked out my own solutions to those problems. I recommend downloading the trial version before you buy. I also recommend downloading the trial version of homesite as well. Homesite gives you syntax highlighting, project management, web deployment. scriptable deployment wich lets you decide what gets sent, where it gets put etc... all in a script that you can re-run. Plus homesite let's you edit the ide to add in features that you may need (http://www.macromedia.com/software/homesite/) Or look into Quanta depending on what platform you plan on developing on. I found it ironic that if you buy PHPed standard, it only runs on windows platforms, and that if you decided to buy PHP advantage, then you get linux support. seems like it should be the other way around given that the product they are trying to sell was built on the back of Open Source technologies. Gerald Jensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: LOAD DATA LOCAL INFILE (Only loads 20,000 or 41,000 Records)
This worked for me: create table tblZips( id int unsigned not null auto_increment primary key, city char(24) not null default '', state char(2) not null default '', zip char(5) not null default '' ) Type=InnoDB; Saved CSV file as a UNIX (instad of DOS format file) LOAD DATA INFILE 'zips.csv' INTO TABLE tblZips FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n' (city,state,zip); Query OK, 41943 rows affected (3.86 sec) Records: 41943 Deleted: 0 Skipped: 0 Warnings: 0 mysql select min(id) AS first, max(id) AS last, count(id) as total FROM tblZips; +---+---+---+ | first | last | total | +---+---+---+ | 20973 | 62915 | 41943 | +---+---+---+ - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Sean O'Donnell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 5:00 PM To: MySQLMailingList Subject: LOAD DATA LOCAL INFILE (Only loads 20,000 or 41,000 Records) his is my first post here so please excuse any irrelevancies. I am trying to load a .csv file that contains 41943 Records of Data. I am only able to load 20972 Records. (it seems to skip almost every-other record). The file, located @: http://thisLinux.kicks-Ass.net/projects/databases/tbl_mapZipCodes.csv and contains 41943 lines that match the following comma-seperated structure: 'Pleasantville','NY','00401' 'Holtsville','NY','00501' 'Holtsville','NY','00544' Here is my query mysql LOAD DATA LOCAL INFILE '/usr/local/httpd/htdocs/projects/databases/tbl_ma - INTO TABLE RecruiterConsole.tbl_mapZipCodes - FIELDS TERMINATED BY ',' - OPTIONALLY ENCLOSED BY '\'' - LINES TERMINATED BY '\n' - (mapCity,mapState,mapZip); Here are the Results Query OK, 20972 rows affected (1.56 sec) Records: 20972 Deleted: 0 Skipped: 0 Warnings: 41943 I have tried a few different trouble-shooting scenarios and none seem to work for me. Anybody have any suggestions or similar experiences? Thanks in advance. Sean O'Donnell Monrovia, CA. [EMAIL PROTECTED] http://dynamicwebdevelopers.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
Innodb Tables in MySql 4.0 ?
Ive just downloaded Mysql Max 4.0 binary distribution for Windows ... Does this distribution supports Innodb tables ? How do I turn on this feature ? I follow the instruction (modifying my.ini to include support for innodb ) buy Mysql does not start ( I am using Winmysqladmin ) and I am using mysqld-max-nt.exe And starts well if I comment all INNODB features ... I don't want to go on trying, if this Binary Dist. have not been compiled with this feature .. Is there anyway to see if the binary Dist, was compiled with Innodb support ? Rogger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 PHP Development IDE
InterAKT produce a Dreamweaver Ultradev extension called Phakt that is pretty good. It creates PHP scripts that manipulate MySQL databases. It is free and allows comparable functionality to the 'native' UltraDev languages (ASP, JSP, Cold Fusion). For more info - http://www.interakt.ro/products/PHAkt/ -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: 21 February 2002 14:58 To: 'Joel Wickard'; [EMAIL PROTECTED] Subject: RE: MySQL PHP Development IDE There is three options I use: 1. Macromedia Dreamweaver UltraDev4 with a PHP plugin or extension 2. PHPEdit, its free I forget the website. Its a good IDE, but kinda annoying editor. 3. Notepad -Original Message- From: Joel Wickard [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 10:34 AM To: [EMAIL PROTECTED] Subject: Re: MySQL PHP Development IDE This off topic, but one of our programmers is interested in NuSphere's PHPEd development interface and I would liek some feed back from other users. His interest lies not so much in using for database table modeling, but for PHP and Perl development. If he's not really that interested in the mysql integration than I would say that you could probably go with a different IDE. PHPed's db integration and built-in php debugger are about the only two things that set it apart from other IDE's. I am well aware of the conflict a few months ago between MySQL and NuSphere, but I have never heard anything one way or the other about PHPEd. At nearly $500 per package, it is not a decision I wish to make without some feedback from the community. How does PHPEd stack up compared to other PHP IDE's? What experience (good or bad) has anyone had with it? Is there another product that is better? Any feed back (either to the list or me me privately) will be appreciated. Before you make a commitment, you can apply for a trial version of PHPed. You have to give them real contact information, (I think I've gotten like 5 sales-pitch emails since I downloaded my free trial 3 months ago.) So you can give it a shot before you buy... and at $500 you should probably try it first. As for feedback on experience with the editor: When you try to install the editor (at least this was what it did three months ago) It wants to install it's own copies of Apache and MySQL. kind of un-handy if you've got current installs you want to use. I think it gave you the option to keep your own, but after I got everything installed, the database integration wouldn't work. So I decided that I had free time on my hands, I hosed my installs of apache and mysql and let it install it's own stuff. after that I still couldn't get it to work properly. I probably could have tweeked something here or there and gotten everything to go, but my reaction was kind of like.. I'm going to pay $300 to have to play with it this much to get it to work? It just didn't seem worth it Given that I currently used allaire's homesite and the only things that PHPed had over that in features was the db integration and php debugger, and I had already worked out my own solutions to those problems. I recommend downloading the trial version before you buy. I also recommend downloading the trial version of homesite as well. Homesite gives you syntax highlighting, project management, web deployment. scriptable deployment wich lets you decide what gets sent, where it gets put etc... all in a script that you can re-run. Plus homesite let's you edit the ide to add in features that you may need (http://www.macromedia.com/software/homesite/) Or look into Quanta depending on what platform you plan on developing on. I found it ironic that if you buy PHPed standard, it only runs on windows platforms, and that if you decided to buy PHP advantage, then you get linux support. seems like it should be the other way around given that the product they are trying to sell was built on the back of Open Source technologies. Gerald Jensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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:
PERL DBB::MYSQL MAKE TEST PROBLEM....
I've been having some troubles install dbd::mysql on Solaris 2.4 - make test is failing and I can't figure out what is going wrong. The following shows the results coming back from make test If anybody can help me out here, I would appreciate it. Thanks. make[1]: Entering directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' make[1]: Leaving directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' make[1]: Entering directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' PERL_DL_NONLAZY=1 /usr/local/bin/perl -I.././blib/arch -I.././blib/lib -I/usr/local/lib/perl5/sun4-solaris/5.00401 -I/usr/local/lib/perl5 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/00baseinstall_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/00base.t line 38 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/10dsnlist.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/10dsnlist.t line 45 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-3 Failed 3/3 tests, 0.00% okay t/20createdrop..install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/20createdrop.t line 47 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-5 Failed 5/5 tests, 0.00% okay t/30insertfetch.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/30insertfetch.t line 48 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40bindparam...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 3) line 2 at t/40bindparam.t line 64 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-28 Failed 28/28 tests, 0.00% okay t/40blobs...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40blobs.t line 68 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40listfields..install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40listfields.t line 57 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-16 Failed 16/16 tests, 0.00% okay t/40nulls...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40nulls.t line 50 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40numrows.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at
RE: MySQL PHP Development IDE
InterAKT produce a Dreamweaver Ultradev extension called Phakt that is pretty good. It creates PHP scripts that manipulate MySQL databases. It is free and allows comparable functionality to the 'native' UltraDev languages (ASP, JSP, Cold Fusion). For more info - http://www.interakt.ro/products/PHAkt/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Logging problem with --log, my.cnf etc.
Hello, I am using MySQL 3.23.36 on Linux. This is a small gripe about a product I am otherwise very happy with. I hope I have not missed the obvious or been stupid some other way. The problem - I want to log somewhere other than $DATADIR. Things are set up like ; | log | ON | log_update | OFF | log_bin | OFF | log_slave_updates | OFF | I would have liked to use the my.cnf file to achieve this but it doesn't appear to work i.e. a line like (in the [mysqld] section) ; log=/var/log/mysql.log has no effect. Looking at 'mysqld --help' output would show this is set i.e. logfile: /var/log/mysql.log but this file is not created. In fact, logging appears to stop. So, I thought I would try adding the argument to my mysql init script - args that are passed to 'safe_mysqld' i.e. In /etc/init.d/mysql I added ; --log=/var/log/mysql.log to the command line for 'safe_mysqld'. But this too had no effect - no log file created, no logging. So ... I decided to look at 'safe_mysqld'. This is a much more complex program but, seemed to me, to be potentially ignoring a '--log' line i.e. In the 'case' statement near the top ; for arg do case $arg in # these get passed explicitly to mysqld --basedir=*) MY_BASEDIR_VERSION=`echo $arg | sed -e s;--basedir=;;` ;; --datadir=*) DATADIR=`echo $arg | sed -e s;--datadir=;;` ;; --pid-file=*) pid_file=`echo $arg | sed -e s;--pid-file=;;` ;; ... There is no catch for '--log', and I assume the catchall default ; *) if test -n $pick_args ... misses it as well (or it's ignored later ...). So ... I (probably badly) hacked this script a bit and now I have logging to /var/log/mysql.log. I was looking in some 'changelogs' and found no mention of problems. I also searched the list archives and found one other person with a similar problem which was never resolved ; http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msp:24135:lkonncgmkabnpiggbdio So, would someone take a look at this and see if they can figure out where the problem lies? I admit it might be me. Cheers, -- Alastair | | [EMAIL PROTECTED]| | http://www.nucoda.com | | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL PHP Development IDE
At 06:37 AM 2/21/2002 , you wrote: This off topic, but one of our programmers is interested in NuSphere's PHPEd development interface and I would liek some feed back from other users. His interest lies not so much in using for database table modeling, but for PHP and Perl development. I am well aware of the conflict a few months ago between MySQL and NuSphere, but I have never heard anything one way or the other about PHPEd. At nearly $500 per package, it is not a decision I wish to make without some feedback from the community. How does PHPEd stack up compared to other PHP IDE's? What experience (good or bad) has anyone had with it? Is there another product that is better? Any feed back (either to the list or me me privately) will be appreciated. Gerald Jensen Gerald, I tried PHPEd from NuSphere and it only partially worked. I spent a lot of time trying to get it to work properly and failed. :( I tried 4 or 5 different PHP debuggers and I didn't like any of them. Not being one to give up easily, I kept looking. I found an excellent PHP debugger and editor from https://sourceforge.net/projects/dbg2/. It took a little while getting it installed because there are no docs, but I've been using it for a month and couldn't be happier. The author Dmitri has borrowed a lot of ideas from the Delphi IDE so using it is quite intuitive. I use it as my main PHP editor. It is also faster than any other PHP debugger I've tried (there are no annoying delays or screen flashes when single stepping through the PHP code) and it can also debug into Include files which other debuggers may not be able to do. It can also display Local, Global, Call Stack, Watch, Immediate, Breakpoint windows at the bottom of the screen. It can also display arrays and all class object properties which is very cool. All in all, it's a great PHP editor/debugger. There are versions for Windows (which is what I'm using), BSD and Linux. You also get the source code for it and you'll definitely like the price. It's free. :-) Brent If you have a problem setting it up, shoot me off an email and I'll see what I can do. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 PHP Development IDE
Thanks for the advice ... I'll take a look at dbg2 ... if I get stuck, I'll shoot you a plea for help! Gerald Jensen -Original Message- From: BD [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 9:38 AM To: Gerald R. Jensen Cc: [EMAIL PROTECTED] Subject: Re: MySQL PHP Development IDE At 06:37 AM 2/21/2002 , you wrote: This off topic, but one of our programmers is interested in NuSphere's PHPEd development interface and I would liek some feed back from other users. His interest lies not so much in using for database table modeling, but for PHP and Perl development. I am well aware of the conflict a few months ago between MySQL and NuSphere, but I have never heard anything one way or the other about PHPEd. At nearly $500 per package, it is not a decision I wish to make without some feedback from the community. How does PHPEd stack up compared to other PHP IDE's? What experience (good or bad) has anyone had with it? Is there another product that is better? Any feed back (either to the list or me me privately) will be appreciated. Gerald Jensen Gerald, I tried PHPEd from NuSphere and it only partially worked. I spent a lot of time trying to get it to work properly and failed. :( I tried 4 or 5 different PHP debuggers and I didn't like any of them. Not being one to give up easily, I kept looking. I found an excellent PHP debugger and editor from https://sourceforge.net/projects/dbg2/. It took a little while getting it installed because there are no docs, but I've been using it for a month and couldn't be happier. The author Dmitri has borrowed a lot of ideas from the Delphi IDE so using it is quite intuitive. I use it as my main PHP editor. It is also faster than any other PHP debugger I've tried (there are no annoying delays or screen flashes when single stepping through the PHP code) and it can also debug into Include files which other debuggers may not be able to do. It can also display Local, Global, Call Stack, Watch, Immediate, Breakpoint windows at the bottom of the screen. It can also display arrays and all class object properties which is very cool. All in all, it's a great PHP editor/debugger. There are versions for Windows (which is what I'm using), BSD and Linux. You also get the source code for it and you'll definitely like the price. It's free. :-) Brent If you have a problem setting it up, shoot me off an email and I'll see what I can do. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
urgent problem with mysql and phpmyadmin
Andrew, Thursday, February 21, 2002, 3:31:16 AM, you wrote: AB I have created a table in the mysql db, using phpmyadmin with the following AB statement: AB create table books AB ( isbn char(13) not null,author char(30),title(60),price float(4,2),primary AB key(isbn)); AB which works fine and creates the table i need the problem comes when i try AB to insert some values into the price column using the phpmyadmin form, if AB for example i try to enter the price 34.99 it will only allow me to enter AB 34.9, and then when i go to the browse feature it shows the value as 34.90 AB any ideas on what's going wrong??? I have tested it and it worked fine. What version of MySQL do you use? AB any help would be much apprecited. AB thanks -- For technical support contracts, goto https://order.mysql.com/ 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: PERL DBB::MYSQL MAKE TEST PROBLEM....
Hi Darren, The tests rely on the perl BDB and mysql-modules packages for perl. Have you installed and tested these? Ken - Original Message - From: Darren Ball [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 21, 2002 10:27 AM Subject: PERL DBB::MYSQL MAKE TEST PROBLEM I've been having some troubles install dbd::mysql on Solaris 2.4 - make test is failing and I can't figure out what is going wrong. The following shows the results coming back from make test If anybody can help me out here, I would appreciate it. Thanks. make[1]: Entering directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' make[1]: Leaving directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' make[1]: Entering directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' PERL_DL_NONLAZY=1 /usr/local/bin/perl -I.././blib/arch -I.././blib/lib -I/usr/local/lib/perl5/sun4-solaris/5.00401 -I/usr/local/lib/perl5 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/00baseinstall_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/00base.t line 38 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/10dsnlist.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/10dsnlist.t line 45 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-3 Failed 3/3 tests, 0.00% okay t/20createdrop..install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/20createdrop.t line 47 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-5 Failed 5/5 tests, 0.00% okay t/30insertfetch.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/30insertfetch.t line 48 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40bindparam...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 3) line 2 at t/40bindparam.t line 64 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-28 Failed 28/28 tests, 0.00% okay t/40blobs...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40blobs.t line 68 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40listfields..install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40listfields.t line 57 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-16 Failed 16/16 tests, 0.00% okay t/40nulls...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line
Gemini Tables and NuSphere Update
I am a new MySQL user, so forgive my stupidity for asking the following question (perhaps taboo). What is the latest on NuSphere vs MySQL AB? What about Gemini. I have searched everywhere for info, but can't seem to find anything after November of last year. I have serious needs for transaction processing, but don't know which way to turn. Should I undertake using NuSphere's (supposedly stable) Gemini tables. There seems to be a lack of info on these tables now. I am trying to get a grasp on what happened to this MYSQL AB vs NuSphere deal, but all of the posts about it seem to have vanished. In addition, my first impression of Gemini was not good as I did some simulated crash test and corrupted the tables without much trouble. Perhaps it was unrelated. But it is worth further investigation. Or should I look at the InnoDB tables (which are still considered beta). Any opinions? Craig 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: PERL DBB::MYSQL MAKE TEST PROBLEM....
Yes, this problem is happening at the 'make test' step while installing these modules... I have performed the perl Makefile.PL, and the make, they seem to go ok, just when I perform the make test, it seems to fail with this relocation and symbol errors --- Ken Menzel [EMAIL PROTECTED] wrote: Hi Darren, The tests rely on the perl BDB and mysql-modules packages for perl. Have you installed and tested these? Ken - Original Message - From: Darren Ball [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 21, 2002 10:27 AM Subject: PERL DBB::MYSQL MAKE TEST PROBLEM I've been having some troubles install dbd::mysql on Solaris 2.4 - make test is failing and I can't figure out what is going wrong. The following shows the results coming back from make test If anybody can help me out here, I would appreciate it. Thanks. make[1]: Entering directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' make[1]: Leaving directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' make[1]: Entering directory `/home/dsball/Msql-Mysql-modules-1.2219/mysql' PERL_DL_NONLAZY=1 /usr/local/bin/perl -I.././blib/arch -I.././blib/lib -I/usr/local/lib/perl5/sun4-solaris/5.00401 -I/usr/local/lib/perl5 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/00baseinstall_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/00base.t line 38 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/10dsnlist.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/10dsnlist.t line 45 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-3 Failed 3/3 tests, 0.00% okay t/20createdrop..install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/20createdrop.t line 47 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-5 Failed 5/5 tests, 0.00% okay t/30insertfetch.install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/30insertfetch.t line 48 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40bindparam...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 3) line 2 at t/40bindparam.t line 64 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-28 Failed 28/28 tests, 0.00% okay t/40blobs...install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40blobs.t line 68 dubious Test returned status 2 (wstat 512) DIED. FAILED tests 1-11 Failed 11/11 tests, 0.00% okay t/40listfields..install_driver(mysql) failed: Can't load '.././blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: /usr/local/bin/perl: fatal: relocation error: file .././blib/arch/auto/DBD/mysql/mysql.so: symbol newSVpvn: referenced symbol not found at /usr/local/lib/perl5/sun4-solaris/5.00401/DynaLoader.pm line 155. at (eval 1) line 2 at t/40listfields.t line 57 dubious
foreign keys to what end?
I have a MySQL InnoDB table for ACH (checking account) transactions that includes, among other fields, the below: trans_id (PRIMARY KEY) cust_type routing_num status site_id payment_type auth_type trans_type trace_num All of the above fields (except the primary key) are related to other tables that describe the customer type, transaction type, etc. or have valid values for routing number, status, etc. Should all of these be set up as foreign keys, or should some of referential integrity be done programmatically? How do you draw the line between how many foreign keys are too many, or is there no such thing? Should things like this always be handled by foreign keys instead of error checking in the program? The indexing along would seem to get fairly large in the transactions table if I set them all up as foreign keys. David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrade problem from 3.22.27-2 - 3.23.37-8
Hi, I have the following problem: I had to install a complete new server with Suse 7.2 (MySql: 3.23.37-8). From the the old installation I only got the backup files (created with 'mysqldump database my_databases.sql') and the the database file (ISD, ISM, frm). My problem is: I can't read the backup file (mysql database backup-file.sql). Executing the command nothing happens. Using the restore table syntax only empty tables are created. Does anybody has an idea who to get the data from the old files in the database version. Greetings, Andreas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Gemini Tables and NuSphere Update
On Thu, Feb 21, 2002 at 10:17:52AM -0600, Scalper wrote: I am a new MySQL user, so forgive my stupidity for asking the following question (perhaps taboo). What is the latest on NuSphere vs MySQL AB? What about Gemini. I have searched everywhere for info, but can't seem to find anything after November of last year. They're in the midst of lawsuits and cannot discuss it. Thus, you'll see little if any public comment about it. Or should I look at the InnoDB tables (which are still considered beta). Any opinions? InnoDB is in pretty heavy use. Give it a try and see how it works for you. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 14 days, processed 454,496,815 queries (372/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql 4.0 or 3.23 with innodb?
Varshavchick Alexander [EMAIL PROTECTED] writes: Gentlemen, Can you advice please if I'd wish to change the database format from myisam to innodb should I upgrade to mysql 4.0 to get better performance and stability, or switching to innodb alone under 3.23 will do the trick? Switching to 4.0 for stability doesn't seem like a good idea - it's still in development, and classified as alpha. The 3.23 series has been around a lot longer, and is stable. -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 JDBC connectivity in Windows.
I am having problems connecting to the mysql databse from a Java application in Windows 2K, I am using JDBC and have the mySQL odbc software installed. I am running mysql from the command line prompt in Windows 2k. I fear I may be doing something wrong, if anyone has a step by step solution, i would be so grateful. This is the code in my Java application that i am having problems with. Becuase of this problem I am having to use Microsoft access, and can connect to that no problem. What do i need to change to make it compatable with myswl, i have been trying fro ages but had no luck.Any help with this would be more than helpful and appreciated. usernameString = nameField.getText(); passwordString = passwordField.getText(); Connection connection = null; String url = jdbc:odbc:FYP; String username = anonymous; String password = guest; // Load the driver to allow connection to the database try { Class.forName( sun.jdbc.odbc.JdbcOdbcDriver ); connection = DriverManager.getConnection(url, username, password ); } catch ( ClassNotFoundException cnfex ) { System.err.println(Failed to load JDBC/ODBC driver. ); cnfex.printStackTrace(); System.exit( 1 ); // terminate program } catch ( SQLException sqlex ) { System.err.println( Unable to connect ); sqlex.printStackTrace(); } Statement statement; ResultSet result; try { System.out.println(usernameString); System.out.println(passwordString); statement = connection.createStatement(); result = statement.executeQuery(SELECT * FROM userProfile WHERE username = ' + usernameString + '); while (result.next()) { text = result.getString(adminlevel); System.out.println(text); } statement.close(); connection.close(); }
Non-Sequential Primary Key Index
I have the following table: CREATE TABLE phpSP_users ( primary_key mediumint(8) unsigned DEFAULT '0' NOT NULL auto_increment, user varchar(50) NOT NULL, password varchar(30) NOT NULL, userlevel smallint(3) DEFAULT '0' NOT NULL, service_name varchar(50) DEFAULT 'A Childs Place', full_name varchar(50), visit_count smallint(5) unsigned DEFAULT '0', last_visit date DEFAULT '-00-00', active_user varchar(5) DEFAULT 'TRUE', teachers_child varchar(5) DEFAULT 'FALSE', email varchar(50), childs_name_1 varchar(50), childs_name_2 varchar(50), phone_number varchar(30), PRIMARY KEY (primary_key), KEY user (user), KEY primary_key (primary_key) ); My host has phpMyAdmin 2.0.1 and MySQL 3.22.21. When I delete rows from the database in phpMyAdmin, it does not update the Primary_Key field, so the numbers are NOT sequential. Would this pose a problem? I've tried to run myisamchk, CHECK TABLE through phpMyAdmin but it doesn't work. It gives me a syntax error all the time. Check this out: http://www.phpwizard.net/phorum/read.php?f=1i=3136t=3103 I was thinking perhaps that I might have another type of table becasue of my MySQl version, I ran ALTER TABLE to change it to a MYISAM table. That didn't give me any errors but I still could not run CHECK TABLE. I tried running the isamchk (old command) no luck. I do not have remote access to the database. Need some help here Thanks a big bunch Also--- I am looking for a good way to edit the database offline, but I havn't seen anything really streamlined. I want to take the SQL files generated, edit the database and upload the changes. What program is good for this? Is there a good tutorial on how to do this? __ Byron 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
mysqlbug: SIGALRM hangs read() on socket racecondition
Description: I'm seeing random hung reads() after a mysql insert that appears to be related to a sig alarm race condition. The basic workflow of this program is while (1) recvfrom() /* few hundred packets a sec */ parse packet insert into sql However, once per second a sig alarm is generated which does house keeping and what-not. This will work for minutes or hours, but eventually the read(3, ...) blocks , which is the mysql socket. When this occurs you just see the repeated sig alarms being catch, but immediately returning to that read() which never returns. Note there is still hundreds of udp packets coming in but never able to be read because the never-returing read(3,) on the mysql sock. Normal syscalls resemble: write(3, \1\1\0\0\3INSERT INTO call_logging.de..., 261) = 261 read(3, \3\0\0\1, 4) = 4 read(3, \0\1\0, 3)= 3 recvfrom(5, ...) = 398 When the condition occurs: read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted) --- SIGALRM (Alarm clock) --- time(NULL) = 1014267832 rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0 alarm(1)= 0 rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0 sigreturn() = ? (mask now []) read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted) --- SIGALRM (Alarm clock) --- time(NULL) = 1014267833 rt_sigaction(SIGALRM, {SIG_IGN}, {0x4002fa10, [ALRM], SA_RESTART|0x400}, 8) = 0 alarm(1)= 0 rt_sigaction(SIGALRM, {0x4002fa10, [ALRM], SA_RESTART|0x400}, {SIG_IGN}, 8) = 0 sigreturn() = ? (mask now []) read(3, 0x814a6b0, 4) = ? ERESTARTSYS (To be restarted) --- SIGALRM (Alarm clock) --- Fix: Have to restart entire program to get it working again. Submitter-Id: [EMAIL PROTECTED] MySQL support: none Synopsis: sig alarm hangs read Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.49 (Official MySQL Binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 17 hours 21 min 38 sec Threads: 2 Questions: 3721598 Slow queries: 2 Opens: 227 Flush tables: 1 Open tables: 7 Queries per second avg: 59.547 Environment: System: Linux mmlog2 2.4.9-21smp #1 SMP Thu Jan 17 14:01:48 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Feb 19 06:53 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283964 Dec 8 07:14 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314296 Dec 8 07:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 8 07:02 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc '--with-comment=Official MySQL Binary' --prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile 'CFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings
Problems finding parts of mysql on FreeBSD
I didn't pick the operating system( I don't know it well at all ), and didn't do the install of mysql on this system, but now I have to figure out where things are and how to fix it. What I am use to, is installing on Red Hat with the rpm. ( if someone knows the differences in the operating systems and the setup of mysql, you can see why I am confused) The rpm is nice enough to put mysqld in the /etc/init.d and respective places on Red Hat The FreeBSD doesn't even have these locations, nor does it have a mysqld anywhere We have the server rebooting every Monday morning ( why are we rebooting the server? don't know, its the specifications set from above ) from a cron job So, the mysql server is not starting up. Is there supposed to be some version of the mysqld somewhere? Or am I expected just to call safe_mysqld myself from cron? Can someone please point me in the right direction? (unfortunately I don't have time to learn the nature of FreeBSD at the moment) Luc Foisy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Logging problem with --log, my.cnf etc.
Does mysql have permission to write to that location? -Original Message- From: alastair [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 10:33 AM To: [EMAIL PROTECTED] Subject: Logging problem with --log, my.cnf etc. Hello, I am using MySQL 3.23.36 on Linux. This is a small gripe about a product I am otherwise very happy with. I hope I have not missed the obvious or been stupid some other way. The problem - I want to log somewhere other than $DATADIR. Things are set up like ; | log | ON | log_update | OFF | log_bin | OFF | log_slave_updates | OFF I would have liked to use the my.cnf file to achieve this but it doesn't appear to work i.e. a line like (in the [mysqld] section) ; log=/var/log/mysql.log has no effect. Looking at 'mysqld --help' output would show this is set i.e. logfile: /var/log/mysql.log but this file is not created. In fact, logging appears to stop. So, I thought I would try adding the argument to my mysql init script - args that are passed to 'safe_mysqld' i.e. In /etc/init.d/mysql I added ; --log=/var/log/mysql.log to the command line for 'safe_mysqld'. But this too had no effect - no log file created, no logging. So ... I decided to look at 'safe_mysqld'. This is a much more complex program but, seemed to me, to be potentially ignoring a '--log' line i.e. In the 'case' statement near the top ; for arg do case $arg in # these get passed explicitly to mysqld --basedir=*) MY_BASEDIR_VERSION=`echo $arg | sed -e s;--basedir=;;` ;; --datadir=*) DATADIR=`echo $arg | sed -e s;--datadir=;;` ;; --pid-file=*) pid_file=`echo $arg | sed -e s;--pid-file=;;` ;; ... There is no catch for '--log', and I assume the catchall default ; *) if test -n $pick_args ... misses it as well (or it's ignored later ...). So ... I (probably badly) hacked this script a bit and now I have logging to /var/log/mysql.log. I was looking in some 'changelogs' and found no mention of problems. I also searched the list archives and found one other person with a similar problem which was never resolved ; http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msp:24135:lkonncgmkabnpiggbdio So, would someone take a look at this and see if they can figure out where the problem lies? I admit it might be me. Cheers, -- Alastair | | [EMAIL PROTECTED]| | http://www.nucoda.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: Problems finding parts of mysql on FreeBSD
In the last episode (Feb 21), Luc Foisy said: I didn't pick the operating system( I don't know it well at all ), and didn't do the install of mysql on this system, but now I have to figure out where things are and how to fix it. What I am use to, is installing on Red Hat with the rpm. ( if someone knows the differences in the operating systems and the setup of mysql, you can see why I am confused) The rpm is nice enough to put mysqld in the /etc/init.d and respective places on Red Hat The FreeBSD doesn't even have these locations, nor does it have a mysqld anywhere Assuming you are using the mysql port (ports/packages are FreeBSD's equivalent to RPMs. See http://www.freebsd.org/ports ), the startup scripts are in /usr/local/etc/rc.d, and mysqld lives in /usr/local/libexec. If you did not build using the port, then no startup scripts were installed and you'll probably have to run mysqld_safe yourself from /etc/rc.local. -- 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: Logging problem with --log, my.cnf etc.
On Thu, Feb 21, 2002 at 01:02:02PM -0500, Luc Foisy wrote: Does mysql have permission to write to that location? Yes. I'm starting and stopping MySQL using an init script a la Redhat. This calls 'safe_mysqld' with some args (e.g. --user,--pid-file etc.) - all started as user root as usual. The problem is that I could not see the '--log' file getting recognised or used in safe_mysqld. As I said, I would much rather be using /etc/my.cnf with the log= syntax. What I did in 'safe_mysqld' was ; 1) Add --log=/var/log/mysql.log to the call to safe_mysqld in my mysql init script 2) Add --log=*)log=`echo $arg | sed -e s;--log=;;` ;; to the 'case' statement at the top. 3) After the USER_OPTION= block further down I added ; LOG_OPTION=--log=$log touch $log; chown $user $log 4) Added $LOG_OPTION to the actual call to $MYSQLD (x2) near the bottom. This is a production machine I am playing with here at work so I am limited in what I can screw around with. I need to re-test this stuff at home again. But - has anyone managed to change MySQL's log file using my.cnf and the log= syntax? Cheers, -- Alastair | | [EMAIL PROTECTED]| | http://www.nucoda.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: foreign keys to what end?
Using foreign keys is a really, really good idea. Programs can't be trusted (and what about running sql queries ad hoc). Even with a really big database I wouldn't get rid of the keys, just time to move to a bigger better machine/configuration. -Original Message- From: David Felio [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 11:19 AM To: [EMAIL PROTECTED] Subject: foreign keys to what end? I have a MySQL InnoDB table for ACH (checking account) transactions that includes, among other fields, the below: trans_id (PRIMARY KEY) cust_type routing_num status site_id payment_type auth_type trans_type trace_num All of the above fields (except the primary key) are related to other tables that describe the customer type, transaction type, etc. or have valid values for routing number, status, etc. Should all of these be set up as foreign keys, or should some of referential integrity be done programmatically? How do you draw the line between how many foreign keys are too many, or is there no such thing? Should things like this always be handled by foreign keys instead of error checking in the program? The indexing along would seem to get fairly large in the transactions table if I set them all up as foreign keys. David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best solution for a very busy MySQL server
Hello, Have a look at : http://www.compagnie.com/stef/solution1.jpg http://www.compagnie.com/stef/solution2.jpg I'm searching for a solution to change a high traffic mysql server with a distributed system. 1)Is the solution 1 is good for a writing + reading access ? Are there problems with locking processus ? 2)Is the solution 2 is good for : -direct writing to mysql server (insert, update, replace ...) -using MyODBC to read tables (select) 3) I found another MySQL cluster solution http://prometheus.zerodivide.net/mysql_cluster/about/ This system uses MySQL replication. Thanks for advices. ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.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: Problems finding parts of mysql on FreeBSD
020221 13:12:19 mysqld started 020221 13:12:20 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020221 13:12:20 mysqld ended also it would be important to note /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/tmcdbs01.mysql.pid ahh could be a permission problem, the directories require an executable tag correct? drw-rw 2 mysql mysql512 Jan 18 18:51 mysql -rw-rw 1 mysql mysql 0 Jan 18 18:45 host.MYD -rw-rw 1 mysql mysql 1024 Jan 18 18:45 host.MYI -rw-rw 1 mysql mysql 8958 Jan 18 18:45 host.frm -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 1:46 PM To: Luc Foisy Cc: MYSQL-List (E-mail) Subject: Re: Problems finding parts of mysql on FreeBSD In the last episode (Feb 21), Luc Foisy said: Ok. I do indeed have /usr/local/etc/rc.d/mysql-server.sh And oops I do have /usr/local/libexec/mysqld Are these supposed to be started up automatically? I tried to run /usr/local/etc/rc.d/mysql-server.sh start TMCBSD101# mysql-server.sh start mysql-server.sh: Command not found. TMCBSD101# ./mysql-server.sh start TMCBSD101# ps -ax TMCBSD101# mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) Take a look at /var/db/mysql/hostname.log and hostname.err; it looks like mysql might have exited immediately after it was started. /usr/local/bin/safe_mysqld -user=mysql /dev/null will load the mysql server. So why is this not performed at start up? And if this script is used, what is mysqld used for?? safe_mysqld calls mysqld and loops so that if mysqld died unexpectedly it is restarted. -- 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
MySQL Book
Any book you guys recommend for learning the specific strenght of MySQL, I'm have a Oracle background and would like to learn more on MySQL. Stephane 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
Install Crash : MySQL-3.23.49-1 /RedHat 7
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Install Crash : MySQL-3.23.49-1 /RedHat 7 Severity: Priority: Category: mysql Class: Release: mysql-3.23.49 (Official MySQL RPM) Environment: System: Linux 12-236-145-86.client.attbi.com 2.2.16-22 #1 Tue Aug 22 16:16:55 EDT 2000 i586 unknown Architecture: i586 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.0) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Feb 18 10:20 /lib/libc.so.6 - libc-2.1.92.so -rwxr-xr-x1 root root 4686077 Aug 30 2000 /lib/libc-2.1.92.so -rw-r--r--1 root root 22607104 Aug 30 2000 /usr/lib/libc.a -rw-r--r--1 root root 178 Aug 30 2000 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc Hello ! Here's what I did : rpm -i MySQL-3.23.49-1.i386.rpm MySQL-client-3.23.49-1.i386.rpm Here's what I got : A mess. Any attempt to start any part of mysql( mysqladmin, etc) blows up in a very similar fashion. I have repeated this, from download of the rpms on down. Kernel = 2.2.16-22, which could be a problem. Before having to do such major surgery, including at least another 8 patches, I'd like to know that it's worth it ! glibc2 = 1.92-14 I'm hoping I've done something obviously stupid. Any suggestions welcomed ! Here's the log : Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables mysqld got signal 4; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 233979 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806e214 0x811d258 0x8147872 0x80e1eaa 0x80b7357 0x80b4584 0x808a186 0x8085fc3 0x8085556 0x8086376 0x8099493 0x8075f8a 0x8078ecc 0x807373d Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x827bc88 = INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') thd-thread_id=1 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 1 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Installation of grant tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/sbin/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show
RE: MySQL Book
MySQL by Paul DuBoise Paul monitors this mailing list, so if you have questions concerning what you've read, he may answer. -Original Message- From: Danis Stéphane (NHQ-AC) [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 1:08 PM To: Mysql (E-mail) Subject: MySQL Book Any book you guys recommend for learning the specific strenght of MySQL, I'm have a Oracle background and would like to learn more on MySQL. Stephane 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Gemini Tables and NuSphere Update
Hi! Or should I look at the InnoDB tables (which are still considered beta). InnoDB tables are classified as gamma by Innobase Oy and MySQL AB. The text on the MySQL-Max download page is lagging behind (says beta), but to balance it, on the front page MySQL AB classifies MySQL-Max as stable :). A December quickpoll at the MySQL website showed InnoDB market share at 10 %, BDB 3 %, MyISAM 80 %, ISAM 5 %, of 3000 people who responded. Mytrix, Inc. is maybe the largest MySQL database site in the world (if someone has more than 1 TB data, please step forward), and they use InnoDB tables. See http://www.innodb.com/userstories.html. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://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: Logging problem with --log, my.cnf etc.
On Thu, Feb 21, 2002 at 01:47:50PM -0500, Luc Foisy wrote: maybe the variable you are using is not right my 'show variables' indicates log with a value of OFF | log | OFF yet mysql still generates its standard error file Hi Luc, Thanks for the reply (but please just reply to the list and not a CC - I've subscribed). I want 'LOG' to be 'ON' so I can log updates,selects etc. i.e. standard logging (-l,--log,--log=file). The stderr log (err_log) is of no relevance to me for this. Cheers, -- Alastair | | [EMAIL PROTECTED]| | http://www.nucoda.com | | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Book
The bible is MySQL by PAul DuBois ISBN: 0735709211 homepage: http://www.kitebird.com/mysql-book/ Gurhan -Original Message- From: Danis Stéphane (NHQ-AC) [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 2:08 PM To: Mysql (E-mail) Subject: MySQL Book Any book you guys recommend for learning the specific strenght of MySQL, I'm have a Oracle background and would like to learn more on MySQL. Stephane 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 replication error
Would anyone be able to tell me why this happened, and how I could fix it? mysql show slave status\G *** 1. row *** Master_Host: 209.217.92.34 Master_User: repslave2 Master_Port: 3306 Connect_retry: 60 Log_File: QBSLXDB1-bin.029 Pos: 2596608 Slave_Running: No Replicate_do_db: qbslive Replicate_ignore_db: Last_errno: 1062 Last_error: error 'Duplicate entry '1376034' for key 1' on query 'INSERT INTO DISPATCHLOG (CreateStamp) VALUES (NOW())' Skip_counter: 0 err_log 020221 14:21:12 mysqld started /usr/local/libexec/mysqld: ready for connections 020221 14:21:14 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'QBSLXDB1-bin.029' at position 2596608 ERROR: 1062 Duplicate entry '1376034' for key 1 020221 14:21:14 Slave: error running query 'INSERT INTO DISPATCHLOG (CreateStamp) VALUES (NOW())' 020221 14:21:14 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We s topped at log 'QBSLXDB1-bin.029' position 2596608 020221 14:21:14 Slave thread exiting, replication stopped in log 'QBSLXDB1-bin.029' at position 2596608 Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 829-7117 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Raw Devices in MySQL?
Have a feeling I know the answer already, but here goes! ... Does anyone know if (and how) MySQL supports raw devices? I¹ve been searching the docs; can find nothing... Thanks - Lou - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie needs help with installation.
Hi, I am running SuSE 7.3 Professional and i downloaded the binary files for Max 4.0 version from here: http://www.mysql.com/downloads/mysql-max-4.0.html I followed the installation procudure to the letter from this file: /mysql-max-4.0.1-alpha-pc-linux-gnu-i686/INSTALL-BINARY in the archive. I ran: mysql_install_db without any problems. All the setting were kept exactly as stated in the INSTALL-BINARY file. However I can not run: bin/safe_mysqld --user=mysql as linux root or as any other user on the local host. As a matter of fact the file does not even exist... The closest I have to the above file is: :/usr/local/mysql/bin/mysqld_safe Now I also tried to run this scripts with all user linux accounts and by using the -u root option as oposed to --user=mysql (I also tried --user=root from within all linux accounts. I keep on getting this error: george@linux:/usr/local/mysql ./bin/mysqld_safe --user=root [2] 4727 [1] Exit 1 ./bin/mysqld_safe --user=root george@linux:/usr/local/mysql Starting mysqld daemon with databases from /usr/local/mysql/data ./bin/mysqld_safe: /usr/local/mysql/data/linux.err: Permission denied ./bin/mysqld_safe: /usr/local/mysql/data/linux.err: Permission denied tee: /usr/local/mysql/data/linux.err: Permission denied 020221 23:17:13 mysqld ended tee: /usr/local/mysql/data/linux.err: Permission denied [2]+ Exit 1 ./bin/mysqld_safe --user=root george@linux:/usr/local/mysql I logged in as linux root and gave all users full access to the /usr/local/mysql directory included files and directories below it. At present I do not know what else I could try. Was the file I downloaded corrupt? My .../mysql/bin/ directory size ATM is: 15,229,278 The mysql directory is a symlink to: /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686 as described in the file INSTALL-BINARY, I gave all users full access to this directory and all its subdirectories as well (obviously this did not help and I will need to change the security back to where it was). If anybody could please assist me or point me to an URL I will apreciate it. G (mysql, 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
-p password
Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
James Austin wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Create .my.cnf in your home directory with: [client] user = username password = real-big-secret Van -- = Linux rocks!!! http://www.dedserius.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
Selecting set members
If I have a table with a set such as this: CREATE TABLE application ( application_num int(6) NOT NULL auto_increment, availability set('weekdays','weekends','mornings','afternoons') default NULL, PRIMARY KEY (application_num) ) TYPE=MyISAM; is it possible to search based on members of the set? For example, someone might have selected ('weekends,mornings') for their entry in this set, and I am interested in searching for people who are available in the morning. However running a query such as: mysql select * from application where availability = 'mornings'; would not return a person who is available for both weekends and mornings, since there set entry would be 'weekends,mornings'. Writing querys to try to cover all the posibilities such as: mysql select * from application where availability = 'mornings' or availability = 'weekends,mornings' or ... ; Seems like the wrong thing to do. Anyone have any ideas? I wouldn't be using sets if I could, but I am stuck with them for this prjoect. Thanks a lot, 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: MySQL Book
At 13:25 -0600 2/21/02, Rick Emery wrote: MySQL by Paul DuBoise Paul monitors this mailing list, so if you have questions concerning what you've read, he may answer. You added an e to my name deliberately just to see if I'd reply, didn't you! :-) -Original Message- From: Danis Stéphane (NHQ-AC) [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 1:08 PM To: Mysql (E-mail) Subject: MySQL Book Any book you guys recommend for learning the specific strenght of MySQL, I'm have a Oracle background and would like to learn more on MySQL. Stephane 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: MySQL Book
oops... Do I get credit for capitalizing the B in DuBois? GRIN -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 3:57 PM To: Rick Emery; 'Danis Stéphane '; Mysql (E-mail) Subject: RE: MySQL Book At 13:25 -0600 2/21/02, Rick Emery wrote: MySQL by Paul DuBoise Paul monitors this mailing list, so if you have questions concerning what you've read, he may answer. You added an e to my name deliberately just to see if I'd reply, didn't you! :-) -Original Message- From: Danis Stéphane (NHQ-AC) [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 1:08 PM To: Mysql (E-mail) Subject: MySQL Book Any book you guys recommend for learning the specific strenght of MySQL, I'm have a Oracle background and would like to learn more on MySQL. Stephane 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: Fake interval data type
On Thursday 21 February 2002 03:06, Heikki Tuuri wrote: Oh, Duh. The bigger Oops was mine. Doesn't help much to have a transaction if you don't ask for a lock! ;o). Thx. Oops, the syntax is SELECT ... FROM ... WHERE .. LOCK IN SHARE MODE; Heikki -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 21, 2002 9:27 AM Subject: Re: Fake interval data type Hi! Its kind of a subtle point, but innodb's locking not only locks rows, it locks the ABSENCE of rows. If I understand correctly this means that something like: BEGIN WORK SELECT COUNT(*) FROM table WHERE starttime? AND endtime? (check the count) INSERT INTO table (if count is zero) COMMIT SHOULD do the trick. The transaction should block anything else that tries to do an insert into the same range before you do your commit. I must add you have to use a LOCKING read in the SELECT. Either SELECT ... FROM ... WHERE .. FOR UPDATE; which sets exclusive locks, or SELECT ... FROM ... WHERE .. FOR LOCK IN SHARE MODE; which sets shared locks on the rows (to be precise, index records) it encounters. Without these additional clauses the default SELECT mode of InnoDB is the consistent non-locking read of Oracle. Locking reads in InnoDB also lock the absence of rows, as Tod states. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 replication error
Luc, I new to this mailinglist ( this is my first posting) But I have worked a little with replication. Try if the replicator is still down. set sql_slave_skip_counter = 1; slave start on the slave as long as you are sure the master is ok. Let me know is this worked. -chad On Thu, 21 Feb 2002 15:48:08 -0500 Luc Foisy [EMAIL PROTECTED] wrote: Would anyone be able to tell me why this happened, and how I could fix it? mysql show slave status\G *** 1. row *** Master_Host: 209.217.92.34 Master_User: repslave2 Master_Port: 3306 Connect_retry: 60 Log_File: QBSLXDB1-bin.029 Pos: 2596608 Slave_Running: No Replicate_do_db: qbslive Replicate_ignore_db: Last_errno: 1062 Last_error: error 'Duplicate entry '1376034' for key 1' on query 'INSERT INTO DISPATCHLOG (CreateStamp) VALUES (NOW())' Skip_counter: 0 err_log 020221 14:21:12 mysqld started /usr/local/libexec/mysqld: ready for connections 020221 14:21:14 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'QBSLXDB1-bin.029' at position 2596608 ERROR: 1062 Duplicate entry '1376034' for key 1 020221 14:21:14 Slave: error running query 'INSERT INTO DISPATCHLOG (CreateStamp) VALUES (NOW())' 020221 14:21:14 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We s topped at log 'QBSLXDB1-bin.029' position 2596608 020221 14:21:14 Slave thread exiting, replication stopped in log 'QBSLXDB1-bin.029' at position 2596608 Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 829-7117 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Selecting set members
Neil, Thank you for giving me that idea. That exact syntax did not work with = but using like worked well. Problem solved. Thanks, John mysql select availability FROM application; +---+ | availability | +---+ | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekends,mornings | | weekends,mornings | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | +---+ 14 rows in set (0.00 sec) mysql mysql select availability from application where availability = '%afternoons%'; Empty set (0.00 sec) mysql mysql select availability from application where availability like '%afternoons%'; +---+ | availability | +---+ | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | +---+ 12 rows in set (0.00 sec) mysql On Fri, 22 Feb 2002, Neil Silvester wrote: What about using wildcards? mysql select * from application where availability = '%mornings%'; Crude, but it should work. Neil -Original Message- From: John Fulton [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 7:49 AM To: [EMAIL PROTECTED] Subject: Selecting set members If I have a table with a set such as this: CREATE TABLE application ( application_num int(6) NOT NULL auto_increment, availability set('weekdays','weekends','mornings','afternoons') default NULL, PRIMARY KEY (application_num) ) TYPE=MyISAM; is it possible to search based on members of the set? For example, someone might have selected ('weekends,mornings') for their entry in this set, and I am interested in searching for people who are available in the morning. However running a query such as: mysql select * from application where availability = 'mornings'; would not return a person who is available for both weekends and mornings, since there set entry would be 'weekends,mornings'. Writing querys to try to cover all the posibilities such as: mysql select * from application where availability = 'mornings' or availability = 'weekends,mornings' or ... ; Seems like the wrong thing to do. Anyone have any ideas? I wouldn't be using sets if I could, but I am stuck with them for this prjoect. Thanks a lot, 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Linking tables using INNER JOIN
I am trying to do an inner join between three tables. Here is what I have tried: select * from MapConfig LEFT JOIN ConfigString USING (id) LEFT JOIN ConfigInt USING (id) WHERE MapConfig.layername = Roads; What I am trying to do is to pull back all rows from MapConfig associated with matching rows from ConfigInt and ConfigString. What I get, though, is info from MapConfig and ConfigString ONLY, nothing from ConfigInt. If I rearrange the order that ConfigString and ConfigInt appear, I get info from MapConfig and ConfigInt ONLY, nothing from ConfigString. Is my SQL messed up or is there a limitation in MySQL on the number of INNER JOINs you can do in one statement? I suppose I could probably use a temporary table for this but I'd rather not. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Book
MySQL by Paul DuBoise Paul monitors this mailing list, so if you have questions concerning what you've read, he may answer. You added an e to my name deliberately just to see if I'd reply, didn't you! :-) Not really, but because he thinks you are Paul of Idaho! =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Linking tables using INNER JOIN
try: select * from MapConfig mc LEFT JOIN ConfigString USING (id) LEFT JOIN ConfigInt cf on mc.id=cf.id WHERE MapConfig.layername = Roads; -Original Message- From: Christopher Thompson [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 4:24 PM To: [EMAIL PROTECTED] Subject: Linking tables using INNER JOIN I am trying to do an inner join between three tables. Here is what I have tried: select * from MapConfig LEFT JOIN ConfigString USING (id) LEFT JOIN ConfigInt USING (id) WHERE MapConfig.layername = Roads; What I am trying to do is to pull back all rows from MapConfig associated with matching rows from ConfigInt and ConfigString. What I get, though, is info from MapConfig and ConfigString ONLY, nothing from ConfigInt. If I rearrange the order that ConfigString and ConfigInt appear, I get info from MapConfig and ConfigInt ONLY, nothing from ConfigString. Is my SQL messed up or is there a limitation in MySQL on the number of INNER JOINs you can do in one statement? I suppose I could probably use a temporary table for this but I'd rather not. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
Yes. Thank you. Just put a pair of around password and it works fine. I had thought this could be handled in mysql, but hey, this works. rc wrote: one way would bein bash - edit your .bashrc file to include the following: alias mysql='mysql -u username -p password' On Thu, 21 Feb 2002, James Austin wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
actually someone sent an email to the list suggesting using .my.cnf file, which is probably better than using the .bashrc file On Thu, 21 Feb 2002, James Austin wrote: Yes. Thank you. Just put a pair of around password and it works fine. I had thought this could be handled in mysql, but hey, this works. rc wrote: one way would bein bash - edit your .bashrc file to include the following: alias mysql='mysql -u username -p password' On Thu, 21 Feb 2002, James Austin wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Raw Devices in MySQL?
On Thu, Feb 21, 2002 at 04:10:58PM -0500, Lou Picciano / Essex Systems wrote: Have a feeling I know the answer already, but here goes! ... Does anyone know if (and how) MySQL supports raw devices? I¹ve been searching the docs; can find nothing... InnoDB can use raw devices. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 14 days, processed 460,706,325 queries (371/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Book
At 22:15 + 2/21/02, DL Neil wrote: MySQL by Paul DuBoise Paul monitors this mailing list, so if you have questions concerning what you've read, he may answer. You added an e to my name deliberately just to see if I'd reply, didn't you! :-) Not really, but because he thinks you are Paul of Idaho! =dn I ain't ne'er been to Idaho even once! Are there any baseball teams there? Maybe I should go visit... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Linking tables using INNER JOIN
On Thursday 21 February 2002 3:29 pm, Rick Emery wrote: try: select * from MapConfig mc LEFT JOIN ConfigString USING (id) LEFT JOIN ConfigInt cf on mc.id=cf.id WHERE MapConfig.layername = Roads; Heh. It's been too long since I worked with SQL. Thanks, that worked perfectly. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
At 14:30 -0800 2/21/02, James Austin wrote: Yes. Thank you. Just put a pair of around password and it works fine. I had thought this could be handled in mysql, but hey, this works. I don't see how it *could* work. When the password is given on the command line, it must follow the -p with no intervening space. rc wrote: one way would bein bash - edit your .bashrc file to include the following: alias mysql='mysql -u username -p password' On Thu, 21 Feb 2002, James Austin wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Selecting set members
At 17:18 -0500 2/21/02, John Fulton wrote: Neil, Thank you for giving me that idea. That exact syntax did not work with = but using like worked well. Problem solved. You might find that using FIND_IN_SET('afternoons',availability) works as well. Thanks, John mysql select availability FROM application; +---+ | availability | +---+ | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekends,mornings | | weekends,mornings | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | +---+ 14 rows in set (0.00 sec) mysql mysql select availability from application where availability = '%afternoons%'; Empty set (0.00 sec) mysql mysql select availability from application where availability like '%afternoons%'; +---+ | availability | +---+ | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | | weekdays,weekends,mornings,afternoons,evenings,overnights | +---+ 12 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
shockingly enough it works like this: alias mysql='mysql -u username -ppassword' On Thu, 21 Feb 2002, Paul DuBois wrote: At 14:30 -0800 2/21/02, James Austin wrote: Yes. Thank you. Just put a pair of around password and it works fine. I had thought this could be handled in mysql, but hey, this works. I don't see how it *could* work. When the password is given on the command line, it must follow the -p with no intervening space. rc wrote: one way would bein bash - edit your .bashrc file to include the following: alias mysql='mysql -u username -p password' On Thu, 21 Feb 2002, James Austin wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
I put this line (with no space after the -p) in the .bashrc file and sourced it; alias mysql='mysql -u root -pbig_secret' It gets me in ok. Van suggested the mysql way putting this in a .my.cnf file in my home directory. [client] user=username password=big_secret I know this is basic stuff, but I've got good help and a good book... thanks Paul DuBois wrote: At 14:30 -0800 2/21/02, James Austin wrote: Yes. Thank you. Just put a pair of around password and it works fine. I had thought this could be handled in mysql, but hey, this works. I don't see how it *could* work. When the password is given on the command line, it must follow the -p with no intervening space. rc wrote: one way would bein bash - edit your .bashrc file to include the following: alias mysql='mysql -u username -p password' On Thu, 21 Feb 2002, James Austin wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Non-Sequential Primary Key Index
Hi Byron, On Fri, 2002-02-22 at 03:23, Byron Scott wrote: I have the following table: CREATE TABLE phpSP_users ( primary_key mediumint(8) unsigned DEFAULT '0' NOT NULL auto_increment, [...] PRIMARY KEY (primary_key), KEY user (user), KEY primary_key (primary_key) ); You can ditch the last key line. My host has phpMyAdmin 2.0.1 and MySQL 3.22.21. Wow, your host is running quite an old version of the MySQL Server there! When I delete rows from the database in phpMyAdmin, it does not update the Primary_Key field, so the numbers are NOT sequential. Would this pose a problem? No. When you delete a row, that does not affect other rows (nor should it). I've tried to run myisamchk, CHECK TABLE through phpMyAdmin but it doesn't work. It gives me a syntax error all the time. Indeed, those internal commands were implemented somewhere in the 3.23 series. I was thinking perhaps that I might have another type of table becasue of my MySQl version, I ran ALTER TABLE to change it to a MYISAM table. That didn't give me any errors but I still could not run CHECK TABLE. MyISAM was first introduced in 3.23. The 3.22 version your host is running may just ignore the TYPE parameter in the ALTER TABLE statement, but the table type will still be old ISAM. I tried running the isamchk (old command) no luck. No luck in what way? Do you have command line access on the server, and are you allowed to stop/start the server? Regards, Arjen. -- MySQL Training in Brisbane: 18-22 March, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ 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: -p password
James Austin wrote: I put this line (with no space after the -p) in the .bashrc file and sourced it; alias mysql='mysql -u root -pbig_secret' It gets me in ok. Van suggested the mysql way putting this in a .my.cnf file in my home directory. [client] user=username password=big_secret The .bashrc trick works, but .my.cnf or my.cnf is platform-nonspecific. Many of the readers of the list don't use *n*x workstations. However, those who do can take advantage of other bourne-again shell tricks like: trap ~/.logout 0 at the end of .bashrc .profile or .bash_profile where .logout is chmod-ded +x and contains commands to do clean-up tasks upon exiting the shell. But, of course we're heading off-topic. Van -- = Linux rocks!!! http://www.dedserius.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: -p password
alias mysql='mysql -u root -pbig_secret' Another, more reliable way, is to use the long-form options and say mysql --user=root --password=big_secret -- Shankar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: -p password
Hi there, the other comments may have got you where you want but as no one had mentioned your final option thought I'd mention (from /h at the mysql command line) source (\.)Execute a SQL script file. Takes a file name as an argument. so you can run the script from the mysql prompt. cheers, noel On Friday, February 22, 2002 8:41 AM, James Austin [SMTP:[EMAIL PROTECTED]] wrote: Hi all, A novice question: How can I do away with having to use the -u user and -p password arguments every time I execute the mysql command? When I create a table with a script the only way I can get it to work is with the command: $ mysql samp_db -u root -p create_member.sql $ Enter password: What can I do to not have to enter a user and password each time? Can I use the script at the mysql prompt? This way I would only have to enter the user and password once. Thanks very much, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
MySQL 4.x Timeline
Is there a timeline/status for 4.x functionality available? I would like to use MySQL 4.x, however, I'm looking for some of the highly requested functionality (ie; subselects, SP's, triggers and Views) and would like to know where/when in the sequence of things todo does everything fall into place. This will help me plan my development effort as well since I don't need everything at once. Thanks, Anthony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Everyone, Have u encountered this kind of query? mysql select edate from traffic where year(edate)='2001' group by month(edate); ++ | edate | ++ | 2001-04-29 | | 2001-05-01 | | 2001-06-01 | | 2001-07-01 | | 2001-08-01 | | 2001-09-01 | | 2001-10-30 | | 2001-11-01 | | 2001-12-01 | ++ 9 rows in set (10 min 53.83 sec) mysql select count(*) from traffic; +--+ | count(*) | +--+ | 2645563 | +--+ 1 row in set (0.00 sec) mysql is there a problem with my select statement?... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 I do it with single query in mysql?
Hello all, Suppose I have 3 tables, 1. master (auto_nr, link, dat) CREATE TABLE master ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned zerofill NOT NULL, dat DATE NOT NULL, PRIMARY KEY auto_nr(auto_nr), KEY link_key(link) ) 2. child1 (auto_nr, link1, value1) CREATE TABLE child1 ( auto_nr int(11) unsigned zerofill NOT NULL, link1 int(11) unsigned , value1 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link1_key(link1) ) 3. child2 (auto_nr, link2, value2) CREATE TABLE child2 ( auto_nr int(11) unsigned zerofill NOT NULL, link2 int(11) unsigned , value2 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link2_key(link2) ) At the moment I use temporary tables following way: CREATE TEMPORARY table temp1 Select link, max(dat) as m_dat, sum(value1) as val1 from master left join child1 on master.link=child1.link1 group by link CREATE TEMPORARY table temp2 Select link, sum(value2) as val2 from master left join child2 on master.link=child2.link2 group by link //OK. Actually I use 2 steps to build one temporary table: a. Full CREATE TEMPORARY TABLE statement using KEY (or maybe better INDEX???) if needed. b. INSERT INTO to populate created table. FINALLY: Select m_dat, temp1.link, val1, val2 from temp1, temp2 where temp1.link=temp2.link Can I do it with one select statement in mysql? Sorry to say, I have to create up to 12 different temporary tables to get result sets for everyday use. As you may guess execution of bunch of queries takes time (up to 1 min) and may be considered as rather messy. Ive come to conclusion that SQL optimization with MySql (using temporary tables) is time consuming. IMHO I need feature called CREATE VIEW , but perhaps I have to learn some of features of standard SQL (i.e. progressive extensive use of different JOIN types;-) so, really good SQL books regarding this subject; your recommendations. My database is not large (50 tables, max 25 columns per table, currently overall amount ~50Mb plus about 0.2-1Mb each day). I try to stick with SQL92 and keep my client code independent from particular SQL server implementation; maybe someday I have to shift to other back-end, so I want to make migration to different back-end as easy as possible. Speed penalty is inescapable. My system configuration is Compaq PROLIANT ML370, 128Mb RAM, 933 Mhz PIII, RedHat Linux 7.1. Its dual processor system, so some hardware upgrade is quite possible. I have small number of clients (1-20). Any suggestions for optimal MySql server configuration? Any help will be appreciated. p.s. Whats the difference between KEY and INDEX in CREATE TABLE syntax (any +/- effect on JOIN)? Regards, Alvis __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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: Can I do it with single query in mysql?
If you only interested in getting the overall sum from the child tables you could try the following: 1) Change your child create statements to: CREATE TABLE child1 ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned , value int(11), PRIMARY KEY auto_nr(auto_nr), KEY link1_key(link) ) CREATE TABLE child2 ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned , value int(11), PRIMARY KEY auto_nr(auto_nr), KEY link2_key(link) ) 2) Then create a merge table like: CREATE TABLE childMerge ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned , value int(11), KEY auto_nr(auto_nr), KEY link_key(link) ) TYPE=MERGE UNION=(child1,child2); 3) The the one statement then becomes: Select master.link, max(dat) as m_dat, sum(childMerge.value) from master left join childMerge using (link) group by master.link KEY is a synonym for INDEX Regards Dan -Original Message- From: Alvis [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 2:44 p.m. To: [EMAIL PROTECTED] Subject: Can I do it with single query in mysql? Hello all, Suppose I have 3 tables, 1. master (auto_nr, link, dat) CREATE TABLE master ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned zerofill NOT NULL, dat DATE NOT NULL, PRIMARY KEY auto_nr(auto_nr), KEY link_key(link) ) 2. child1 (auto_nr, link1, value1) CREATE TABLE child1 ( auto_nr int(11) unsigned zerofill NOT NULL, link1 int(11) unsigned , value1 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link1_key(link1) ) 3. child2 (auto_nr, link2, value2) CREATE TABLE child2 ( auto_nr int(11) unsigned zerofill NOT NULL, link2 int(11) unsigned , value2 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link2_key(link2) ) At the moment I use temporary tables following way: CREATE TEMPORARY table temp1 Select link, max(dat) as m_dat, sum(value1) as val1 from master left join child1 on master.link=child1.link1 group by link CREATE TEMPORARY table temp2 Select link, sum(value2) as val2 from master left join child2 on master.link=child2.link2 group by link //OK. Actually I use 2 steps to build one temporary table: a. Full CREATE TEMPORARY TABLE statement using KEY (or maybe better INDEX???) if needed. b. INSERT INTO to populate created table. FINALLY: Select m_dat, temp1.link, val1, val2 from temp1, temp2 where temp1.link=temp2.link Can I do it with one select statement in mysql? Sorry to say, I have to create up to 12 different temporary tables to get result sets for everyday use. As you may guess execution of bunch of queries takes time (up to 1 min) and may be considered as rather messy. Ive come to conclusion that SQL optimization with MySql (using temporary tables) is time consuming. IMHO I need feature called CREATE VIEW , but perhaps I have to learn some of features of standard SQL (i.e. progressive extensive use of different JOIN types;-) so, really good SQL books regarding this subject; your recommendations. My database is not large (50 tables, max 25 columns per table, currently overall amount ~50Mb plus about 0.2-1Mb each day). I try to stick with SQL92 and keep my client code independent from particular SQL server implementation; maybe someday I have to shift to other back-end, so I want to make migration to different back-end as easy as possible. Speed penalty is inescapable. My system configuration is Compaq PROLIANT ML370, 128Mb RAM, 933 Mhz PIII, RedHat Linux 7.1. Its dual processor system, so some hardware upgrade is quite possible. I have small number of clients (1-20). Any suggestions for optimal MySql server configuration? Any help will be appreciated. p.s. Whats the difference between KEY and INDEX in CREATE TABLE syntax (any +/- effect on JOIN)? Regards, Alvis __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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: SQL query
I presuming you are expecting more results from the first select statement, hence the reason and indicating this in the second, should then your first statement be: select edate from traffic where year(edate)='2001' group by edate; ? Regards, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 2:30 p.m. To: [EMAIL PROTECTED] Subject: SQL query Everyone, Have u encountered this kind of query? mysql select edate from traffic where year(edate)='2001' group by month(edate); ++ | edate | ++ | 2001-04-29 | | 2001-05-01 | | 2001-06-01 | | 2001-07-01 | | 2001-08-01 | | 2001-09-01 | | 2001-10-30 | | 2001-11-01 | | 2001-12-01 | ++ 9 rows in set (10 min 53.83 sec) mysql select count(*) from traffic; +--+ | count(*) | +--+ | 2645563 | +--+ 1 row in set (0.00 sec) mysql is there a problem with my select statement?... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SQL query
I can't see any way of making this statement faster, however: One method to increase speed, especially since the amount of data is large is to create seperate tables into your lowest common denominator, here year (e.g. traffic_2000,traffic_2001 etc). Then create a merge table with the same name as your current one i.e. 'traffic', such that you don't break any current functionality in your application, then depending on your year value, to increase speed, consult the specific year table e.g. traffic_2001. re: http://www.mysql.com/doc/M/E/MERGE.html Regards, Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 4:20 p.m. To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: SQL query My concern here is the query speed Mysql query on the statement result so slow...is there a way to improve it? R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 -Original Message- From: Daniel Rosher [SMTP:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:15 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:RE: SQL query I presuming you are expecting more results from the first select statement, hence the reason and indicating this in the second, should then your first statement be: select edate from traffic where year(edate)='2001' group by edate; ? Regards, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 2:30 p.m. To: [EMAIL PROTECTED] Subject: SQL query Everyone, Have u encountered this kind of query? mysql select edate from traffic where year(edate)='2001' group by month(edate); ++ | edate | ++ | 2001-04-29 | | 2001-05-01 | | 2001-06-01 | | 2001-07-01 | | 2001-08-01 | | 2001-09-01 | | 2001-10-30 | | 2001-11-01 | | 2001-12-01 | ++ 9 rows in set (10 min 53.83 sec) mysql select count(*) from traffic; +--+ | count(*) | +--+ | 2645563 | +--+ 1 row in set (0.00 sec) mysql is there a problem with my select statement?... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SQL query
Hi, On Fri, 2002-02-22 at 11:29, [EMAIL PROTECTED] wrote: Have u encountered this kind of query? mysql select edate from traffic where year(edate)='2001' group by month(edate); ++ | edate | ++ | 2001-04-29 | | 2001-05-01 | | 2001-06-01 | | 2001-07-01 | | 2001-08-01 | | 2001-09-01 | | 2001-10-30 | | 2001-11-01 | | 2001-12-01 | ++ 9 rows in set (10 min 53.83 sec) mysql select count(*) from traffic; +--+ | count(*) | +--+ | 2645563 | +--+ 1 row in set (0.00 sec) mysql is there a problem with my select statement?... Well, you make both the WHERE as well as the GROUP statement work on the result of an expression YEAR(...) MONTH(...) rather than a constant (like a plain column). Also, since you use an expression in your WHERE, the server can't use an index on edate even if there is one. So, to process your query, the server has to do the following: - Do a full table scan, calculating the YEAR() from every edate, checking it for the right year... - Once all that is collected, it has to go through all of the result set, calculating the MONTH() from every edate, and only THEN can it start grouping and summarising. No wonder the query takes a while What type is 'edate' exactly? If it's just a DATE field, one idea could be to store the DAY/MONTH/YEAR bits as separate columns. Then, if you index the YEAR field, that index can be used to quickly find all the rows within the right year. No expression evaluation necessary. As someone else mentioned, it might also be a good idea to simply have a separate table for each year. You can always use a MERGE table if you need to do queries that span over multiple years. Anyway, with the MONTH being a separate column, the GROUP statement no longer has to evaluate an expression. So there too will it be much faster. These are just a few basic ideas... issues such as these (and much more) are covered in MySQL training courses: http://www.mysql.com/training/. Regards, Arjen. -- MySQL Training in Brisbane: 18-22 March, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ 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
ERD for MySQL?
Are there any open source ERD programs for MySQL for doing basic data modelling? I tried myERD at sourceforge and it couldn't parse the sql dump from my database. It died with a parse exception on the unsigned attribute for my int columns. Any other choices out there? Thanks, --jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Wide Indexes
For reporting purposes, I usually use a wide index across all the fields that are relevant to creating the reports. So, for example, my sales table has this type of data: sale_id sale_date salesperson_id product_id referral_id [other sales data] sale_id is an auto_incrementing primary key. Most of the reports I run are date-based -- show me the sales for date, for salesperson, grouped by product_id and referral_id. My index for this table would be: (sale_date, salesperson_id, product_id, referral_id) My question is this: I know it's not good to index fields that have few unique values, but what about including a field like that in a wide index? If product_id only has 5 values, does it hurt to include it in this wide index? Would it be better to include it or leave it out, if I'm grouping by that field? Is there a rule of thumb for unique-ness when indexing? (i.e. a column should have X unique values before using it in an index...) Any insight is appreciated. Thanks, --jeff P.S. -- um, sql sql sql -- I can't believe how strict the filter for this list is... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Finding overlapping intervals efficiently
Given two tables (t1 and t2) with fields start and end, what is the most efficient method of finding the all the intervals in the first table that overlap any interval in the second table? Right now, I use a query like: SELECT t1.name, t2.name FROM t1, t2 WHERE t1.start = t2.end AND t1.end = t2.start; I use a key (start, end) in each table. Is there anything else that I can do to optimize this type of query (i.e. add keys, change the select statement)? The tables typically have thousands of records, so optimizing this query is very important. Thanks in advance for any help you can give me, Colin _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrade Problem
Hi, I have the following problem: I had to install a complete new server with Suse 7.2 (MySql: 3.23.37-8). From the the old installation I only got the backup files (created with 'mysqldump database my_databases.sql') and the the database file (ISD, ISM, frm). My problem is: I can't read the backup file (mysql database backup-file.sql). Executing the command nothing happens. Using the restore table syntax only empty tables are created. Does anybody has an idea who to get the data from the old files in the database version. Greetings, Andreas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Wide Indexes
On Thu, Feb 21, 2002 at 08:13:51PM -0800, Jeff Kilbride wrote: For reporting purposes, I usually use a wide index across all the fields that are relevant to creating the reports. So, for example, my sales table has this type of data: sale_id sale_date salesperson_id product_id referral_id [other sales data] sale_id is an auto_incrementing primary key. Most of the reports I run are date-based -- show me the sales for date, for salesperson, grouped by product_id and referral_id. My index for this table would be: (sale_date, salesperson_id, product_id, referral_id) Got it. My question is this: I know it's not good to index fields that have few unique values, but what about including a field like that in a wide index? I asked Monty about this last year and was surprised at his response. It's not nearly as bad of an idea as I had thought at the time. If product_id only has 5 values, does it hurt to include it in this wide index? Would it be better to include it or leave it out, if I'm grouping by that field? Is there a rule of thumb for unique-ness when indexing? (i.e. a column should have X unique values before using it in an index...) With a 4-field index like that, if you're mainly selected based on sale_date, there are only a few cases when having the product_id and referral_id indexed will really help. Here's one. If your query retrieves ONLY fields appearing in that index, MySQL will be smart enough to never even look at the table--it'll just used the index data directly. Is there another? Probably, but I haven't thought of it... Someone else my chime in. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 14 days, processed 468,981,120 queries (369/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Wide Indexes
Hmmm... Ok, my original assumption was that if I was grouping by those fields in my SELECT, then including them in the index would help -- if the order of my GROUP BY followed the order of the fields in the index. Your saying it doesn't make much of a difference whether I include them or not? Thanks, --jeff - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jeff Kilbride [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, February 21, 2002 11:19 PM Subject: Re: Wide Indexes On Thu, Feb 21, 2002 at 08:13:51PM -0800, Jeff Kilbride wrote: For reporting purposes, I usually use a wide index across all the fields that are relevant to creating the reports. So, for example, my sales table has this type of data: sale_id sale_date salesperson_id product_id referral_id [other sales data] sale_id is an auto_incrementing primary key. Most of the reports I run are date-based -- show me the sales for date, for salesperson, grouped by product_id and referral_id. My index for this table would be: (sale_date, salesperson_id, product_id, referral_id) Got it. My question is this: I know it's not good to index fields that have few unique values, but what about including a field like that in a wide index? I asked Monty about this last year and was surprised at his response. It's not nearly as bad of an idea as I had thought at the time. If product_id only has 5 values, does it hurt to include it in this wide index? Would it be better to include it or leave it out, if I'm grouping by that field? Is there a rule of thumb for unique-ness when indexing? (i.e. a column should have X unique values before using it in an index...) With a 4-field index like that, if you're mainly selected based on sale_date, there are only a few cases when having the product_id and referral_id indexed will really help. Here's one. If your query retrieves ONLY fields appearing in that index, MySQL will be smart enough to never even look at the table--it'll just used the index data directly. Is there another? Probably, but I haven't thought of it... Someone else my chime in. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 14 days, processed 468,981,120 queries (369/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php