MySQL database and user creation from script file
Hi, I have to create user and database using script file. My requirements are given below. 1. Login as root 2. Execute the script file for database and user creation. 3. Exit My script file should have mysql -u root create database mnms; user creation command The script file will be called in Win batch file and the same batch file will be executed. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: big table corruption
Mark Maggelet wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Not sure if this would apply in your case but have a look at the discussion on http://bugs.mysql.com/bug.php?id=563 . Some useful hints provided there. Regards -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sorting doesn't work correctly with UTF-8 data
Hi, I have mysql-4.0.18 installed and entered data with UTF-8 characters. When I use a Select command, mysql doesn't sort the data correctly which starts with native Turkish letters. Is there a way to fix it? Thanks.. -- Erol YILDIZ HEV Schools Kemerkoy http://www.kemerkoy.k12.tr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sorting doesn't work correctly with UTF-8 data
Erol YILDIZ wrote: Hi, I have mysql-4.0.18 installed and entered data with UTF-8 characters. When I use a Select command, mysql doesn't sort the data correctly which starts with native Turkish letters. Is there a way to fix it? MySQL 4.0.x doesn't support UTF-8, you need MySQL 4.1.x (4.1.8 is the altest version) to handle UTF-8. Thanks.. -- Erol YILDIZ HEV Schools Kemerkoy http://www.kemerkoy.k12.tr HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database and user creation from script file
[EMAIL PROTECTED] wrote: Hi, I have to create user and database using script file. My requirements are given below. 1. Login as root 2. Execute the script file for database and user creation. 3. Exit My script file should have mysql -u root create database mnms; user creation command The script file will be called in Win batch file and the same batch file will be executed. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Perhaps reading the manual might be part of your exercise. Try http://dev.mysql.com/doc/mysql/en/index.html Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automated DELETEs or/and UPDATES ?
Hi all, from time to time I have a huge number of DELETEs or/and UPDATES processed by MySQL at noon and 8pm. The trouble is that I didn't send those queries to MySQL ! i don't have any crons running. Is this an automated MySQL procedure ? Is there any automated procedure within MySQL? thanks in advance Dilipan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble w/ mysqldump
Hi All, I run a website, and am having a problem w/ corrupt databases.. I was runnig MySQL v3.23 previously, and my db dumps worked fine .. However, I have since upgraded to v4.1.8a-log and I am having problems getting completed (or non corrupt) backups.. The problem is pretty big.. My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc are totally whacked out. Does anyone have any insight on what is causing this, and how to fix it? Any help is appreciated.. I need a new good backup desperately -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database and user creation from script file
The -e option with mysql allows you to run queries and would allow doing user creation etc as well. Aman Raheja [EMAIL PROTECTED] wrote: Hi, I have to create user and database using script file. My requirements are given below. 1. Login as root 2. Execute the script file for database and user creation. 3. Exit My script file should have mysql -u root create database mnms; user creation command The script file will be called in Win batch file and the same batch file will be executed. Thanks, Narasimha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded Quotes and Special Characters.
I'm moving a database from one server to another. Both are unix based, however the original database is on a Solaris box, and the new server is RedHat. The database has embedded s and 's in some of the varchar fields. When I moved the database, I did a mysqldump, copied the text file to the new server, and imported the data. The database on the new server is now showing odd characters in place of the original s and 's. Is there a way to fix this problem? Thanks! -Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex joining - multiple tables to one
It's probably your JOIN on shipped items producing a cross product with your JOIN on ordered items. What happens if you take the entire shippeditems JOIN out of your statement? Are your numbers still off? Since your query is not using data from that table you should be able to eliminate it from your join without introducing any errors. You should be able to see the duplication of records if you run this query: SELECT o.orderid , c.customerid , oi.itemid , oi.productid , oi.quantity , si.shippingid , si.productid , si.shippedqty FROM orders o INNER JOIN customers c on c.id = o.customerid LEFT JOIN orderitems oi on o.orderid = oi.orderid LEFT JOIN shippeditems si on o.orderid = si.orderid WHERE o.orderid = some orderid with the wrong results I think that when you look at this you will see that some of the orderitems and/or shippeditems will be duplicated. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ville Mattila [EMAIL PROTECTED] wrote on 01/03/2005 07:18:16 AM: Hi there! I have some problems with the complex MySQL join operations. In most cases, LEFT JOIN fulfills my needs but sometimes it doesn't work in the expected way. I'll give an example: orders -- - orderid - customerid customers - - id - customername orderitems -- - itemid - orderid - productid - quantity shippeditems - shippingid - orderid - productid - shippedqty Now I try to find out a list of orders, giving also the quantity of shipped and unshipped products in the list. SELECT o.*,c.customername, SUM(oi.quantity) orderedTotal, SUM(si.shippedqty) shippedTotal FROM orders o LEFT JOIN customers c ON (c.id = o.customerid) LEFT JOIN orderitems oi ON (oi.orderid = o.orderid) LEFT JOIN shippeditems si ON (si.orderid = o.orderid) GROUP BY o.orderid ORDER BY o.orderid; This query returns all other information correct but the SUM functions return too large numbers. Why I'm not able to user normal JOIN, is that I need also order information in that case that no items are shipped. Thanks for your tips! Ville -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
Raj Shekhar wrote: Mark Maggelet wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Not sure if this would apply in your case but have a look at the discussion on http://bugs.mysql.com/bug.php?id=563 . Some useful hints provided there. Regards I have the same problem, with 4.1.9 always the same table, very simple setup every day 5 times the table has a set of records deleted, and then reloaded from the text files we recieve from the old mainframe delete from mrf where site='site1' then load data concurrent infile blah blah for 5 separate sites that's it, it's queried at a low load via websites every few days I get one of these MySql: Can't open file: 'mrf.MYI' (errno: 145) For more information, see Help and Support Center at http://www.mysql.com. Checking table: './db/mrf' For more information, see Help and Support Center at http://www.mysql.com. Recovering table: './db/mrf' For more information, see Help and Support Center at http://www.mysql.com. always recovers fine I guess, nothing gets wrote to the .err file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ISAM to MyISAM Convert pls
Hello. As of MySQL 4.1, ISAM support is included in the source but not enabled in binary distributions. [EMAIL PROTECTED] wrote: hello i hope im correct here i have a problem, after upgrade 4.1 from 4.0 one table is away (index) i have table.ISM,.frm and .ISD. when i try to convert it with ALTER TABLE table TYPE = MYISAM; he only says did not found table.MYI (where table is my name of the table) how i can convert it to MYI? before update i had MYI file with this table..the other tables are ok only this are now ISM,FRM and )ISD file whoich cannot be read my mysql now... thx richard -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: automated DELETEs or/and UPDATES ?
Hello. Use --log and --log-long-format (if your MySQL version below 4.1) to determine who makes these queries. Dilipan Sebastiampillai [EMAIL PROTECTED] wrote: Hi all, from time to time I have a huge number of DELETEs or/and UPDATES processed by MySQL at noon and 8pm. The trouble is that I didn't send those queries to MySQL ! i don't have any crons running. Is this an automated MySQL procedure ? Is there any automated procedure within MySQL? thanks in advance Dilipan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Trouble w/ mysqldump
Hello. In what way have you upgraded? How do you dump the data? Is everything fine, execept the database dump? As of MySQL 4.1, --opt command line option is on by default, so it can produce a smaller output. The definions of tables which stores Avatars are also absent in dumps? May be you use mysqldump from old 3.23 version? [EMAIL PROTECTED] wrote: Hi All, I run a website, and am having a problem w/ corrupt databases.. I was runnig MySQL v3.23 previously, and my db dumps worked fine .. However, I have since upgraded to v4.1.8a-log and I am having problems getting completed (or non corrupt) backups.. The problem is pretty big.. My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc are totally whacked out. Does anyone have any insight on what is causing this, and how to fix it? Any help is appreciated.. I need a new good backup desperately -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Embedded Quotes and Special Characters.
Hello. You can write a script which will walk through all tables and update the rows using replace() function. But, I think, you should find the source of the problem. What odd characters have appeared after importing? If they are just unreadable, may be the clue is in wrong character set. Andrew Mull [EMAIL PROTECTED] wrote: I'm moving a database from one server to another. Both are unix based, however the original database is on a Solaris box, and the new server is RedHat. The database has embedded s and 's in some of the varchar fields. When I moved the database, I did a mysqldump, copied the text file to the new server, and imported the data. The database on the new server is now showing odd characters in place of the original s and 's. Is there a way to fix this problem? Thanks! -Andy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 info
Hi everybody, I want to connect with people who have experience in modeling/building a MySQL DB where most of the data are measured timeseries, structured by physical/geographical locality of the measuring equipment and different sets of measured values (attributes, ie. sci-tech data, no commercial relationships). I am not yet sure whether a relational model is quite suitable - but there will be also some non-timeseries data for which a rel. model and MySQL will be suitable. Thanks for giving me some hints an/or helping me to find people with similar problem domain. Theo Formanek
Database hung; please help!
All, I found my database hung this morninig on a Linux system. I could not connect locally to /tmp/mysql.sock or remotelly. I tried to do a mysqladmin status; but that hung too. Linux was reporting high CPU utilization. The proces infromation is as follows: Name: mysqld State: T (stopped) SleepAVG: 83% Tgid: 10326 Pid:10326 PPid: 10309 TracerPid: 0 Uid:60 60 60 60 Gid:60 60 60 60 FDSize: 256 Groups: 60 VmSize:73964 kB VmLck: 0 kB VmRSS: 33664 kB VmData:67312 kB VmStk:32 kB VmExe: 3964 kB VmLib: 2316 kB Threads:25 SigPnd: 0004 ShdPnd: SigBlk: 00085007 SigIgn: 1006 SigCgt: 800064e9 CapInh: CapPrm: CapEff: The CPU and memory information were OK (only high CPU utliziation to about 70%) Any ideas? Beforehand, thank you for your help! __ Do you Yahoo!? Jazz up your holiday email with celebrity designs. Learn more. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble w/ mysqldump
Thanks for the reply. My original install was w/ an RPM from the Redhat Network, I uninstalled all RPM's associated w/ MySQL, Apache, and PHP and downloaded the latest of everything and compiled it. After I did an updatedb on my box, I searched for mysqldump and mysql to confirm it was gone -- nothing was found. ./mysqldump Ver 10.9 Distrib 4.1.8a, for redhat-linux-gnu (i686) The actual tables are there, but the images (or any attachments) are really messed up. Looks like something from the 60's, lol. In a message dated 1/4/2005 9:36:01 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. In what way have you upgraded? How do you dump the data? Is everything fine, execept the database dump? As of MySQL 4.1, --opt command line option is on by default, so it can produce a smaller output. The definions of tables which stores Avatars are also absent in dumps? May be you use mysqldump from old 3.23 version? [EMAIL PROTECTED] wrote: Hi All, I run a website, and am having a problem w/ corrupt databases.. I was runnig MySQL v3.23 previously, and my db dumps worked fine .. However, I have since upgraded to v4.1.8a-log and I am having problems getting completed (or non corrupt) backups.. The problem is pretty big.. My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc are totally whacked out. Does anyone have any insight on what is causing this, and how to fix it? Any help is appreciated.. I need a new good backup desperately -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Fwd: Load data infile...
This is using MySQL version 3.23.49 I think I'm bringing this up again but with different errors. This: $sql = LOAD DATA INFILE '$file' REPLACE INTO TABLE `jobs` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; is getting the error Access denied for user: '[EMAIL PROTECTED]' (Using password: YES I have no idea where the % sign is coming from. shouldn't it say [EMAIL PROTECTED] like it's setup in the db connection script? Any clues? Thanks! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://xendhosting.com rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - End forwarded message - R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://xendhosting.com rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load data infile...
I think I'm bringing this up again but with different errors. This: $sql = LOAD DATA INFILE '$file' REPLACE INTO TABLE `jobs` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; is getting the error Access denied for user: '[EMAIL PROTECTED]' (Using password: YES I have no idea where the % sign is coming from. shouldn't it say [EMAIL PROTECTED] like it's setup in the db connection script? Any clues? Thanks! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://xendhosting.com rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disk Block size
Hi! We're setting up a Solaris system which is behaving poorly, very slow disk performance. The nice folks at Sun have suggested a mismatch between the block size MySQL is writing to disk and the block size the the Operating System is writing to disk. While we can see the logic in the argument, I have over the years not been able to find anywhere that this information was available or controlable, so my assumption has been that mysql gives the data to the operating system to write to disk and doesn't deal with block sizes. So here's the questions... In mysql 4.0.23 or 4.1.8 for Solaris Sparc 64 bit what is the block size mysql uses for writes to disk, and is there a way to control that? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedded Quotes and Special Characters.
That is what I was wondering, but not sure how to check/set the proper character set. Thanks. -Andy Quoting Gleb Paharenko [EMAIL PROTECTED]: Hello. You can write a script which will walk through all tables and update the rows using replace() function. But, I think, you should find the source of the problem. What odd characters have appeared after importing? If they are just unreadable, may be the clue is in wrong character set. Andrew Mull [EMAIL PROTECTED] wrote: I'm moving a database from one server to another. Both are unix based, however the original database is on a Solaris box, and the new server is RedHat. The database has embedded s and 's in some of the varchar fields. When I moved the database, I did a mysqldump, copied the text file to the new server, and imported the data. The database on the new server is now showing odd characters in place of the original s and 's. Is there a way to fix this problem? Thanks! -Andy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Embedded Quotes and Special Characters.
When I look at the dump file, I see that one of the invalid characters is
represented as /226 I'll have to look to see if it is consistent, and just
replace it.
Thanks.
Quoting [EMAIL PROTECTED]:
are you sure that the single/double quotes in your original data were
actually the standard ascii characters (decimal 39 (') and 34 ()) --
not smart quotes? you shouldn't have any trouble with the standard
quote characters, but as the representation of smart quotes isn't
standard you will.
-- Original Message --
From: Andrew Mull [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Date: Tuesday, January 04, 2005 10:19:20 AM -0500
Subject: Embedded Quotes and Special Characters.
I'm moving a database from one server to another. Both are unix
based, however the original database is on a Solaris box, and the
new server is RedHat.
The database has embedded s and 's in some of the varchar fields.
When I moved the database, I did a mysqldump, copied the text file
to the new server, and imported the data. The database on the new
server is now showing odd characters in place of the original s and
's.
Is there a way to fix this problem?
Thanks!
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
-- End Original Message --
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database hung; please help!
I would suggest using Reply All. I can not figure out anything with the information here, and I have not used this linux version and kernel with mysql. Slow queries: 2 - that might mean something here - the info is at http://dev.mysql.com/doc/mysql/en/Slow_query_log.html This could have cause the server slow down. Let us see what experts have, for comments. Regards Aman Raheja E SA wrote: Aman, The system is SuSE Enterprise 9 with kernel 2.6.5-7.111.5-bigsmp. MySQL is version 4.0.21 From mysqladmin status: Uptime: 7091 Threads: 13 Questions: 174188 Slow queries: 2 Opens: 100 Flush tables: 1 Open tables: 64 Queries per second avg: 24.565 Is there anything else that would help you? I would like to know how to obtain any kind of info from the database on this state... Beforehand, thank you for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedded Quotes and Special Characters.
On other instances, are escaped as \...I would think that should translate
correctly?
Thanks
Quoting Andrew Mull [EMAIL PROTECTED]:
When I look at the dump file, I see that one of the invalid characters is
represented as /226 I'll have to look to see if it is consistent, and just
replace it.
Thanks.
Quoting [EMAIL PROTECTED]:
are you sure that the single/double quotes in your original data were
actually the standard ascii characters (decimal 39 (') and 34 ()) --
not smart quotes? you shouldn't have any trouble with the standard
quote characters, but as the representation of smart quotes isn't
standard you will.
-- Original Message --
From: Andrew Mull [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Date: Tuesday, January 04, 2005 10:19:20 AM -0500
Subject: Embedded Quotes and Special Characters.
I'm moving a database from one server to another. Both are unix
based, however the original database is on a Solaris box, and the
new server is RedHat.
The database has embedded s and 's in some of the varchar fields.
When I moved the database, I did a mysqldump, copied the text file
to the new server, and imported the data. The database on the new
server is now showing odd characters in place of the original s and
's.
Is there a way to fix this problem?
Thanks!
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
-- End Original Message --
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyODBC 3.51.10
I somehow managed to download and install MyODBC 3.51.10 on my XP box, and it connects with no problems to my database server running version 4.1.7. However, it seems that MySQL is only allowing downloads for 3.51.9, and when I installed it on my Server 2003 and attempted to connect, I get the following error: Client does not support authentication protocol requested by server; consider upgrading MySQL client I can only assume it's because of the older driver? Best regards, Erich Beyrent Systems Administrator Information Technology Services Plymouth State University (603) 535-2948 [EMAIL PROTECTED] Si vis pacem para bellum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyODBC 3.51.10
You need to set the OLD_PASSWORDS variable in the [MYSQLD] section of the my.ini file. Set-variable = old_passwords=1 It's not the ODBC, but your version of MySQL. 4.0.x uses 1 variant of password hashing, 4.1.x uses a more secure one that will be covered in MyODBC 3.53 (coming soon...). J.R. -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 2:53 PM To: mysql@lists.mysql.com Subject: MyODBC 3.51.10 I somehow managed to download and install MyODBC 3.51.10 on my XP box, and it connects with no problems to my database server running version 4.1.7. However, it seems that MySQL is only allowing downloads for 3.51.9, and when I installed it on my Server 2003 and attempted to connect, I get the following error: Client does not support authentication protocol requested by server; consider upgrading MySQL client I can only assume it's because of the older driver? Best regards, Erich Beyrent Systems Administrator Information Technology Services Plymouth State University (603) 535-2948 [EMAIL PROTECTED] Si vis pacem para bellum -- 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: MyODBC 3.51.10
You need to set the OLD_PASSWORDS variable in the [MYSQLD] section of the my.ini file. Set-variable = old_passwords=1 It's not the ODBC, but your version of MySQL. 4.0.x uses 1 variant of password hashing, 4.1.x uses a more secure one that will be covered in MyODBC 3.53 (coming soon...). J.R. I did read that in the manual, but I was confused as to why I get the error on one pc and not the other. I am also somewhat confused as to why I have 3.51.10 on one pc, but cannot find it anymore for my other. Did it get pulled from the website? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyODBC 3.51.10
4.1 auth is not supported until MyODBC 3.51.10. I don't know why the win binaries are not on the download page anymore, though the source is there. Here is a mirror with the windows binaries. http://mysql.netvisao.pt/Downloads/MyODBC3/MyODBC-3.51.10-x86-win-32bit.exe --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Erich Beyrent Sent: Tuesday, January 04, 2005 2:53 PM To: 'J.R. Bullington';Subject: RE: MyODBC 3.51.10 You need to set the OLD_PASSWORDS variable in the [MYSQLD] section of the my.ini file. Set-variable = old_passwords=1 It's not the ODBC, but your version of MySQL. 4.0.x uses 1 variant of password hashing, 4.1.x uses a more secure one that will be covered in MyODBC 3.53 (coming soon...). J.R. I did read that in the manual, but I was confused as to why I get the error on one pc and not the other. I am also somewhat confused as to why I have 3.51.10 on one pc, but cannot find it anymore for my other. Did it get pulled from the website? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disk Block size
While the advice on the problem may be correct, their explanation of the culprit I think is incorrect. Applications don't care about the block size, that is abstracted out by the file system. Typically the block size defaults to 4k, but that is changeable to a wide range to suit your data needs. If you are using very large files (i.e. video), you would want to increase that to maybe 128k or larger. If you have lots of tiny files, you may want to reduce that to 2k or less to fit the file size. What happens is that a disk is accessed a block at a time. If the file system is setup for 4k blocks, a very large file will be accessed 4k at a time. That can be a lot of reads or writes. A tiny file, say 1k, is also accessed 4k at a time. That's 3k wasted. Now, database tables and indexes are really just large files, but they are typically accessed randomly. The ideal is to have your block size match your record size. This is rarely possible since different tables have different record sizes. And of course, some tables have variable length records. Unfortunately, there is no simple formula or tool to tell you the optimal block size for your data. You need to run tests. For starters, figure out your average record length for your busy tables. If your records are large, you may want to increase the block size. If they are small, maybe decrease the block size. BUT, you also need to keep in mind how your data is accessed. If you are typically doing full table scans, then your record length shouldn't carry as much weight, you would want to access the entire file as quick as you can, which means large block sizes. To get the optimal setup, it will eventually come down to trial and error testing. You don't have to test on a high end system, just something that won't bottleneck at the CPU or RAM. Partition a disk with various block sizes and move the database around running tests on each partition. Regardless, you should first double check that the bottleneck is I/O. The bottleneck in any system is always RAM, CPU, Network, or I/O (disk). Use the various stat tools (iostat, vmstat, netstat, etc.) to figure out your bottleneck. Not sure if they are named something else on Solaris. Also, see you if you can your hands on a copy of dtrace. I've never used it, but it's supposed to be an amazing performance profiling tool by Sun for Solaris. But it may be only for Solaris 10. Keep in mind that I/O isn't necessarily restricted to disk, it could be the I/O card or even the bus on the logic board. Apple had this issue in their late model G4 systems where the CPU and RAM were faster than the bus between them. Two gigabit ethernet cards can theoretically saturate a typical PCI bus. On Jan 4, 2005, at 1:47 PM, Bruce Dembecki wrote: Hi! We're setting up a Solaris system which is behaving poorly, very slow disk performance. The nice folks at Sun have suggested a mismatch between the block size MySQL is writing to disk and the block size the the Operating System is writing to disk. While we can see the logic in the argument, I have over the years not been able to find anywhere that this information was available or controlable, so my assumption has been that mysql gives the data to the operating system to write to disk and doesn't deal with block sizes. So here's the questions... In mysql 4.0.23 or 4.1.8 for Solaris Sparc 64 bit what is the block size mysql uses for writes to disk, and is there a way to control that? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
not all rows returned when using order by and null values?
See below: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508 order by avg); +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 13 rows in set (0.00 sec) mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 9 rows in set (0.00 sec) mysql As you can see, I have 13 rows that match the query, if I put the order by in the brackets it works fine. But when moved outside of them it fails. The reason I have the brackets is this is a dynamically generated query and may span tables ie: (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT avg FROM event_log_5minute_20050105 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; Am I missing something and this is expected behavior and not a bug? I'm using 4.0.18, I haven't checked the changelogs yet, maybe its been fixed. -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not all rows returned when using order by and null values?
- Original Message - From: John McCaskey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 04, 2005 5:22 PM Subject: not all rows returned when using order by and null values? See below: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508 order by avg); +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 13 rows in set (0.00 sec) mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 9 rows in set (0.00 sec) mysql As you can see, I have 13 rows that match the query, if I put the order by in the brackets it works fine. But when moved outside of them it fails. The reason I have the brackets is this is a dynamically generated query and may span tables ie: (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT avg FROM event_log_5minute_20050105 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; Am I missing something and this is expected behavior and not a bug? I'm using 4.0.18, I haven't checked the changelogs yet, maybe its been fixed. Wow, that's just weird if you ask me ;-) It's as if you added a 'DISTINCT' to the SELECT when move the ORDER BY outside of the brackets - except for the fact that it returns BOTH of 55854.1 values. Sorry, I don't have any idea what is going on there or if it is a bug that has been fixed already. ;-) Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cursors are not available in MySQL 5.0
Hello, I downloaded and installed MySQL 5.0. But I could not run declare cursor features in 5.0. Is the cursor implementation present in 5.0 ? Thanks, S. Sarkar [EMAIL PROTECTED]
Re: not all rows returned when using order by and null values?
On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote: - Original Message - From: John McCaskey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 04, 2005 5:22 PM Subject: not all rows returned when using order by and null values? See below: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508 order by avg); +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 13 rows in set (0.00 sec) mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 9 rows in set (0.00 sec) mysql As you can see, I have 13 rows that match the query, if I put the order by in the brackets it works fine. But when moved outside of them it fails. The reason I have the brackets is this is a dynamically generated query and may span tables ie: (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT avg FROM event_log_5minute_20050105 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; Am I missing something and this is expected behavior and not a bug? I'm using 4.0.18, I haven't checked the changelogs yet, maybe its been fixed. Wow, that's just weird if you ask me ;-) It's as if you added a 'DISTINCT' to the SELECT when move the ORDER BY outside of the brackets - except for the fact that it returns BOTH of 55854.1 values. Sorry, I don't have any idea what is going on there or if it is a bug that has been fixed already. ;-) Yeah, thats exactly what it is like, and its causing my computations to be corrupted. I haven't been able to come up with a good workaround that doesn't result in a significant slowdown in my application (these queries are run several million times daily... using a limit x,1 to select the 95th or 99th percentile value, and then store it in another table). If anyone else has ideas please comment! Thanks. Rhino -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking to resolve odd response to DOS
Hi, I've got an odd setup that I need to diagnose. The customer won't allow us to upgrade very far with the OS or MySQL. Its currently on a BSD/OS 4.0.1 system, and 4.2 is the highest we could possibly be allowed to go. We have upgraded to mysql-3.23.58 (Customer won't let us go to 4 or 5), with the following patches from the RedHat distro: mysql-3.23.58-config.patch mysql-3.23.58-setpermission.patch mysql-3.23.58-dropdb.patch mysql-3.23.58-symlink.patch mysql-3.23.58-hotcopy.patch mysql-3.23.58-typo.patch mysql-3.23.58-security.patch The configure is just ./configure --with-libwrap. (We do have to go in to client/Makefile, mysys/Makefile and sql/Makefile to take the -fno-exceptions out otherwise it won't compile) With the recent reports of an issue with MySQL we've been getting alot of probes against the server. Its currently set to only allow connections from a monitoring machine, and locally. It appears that after a while the server starts to get QUITE slow... Requests take over a minute, even using the local socket. Until we do a flush-hosts, it runs quite slow. Has anyone heard/seen/experienced this? On some other customer servers, we just had to put mysqld:ALL into /etc/hosts.allow to prevent the database from becoming unusable. Thanks, Tuc/TTSG Internet Services, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not all rows returned when using order by and null values?
FYI, I have now verified this bug (?) occurs in 4.0.23 as well. On Tue, 2005-01-04 at 15:03 -0800, John McCaskey wrote: On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote: - Original Message - From: John McCaskey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 04, 2005 5:22 PM Subject: not all rows returned when using order by and null values? See below: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508 order by avg); +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 13 rows in set (0.00 sec) mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 9 rows in set (0.00 sec) mysql As you can see, I have 13 rows that match the query, if I put the order by in the brackets it works fine. But when moved outside of them it fails. The reason I have the brackets is this is a dynamically generated query and may span tables ie: (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT avg FROM event_log_5minute_20050105 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; Am I missing something and this is expected behavior and not a bug? I'm using 4.0.18, I haven't checked the changelogs yet, maybe its been fixed. Wow, that's just weird if you ask me ;-) It's as if you added a 'DISTINCT' to the SELECT when move the ORDER BY outside of the brackets - except for the fact that it returns BOTH of 55854.1 values. Sorry, I don't have any idea what is going on there or if it is a bug that has been fixed already. ;-) Yeah, thats exactly what it is like, and its causing my computations to be corrupted. I haven't been able to come up with a good workaround that doesn't result in a significant slowdown in my application (these queries are run several million times daily... using a limit x,1 to select the 95th or 99th percentile value, and then store it in another table). If anyone else has ideas please comment! Thanks. Rhino -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL work with DTS?
I have a simple table in SQL Server that doesn't have any BLOB field (no text or image columns). Only char, varchar, bit, and int columns. I created a DTS package that uses a simple select to copy the contents of this table into an identical table created in MySQL through MyODBC 3.51. Whenver I execute this package, I get this weird error: Query-based insertion or updating of BLOB values is not supported But like I said above, I'm not using any BLOB column neither on the source SQL Server table nor on the destination MySQL table. I searched for this problem on the Internet and all I got was people having the same the problem with no answer other than more poeple looking for a solution. Any ideas about this problem? Does MySQL work with DTS, or I have to revert to dump files? I apprecaite your help, Homam __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not all rows returned when using order by and null values?
Even more interesting...: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 43282 | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 124671 | | 128803 | | 172269 | | 200622 | | 211235 | | 273802 | | 320097 | | 324012 | | 422318 | | 465117 | | 543541 | | 578753 | | 606568 | | 634915 | | 698947 | | 746309 | | 882563 | | 1.18043e+06 | | 1.50719e+06 | | 1.54081e+06 | | 1.60248e+06 | | 1.68207e+06 | +-+ 29 rows in set (0.00 sec) mysql The above returns only 1 of 5 nulls actually in my table... but if I do: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT NULL) order by avg; +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | |NULL | | 43282 | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 124671 | | 128803 | | 172269 | | 200622 | | 211235 | | 273802 | | 320097 | | 324012 | | 422318 | | 465117 | | 543541 | | 578753 | | 606568 | | 634915 | | 698947 | | 746309 | | 882563 | | 1.18043e+06 | | 1.50719e+06 | | 1.54081e+06 | | 1.60248e+06 | | 1.68207e+06 | +-+ 34 rows in set (0.00 sec) Now I get all 5, plus the one I added with the union... so it appears to only affect queries that have () like a union, but do not actually have a union? On Tue, 2005-01-04 at 15:40 -0800, John McCaskey wrote: FYI, I have now verified this bug (?) occurs in 4.0.23 as well. On Tue, 2005-01-04 at 15:03 -0800, John McCaskey wrote: On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote: - Original Message - From: John McCaskey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 04, 2005 5:22 PM Subject: not all rows returned when using order by and null values? See below: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508 order by avg); +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 13 rows in set (0.00 sec) mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 9 rows in set (0.00 sec) mysql As you can see, I have 13 rows that match the query, if I put the order by in the brackets it works fine. But when moved outside of them it fails. The reason I have the brackets is this is a dynamically generated query and may span tables ie: (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT avg FROM event_log_5minute_20050105 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; Am I missing something and this is expected behavior and not a bug? I'm using 4.0.18, I haven't checked the changelogs yet, maybe its been fixed. Wow, that's just weird if you ask me ;-) It's as if you added a 'DISTINCT' to the SELECT when move the ORDER BY outside of the brackets - except for the fact that it returns BOTH of 55854.1 values. Sorry, I don't have any idea what is going on there or if it is a bug that has been fixed already. ;-) Yeah, thats exactly what it is like, and its causing my computations to be corrupted. I haven't been able to come up with a good workaround that doesn't result in a significant slowdown in my application (these queries are run several million times daily... using a limit x,1 to select the 95th or 99th percentile value, and then store it in another table). If anyone else has ideas please comment! Thanks. Rhino -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL
Re: Does MySQL work with DTS?
OK, I figured out what was going on. When I created a varchar(256) in the MySQL destination table, MySQL silently converted to it text (instead of protesting that varchar maxes out at 255), and I just found the problem by executing show create table MyTable. --- Homam S.A. [EMAIL PROTECTED] wrote: I have a simple table in SQL Server that doesn't have any BLOB field (no text or image columns). Only char, varchar, bit, and int columns. I created a DTS package that uses a simple select to copy the contents of this table into an identical table created in MySQL through MyODBC 3.51. Whenver I execute this package, I get this weird error: Query-based insertion or updating of BLOB values is not supported But like I said above, I'm not using any BLOB column neither on the source SQL Server table nor on the destination MySQL table. I searched for this problem on the Internet and all I got was people having the same the problem with no answer other than more poeple looking for a solution. Any ideas about this problem? Does MySQL work with DTS, or I have to revert to dump files? I apprecaite your help, Homam __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dates range query
Hi everyone, I've always had a challenge working with dates. I'm building an app that needs to query a range of dates. I'm using ASP (for the first time, I usually write in Java). Anyway my date column uses the -00-00 format, which I think is the default format? Is it? I've read different approaches doing a google search, but am more confused than ever. I have used several date functions in java, but want to know if I can somehow use date functions in a regular sql query to somehow reformat the dates so I can query a range of dates. Does that make sense? Can someone give me an example of how to do this please? I'm sure people do this all the time, but it confuses the heck outta me :-) Thanks! -Brian Menke
Slow ORDER BY in MySQL v4.1.8
Hi list, I am thinking of upgrading to MySQL v4.1.8, I installed on my local machine(Windows XP). Copied tables from MySQL v3.23.xx to MySQL 4(eg, mytest.MYD, mytest.MYI, mytest.frm), it works just fine, query, sorting as usual. I created a new table (say 'newtest') with exactly same structure as 'mytest' table under MySQL 4, then select all data from 'mytest' and insert into 'newtest'. I have two problems here: the table structure is simple: CREATE TABLE `newtest` ( `id` int(11) NOT NULL auto_increment, `sessionid` int(11) NOT NULL default '0', `timestamp` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `sessionid` (`sessionid`, `timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # or ENGINE=MyISAM 1, If I created table 'newtest' using InnoDB type, inserting 50k records took me 20min! If I use MyISAM type, inserting took me less then 1 min. I don't know much about InnoDB but how come the performance is dropped down so much? 2, Even I choose MyISAM type, the same perl script has another huge performance difference on 'mytest' which is copied from MySQL 3 and 'newtest' which is created under MySQL 4. query is simple too, something like: SELECT id, sessionid, timestamp FROM newtest ORDER BY sessionid DESC LIMIT 0, 100; If I didn't use ORDER BY, or use ORDER BY primary key id, performance is same on these two tables, if I use ORDER BY (sessionid or timestamp), 'mytest' is 10 times faster than 'newtest'. Did I do anything wrong? Anyone can help on this? Thanks a lot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: not all rows returned when using order by and null values?
FYI, I created a bug for this (http://bugs.mysql.com/bug.php?id=7672) which has now been updated to verified. So it looks like it is in fact a mysql bug. From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Tue 1/4/2005 3:59 PM Cc: mysql@lists.mysql.com Subject: Re: not all rows returned when using order by and null values? Even more interesting...: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 43282 | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 124671 | | 128803 | | 172269 | | 200622 | | 211235 | | 273802 | | 320097 | | 324012 | | 422318 | | 465117 | | 543541 | | 578753 | | 606568 | | 634915 | | 698947 | | 746309 | | 882563 | | 1.18043e+06 | | 1.50719e+06 | | 1.54081e+06 | | 1.60248e+06 | | 1.68207e+06 | +-+ 29 rows in set (0.00 sec) mysql The above returns only 1 of 5 nulls actually in my table... but if I do: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT NULL) order by avg; +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | |NULL | | 43282 | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 124671 | | 128803 | | 172269 | | 200622 | | 211235 | | 273802 | | 320097 | | 324012 | | 422318 | | 465117 | | 543541 | | 578753 | | 606568 | | 634915 | | 698947 | | 746309 | | 882563 | | 1.18043e+06 | | 1.50719e+06 | | 1.54081e+06 | | 1.60248e+06 | | 1.68207e+06 | +-+ 34 rows in set (0.00 sec) Now I get all 5, plus the one I added with the union... so it appears to only affect queries that have () like a union, but do not actually have a union? On Tue, 2005-01-04 at 15:40 -0800, John McCaskey wrote: FYI, I have now verified this bug (?) occurs in 4.0.23 as well. On Tue, 2005-01-04 at 15:03 -0800, John McCaskey wrote: On Tue, 2005-01-04 at 17:43 -0500, Rhino wrote: - Original Message - From: John McCaskey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 04, 2005 5:22 PM Subject: not all rows returned when using order by and null values? See below: mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508 order by avg); +-+ | avg | +-+ |NULL | |NULL | |NULL | |NULL | |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 13 rows in set (0.00 sec) mysql (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; +-+ | avg | +-+ |NULL | | 55854.1 | | 55854.1 | | 63566.8 | | 70157.6 | | 121185 | | 128803 | | 172269 | | 320097 | +-+ 9 rows in set (0.00 sec) mysql As you can see, I have 13 rows that match the query, if I put the order by in the brackets it works fine. But when moved outside of them it fails. The reason I have the brackets is this is a dynamically generated query and may span tables ie: (SELECT avg FROM event_log_5minute_20050104 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) UNION ALL (SELECT avg FROM event_log_5minute_20050105 WHERE monitor_id=479139 AND monitor_server_id=1 AND timestamp = 2005010408 AND timestamp 2005010508) order by avg; Am I missing something and this is expected behavior and not a bug? I'm using 4.0.18, I haven't checked the changelogs yet, maybe its been fixed. Wow, that's just weird if you ask me ;-) It's as if you added a 'DISTINCT' to the SELECT when move the ORDER BY outside of the brackets - except for the fact that it returns BOTH of 55854.1 values. Sorry, I don't have any idea what is going on there or if it is a bug that has been fixed already. ;-) Yeah, thats exactly what it is like, and its causing my computations to be corrupted. I haven't been able to come up with a good workaround that doesn't result in a significant slowdown in my application (these queries are run several million times daily... using a limit x,1 to select
distinctSelection(veryUrgent)
hello, i m using mysql 4.0.18-standard version it is not accepting subqurries. problem is: i have 2 tables: First(id,Flower,Color); = color is char(2); Second(Shop,Date,Flower,Color); first table will contain only unique flowers , and colors, id is autoincrement. second table may contain duplicate entries. i want to select from Second table only those Flowers and Colors which are not there in First table. i tried with : select distinct(First.Flower),First.color from First,Second where First.color!=Second.color; but it will select all fileds in first. As this version of mysql will not support for subqurries...problem. can anybody reply with answer? === kavi === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: distinctSelection(veryUrgent)
select * from secondtable left outer join second.flower=first.flower and second.color=first.color where first.flower is NULL N. Kavithashree wrote: hello, i m using mysql 4.0.18-standard version it is not accepting subqurries. problem is: i have 2 tables: First(id,Flower,Color); = color is char(2); Second(Shop,Date,Flower,Color); first table will contain only unique flowers , and colors, id is autoincrement. second table may contain duplicate entries. i want to select from Second table only those Flowers and Colors which are not there in First table. i tried with : select distinct(First.Flower),First.color from First,Second where First.color!=Second.color; but it will select all fileds in first. As this version of mysql will not support for subqurries...problem. can anybody reply with answer? === kavi === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load on server
Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED]
RE: distinctSelection(veryUrgent)
SELECT s.* FROM Second s LEFT JOIN First f USING (Flower,Color) WHERE f.Flower IS NULL; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: N. Kavithashree Sent: Tuesday, January 04, 2005 11:17 PM To: mysql@lists.mysql.com Subject: distinctSelection(veryUrgent) hello, i m using mysql 4.0.18-standard version it is not accepting subqurries. problem is: i have 2 tables: First(id,Flower,Color); = color is char(2); Second(Shop,Date,Flower,Color); first table will contain only unique flowers , and colors, id is autoincrement. second table may contain duplicate entries. i want to select from Second table only those Flowers and Colors which are not there in First table. i tried with : select distinct(First.Flower),First.color from First,Second where First.color!=Second.color; but it will select all fileds in first. As this version of mysql will not support for subqurries...problem. can anybody reply with answer? === kavi === -- 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 Load on server
If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Load on server
Dear Tom, Thank you very much. Is there a way to log all the processes on the MySQL server to analyze later. At 09:48 AM 1/5/2005, Tom Crimmins wrote: If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Load on server
In you're my.cnf file (in /etc by default on a linux box) add under [mysqld] the option log for text logging. for example [mysqld] log=mylogfile Whatever user mysqld is running as must have write perms to the log file. You can then anaylze this as it will contain connections and queries. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath Sent: Wednesday, January 05, 2005 12:18 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: MySQL Load on server Dear Tom, Thank you very much. Is there a way to log all the processes on the MySQL server to analyze later. At 09:48 AM 1/5/2005, Tom Crimmins wrote: If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
manyaccesses
hello, if a progm read difft tables for some 10 times for one run each day..how will be the load on mysql server. usually how many table accesses per run will be there if a company is maintaining an export business and daily read and write will be there on tables. === Kavi === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-python compiling error on Fedora Core 3 x86_64
Dear List Members Can anybody give me a hint about how to compile mysql-python on a 64bit opteron system (see the error below) with mysql tarball? - Fedora Core 3 x86_64 - python-2.3.4 - MySQL-python-1.1.8 - mysql-standard-4.1.8-unknown-linux-x86_64-glibc23.tar.gz Thanks in advance Marc python setup.py build running build running build_py running build_ext building '_mysql' extension gcc -pthread -shared build/temp.linux-x86_64-2.3/_mysql.o -L/usr/local/mysql/lib -lmysqlclient_r -lssl -lcrypto -lz -lcrypt -o build/lib.linux-x86_64-2.3/_mysql.so /usr/bin/ld: /usr/local/mysql/lib/libmysqlclient_r.a(libmysql.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/mysql/lib/libmysqlclient_r.a: could not read symbols: Bad value collect2: ld returned 1 exit status error: command 'gcc' failed with exit status 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dates range query
Brian Menke wrote: Hi everyone, I've always had a challenge working with dates. I'm building an app that needs to query a range of dates. I'm using ASP (for the first time, I usually write in Java). Anyway my date column uses the -00-00 format, which I think is the default format? Is it? Thats the ISO date format, an unambiguous format for date. I've read different approaches doing a google search, but am more confused than ever. I have used several date functions in java, but want to know if I can somehow use date functions in a regular sql query to somehow reformat the dates so I can query a range of dates. Does that make sense? Can someone give me an example of how to do this please? I'm sure people do this all the time, but it confuses the heck outta me :-) If you want to query a range of dates you can do something like this: select * from tbl where datfield 2005-01-06 and datfield 2004-12-01 For more information about date-/time-functions see the excellent manual: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Thanks! -Brian Menke HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
