LOG ON SLAVE WITH 4.0.15 (windows platform)
In my network there are one master 4.0.13 and 3 slaves with 4.0.15. STarting the slaves's update to 4.0.15 from 4.0.13 during the night when on the master there are not update I read on slaves's logs as follow: 30918 3:41:06 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 030918 3:41:06 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'pissarro-bin.330' position 34265744 030918 4:41:07 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 030918 4:42:07 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'pissarro-bin.330' position 34265744 Every hour the slaves writel about a problem to remain connect with the master. This situation are only from 4.0.15 . There is a way to avoid similar trace in the logs ? tks - Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escaping slashes not present in table?
--- Daniel Kasak [EMAIL PROTECTED] wrote: Abs wrote: mysql and php question: the magic_quotes_gpc is set to 1. when i echo it to the browser, it shows the added slashes. i inserted these same values into a database. when i read the database values and printed them, i forgot to use stripslashes. but i was surprised to see that the slashes were already removed. now i know this depends on magic_quotes_runtime, which i checked, was OFF, so how did the slashes get removed without me explicitly doing so? what's more interesting is that when i view the data in the table using phpMyAdmin, the quote i put wasn't escaped with a backslash. when i tried to do a dump/export of the table, the output showed a backslash before the quote. so how come? the slashes should be showing when i say SELECT * FROM MYTABLE. abs I think this is why people recommend that you *don't* use PHP's magic quotes. I hit this problem in a few areas and decided to turn it off. Use PHP's functions: stripslashes() and addslashes() You'll be sorry later if you don't, and continue using magic quotes... that still doesn't answer the question though. if the variables already had the slashes in them because of magic_quotes_gpc, then shouldn't the table i inerted it into also have the slash in it? magic_quotes_runtime ADDS slashes if it is ON, so it leaves the data untouched if it's OFF (if this is incorrect, the documentation needs to be updated). hence, when i retrieved the data from the db, the quotes should have still been there. and the o/p to browser, phpMyadmin (and macromedia dreamweaver's test window) all don't show any slashes in the data, but 'export' in phpMyadmin adds slashes to the text it outputs. magic_quotes_gpc is On magic_quotes_runtime is Off magic_quotes_sybase is Off (to be sure) another thing that comes to mind is... does mysql store the data in the .tbl/.frm files in their text form? or is the slash we add there just to tell mysql to disregard the significance of the next character? the 2nd one seems logical considering that mysql would read data per column in the specific size it's supposed to be (and not pay attention to the mean of what it's reading). so if a varchar(50) column has the data: `qwer'y\19o` then it knows that it should read and return 10 chars/bytes. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The server is not configured as slave???
I did a: CHANGE MASTER TO MASTER_HOST='XXX', MASTER_USER='XXX', MASTER_PASSWORD='XXX', MASTER_LOG_FILE='mysql-bin.034', MASTER_LOG_POS=1089001; and a: SLAVE START; but it gives me: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO What am i missing here, i have done the CHANGE MASTER TO?? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixed: The server is not configured as slave???
Well after a restart of the server, it seemed to work?? -Original Message- From: Søren Neigaard [mailto:[EMAIL PROTECTED] Sent: 18. september 2003 09:45 To: '[EMAIL PROTECTED]' Subject: The server is not configured as slave??? I did a: CHANGE MASTER TO MASTER_HOST='XXX', MASTER_USER='XXX', MASTER_PASSWORD='XXX', MASTER_LOG_FILE='mysql-bin.034', MASTER_LOG_POS=1089001; and a: SLAVE START; but it gives me: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO What am i missing here, i have done the CHANGE MASTER TO?? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave not replicating database
I have just added aother slave to a master i have running. I have used the command: CHANGE MASTER TO MASTER_HOST='XXX', MASTER_USER='XXX', MASTER_PASSWORD='XXX', MASTER_LOG_FILE='mysql-bin.034', MASTER_LOG_POS=1091117; With the correct IP/user/password/log-file/position. There are two databases on the master, but they do not get replicated to the slave? Why not? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slave not replicating database
whats the error? -Original Message- From: Søren Neigaard [mailto:[EMAIL PROTECTED] Sent: 18 September 2003 09:52 To: '[EMAIL PROTECTED]' Subject: Slave not replicating database I have just added aother slave to a master i have running. I have used the command: CHANGE MASTER TO MASTER_HOST='XXX', MASTER_USER='XXX', MASTER_PASSWORD='XXX', MASTER_LOG_FILE='mysql-bin.034', MASTER_LOG_POS=1091117; With the correct IP/user/password/log-file/position. There are two databases on the master, but they do not get replicated to the slave? Why not? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump with innoDB
Daniel Kasak wrote: Paul DuBois wrote: If you have problems reloading the table due to the order in which the InnoDB tables appear in the dump files, add SET FOREIGN_KEY_CHECKS = 0; to the beginning of the file before reloading it. Our backups are quick large - over 500 MB. Opening the file and adding the above line at the top takes a lot of CPU time and memory - and when I'm restoring, I don't have a lot of time... Is there an easier way to get it there - can I 'cat' to the beginning of a file, or should I make my backup scripts cat the output of mysqldump to the end of a file with 'set foreign_key_check=0;' at the top? Maybe we could have a switch for mysqldump that does this for us? quick solution: put the line SET FOREIGN_KEY_CHECKS = 0; in a file (eg.header.txt) and then: cat header.txt mysql.dump mysqlgood.dump should take about 5 secs. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Round Robin Replication, Add a server; Renumber
Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 05:34:12PM -0500, Lewis Watson wrote: I currently have three mysql machines replication from A-B-C-A type fashion. I need to replace A. I am thinking that I could add D and have it pull from C. Then once A is removed restart D as A. Is this a good way to do this or is there a better way that I should do this? Can't you make D a slave of A? Then swap it for A when you're ready? Are you saying that D will detect the correct master_file pos when you change it's master from A to B then (since D was then a slvae of A and you replace A by D)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication blues
Hi Victoria, Read the bug report, but couldn't find if the problem was fixed in 4.0.14 or not... bogdan iCom Media AG Kirchweg 36 Koln, 50858 Germany Phone: +49-(0)221-485-689-16 Fax : +49-(0)221-485-689-20 Mobile:+49-(0)173-269-76-62 On Wed, 17 Sep 2003, Victoria Reznichenko wrote: Bogdan TARU [EMAIL PROTECTED] wrote: So, back to the problems with auto_increment columns and replication problems. I have noticed this problem always occures when using INSERT SELECT syntax with an auto_increment key simple example: CREATE TABLE test1 (value INT); INSERT INTO test1 SET value=1; INSERT INTO test1 SET value=2; INSERT INTO test1 SET value=3; CREATE TABLE test2 (id INT PRIMARY KEY AUTO_INCREMENT, value INT); INSERT INTO test2 SELECT NULL, value FROM test1; test2 will have the keys 1,2,3 on the master and 3,4,5 on the slave. Both the master and the slave are running 4.0.13, master is on freebsd 4.8 and slave on linux. Thanks for report. Your bug the same as: http://bugs.mysql.com/bug.php?id=490 Upgrade MySQL server to the recent version. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
Jeremy, I don't think there's a huge difference in WinTel performance and Linux, given the same hardware. Anyway, your application is so lightweight, it's not really going matter very much. Ease of administration for you and your client will be far more important. Andy -Original Message- From: Jeremy Proffitt [mailto:[EMAIL PROTECTED] Sent: 17 September 2003 23:18 To: [EMAIL PROTECTED] Subject: Platform vs. Performance We are currently using an access database going through an odbc connection in vb.net for our application (which is still in development). The plan was to use MySQL in the end and we are needing to cross that bridge. I was wondering the Performance differences between running MySQL on a Linux box vs. a WinTel Platform. The WinTel looks inviting because we can put in an easy to administrate Windows 98 or XP Pro box in the corner of our clients office. I would rather not tackle Linux as I would need file sharing and the ability to backup and remote administration. Pros? Cons? I know the windows box would need a good reboot now and then and what is the performance hit on a WinTel 98/XP Pro platform vs Linux? Looking at a 40K record database with maybe 3 users at a time running 5-10 Large (length of the select statment is over 500 characters normally) queries at a time. Any help is greatly appriciated! Thanks! Jeremy Proffitt Computer Programmer, Pearson Appraisal Services, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Problems
Hei :) I have an extreme performance problem with a MySQL-DB. The database consists of 21 tables where all except three are storing only a few records. Two have about 150.000 records but they are only used temporary. The main table is rather huge, it has 90 columns and now after three month it has 500.000 records... but in the end it has to store data of 36 month. But since the table has grown to over 350.000 records I ran into massive performance problems. Querying for one record (Example: SELECT sendnr FROM pool where sendnr = 111073101180) takes 8 seconds via command line! The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf How can I get more performance out of the mysql? The server which currently only hosts this database and is running apache1.3 with php4 for providing results via intranet. The OS is FreeBSD 5.1. We are running two servers with the same enviroment One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5) the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD The Dual is the Productionserver, the P4 the developement- and test server. The querey takes that long on BOTH machines so it seems clear the DB itself is causing the performance problem. So anyone can help? This is really urgend and will save my life :) Big thanks in advance. Pacem, Matthias Schonder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
Pros? Cons? I know the windows box would need a good reboot now and then and what is the performance hit on a WinTel 98/XP Pro platform vs Linux? Looking at a 40K record database with maybe 3 users at a time running 5-10 Large (length of the select statment is over 500 characters normally) queries at a time. I would not bother with '98 if XP is an option. If it is a windows shop then go with that, but if it is OS agnostic, and you or ANOTHER will be admining it, then my choice would be a *nix variant as it is less likely to have some nasty RPC bug and easier to admin remotely. This assume good *nix knowledge. Apart from that the performance will be roughly the same, I say roughly as you do not need to run a truck load of stuff on a DB server in *nix land, so there is more resource available for apps, but I doubt your app will stress a modern machine that much. There cat amongst the pigeons. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set problem Linux - Windows
Hi Marcin, have you tried using unicode? Adam On 09/17/2003 02:02 PM Marcin Giedz wrote: Hi all, My company(situated in Poland) produce cross platform software based on Borland Delphi/Kylix. We also use mysql server to store all our date.Server is configured with latin2 character set. Nowadays several users work on Linux boxes(Debian + XFree 4.3.1 + KDE 3.1.3 + iso 8859-2 with LC_ALL=pl_PL) and rest on Windows 2000. The following problem occured: - when linux user insert string with Polish letters into table it looks OK but only on Linux application. In windows Polish letters like s with upper mark - and a with lower mark - and also z with upper mark - are changed with +- etc... - when windows user insert string with Polish letters into table it looks OK but only on Windows application. In linux Polish letters like s with upper mark - and a with lower mark - and also z with upper mark - are changed with +- etc... Is it possible to fix it??? Thanks,Marcin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
combining fields in select
I am trying to select the firstname and surname fields from a table. select firstname + surname fullname from people; This does work in other databases but does not seem to work here. Am I doing something wrong or is there a different way to achieve this with mysql? Michael
Re: combining fields in select
I am trying to select the firstname and surname fields from a table. select firstname + surname fullname from people; This does work in other databases but does not seem to work here. Am I doing something wrong or is there a different way to achieve this with mysql? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set problem Linux - Windows
- Original Message - From: Adam Hardy [EMAIL PROTECTED] To: Marcin Giedz [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:34 AM Subject: Re: Character set problem Linux - Windows Hi Marcin, have you tried using unicode? Not yet Adam but though about it!!! If I change to unicode how to change existing strings with Polish letters in mysql tables?? Marcin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: combining fields in select
On 18 September, 2003 11:56, Michael Piko [SMTP:[EMAIL PROTECTED] wrote: I am trying to select the firstname and surname fields from a table. select firstname + surname fullname from people; try: select concat(firstname,surname) as fullname from people; HTH, Mark --- Windows, Linux and Internet Development Consultant Email: [EMAIL PROTECTED] Web: http://www.scriptsmiths.co.za --- This does work in other databases but does not seem to work here. Am I doing something wrong or is there a different way to achieve this with mysql? Michael DISCLAIMER: This e-mail and its attachments may contain information that is confidential and that may be subject to legal privilege and copyright. If you are not the intended recipient you may not peruse, use, disclose, distribute, copy or retain this message. If you have received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and return and thereafter destroy the original message.Please note that e-mails are subject to viruses, data corruption, delay, interception and unauthorised amendment, and that the sender does not accept liability for any damages that may be incurred as a result of communication by e-mail. No employee or intermediary is authorised to conclude a binding agreement on behalf of the sender by e-mail without express written confirmation by a duly authorised representative of the sender. By transmitting this e-mail message over the Internet the sender does not intend to allow the contents hereof to become part of the public domain, and the confidential nature of the contents shall not be altered or diminished from by such transmission. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance Problems
Matthias, Can you send us your table index definitions and the output of an EXPLAIN command on your query? ie DESCRIBE pool; SHOW INDEX FROM pool; EXPLAIN SELECT sendnr FROM pool where sendnr = 111073101180; I'm pretty sure we can improve this - I've got a table with 55 million records (though only 3 columns) and a search like the one you've got takes 0.07 seconds on a box similar to your dev box. Andy -Original Message- From: Schonder, Matthias [mailto:[EMAIL PROTECTED] Sent: 18 September 2003 10:25 To: '[EMAIL PROTECTED]' Subject: Performance Problems Hei :) I have an extreme performance problem with a MySQL-DB. The database consists of 21 tables where all except three are storing only a few records. Two have about 150.000 records but they are only used temporary. The main table is rather huge, it has 90 columns and now after three month it has 500.000 records... but in the end it has to store data of 36 month. But since the table has grown to over 350.000 records I ran into massive performance problems. Querying for one record (Example: SELECT sendnr FROM pool where sendnr = 111073101180) takes 8 seconds via command line! The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf How can I get more performance out of the mysql? The server which currently only hosts this database and is running apache1.3 with php4 for providing results via intranet. The OS is FreeBSD 5.1. We are running two servers with the same enviroment One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5) the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD The Dual is the Productionserver, the P4 the developement- and test server. The querey takes that long on BOTH machines so it seems clear the DB itself is causing the performance problem. So anyone can help? This is really urgend and will save my life :) Big thanks in advance. Pacem, Matthias Schonder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: combining fields in select
Michael --Original Message- I am trying to select the firstname and surname fields from a table. select firstname + surname fullname from people; This does work in other databases but does not seem to work here. Am I doing something wrong or is there a different way to achieve this with mysql? select CONCAT(TRIM(firstname),' ',surname) AS fullname from people should work Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large number of Databases
Does anybody know of any issues when have a large (+1000) databases in MySQL? It will be running on RedHat 9. Would there be any problems running backups with this many DBs on one box? Regards Richard - Original Message - From: Harald Fuchs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 12:15 PM Subject: Re: mysqldump with innoDB In article [EMAIL PROTECTED], Daniel Kasak [EMAIL PROTECTED] writes: Paul DuBois wrote: If you have problems reloading the table due to the order in which the InnoDB tables appear in the dump files, add SET FOREIGN_KEY_CHECKS = 0; to the beginning of the file before reloading it. Our backups are quick large - over 500 MB. Opening the file and adding the above line at the top takes a lot of CPU time and memory - and when I'm restoring, I don't have a lot of time... Is there an easier way to get it there - can I 'cat' to the beginning of a file, or should I make my backup scripts cat the output of mysqldump to the end of a file with 'set foreign_key_check=0;' at the top? Maybe we could have a switch for mysqldump that does this for us? Here's an excerpt from my backup script: for db in $DBNAMES; do ( echo SET FOREIGN_KEY_CHECKS = 0; echo mysqldump -u backup --opt $db echo SET FOREIGN_KEY_CHECKS = 1; ) | bzip2 $date.$db.bz2 done -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fragmentation problem with MYD/MYI files
Hi, I have a large MyISAM database with 22 million rows. The MYD file is now 4 GB in size, and the MYI file is 2 GB. I am constantly adding rows to this database and rarely deleting any. Thus the database size grows constantly. MySQL seems to increase the database files (MYD/MYI) in very small increments. This has led to massive fragmentation of the files. Just recently my MYD file was in over 1 fragments! I am running MySQL 4.0.14b on Windows XP Pro. I have a 40 GB NTFS disk with 20 GB free space. I thought that NTFS could handle a situation like this without fragmenting files, but apparently that is not the case! Is it possible to configure MySQL so that it would allocate a large amount of extra space for the MYD/MYI files, and then use this space as necessary? This way the files would need to be grown only very rarely. It would be ideal if I could configure the increment in which MySQL increases the file sizes (for example, 100 MB, or a percentage of current file size). If this is not possible, I'd suggest to add such a feature to MySQL. I believe it would give a tremendous performance-boost for application where the databases keep growing. -- Mikko Noromaa ([EMAIL PROTECTED]) - tel. +358 40 7348034 Noromaa Solutions - see http://www.nm-sol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication blues
Bogdan TARU [EMAIL PROTECTED] wrote: Read the bug report, but couldn't find if the problem was fixed in 4.0.14 or not... Yes, it's fixed. http://www.mysql.com/doc/en/News-4.0.14.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for workaround for a SELECT statement.
Hi, Someone asked me if it were possible to do the following SELECT statement in MySQL. I am sure we need a workaround for this. Can someone please offer any clues or even answers? The statement: SELECT ename, empno, mgrnr, job FROM emp START WITH job = PRESIDENT CONNECT BY PRIOR empno = mgr Outcome: ename empno mgrnrjob KING 1122PRESIDENT JONES2233 1122 MANAGER SCOTT2345 2233 ANALYST ADAMS 4567 2233 ANALYST FORD 6677 1122 MANAGER SMITH 5432 6677 SALES BLAJE 5421 6677 SALES ALLEN 5467 6677 SALES WARD 9876 1122 MANAGER MARTIN 5643 9876 CLERK CLARK8743 9876 CLERK MILLER9832 9876 CLERK Here is what Oracle says about it: 1 Oracle selects the root rows of the hierarchy. These are the rows that satisfy the condition of the START WITH clause. 2 Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY clause with respect to one of the root row. 3 Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those chilren and so on. Thank you for your time. Angela Olmeijer
Re: Performance Problems
Do you use indexes? See http://www.mysql.com/doc/en/CREATE_INDEX.html. In my system a retrieval from a 24 million records table (3 columns) with a result of 25 records only took 0.09 sec and 24 million records table with 5 columns 0.25 sec Harald - Original Message - From: Schonder, Matthias [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:24 AM Subject: Performance Problems Hei :) I have an extreme performance problem with a MySQL-DB. The database consists of 21 tables where all except three are storing only a few records. Two have about 150.000 records but they are only used temporary. The main table is rather huge, it has 90 columns and now after three month it has 500.000 records... but in the end it has to store data of 36 month. But since the table has grown to over 350.000 records I ran into massive performance problems. Querying for one record (Example: SELECT sendnr FROM pool where sendnr = 111073101180) takes 8 seconds via command line! The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf How can I get more performance out of the mysql? The server which currently only hosts this database and is running apache1.3 with php4 for providing results via intranet. The OS is FreeBSD 5.1. We are running two servers with the same enviroment One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5) the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD The Dual is the Productionserver, the P4 the developement- and test server. The querey takes that long on BOTH machines so it seems clear the DB itself is causing the performance problem. So anyone can help? This is really urgend and will save my life :) Big thanks in advance. Pacem, Matthias Schonder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escaping slashes not present in table?
Abs, On Thu, Sep 18, 2003 at 08:35:25AM +0100, Abs wrote: --- Daniel Kasak [EMAIL PROTECTED] wrote: Abs wrote: mysql and php question: the magic_quotes_gpc is set to 1. when i echo it to the browser, it shows the added slashes. i inserted these same values into a database. when i read the database values and printed them, i forgot to use stripslashes. but i was surprised to see that the slashes were already removed. now i know this depends on magic_quotes_runtime, which i checked, was OFF, so how did the slashes get removed without me explicitly doing so? what's more interesting is that when i view the data in the table using phpMyAdmin, the quote i put wasn't escaped with a backslash. when i tried to do a Correct, it isn't there, so you don't see it. See below where you repeat your question. dump/export of the table, the output showed a backslash before the quote. so how come? the Correct. If mysqldump didn't put it there, it wouldn't be a valid MySQL query. slashes should be showing when i say SELECT * FROM MYTABLE. abs I think this is why people recommend that you *don't* use PHP's magic quotes. I hit this problem in a few areas and decided to turn it off. Use PHP's functions: stripslashes() and addslashes() You'll be sorry later if you don't, and continue using magic quotes... soapbox The recommended configuration of PHP has both magic_quotes_runtime and magic_quotes_gpc off, which is a good thing IMHO. The whole magic quotes is a hack anyway. Escaping (backslashing) is only needed when you put the values in quoted strings, e.g. HTML parameters and MySQL queries. The first case would need htmlspecialchars anyway, eliminating the need for addslashes. The second case could have been solved more cleanly by something like this: mysql_query_params(UPDATE mytable SET mycolumn = ?, $gpcval); This is how it's done in Perl DBI and some other database bindings and stored procedures. This reads more cleanly anyway, especially if you also need to manipulate $gpcvalue for this query. People would just need to be taught to use this method. Maybe it's too late for that now. By using magic quoting, users need to use stripslashes in places that are unrelated to the code that needs slashes. People don't do that so you see O\'Brien on their webpages. Or they do it too often, leading to errors in subsequent MySQL queries. The distinction between sources of strings (magic_quotes_gpc vs magic_quotes_runtime) doesn't help either. Anyway, maybe the whole magic quotes thingy prevents some security problems (XSS and SQL injections) in sloppy code. /soapbox that still doesn't answer the question though. if the variables already had the slashes in them because of magic_quotes_gpc, then shouldn't the table i inerted it into also have the slash in it? Like someone else mentioned on the list a short while ago, the slashes are only there in an attempt to make sure that queries are syntactically correct, even when users don't think twice (or once). Take this PHP statement: mysql_query(UPDATE mytable SET mycolumn = '$gpcval'); Now suppose that someone inputs O'Brien with magic_quotes_gpc off. Then $gpcvalue will just be O'Brien. The query will be: mysql_query(UPDATE mytable SET mycolumn = 'O'Brien'); This is syntactically incorrect because the value is O and Brien' is garbage that follows. [ If $gpcvalue contained '; DELETE from mytable and your database supports multiple queries (MySQL 5, 4.1?), you would just have lost all data. ] Now suppose that someone inputs O'Brien with magic_quotes_gpc on. Then $gpcvalue will be O\'Brien. The query will be: mysql_query(UPDATE mytable SET mycolumn = 'O\'Brien'); This is syntactically correct. The slash escapes the quote and MySQL interprets the value as O'Brien, which is what you want. Having O\'Brien in your database makes no sense. You shouldn't use stripslashes on values you get from the database. That is, unless your PHP configuration has magic_quotes_runtime set. magic_quotes_runtime ADDS slashes if it is ON, so it leaves the data untouched if it's OFF (if this is incorrect, the documentation needs to be updated). hence, when i retrieved the data from the db, the quotes should have still been there. and the o/p to browser, phpMyadmin (and macromedia dreamweaver's test window) all don't show any slashes in the data, but 'export' in phpMyadmin adds slashes to the text it outputs. magic_quotes_gpc is On magic_quotes_runtime is Off magic_quotes_sybase is Off (to be sure) another thing that comes to mind is... does mysql store the data in the .tbl/.frm files in their text form? or is the slash we add there just to tell mysql to disregard the significance of the Correct, it is just there to escape the next character. next character? the 2nd one seems logical considering that mysql would read data per column in the specific size
Re: combining fields in select
Piko, Please see http://www.mysql.com/doc/en/String_functions.html for more information about String functions in the SELECT. The function you're looking for is CONCAT or CONCAT_WS. Harald - Original Message - From: Michael Piko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:55 AM Subject: combining fields in select I am trying to select the firstname and surname fields from a table. select firstname + surname fullname from people; This does work in other databases but does not seem to work here. Am I doing something wrong or is there a different way to achieve this with mysql? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help writing query
I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
I have only one thing to say really on this. Data never has any meaning at all. It is simply data, and not information. Information is extracted from data and is then given meaning by the viewer of the data. In the case of NULL I have always thought of it is simply undefined. Thats what Orecal says it is, and Paradox, Interbase, informix, and the if I type the list of databases I've used where that was the general idea it will go on and on and on all day long :-) As an aside, it was a sad day when codd died. - Original Message - From: Bob Hall [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 8:57 PM Subject: Re: Does Null == ? : Bruce Feist has initiated a discussion with me off the list, and : has forced me to refine the way I express my position somewhat. : The combination of on-list and off-list discussion has been interesting, : but it's getting overwhelming, and reminds me that I have a tendency to : overindulge in controversy. Or, more generally, I never met a keyboard : I didn't like. (Actually, I own one that I hate, but that's another : matter.) This will be my last contribution to this thread. I'll : read the thread, so you can all make points at my expense, but I'll : take a tip from Odysseus and strap myself to the mast. : : I'm not an expert on the SQL standard. Specifically, it has occured to me : that I don't actually know whether the SQL standard defines NULL as : Not Known or Not Applicable. I've always been told that it does, : but I can't confirm that. : : Here's what I do know. The Interim Report 75-02-08 to the ANSI X3 : (SPARC Study Group 1975) described fourteen possible kinds of missing : data. Codd divided them into two groups, designated by his I-mark and : A-mark. The ANSI SQL committee lumped them under one heading, and called : that NULL. I think we all agree that we would have preferred Codd's : approach. : : The conventional definition of NULL, whether or not it is included in : the SQL standard, is Not Known or Not Applicable. This is both : precise and ambiguous: Ambiguous because it has two possible meanings, : and precise because it has only those two meanings. Of course, it can : be further subdivided into fourteen types of missing data. : : NULL has another, operational definition which is both precise and : unambiguous, and I can confirm that this definition is in the SQL : standard. This definition has several parts, which define how NULL : effects various database operations, such as SORT, SELECT, addition, : subtraction, etc. We only need to deal with one part, from which the : other parts can more or less be implied. That part is the requirement : that whenever a comparison operator compares NULL with any other value, : including itself, the operator should return UNKNOWN. Every DBMS I know : of implements UNKNOWN as FALSE, but the standard calls for UNKNOWN. : So an unambiguous definition of NULL, found in the SQL standard, is : the value that always causes a comparison operator to return FALSE in : any known DBMS. : : The problem with the unambiguous definition is that it is often the : hardest to use. The unambiguous definition of NULL doesn't make it : obvious why, or when, a database would contain NULL. The Not Known : or Not Applicable definition, while ambiguous, does make it clear. : So those of us who design databases use the ambiguous definition, and : our computers use the unambiguous definition. This works fine, as long : as we remember that there's no ambiguity in the way that NULL is : implemented in a properly designed RDBMS. A computer doesn't know whether : data is unknown or inapplicable; those concepts don't mean anything to : it. All it cares about is whether it receives a 0 or 1 from a comparison : operation. : : To carry the anthropomorphism further, a computer doesn't care, and : an RDBMS doesn't care, but a database lives at a higher level of : abstraction, and it may care. For example, when I bought a radiator : for my car, the auto parts clerk asked me if I had an automatic : transmission. Cars that have automatic transmissions need radiators : with extra tubes for cooling the transmission fluid. Cars that have : manual transmissions don't need the extra tubes, and the radiators : usually aren't interchangeable. On the other hand, I use the same : valve lifters, regardless of the transmission type. So the auto parts : table has to include an attribute for goes with an automatic : transmission. For a radiator, this would contain TRUE or FALSE. : For a valve lifter, this would be inapplicable. If it were unknown : instead of inapplicable, the data would have to be supplied before the : part could be sold. So the distinction between unknown and inapplicable : can be important, in theory. : : In practice, this rarely happens. If necessary data is missing, no : record is created until the missing data is supplied. Parts warehouses : don't want parts in their databases if they don't know which cars can :
Re: Performance Problems
The main table is rather huge, it has 90 columns and now after three month it has 500.000 records... but in the end it has to store data of 36 month. Hmm, I think you had better look at normalizing your data, and creating indexes. Start with the indexes since that won't force you to make any other changes, but 90 columns in one table sound like you put everything in that one table rather than normalizing. Take a look at this article for some (somewhat dated) info. http://www.linux-mag.com/2001-06/mysql_01.html Also what version of MySQL are you running and what table type? Have you looked at the processes that are running when run your query? mysqladmin -p process Will show what is happening, another very useful tool is mytop: http://jeremy.zawodny.com/mysql/mytop/ Scott Helms -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB error
Hello, I started getting this error since I upgraded from mysql 4.0.13 to 4.0.15: 030918 7:17:13 InnoDB: Error: page 12412 log sequence number 0 670697749 InnoDB: is in the future! Current system log sequence number 0 186563990. InnoDB: Your database may be corrupt. any idea how to get rid of this? thanks -- -- Gustavo Baratto - Systems Engineer [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: /etc/my.cnf config file doesn`t exist on my system
Hi Jamie: Thank very much you for your help. EDWIN LIMACHI N. DATACOM - Instalaciones TSE - INFONET BOLIVIA Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 James M Kupernik [EMAIL PROTECTED] 17/09/2003 21:01 Por favor, responda a [EMAIL PROTECTED] Para [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] cc Asunto RE: /etc/my.cnf config file doesn`t exist on my system you just need to restart mysql now as long as you put the file into /etc/my.cnf shutdown mysql: mysqladmin -u root -p shutdown start: safe_mysqld The file never installed from me from the RPM either, i had to manually copy it in, yet the server will work without it. Hope that helps. Jamie -Original Message- From: James M Kupernik [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 4:35 PM To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: /etc/my.cnf config file doesn`t exist on my system Look in /usr/share/doc/packages/MySQL-server/ for some example my.cnf and find the one that works for you and copy it the /etc Jamie -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 4:17 PM To: [EMAIL PROTECTED] Subject: /etc/my.cnf config file doesn`t exist on my system Importance: High Dear list: I`ve just to install the binary RPM MySQL-serverv 4.0.15 into my Linux RH7.3 box. The installation was successfull however when I tried to edit the /etc/my.cnf file to configure error and query path logs, my Linux system says : /etc/cnf: No such file or directory However when I runn rpm -ql MySQL-server I see this output: /etc/init.d/mysql /etc/logrotate.d/mysql /etc/my.cnf /usr/bin/isamchk /usr/bin/isamlog /usr/bin/my_print_defaults /usr/bin/myisamchk /usr/bin/myisamlog /usr/bin/myisampack /usr/bin/mysql_convert_table_format /usr/bin/mysql_explain_log /usr/bin/mysql_fix_extensions /usr/bin/mysql_fix_privilege_tables /usr/bin/mysql_install_db /usr/bin/mysql_secure_installation /usr/bin/mysql_setpermission /usr/bin/mysql_zap /usr/bin/mysqlbug /usr/bin/mysqld_multi /usr/bin/mysqld_safe /usr/bin/mysqlhotcopy /usr/bin/mysqltest /usr/bin/pack_isam /usr/bin/perror /usr/bin/replace /usr/bin/resolve_stack_dump /usr/bin/resolveip /usr/bin/safe_mysqld /usr/lib/mysql/mysqld.sym /usr/sbin/mysqld /usr/sbin/rcmysql the rest was ommited for me I reinstalled the RPM packet but the problem exist still. What should I do? Some one have any suggestions? EDWIN LIMACHI N. DATACOM - Instalaciones TSE - INFONET BOLIVIA Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
help with table design/performance
Hi All, I'm designing a rather large database, and I'm concerned about performance. Was wondering if anyone had any comments/advice? The particular table I'm concerned about will have about 10 million lines, each referencing a physical item I need to track. Each item in the table will have a auto_increment primary key that will be indexed (of course). However, I will also want to search on these items by other fields... their current location, for example (and a few other fields). This table will also be very active-- probably 10-20 inserts/deletions a second, so I am concerned about performance with having lots of fields indexed. So, I thought, instead of indexing all of these fields in the single table, I was considering creating multiple auxillary lookup tables that would only have two fields for each of the 10 million lines. First, the field (indexed) I want to search on (item location, for example), and the second field being the auto_increment primary key for the master table. So, I could search on these other fields (find all items in location A, for example), but the only field indexed in the main table will be the primary key, while the other search points will be easily accessible/searchable from the auxillary lookup tables. The main item table will still store the value, but it will not be indexed. Make sense? I feel like breaking the table apart, and having only a single index per table should help, rather than having a single table with multiple indexes. How have other people handled this sort of thing? TIA. -=-=-=-=-=-=-=- Brad LaJeunesse, PINES System Administrator Georgia Public Library Service www.georgialibraries.org Scotty, I need warp speed in three minutes or we're all dead! --Admiral James T. Kirk, Star Trek II: The Wrath of Khan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help writing query
Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
I don't see anything in there that is relevant to the original posting. Andy -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: 18 September 2003 14:04 To: [EMAIL PROTECTED] Subject: Re: Platform vs. Performance In article [EMAIL PROTECTED], Andy Eastham [EMAIL PROTECTED] writes: Jeremy, I don't think there's a huge difference in WinTel performance and Linux, given the same hardware. The following URL tells you that there's a big difference between Windoze and Linux: http://www.mysql.com/information/presentations/presentation-oscon2 000-2719/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running a definition file from MS-SQL
Hi all! (My apologies if this has been beaten to death allready, but I *did* search the archives without much luck.) I'm moving from MS SQL to MySQL. Before jumping from the old server, I scripted out the definitions for tables and their fields, thinking I might just run them into MySQL. Guess I need to edit the script some, because it keeps giving me a Syntax error message. Anyone know of a good source for info on how to make MS SQL scripts work on MySQL? I also have a copy of the database exported in MS Access format to hold the data - don't suppose it's any easier that way, uh? Thanks! peace, Tormod in Stockholm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
Hi, All! SQL, mysql I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; Is it right? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running a definition file from MS-SQL
Most likely you'd need to do some datatype mapping changes to the script... Everyone I know who's had to do this has typically used something like sqlyog (search google) and used the ODBC import cabability to transfer data from MSSQL - MySQL.. On Thu, 18 Sep 2003, Tormod Halvorsen wrote: Hi all! (My apologies if this has been beaten to death allready, but I *did* search the archives without much luck.) I'm moving from MS SQL to MySQL. Before jumping from the old server, I scripted out the definitions for tables and their fields, thinking I might just run them into MySQL. Guess I need to edit the script some, because it keeps giving me a Syntax error message. Anyone know of a good source for info on how to make MS SQL scripts work on MySQL? I also have a copy of the database exported in MS Access format to hold the data - don't suppose it's any easier that way, uh? Thanks! peace, Tormod in Stockholm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large number of Databases
no problem for me... 1000+ DBs on RH linux (ext3 fs), then moved it to freebsd 5.1 (almost 1500). Linux is probably faster because of the kernel based thread, but I like BSD. You definetely have to tune your my.cnf... use thread and query cache, increase the key buffer, optimize tables very often (I do it every day... takes 7 minutes in a dual xeon 2Ghz), increase sort and join size. good luck Harald Fuchs wrote: In article [EMAIL PROTECTED], Richard [EMAIL PROTECTED] writes: Does anybody know of any issues when have a large (+1000) databases in MySQL? It will be running on RedHat 9. Would there be any problems running backups with this many DBs on one box? Some filesystems become slow if you have +1000 subdirectories. ReiserFS doesn't have that problem. -- -- Gustavo Baratto - Systems Engineer [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
I'm pretty sure you need to sync the entire database (all tables) to all slaves before starting replication..Your servers are technically already out of sync.. And no wonder it crashes, tables are missing in it's view..You need to hit the initial replication setup manual pages.. On Thu, 18 Sep 2003, Andrey Kotrekhov wrote: Hi, All! SQL, mysql I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; Is it right? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA
I am using the following command to load a table: LOAD DATA LOCAL INFILE email.txt INTO TABLE email; The command works and the second and third column go in correctly. But the first column is not loading correctly. I am using Notepad on WIN ME to generate the txt file. JohnDoe[EMAIL PROTECTED] The problem appears to be the enter key that allows me to go to the next line. The last line where I donot use the enter key works fine. Has anyone experienced this problem or know a fix. I am manual executing the load data from a DOS window. I have mysql server 4.0.14 - max -debug and client 4.0.14. Thanks for any help in advance. upscope This email scanned by NAV2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Running a definition file from MS-SQL
Tormod, I feel your pain brother! Actually, I do this allot more than I'd prefer to admit. I assume when you say MS SQL you mean SQL Server 2000. When you say MySQL, you mean MySQL 4.0.x. Scripting the database/tables from query analyzer doesn't produce files, which are immediately executable in MySQL - as you know. The reason being the syntax is a little different and more importantly SQL Server supports a number of features MySQL doesn't. Keep an eye on the dates. The format for dates in SQL (MM-DD-) is a different from MySQL (-MM-DD). My SQL wraps dates with single quotes while SQL Server uses hashes. If you're using referential integrity, try InnoDB or BDB tables versus MyISAM tables. Note, default values are constraints in SQL Server not in MySQL. No triggers or stored procs. Function names will change from sp_xxx to the mysql equivalent. Lots of security differences especially where user setup is concerned. Make sure you're security scripts account for user ID and host. To answer your question, there is a data base migration tool, which I've used to move DBs from any_db to Sybase's ASE. Not sure if it works for MySQL. To be honest, I just usually run through the script myself and make the necessary changes. If you run into any specific problems, shoot me a note. I might be able to help. Regards, Adam -Original Message- From: Tormod Halvorsen [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:41 AM To: [EMAIL PROTECTED] Subject: Running a definition file from MS-SQL Hi all! (My apologies if this has been beaten to death allready, but I *did* search the archives without much luck.) I'm moving from MS SQL to MySQL. Before jumping from the old server, I scripted out the definitions for tables and their fields, thinking I might just run them into MySQL. Guess I need to edit the script some, because it keeps giving me a Syntax error message. Anyone know of a good source for info on how to make MS SQL scripts work on MySQL? I also have a copy of the database exported in MS Access format to hold the data - don't suppose it's any easier that way, uh? Thanks! peace, Tormod in Stockholm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Enquiry
Hello I hope you can help with this enquiry. I have a small access db split front and back end used by a small group. I would like to convert it to MySQL but my only knowledge is with access. I would also like to investigate the possibilty of producing small db for other people. I accept that i would require a licence from you to do this at a cost. Could you answer the following: What would be the best GUI/front end to use? Do the relationships, primary keys etc work the same way as access? Once MySQL is on our server can we connect with just the front a client or both? Regards M.A.Worrell 011 454 0838 083 320 4991 [EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
I don't see anything in there that is relevant to the original posting. The tables headed Speed difference between different SQL servers (times in seconds) The top row of each table shows times for the same operation on Linux and Windows, showing that for both operations tested, Linux achieves 50% better throughput (takes 2/3 of the time). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
Alec, My point was that in a 40,000 row database, server speed is irrelevant - it's going to be sub second on anything more powerful than my mobile phone. Cost of ownership is much more important for this application, and that depends on the particular circumstances. Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 September 2003 14:53 To: [EMAIL PROTECTED] Cc: Mysql List Subject: RE: Platform vs. Performance I don't see anything in there that is relevant to the original posting. The tables headed Speed difference between different SQL servers (times in seconds) The top row of each table shows times for the same operation on Linux and Windows, showing that for both operations tested, Linux achieves 50% better throughput (takes 2/3 of the time). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
Hi, All! SQL, mysql Sorry. I am wrong. Situation is worse. Both tables aren't replicate to the slave. But query try run it on slave :( slave: mysql-4.0.13 master: mysql-4.0.14 I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; Is it right? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique Key Violation - How to determine which key
O.K., I understand. And I have always coded in this manner. Throw the data at the system and see if it sticks. In most cases it will. However, when it does fail, it would be nice to determine the error w/o issuing more calls. So, the school solution is, When a unique constraint is violated, issue selects for each of the unique contrained columns to determine which one was violated? Thanks From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Scot Campbell [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Unique Key Violation - How to determine which key Date: Wed, 17 Sep 2003 21:48:23 -0700 On Wed, Sep 17, 2003 at 03:07:41PM -0700, Scot Campbell wrote: These will be random atomic Inserts originating from a Web page. I'm not sure I catch your drift. The inserts are not in a batch. I need to notify the user on the page which field was in error. I'd like to refer to the index name in the schema and relate it to the field that contains the non-duplicate data (i.e., error message on the email address vs. error message on the userid field). What I'm saying is this. If you expect most inserts to succeede, then code that way. Insert a row. If it fails, then go to the effort of finding the offending key. Otherwise, if you check every key before the insert, you're wasting effort most of the time. That's all. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 158,913,444 queries (438/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize tables and innodb
Hi all, for myisam tables we have optimize table that can be cronned to run at night, but is there something like this for the innodb table type as well? Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query join problem
I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the sending state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? -- Allen Brost -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize tables and innodb
from the manual: - 7.5.12.3 Defragmenting a Table If there are random insertions or deletions in the indexes of a table, the indexes may become fragmented. By fragmentation we mean that the physical ordering of the index pages on the disk is not close to the alphabetical ordering of the records on the pages, or that there are many unused pages in the 64-page blocks which were allocated to the index. It can speed up index scans if you periodically use mysqldump to dump the table to a text file, drop the table, and reload it from the dump. Another way to do the defragmenting is to ALTER the table type to MyISAM and back to InnoDB again. Note that a MyISAM table must fit in a single file on your operating system. If the insertions to and index are always ascending and records are deleted only from the end, then the file space management algorithm of InnoDB guarantees that fragmentation in the index will not occur. Franky wrote: Hi all, for myisam tables we have optimize table that can be cronned to run at night, but is there something like this for the innodb table type as well? Franky -- -- Gustavo Baratto - Systems Engineer [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize tables and innodb
from the manual: - 7.5.12.3 Defragmenting a Table If there are random insertions or deletions in the indexes of a table, the indexes may become fragmented. By fragmentation we mean that the physical ordering of the index pages on the disk is not close to the alphabetical ordering of the records on the pages, or that there are many unused pages in the 64-page blocks which were allocated to the index. It can speed up index scans if you periodically use mysqldump to dump the table to a text file, drop the table, and reload it from the dump. Another way to do the defragmenting is to ALTER the table type to MyISAM and back to InnoDB again. Note that a MyISAM table must fit in a single file on your operating system. If the insertions to and index are always ascending and records are deleted only from the end, then the file space management algorithm of InnoDB guarantees that fragmentation in the index will not occur. Franky wrote: Hi all, for myisam tables we have optimize table that can be cronned to run at night, but is there something like this for the innodb table type as well? Franky -- -- Gustavo Baratto - Systems Engineer [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique Key Violation - How to determine which key
On Thu, Sep 18, 2003 at 08:20:00AM -0700, Scot Campbell wrote: O.K., I understand. And I have always coded in this manner. Throw the data at the system and see if it sticks. In most cases it will. However, when it does fail, it would be nice to determine the error w/o issuing more calls. Agreed. So, the school solution is, When a unique constraint is violated, issue selects for each of the unique contrained columns to determine which one was violated? Yup. That's all you can do, aside from parsing the error... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 181,918,749 queries (453/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large number of Databases
On Thu, Sep 18, 2003 at 10:51:49AM -0300, Gustavo A. Baratto wrote: no problem for me... 1000+ DBs on RH linux (ext3 fs), then moved it to freebsd 5.1 (almost 1500). Linux is probably faster because of the kernel based thread, but I like BSD. If you use LinuxThreads on FreeBSD, it's nearly as fast, FWIW. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 181,946,979 queries (453/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query join problem
In the last episode (Sep 18), Allen said: I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the sending state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? You _have_ to add indexes. Mysql is currently scanning the entire 250k table for each record in the big table to find matching records. You can verify this by running EXPLAIN SELECT .. etc on your query and multiplying all the rows values together. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large number of Databases
I already use linuxthreads for freebsd... Do you think mysql would run well with the new threads for freebsd 5.XX? Did anybody try it? thanks Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 10:51:49AM -0300, Gustavo A. Baratto wrote: no problem for me... 1000+ DBs on RH linux (ext3 fs), then moved it to freebsd 5.1 (almost 1500). Linux is probably faster because of the kernel based thread, but I like BSD. If you use LinuxThreads on FreeBSD, it's nearly as fast, FWIW. -- -- Gustavo Baratto - Systems Engineer [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
Andrey Kotrekhov [EMAIL PROTECTED] wrote: Sorry. I am wrong. Situation is worse. Both tables aren't replicate to the slave. But query try run it on slave :( slave: mysql-4.0.13 master: mysql-4.0.14 What replication options do you use? I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query join problem
Allen wrote: I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the sending state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? Well, running a join on tables without indexes is like running a simple select on a table that is the cartesian product of both tables. In your case, you are trying to run a select on a table with: 13million x 250,000 = 3,250,000,000,000 rows ! I am afraid that you won't get any result soon, even with a big join buffer :( Regards, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large number of Databases
In the last episode (Sep 18), Gustavo A. Baratto said: I already use linuxthreads for freebsd... Do you think mysql would run well with the new threads for freebsd 5.XX? Did anybody try it? I've been running mysql 4.1 on a -current box with likse threads and haven't had any problems. I haven't stressed it, though. It's a very lightly-used database. If you have a testbox, try it out and report any lockups or panics to [EMAIL PROTECTED] I have libc_r mapped to libkse (man libmap.conf) and have had good results with all of the pthreads-using programs I run. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another permission question...
Tore Bostrup [EMAIL PROTECTED] wrote: MySQL 3.23.36 (yeah, I know it's getting old...): What permission do I need to run the following query: REVOKE ALL ON mydb.* FROM 'sdaf'@'%'; I get an error 1044: Access denied for user: 'Admin@IP address' to database 'mydb' My user has GRANT permission on mydb (database) and GRANT permission on all tables except two (with only SELECT permission). I tried removing all table based permissions, but that didn't help. I assigned GRANT permissions to all tables in the database, but that didn't help either. Assigning SELECT, UPDATE permissions on the mysql database didn't help either? You should have GRANT privilege and the same privileges as you try to revoke, i.e. if you write REVOKE ALL .., you should have all privileges on the database mydb. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: random access denied
It's not a continued access denied, because it can accept connections all day long, but randomly it will just deny one, but the accept it on the next try. I've googled this problem to death, I don't know what else I can do -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 5:20 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: random access denied At 03:09 PM 9/17/2003, you wrote: Anything that anyone can suggest that might point me in some direction would be very helpful. I'm just at a complete lose right now ... James, See the Google Group search: http://groups.google.ca/groups?hl=enlr=ie=UTF-8oe=UTF-8safe=offq=mysql+ %22Access+denied+for+user%22btnG=Google+Search There are quite a few suggestions listed there. Mike -Original Message- From: James M Kupernik [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 3:58 PM To: [EMAIL PROTECTED] Subject: random access denied Hello, I am having a frustrating problem with MySQL. I don't consider myself a newbie, nor am I a master, but either way I can't figure out this problem and I'm hoping someone here has an idea of what is going wrong. Every so often MySQL decides it doesn't want to authenticate a user, doesn't matter on the user/db, etc;. The error message is: 030916 16:10:50 39 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have a whole log full of these messages. Now and soon as the page is refreshed or you try logging in again (from the command line) it goes through. It's only a temp error. If anyone has any suggestion I'm more than willing to try them. This server was supposed to be in production about a week ago and I can't let it go like this. Thanks very much!! Jamie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: random access denied
At 11:17 AM 9/18/2003, you wrote: It's not a continued access denied, because it can accept connections all day long, but randomly it will just deny one, but the accept it on the next try. I've googled this problem to death, I don't know what else I can do James, Are you sure it's not something simple like exceeding the Max_Connections? (Default is 100) I'm sure you've checked this but there's no harm in asking. For something a little more esoteric, check out the user's Grant properties. It is possible to define Max_Connections_Per_Hour, Max_Updates_Per_Hour or Max_Queries_Per_Hour for the user. So he could be prohibited one hour, but next hour he regains access. Mike -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 5:20 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: random access denied At 03:09 PM 9/17/2003, you wrote: Anything that anyone can suggest that might point me in some direction would be very helpful. I'm just at a complete lose right now ... James, See the Google Group search: http://groups.google.ca/groups?hl=enlr=ie=UTF-8oe=UTF-8safe=offq=mysql+ %22Access+denied+for+user%22btnG=Google+Search There are quite a few suggestions listed there. Mike -Original Message- From: James M Kupernik [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 3:58 PM To: [EMAIL PROTECTED] Subject: random access denied Hello, I am having a frustrating problem with MySQL. I don't consider myself a newbie, nor am I a master, but either way I can't figure out this problem and I'm hoping someone here has an idea of what is going wrong. Every so often MySQL decides it doesn't want to authenticate a user, doesn't matter on the user/db, etc;. The error message is: 030916 16:10:50 39 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have a whole log full of these messages. Now and soon as the page is refreshed or you try logging in again (from the command line) it goes through. It's only a temp error. If anyone has any suggestion I'm more than willing to try them. This server was supposed to be in production about a week ago and I can't let it go like this. Thanks very much!! Jamie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large number of Databases
On Thu, Sep 18, 2003 at 12:49:09PM -0300, Gustavo A. Baratto wrote: I already use linuxthreads for freebsd... Ah, okay. Most don't, which is why I mentioned it. Do you think mysql would run well with the new threads for freebsd 5.XX? Did anybody try it? In theory, yes. In practice, not yet. I'm told by those with more FreeBSD knowledge that me that it's not stable/mature enough. But I do plan to try that out myself soon... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 182,382,731 queries (453/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting only the exact record from double
Chris [EMAIL PROTECTED] wrote: Quick question... I need to be able to send a query like: select cd,fid,s_from,s_to from ima where '3355443200' between s_to and s_from Thats fine and all, but when I get my results I pull 2 records: MA smith 335544320 352321535 BS mango 3355443200 3355445247 I must only return the 1 record ( 3355443200 ), but it appears that the zero's are throwing it off. If s_to and s_from have string type, value are compared as strings. Remove quotes around 3355443200. mysql select '3355443200' between '335544320' and '352321535'; +--+ | '3355443200' between '335544320' and '352321535' | +--+ |1 | +--+ 1 row in set (0.00 sec) mysql select 3355443200 between '335544320' and '352321535'; ++ | 3355443200 between '335544320' and '352321535' | ++ | 0 | ++ 1 row in set (0.00 sec) How can I select only the EXACT record?? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: random access denied
Thank you, I have checked all of the above. I have set the max connections to 200, but that didn't help any. The connection per hour is not a factor because it doesn't lock out the user, it just fails to authenticate, but as soon as you refresh the page or try to connect via the command line to goes through. Jamie -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:37 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: random access denied At 11:17 AM 9/18/2003, you wrote: It's not a continued access denied, because it can accept connections all day long, but randomly it will just deny one, but the accept it on the next try. I've googled this problem to death, I don't know what else I can do James, Are you sure it's not something simple like exceeding the Max_Connections? (Default is 100) I'm sure you've checked this but there's no harm in asking. For something a little more esoteric, check out the user's Grant properties. It is possible to define Max_Connections_Per_Hour, Max_Updates_Per_Hour or Max_Queries_Per_Hour for the user. So he could be prohibited one hour, but next hour he regains access. Mike -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 5:20 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: random access denied At 03:09 PM 9/17/2003, you wrote: Anything that anyone can suggest that might point me in some direction would be very helpful. I'm just at a complete lose right now ... James, See the Google Group search: http://groups.google.ca/groups?hl=enlr=ie=UTF-8oe=UTF-8safe=offq=m ysql+ %22Access+denied+for+user%22btnG=Google+Search There are quite a few suggestions listed there. Mike -Original Message- From: James M Kupernik [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 3:58 PM To: [EMAIL PROTECTED] Subject: random access denied Hello, I am having a frustrating problem with MySQL. I don't consider myself a newbie, nor am I a master, but either way I can't figure out this problem and I'm hoping someone here has an idea of what is going wrong. Every so often MySQL decides it doesn't want to authenticate a user, doesn't matter on the user/db, etc;. The error message is: 030916 16:10:50 39 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have a whole log full of these messages. Now and soon as the page is refreshed or you try logging in again (from the command line) it goes through. It's only a temp error. If anyone has any suggestion I'm more than willing to try them. This server was supposed to be in production about a week ago and I can't let it go like this. Thanks very much!! Jamie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize tables and innodb
Recently, the great Heikki said that the following directive should also defragment your InnoDB tables: ALTER TABLE table_name TYPE=InnoDB; How was it put...ah yes, a table no-op. Regards, Chris On Fri, 2003-09-19 at 01:32, Gustavo A. Baratto wrote: from the manual: - 7.5.12.3 Defragmenting a Table If there are random insertions or deletions in the indexes of a table, the indexes may become fragmented. By fragmentation we mean that the physical ordering of the index pages on the disk is not close to the alphabetical ordering of the records on the pages, or that there are many unused pages in the 64-page blocks which were allocated to the index. It can speed up index scans if you periodically use mysqldump to dump the table to a text file, drop the table, and reload it from the dump. Another way to do the defragmenting is to ALTER the table type to MyISAM and back to InnoDB again. Note that a MyISAM table must fit in a single file on your operating system. If the insertions to and index are always ascending and records are deleted only from the end, then the file space management algorithm of InnoDB guarantees that fragmentation in the index will not occur. Franky wrote: Hi all, for myisam tables we have optimize table that can be cronned to run at night, but is there something like this for the innodb table type as well? Franky -- -- Gustavo Baratto - Systems Engineer [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Funny behaviour with '?' - SQL, SELECT
Franz, Fa. PostDirekt MA [EMAIL PROTECTED] wrote: I tried SELECT * FROM Table WHERE Column LIKE '%?%'; to find out if german umlauts (???) are imported. In the resultset where some ds with '?' and some with 'y'. So the charset itself seems to be OK but why did I get 'y'. They sound sometimes the same, but they are not the same. How can I fix this? MySQL-version is 3.23 , same with 4.1. What character set do you use? (default-character-set for 3.23 and character set and collation for 4.1) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Platform vs. Performance
Are these numbers still valid? Those documents are quite old - well before the time of 4.0 being declared production ready and before the inclusion of InnoDB tables. Since then, the Windows port has had a lot of work done on it and many of the TODO sections at the bottom have been taken care of. In other news, Windows is a hideous OS and should be avoided for all server duties, unless your only alternative is made by SCO. Regards, Chris On Fri, 2003-09-19 at 01:59, Harald Fuchs wrote: In article [EMAIL PROTECTED], Andy Eastham [EMAIL PROTECTED] writes: I don't see anything in there that is relevant to the original posting. Andy The following URL tells you that there's a big difference between Windoze and Linux: http://www.mysql.com/information/presentations/presentation-oscon2 000-2719/index.html Speed difference between different SQL servers (times in seconds) Reading 200 rows by key:NT Linux mysql 367 249 Inserting (350768) rows:NT Linux mysql 381 206 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and Auto_Increment
We're running bi-directional replication between two servers running
MySQL 3.23.56.
One server being active the other a hot standby.
Wanting to switch to the hot standby I noticed that replication had
ceased on the HS server. The show slave status:
| 192.168.1.10 | mysql_replicator | 3309| 60|
hostname-bin.075 | 1159 | No
| | | 1062 | error
'Duplicate entry '11609031'
for key 1' on query 'insert into CLOCK_INSTALL (URL, MACHINE_ID,
INSTALL_TIME,REMOTE_IP,LASTURL) values
('FRZE1002','81E7A3FBE4A64E6A8CDE92EB00B4D8B0',Now(),'127.0.0.1','')' |
0|
The install_id('11609031') for the table is of auto_increment and that
id is already present in the HS server.
The previous entry '11609030' is the same accross the servers. How
could this happen and why doesn't mysql just increment to '11609032' for
this insert?
The replication has not been touched for weeks and it was working
properly when it was set up. The HS was never in an active situation so
corruption is ruled out on its end.
I'd like to solve this without downtime to the primary server.
Any help would be appreciated.
Thanks
Chris
--
Christopher Ferry [EMAIL PROTECTED]
signature.asc
Description: This is a digitally signed message part
Re: LOAD DATA
Well I figured out what was going on, MS uses a LF,CR instead of just a Line feed. The was corrupting the first column. I added a LINES TERMINATED BY statement and put in the '\r\n'. Now everything works fine. Thanks again. - Original Message - From: upscope [EMAIL PROTECTED] To: MySql AB [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:52 AM Subject: LOAD DATA I am using the following command to load a table: LOAD DATA LOCAL INFILE email.txt INTO TABLE email; The command works and the second and third column go in correctly. But the first column is not loading correctly. I am using Notepad on WIN ME to generate the txt file. JohnDoe[EMAIL PROTECTED] The problem appears to be the enter key that allows me to go to the next line. The last line where I donot use the enter key works fine. Has anyone experienced this problem or know a fix. I am manual executing the load data from a DOS window. I have mysql server 4.0.14 - max -debug and client 4.0.14. Thanks for any help in advance. upscope This email scanned by NAV2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL/Cron problem
Somebody suggested that I create the cron job as a php scripts, so I did: include(dbinfo.inc.php); $connect = MYSQL_CONNECT($hostname, $username, $password) OR DIE(Unable to connect to the database); @mysql_select_db( $dbName) or die( Unable to select database); $insert = INSERT INTO sysklog2 (machine,facility,priority,logdate,logtime,message) SELECT machine, facility, priority, cast(left(date1,8) as date) as logdate, cast(right(date1,6) as time) as logtime, message FROM sysklog;; $query = mysql_query($insert); $delete = DELETE from sysklog;; $query = mysql_query($delete); mysql_close($connect); I am still having the same issue, when the above script runs every minute memory usage increases by 200-500k and doesn't release itself. Any ideas/suggestions? Thanks! -Original Message- From: Jason Linden [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 2:32 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: MySQL/Cron problem Tried it... didn't do anything. Also, there are 11 different instances of mysql running. Any ideas why? Or is that just how it works? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 9:37 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: MySQL/Cron problem Hmmm, have you tried putting is a folling mysqladmin flush-threads command? -Original Message- From: Jason Linden [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 8:57 AM To: [EMAIL PROTECTED] Subject: MySQL/Cron problem I am fairly new to both linux and mysql. I have a cron job that runs every minute to move data from one mysql table to another (both tables are in the same database), see below: Cron job is: mysql -u *** --password=*** syslog /var/scripts/updsysklog2 Script is: INSERT INTO sysklog2 (machine,facility,priority,logdate,logtime,message) SELECT machine, facility, priority, cast(left(date1,8) as date) as logdate, cast(right(date1,6) as time) as logtime, message FROM sysklog; DELETE FROM sysklog; The problem I am having is every time this runs it adds ~250k to memory and doesn't release it then server locks up every 5 or 6 days. Any ideas on how to fix this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL/Cron problem
What do you mean it doesn't release itself? Can you provide some free examples of before and after then 5 min after that? The indication from the content of the below message is that the PHP version in use has a memory leak. 1st you need to make sure that the memory is not being put into a cache pool. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Jason Linden [mailto:[EMAIL PROTECTED] --Sent: Thursday, September 18, 2003 10:24 AM --To: [EMAIL PROTECTED] --Subject: RE: MySQL/Cron problem -- --Somebody suggested that I create the cron job as a php scripts, so I --did: -- --include(dbinfo.inc.php); -- --$connect = MYSQL_CONNECT($hostname, $username, $password) OR DIE(Unable --to connect to the database); --@mysql_select_db( $dbName) or die( Unable to select database); --$insert = INSERT INTO sysklog2 --(machine,facility,priority,logdate,logtime,message) SELECT machine, --facility, priority, cast(left(date1,8) as date) as logdate, --cast(right(date1,6) as time) as logtime, message FROM sysklog;; --$query = mysql_query($insert); --$delete = DELETE from sysklog;; --$query = mysql_query($delete); --mysql_close($connect); -- --I am still having the same issue, when the above script runs every --minute memory usage increases by 200-500k and doesn't release itself. -- --Any ideas/suggestions? -- --Thanks! -- ---Original Message- --From: Jason Linden [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 17, 2003 2:32 PM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: RE: MySQL/Cron problem -- --Tried it... didn't do anything. Also, there are 11 different instances --of mysql running. Any ideas why? Or is that just how it works? -- ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 17, 2003 9:37 AM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: Re: MySQL/Cron problem -- --Hmmm, have you tried putting is a folling mysqladmin flush-threads --command? -- ---Original Message- --From: Jason Linden [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 17, 2003 8:57 AM --To: [EMAIL PROTECTED] --Subject: MySQL/Cron problem -- --I am fairly new to both linux and mysql. I have a cron job that runs --every minute to move data from one mysql table to another (both tables --are in the same database), see below: -- --Cron job is: -- --mysql -u *** --password=*** syslog /var/scripts/updsysklog2 -- --Script is: -- --INSERT INTO sysklog2 (machine,facility,priority,logdate,logtime,message) --SELECT machine, facility, priority, cast(left(date1,8) as date) as --logdate, cast(right(date1,6) as time) as logtime, message FROM sysklog; --DELETE FROM sysklog; -- --The problem I am having is every time this runs it adds ~250k to memory --and doesn't release it then server locks up every 5 or 6 days. -- --Any ideas on how to fix this? -- --Thanks! -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't Show Warnings
Here's a snapshot from a mysql environment: mysql LOAD DATA LOCAL INFILE 'c:/mysql/miamibilling-Cash.txt' - REPLACE INTO TABLE ln_cash - FIELDS TERMINATED BY '|' - ENCLOSED BY '~' - LINES TERMINATED BY '\r\n' - ; Query OK, 16374 rows affected (20.83 sec) Records: 16374 Deleted: 0 Skipped: 0 Warnings: 69 mysql show warnings; Empty set (0.00 sec) What am I doing wrong that I can't find out what the 69 warnings are? I'm using 4.1.0 alpha. Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and mysqldump issues
I have a database called abc-100 which seems to cause problems with replication and importing sql files created with mysqldump. First, if on the master we create a temporary table (because we don't have subselects yet) it causes the slave to die with the following. Relay log says: DROP /*!40005 TEMPORARY */ TABLE abc-100.temp_summary And the error log says: ERROR: 1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-100.temp_summary Second, on doing a mysql dump.sql for a database restore, I have to manually edit the dump file first and use `` around the abc-100. So, apparently the - or the -100 is an invalid database name? If so, why would mysql allow me to create it in the first place? Is there any way to make this work without changing the database name? Changing the name is not a trivial matter because I am dealing with many databases with the same issue, 100, 101, 102 etc. I am more concerned with replication than the dump.sql as I can always edit the database names to be contained in ``. Thanks for any help you can offer. Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query join problem
Yes, you are correct. I was thinking that wouldn't be the case, but it makes sense now. I added indexes and then the query returned in a few seconds. Definitely have to have indexes. Thanks! Dan Nelson wrote: In the last episode (Sep 18), Allen said: I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the sending state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? You _have_ to add indexes. Mysql is currently scanning the entire 250k table for each record in the big table to find matching records. You can verify this by running EXPLAIN SELECT .. etc on your query and multiplying all the rows values together. -- Allen Brost Motorola - DMTS [EMAIL PROTECTED] Work: (847)-435-2019 Cell: (847)-878-7784 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL/Cron problem
The same thing happens whether or not I use the PHP script or a standard SQL script as described in my original post, so I don't think it is a PHP problem. Below is two snapshots, one @ 2:06 and the second @ 2:11, if this is hard to read due to formatting let me know and I can send it to you as an attachment. Thanks! [EMAIL PROTECTED] root]# clock Thu 18 Sep 2003 02:06:27 PM EDT -0.247002 seconds [EMAIL PROTECTED] root]# ps -eo pid,ppid,rss,vsize,pcpu,pmem,cmd -ww --sort=pid PID PPID RSS VSZ %CPU %MEM CMD 1 0 476 1376 0.1 0.1 init 2 10 0 0.0 0.0 [keventd] 3 10 0 0.0 0.0 [kapmd] 4 10 0 0.0 0.0 [ksoftirqd_CPU0] 5 10 0 0.0 0.0 [kswapd] 6 10 0 0.0 0.0 [kscand/DMA] 7 10 0 0.0 0.0 [kscand/Normal] 8 10 0 0.0 0.0 [kscand/HighMem] 9 10 0 0.0 0.0 [bdflush] 10 10 0 0.0 0.0 [kupdated] 11 10 0 0.0 0.0 [mdrecoveryd] 15 10 0 0.0 0.0 [kjournald] 73 10 0 0.0 0.0 [khubd] 1036 10 0 0.0 0.0 [kjournald] 1073 10 0 0.0 0.0 [kjournald] 1542 1 956 1964 0.0 0.2 /sbin/dhclient -1 -q -lf /var/lib/dhcp/dhclient-eth1.leases -pf /var/run/dhclient-eth1.pid -cf /etc/dhclient-eth1.conf eth1 1597 1 428 1368 0.0 0.1 klogd -x 1667 1 488 1360 0.0 0.1 /usr/sbin/apmd -p 10 -w 5 -W -P /etc/sysconfig/apm-scripts/apmscript 1704 1 1496 3504 0.0 0.3 /usr/sbin/sshd 1718 1 868 2024 0.0 0.2 xinetd -stayalive -reuse -pidfile /var/run/xinetd.pid 1732 1 2392 2396 0.0 0.6 [ntpd] 1741 1 444 1412 0.0 0.1 gpm -t ps/2 -m /dev/mouse 1753 1 5792 14316 0.0 1.5 /usr/sbin/httpd 1762 1 572 1428 0.0 0.1 crond 1770 1753 6356 14520 0.0 1.6 [httpd] 1774 1 1928 7508 0.0 0.5 cupsd 1809 1 1164 4184 0.0 0.3 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/idsconsole.abc.com.pid 1835 1809 19840 31216 0.0 5.1 [mysqld] 1855 1835 19840 31216 0.0 5.1 [mysqld] 1856 1855 19840 31216 0.0 5.1 [mysqld] 1857 1855 19840 31216 0.0 5.1 [mysqld] 1858 1855 19840 31216 0.0 5.1 [mysqld] 1859 1855 19840 31216 0.0 5.1 [mysqld] 1861 1 3080 4524 0.0 0.8 [xfs] 1879 1 528 1408 0.0 0.1 [atd] 1889 1 524 3396 0.0 0.1 rhnsd --interval 240 1896 1 588 3956 0.0 0.1 pure-ftpd (SERVER) 1903 1855 19840 31216 0.0 5.1 [mysqld] 1904 1855 19840 31216 0.0 5.1 [mysqld] 1905 1855 19840 31216 0.0 5.1 [mysqld] 1906 1855 19840 31216 0.0 5.1 [mysqld] 1917 1 888 2208 0.0 0.2 syslogd -r -f /etc/syslog.conf 1918 1855 19840 31216 0.0 5.1 [mysqld] 1920 1 400 1352 0.0 0.1 /sbin/mingetty tty1 1921 1 400 1352 0.0 0.1 /sbin/mingetty tty2 1922 1 400 1352 0.0 0.1 /sbin/mingetty tty3 1923 1 400 1352 0.0 0.1 /sbin/mingetty tty4 1924 1 400 1352 0.0 0.1 /sbin/mingetty tty5 1925 1 400 1352 0.0 0.1 /sbin/mingetty tty6 1973 1753 6416 14548 0.0 1.6 [httpd] 1976 1704 2392 6776 0.0 0.6 /usr/sbin/sshd 1978 1976 1440 4364 0.0 0.3 -bash 2065 1855 19840 31216 0.0 5.1 [mysqld] 2066 1855 19840 31216 0.0 5.1 [mysqld] 2067 1855 19840 31216 0.0 5.1 [mysqld] 2166 1855 19840 31216 0.0 5.1 [mysqld] 2330 1978 692 2636 0.0 0.1 ps -eo pid,ppid,rss,vsize,pcpu,pmem,cmd -ww --sort=pid [EMAIL PROTECTED] root]# free total used free sharedbuffers cached Mem:383268 134548 248720 0 28800 67556 -/+ buffers/cache: 38192 345076 Swap: 522104 0 522104 [EMAIL PROTECTED] root]# clock Thu 18 Sep 2003 02:11:43 PM EDT -0.810180 seconds [EMAIL PROTECTED] root]# ps -eo pid,ppid,rss,vsize,pcpu,pmem,cmd -ww --sort=pid PID PPID RSS VSZ %CPU %MEM CMD 1 0 476 1376 0.1 0.1 init 2 10 0 0.0 0.0 [keventd] 3 10 0 0.0 0.0 [kapmd] 4 10 0 0.0 0.0 [ksoftirqd_CPU0] 5 10 0 0.0 0.0 [kswapd] 6 10 0 0.0 0.0 [kscand/DMA] 7 10 0 0.0 0.0 [kscand/Normal] 8 10 0 0.0 0.0 [kscand/HighMem] 9 10 0 0.0 0.0 [bdflush] 10 10 0 0.0 0.0 [kupdated] 11 10 0 0.0 0.0 [mdrecoveryd] 15 10 0 0.0 0.0 [kjournald] 73 10 0 0.0 0.0 [khubd] 1036 10 0 0.0 0.0 [kjournald] 1073 10 0 0.0 0.0 [kjournald] 1542 1 956 1964 0.0 0.2 /sbin/dhclient -1 -q -lf /var/lib/dhcp/dhclient-eth1.leases -pf /var/run/dhclient-eth1.pid -cf /etc/dhclient-eth1.conf eth1 1597 1 428 1368 0.0 0.1 klogd -x 1667 1 488 1360 0.0 0.1 /usr/sbin/apmd -p 10 -w 5 -W -P /etc/sysconfig/apm-scripts/apmscript 1704 1 1496 3504 0.0 0.3 /usr/sbin/sshd 1718 1 868 2024 0.0 0.2 xinetd -stayalive -reuse -pidfile
Upgrade from 3.23.52 to 4.0.15 all my database show up as empty.
I'm not normally one to panic, and I know I can revert back to an old 3.23.57 version, but this is concerning and was hoping a guru could tell me what is wrong here. After the rpm upgrade, all my databases show up (ie. In phpMyAdmin for example), but they all show (-) for the tables -- in otherwords, they're aren't any tables. /var/lib/mysql/ shows all the databases and there appears to be data and files in the directories. Reverting back to the 3.23 version they all work. No errors shown in the .err file either... [EMAIL PROTECTED] mysql]# cat daevid.err 030918 11:29:50 mysqld started 030918 11:29:50 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.15-standard' socket: '/tmp/mysql.sock' port: 3306 I have a RedHat 8 system that's run solid for years. I was running the 3.23.52 RPMS and recently a project required me to have 4.0.15 (for cascading updates). I removed all the old rpms, then installed the new rpms. No problems encountered except I can't seem to remove mysql-server-3.23.52-3 from the rpm list but I don't think it's really there on my system. I performed this same update last week on another RH8 system running 2.23.56. in fact I'm using the exact same 4.0.15 rpms copied from the other machine. Google search didn't turn up any solutions that I saw. [EMAIL PROTECTED] mysql]# rpm -qa | grep mysql -i libdbi-dbd-mysql-0.6.5-2 MySQL-shared-compat-4.0.15-0 qt-MySQL-3.0.5-17 php-mysql-4.1.2-7.3.4 mysql-server-3.23.52-3 MySQL-python-0.9.1-4 MySQL-client-4.0.15-0 perl-DBD-MySQL-2.1017-3 mod_auth_mysql-1.11-1 arkpmysql-5.1.7-1 MySQL-devel-4.0.15-0 MySQL-shared-4.0.15-0 MySQL-server-4.0.15-0 [EMAIL PROTECTED] mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 to server version: 4.0.15-standard [EMAIL PROTECTED] mysql]# mysql --version; mysql Ver 12.21 Distrib 4.0.15, for pc-linux (i686) [EMAIL PROTECTED] mysql]# cat /etc/my.cnf [client] socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock innodb_data_home_dir= # Data file(s) must be able to # hold your data and indexes. # Make sure you have enough # free disk space. innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # Set the log file size to about # 25 % of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # Set ..flush_log_at_trx_commit # to 0 if you can afford losing # some last transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 #innodb_flush_method=fdatasync #set-variable = innodb_thread_concurrency=5 skip-locking set-variable = max_connections=200 #set-variable = read_buffer_size=1M set-variable = sort_buffer=1M # Set key_buffer to 5 - 50% # of your RAM depending on how # much you use MyISAM tables, but # keep key_buffer + InnoDB # buffer pool size 80% of # your RAM set-variable = key_buffer=10M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I asked a question about slave compatibility between versions
FYI I want to share this with others JIC you didn't know. I asked a question about slave compatibility between masters and slaves of different versions. Here is the compatibility matrix chart found. http://www.mysql.com/doc/en/Replication_Implementation.html Master Master Master Master 3.23.33 and up 4.0.0 4.0.1 4.0.3 and up Slave 3.23.33 and up yes no no no Slave 4.0.0 no yes no no Slave 4.0.1 yes no yes no Slave 4.0.3 and up yes no no yes - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
mysql memory usage
Is there any way to see what MySQL is storing in memory? Like, for instance, what is stored in the query cache, or at least what tables have data stored in the query cache, and how much they have stored? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Thu, Sep 18, 2003 at 12:44:10PM +0100, Haydies wrote: I have only one thing to say really on this. Data never has any meaning at all. It is simply data, and not information. Information is extracted from data and is then given meaning by the viewer of the data. True. We were arguing about how to deal with meanings assigned by users. The words meaning and definition occur frequently in the thread. In the case of NULL I have always thought of it is simply undefined. Thats what Orecal says it is, and Paradox, Interbase, informix, and the if I type the list of databases I've used where that was the general idea it will go on and on and on all day long :-) It's a good definition, but it doesn't tell database designers when to use NULL. Unknown and Inapplicable give some guidance, whatever other problems they cause. As an aside, it was a sad day when codd died. Michael Stonebreaker may be the Codd of the current generation, but Edward was the Codd of our fathers. ducking and running Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL-4.0.15a packages now available
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, several binary packages of the initial release of MySQL 4.0.15 did not include the embedded server library libmysqld.a, because of a compile/linking error with the compilers used on these platforms. As a workaround, the creation of the embedded server was disabled for these packages for the release. The affected platforms were: Sun Solaris 9 (64bit) HP-UX 11.11 (PA-RISC 32bit and 64bit) HP-UX 11.20 (IA64) IBM AIX 4.3.3 Linux/alpha SGI IRIX DEC OSF FreeBSD (x86, sparc64) We have resolved this problem in the meanwhile and have rebuilt the affected packages to include the embedded server now. They have been re-released as version 4.0.15a. If you don't use the embedded server, there is no need for you to upgrade, as 4.0.15a uses the same code base as 4.0.15 and does not include any other additional changes. The source archive has also been updated to include this fix (plus two small additional compile fixes for gcc 3.3 on Mac OS X and IBM AIX - see the ChangeSets 1.1563.2.1 and 1.1563.2.2 in our BitKeeper repository at http://mysql.bkbits.net:8080/mysql-4.0 for details). Additionally, we also updated the Novell binaries to 4.0.15a - they correct an erratum where certain utilities wouldn't properly display the password prompt. Novell users are encouraged to update to this version. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/agbcSVDhKrJykfIRAlZYAJ0Ty9WVGHZjKtCG9mzXEnKPcHysYgCeNM8F NYbXL7Pr04F5/S/O6vqIrK4= =Ed0G -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
add this to a wish list please.
Sometimes I get this error on a slave... for unknown reasons Error 'Incorrect key file for table: 'hasit'. Try to repair it' on query 'REPLACE INTO hasit If there is a wish list could the error report 'db.table'? Also why doesn't mysql automatically fix the error itself when the error has been encountered? And if anyone can let me know why these errors occur even when the table has been repaired within a month and the server has not ever been forced down that would help a DBA OUT! - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
Re: Need help writing query
I loaded MySQL 4.1, but now the text is gibberish in the Group_Concat field SELECT Cat_Item_Img,Cat_Price,Product.Item_Code,GROUP_CONCAT(Item_Size SEPARATOR ;) as sizes,Item_Img, Description,Category,Retail_Price,Short_Desc,Product.Item_Color FROM Cat_Items,Product WHERE cat_code =BoltTech and Cat_Items.Item_Code = Product.Item_Code and Category =Casual Wear GROUP BY Product.Item_Code RESULTS: | Cat_Item_Img | Cat_Price | Item_Code | sizes | Item_Img | Description | Category| | | 19.00 | 288 | ?L.MYI | 288 | bPromenade/b Blended Knit Shirt | Casual Wear | | | 28.00 | 71080 | ?L.MYI | 71080| bLee Denim Shirt/bbr | Casual Wear | | | 24.00 | 77123 | ?L.MYI | 77123| bLee Golf Shirt/bbr | Casual Wear | The data should read S;M;L;XL;2XL;3XL not ?L.MYI (?L.MYI is translation when using copy/paste, there were other characters in sizes field.) Is this a 4.1 Bug? I loaded from the 4.1.0-0 Linux x86 RPM files. So far nothing else appears broken. Any suggestions? Patrick At 09:11 AM 9/18/2003, you wrote: Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Failed connectionattempts
Hi, I have a server which has been running for about a year now. It started with kernel 2.4.19 and mysql 3.x it has since been continually upgraded so that it is now kernel 2.4.20 and mysql 4.0.13. The server is a Compaq server with dual Pentium II (Deschutes) 400MHz 512Kb cache CPUs, 320Mb RAM and SCSI drives. The OS is Gentoo Linux. From the beginning it has behaved strangely - connections to mysql (be that with the mysql textclient or from PHP) fails about 2 out of 3 times in periods. Some times there are no problems, other times it's almost impossible to connect. This behavior has been consistent from the start, so it has been a problem when running both mysql 3.x and 4.0.x. I have monitored the servers load and the loads are the same in the periods when it fails as in the periods when it doesn't. I have tried monitoring the mysql logs, but no entries show up upon the failed connection attempts. No other services malfunctions in this way, or indeed at all on the server. I have run out of ideas on how to debug this problem, can anybody here help me? -- Anders -BEGIN GEEK CODE BLOCK- Version: 3.12 GCS/O d--@ s:+ a-- C++ UL+++$ P++ L+++ E- W+ N(+) o K? w O-- M- V PS+ PE@ Y+ PGP+ t 5 X R+ tv+ b++ DI+++ D+ G e- h !r y? --END GEEK CODE BLOCK-- PGPKey: http://pgp.mit.edu:11371/pks/lookup?op=getsearch=0x8BFECB41 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Auto_Increment
On Thu, Sep 18, 2003 at 01:14:47PM -0400, Christopher Ferry wrote:
We're running bi-directional replication between two servers running
MySQL 3.23.56.
One server being active the other a hot standby.
Wanting to switch to the hot standby I noticed that replication had
ceased on the HS server. The show slave status:
| 192.168.1.10 | mysql_replicator | 3309| 60|
hostname-bin.075 | 1159 | No
| | | 1062 | error
'Duplicate entry '11609031'
for key 1' on query 'insert into CLOCK_INSTALL (URL, MACHINE_ID,
INSTALL_TIME,REMOTE_IP,LASTURL) values
('FRZE1002','81E7A3FBE4A64E6A8CDE92EB00B4D8B0',Now(),'127.0.0.1','')' |
0|
The install_id('11609031') for the table is of auto_increment and that
id is already present in the HS server.
The previous entry '11609030' is the same accross the servers. How
could this happen and why doesn't mysql just increment to '11609032' for
this insert?
It doesn't use auto increment on the slave because the record wouldn't
be identical to the master's record.
The replication has not been touched for weeks and it was working
properly when it was set up. The HS was never in an active situation so
corruption is ruled out on its end.
Are you sure that no insert was done on the HS? You can check this in
its binlog with mysqlbinlog. Look for the server id in the output
comments like this:
#030815 11:59:59 server id 1 Query thread_id=135377exec_time=0
error_code=0
Did the HS ever crash or reboot? Did you ever change its server id? Just
guessing here.
I'd like to solve this without downtime to the primary server.
If there really were two distinct records (one on the active and the
other on the HS) you will need to remove the record on the HS and fix
anything that may depend on this record. Only then can it resume
replication.
If there are too many conflicts, you can:
- shut down MySQL on the HS
- FLUSH TABLES WITH READ LOCK on the active server
- copy all tables on the active server somewhere that takes the least
time (to minimize the 'downtime' of the active server)
- note the last binlog file and size on the active server
- UNLOCK TABLES on the active server
- move the copy to the HS
- update the HS's master.info with the active server's binlog file and
size
- restart MySQL on the HS
Maybe there's an easier way, but this is (from memory) what I've used
before with 3.23.xx.
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in finding Duplicates with Group By?
Ok. I looked through google and saw a couple examples of how to find the duplicates in a table that looked like this... SELECT date, time, id, count(*) as n from session GROUP BY date,time,id HAVING n 1; Ok. So I tried it. I have a table of 13,128,178 rows (not a small example). I ran a DISTINCT on the key field and the result was 12,787,768 so that tells me there are 340,410 duplicate rows. Ok. I run the above command. I get 272,626 as an answer. What? That doesn't match. For grins I run the query again with. HAVING n 2; This time I get 67,756. Again HAVING n 3 I get 30. Add them all up I get 340,413. Now they match! What seems to be happening is that the HAVING n 1 is really doing n == 2 not GREATER than 1 and so on. I would say this looks like a bug or I am crazy either of which could be true. - Allen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in finding Duplicates with Group By?
At 4:22 PM -0500 9/18/03, Allen wrote: Ok. I looked through google and saw a couple examples of how to find the duplicates in a table that looked like this... SELECT date, time, id, count(*) as n from session GROUP BY date,time,id HAVING n 1; Ok. So I tried it. I have a table of 13,128,178 rows (not a small example). I ran a DISTINCT on the key field and the result was 12,787,768 so that tells Do you mean key field (singular) or do you really mean DISTINCT date, time, id ? me there are 340,410 duplicate rows. Ok. I run the above command. I get 272,626 as an answer. What? That doesn't match. For grins I run the query again with. HAVING n 2; This time I get 67,756. Again HAVING n 3 I get 30. Add them all up I get 340,413. Now they match! What seems to be happening is that the HAVING n 1 is really doing n == 2 not GREATER than 1 and so on. I would say this looks like a bug or I am crazy either of which could be true. - Allen -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in finding Duplicates with Group By?
DISTINCT key_field is fine. I don't think it will make a difference. My example was using only one field. Paul DuBois wrote: At 4:22 PM -0500 9/18/03, Allen wrote: Ok. I looked through google and saw a couple examples of how to find the duplicates in a table that looked like this... SELECT date, time, id, count(*) as n from session GROUP BY date,time,id HAVING n 1; Ok. So I tried it. I have a table of 13,128,178 rows (not a small example). I ran a DISTINCT on the key field and the result was 12,787,768 so that tells Do you mean key field (singular) or do you really mean DISTINCT date, time, id ? me there are 340,410 duplicate rows. Ok. I run the above command. I get 272,626 as an answer. What? That doesn't match. For grins I run the query again with. HAVING n 2; This time I get 67,756. Again HAVING n 3 I get 30. Add them all up I get 340,413. Now they match! What seems to be happening is that the HAVING n 1 is really doing n == 2 not GREATER than 1 and so on. I would say this looks like a bug or I am crazy either of which could be true. - Allen -- Allen Brost Motorola - DMTS [EMAIL PROTECTED] Work: (847)-435-2019 Cell: (847)-878-7784 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 188,913,819 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Show Warnings
At 1:43 PM -0400 9/18/03, Randy Chrismon wrote: Here's a snapshot from a mysql environment: mysql LOAD DATA LOCAL INFILE 'c:/mysql/miamibilling-Cash.txt' - REPLACE INTO TABLE ln_cash - FIELDS TERMINATED BY '|' - ENCLOSED BY '~' - LINES TERMINATED BY '\r\n' - ; Query OK, 16374 rows affected (20.83 sec) Records: 16374 Deleted: 0 Skipped: 0 Warnings: 69 mysql show warnings; Empty set (0.00 sec) What am I doing wrong that I can't find out what the 69 warnings are? I'm using 4.1.0 alpha. For LOAD DATA, it works better in 4.1.1, as indicated here: http://www.mysql.com/doc/en/SHOW_WARNINGS.html Thanks. Randy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fragmentation problem with MYD/MYI files
On Thu, Sep 18, 2003 at 01:57:17PM +0300, Mikko Noromaa wrote: Hi, Is it possible to configure MySQL so that it would allocate a large amount of extra space for the MYD/MYI files, and then use this space as necessary? I don't know of any. This way the files would need to be grown only very rarely. It would be ideal if I could configure the increment in which MySQL increases the file sizes (for example, 100 MB, or a percentage of current file size). Right. If this is not possible, I'd suggest to add such a feature to MySQL. I believe it would give a tremendous performance-boost for application where the databases keep growing. If seek times become an issue, yes, reducing fragmentation may help. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 188,921,457 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
On Thu, Sep 18, 2003 at 02:53:29PM -0400, Gabriel Ricard wrote: Is there any way to see what MySQL is storing in memory? Like, for instance, what is stored in the query cache, or at least what tables have data stored in the query cache, and how much they have stored? Nothing other than what SHOW STATUS tells you. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 188,953,956 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in finding Duplicates with Group By?
At 4:56 PM -0500 9/18/03, Allen wrote: DISTINCT key_field is fine. I don't think it will make a difference. My example was using only one field. Your example was using three fields by my count. DISTINCT field1 is likely to yield a different result than DISTINCT field1, field2, field3. What form of DISTINCT did you use? Paul DuBois wrote: At 4:22 PM -0500 9/18/03, Allen wrote: Ok. I looked through google and saw a couple examples of how to find the duplicates in a table that looked like this... SELECT date, time, id, count(*) as n from session GROUP BY date,time,id HAVING n 1; Ok. So I tried it. I have a table of 13,128,178 rows (not a small example). I ran a DISTINCT on the key field and the result was 12,787,768 so that tells Do you mean key field (singular) or do you really mean DISTINCT date, time, id ? me there are 340,410 duplicate rows. Ok. I run the above command. I get 272,626 as an answer. What? That doesn't match. For grins I run the query again with. HAVING n 2; This time I get 67,756. Again HAVING n 3 I get 30. Add them all up I get 340,413. Now they match! What seems to be happening is that the HAVING n 1 is really doing n == 2 not GREATER than 1 and so on. I would say this looks like a bug or I am crazy either of which could be true. - Allen -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
Harald, How often do you read 200 rows by key in a daya to day application? How often do you insert 350768 rows in an application that you're considering Win98 as the platform? I've never deployed MySql on Wintel, except for developing on my laptop. I always use Solaris or Redhat for serious deployments. However, Wintel was the best platform for the deployment we were talking about because that was where the experience lay. It doesn't need *nix - it's only replacing a tiny little access dastabase... Don't get so hung up on your platform preaching that you forget your sense of reality... Andy -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: 18 September 2003 17:00 To: [EMAIL PROTECTED] Subject: Re: Platform vs. Performance In article [EMAIL PROTECTED], Andy Eastham [EMAIL PROTECTED] writes: I don't see anything in there that is relevant to the original posting. Andy The following URL tells you that there's a big difference between Windoze and Linux: http://www.mysql.com/information/presentations/presentation-oscon2 000-2719/index.html Speed difference between different SQL servers (times in seconds) Reading 200 rows by key:NT Linux mysql 367 249 Inserting (350768) rows:NT Linux mysql 381 206 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update question
I'm not quite sure why I haven't run across this in the past, but now that I have I am stumped. I am needing to update a table based on criteria found in it and one other table, but I am uncertain how to proceed. If I had subselects I would run the query as follows, I believe: UPDATE suppliercatlink SET suppliercatlink.catid=124 WHERE suppliercatlink.supid IN (SELECT supplier.id FROM supplier WHERE supplier.company_name LIKE %exteri%) AND suppliercatlink.catid=10 ; Knowing that this is not an option I figure maybe I could join the tables in my UPDATE statement like: UPDATE suppliercatlink, supplier SET suppliercatlink.catid=124 WHERE supplier.company_name LIKE '%brick%' AND supplier.id=suppliercatlink.supid AND suppliercatlink.catid=10 ; Looking at the documentation it appears this will not work, at least not with 3.23 which I am currently running. It appears that something of this nature would work if I upgraded to 4.0.4, but I really prefer to update mySQL before or after a project, not right in the middle of it. Can anyone help me figure out a way around this problem? Jay Drake [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock tables in myisam
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: electroteque [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 17, 2003 6:38 PM --To: [EMAIL PROTECTED] --Subject: Re: Lock tables in myisam -- --rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a --try. No rollback does not work on myisam Jeremy was stating that you don't have to do what you suggested to implement a correct ROLLBACK in mySQL. Use INNODB. http://www.mysql.com/doc/en/COMMIT.html
Re: Lock tables in myisam
At 11:38 AM +1000 9/18/03, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. Rollback *doesn't* work with MyISAM, that's why Jeremy said you have to put the necessary logic in your application if you want to achieve the same effect. At least, that's what I think he said. :-) On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
At 5:14 PM -0500 9/18/03, [EMAIL PROTECTED] wrote: I'm not quite sure why I haven't run across this in the past, but now that I have I am stumped. I am needing to update a table based on criteria found in it and one other table, but I am uncertain how to proceed. If I had subselects I would run the query as follows, I believe: UPDATE suppliercatlink SET suppliercatlink.catid=124 WHERE suppliercatlink.supid IN (SELECT supplier.id FROM supplier WHERE supplier.company_name LIKE %exteri%) AND suppliercatlink.catid=10 ; Knowing that this is not an option I figure maybe I could join the tables in my UPDATE statement like: UPDATE suppliercatlink, supplier SET suppliercatlink.catid=124 WHERE supplier.company_name LIKE '%brick%' AND supplier.id=suppliercatlink.supid AND suppliercatlink.catid=10 ; Looking at the documentation it appears this will not work, at least not with 3.23 which I am currently running. It appears that something of this nature would work if I upgraded to 4.0.4, but I really prefer to update mySQL before or after a project, not right in the middle of it. Can anyone help me figure out a way around this problem? If you don't want to update to MySQL 4 (which will indeed allow you to run your second UPDATE above, then you'll need to code the equivalent logic in an application. Select the ID list from supplier for those records that need updating, then use them to construct a set of UPDATE statements for the suppliercatlink table. Jay Drake [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Hi, No, ROLLBACK doesn't work with MyISAM. Jeremy meant that you can add logic/code between LOCK/UNLOCK to simulate ROLLBACK. e.g. queries that undo what you did if something goes wrong. Of course this won't cover you if mysqld dies, is killed, or you lose the connection etc. as real transactions would. Matt - Original Message - From: electroteque Sent: Wednesday, September 17, 2003 8:38 PM Subject: Re: Lock tables in myisam rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock tables in myisam
No rollback does not work on myisam Jeremy was stating that you don't have to do what you suggested to implement a correct ROLLBACK in mySQL. Use INNODB. http://www.mysql.com/doc/en/COMMIT.html Hmm if you got my other post i am trying to simulate innodb in myisiam for projects that require fulltext search i have no choice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Righty, so if error unlock table hehe, i have found i need to produce my error first then do a rollback for an error to display in php as it wouldnt show a mysql error after a rollback, i guess i could add an unlock table in my trigger error function too. At 11:38 AM +1000 9/18/03, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. Rollback *doesn't* work with MyISAM, that's why Jeremy said you have to put the necessary logic in your application if you want to achieve the same effect. At least, that's what I think he said. :-) On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
