Re: changing ip addresses
Am Freitag, den 25.04.2008, 12:25 -0500 schrieb James Sheffer: Hi all, I've got a problem. A client of ours changed ip addresses on their mysql server, and I believe MySL was set up to listen on the old ip address. How can I see what ip address mysql is set to listen on, and how can I change it? It's configured with the bind-address option in the my.cnf configuration file. Norbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd Results on Mysql LIMIT and ORDER BY
Hi Guys, Firstly, this is the only time I have ever encountered this problem and searching archives or google shed no luck since yesterday so here I am . I have a table described below: mysql describe containers; +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | internal_id| mediumint(20) unsigned | NO | PRI | NULL| auto_increment | | category_id| smallint(20) unsigned | YES | MUL | NULL || | user_id| mediumint(20) unsigned | YES | MUL | NULL || | parts_amount | int(2) | NO | | 0 || | file_name | varchar(64)| NO | MUL | || | file_format| varchar(5) | NO | MUL | || | file_info | text | NO | | NULL || | file_description | text | YES | | NULL || | admin_comments | text | YES | | NULL || | is_approved| tinyint(1) | YES | MUL | 0 || | is_shared | tinyint(1) | YES | MUL | 1 || | is_deleted | tinyint(1) | YES | | 0 || | upload_date| bigint(10) | NO | MUL | 0 || | downloads | int(11)| YES | MUL | 0 || | last_download_date | bigint(10) | NO | MUL | 0 || | rating | decimal(3,1) | YES | MUL | 0.0 || | ftp_site | smallint(6)| NO | MUL | 0 || | total_votes| int(11)| NO | MUL | NULL || | total_dnloads | int(11)| NO | | NULL || | total_votes_ave| float | NO | | 0 || | total_votes_sum| int(11)| NO | | NULL || | file_img | varchar(120) | NO | | NULL || | file_extended_info | text | NO | | NULL || | file_exist | tinyint(4) | NO | MUL | 0 || | post_options | varchar(20)| NO | | NULL || +++--+-+-++ 25 rows in set (0.00 sec) mysql select count(*) from containers; +--+ | count(*) | +--+ | 9504 | +--+ 1 row in set (0.00 sec) mysql select count(*) from containers where upload_date 1209208414 and category_id = 120; +--+ | count(*) | +--+ | 795 | +--+ 1 row in set (0.01 sec) And I have queries like these: select * from containers where upload_date 1209208414 and category_id = 120 order by upload_date desc limit 0,25 and select * from containers where upload_date 1209208414 and category_id = 120 order by upload_date desc limit 175,25 These queries are dynamically generated and is is being paged for browser display so the second query means I am on the 8th page for 25 items each page. The problem is, offsets 0...150 (LIMIT [0...150],25) will not return any results while 175 onwards will. This happens only when I am filtering category_id 120, all other categories does not yield this odd result. I have no clue whatsoever what is going on, executing the query directly from the server yields the same results. Now, if I omit either the order by or limit clauses I get results all through out. Hope someone can shed some light. Jervin
Re: Migration from 32-bit to 64-bit MySQL
another option is to do a mysqldump and pipe it over to the new server, e.g: mysqldump --opt 32bitserver | mysql --host=64bithost -C 64bitserver - Original Message From: Mike To: B. Keith Murphy Cc: mysql list Sent: Friday, 25 April, 2008 5:33:49 PM Subject: Re: Migration from 32-bit to 64-bit MySQL On Fri, Apr 25, 2008 at 12:08 PM, B. Keith Murphy wrote: Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy I know you can take a mysqldump and copy over the data directory.. I not sure what you mean by binary copy. Can you please explain? We have one database in memory that why we are moving over to 64bit. I'm planing like a year ahead of time. ___ Yahoo! For Good. Give and get cool things for free, reduce waste and help our planet. Plus find hidden Yahoo! treasure http://green.yahoo.com/uk/earth-day/ __ Sent from Yahoo! Mail. A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Results on Mysql LIMIT and ORDER BY
You may want to check on the version you are running. There have been a few odd bugs in various MySQL versions in regards to limits and order by filtering. Although it usually involved joins and/or unions. If you can't or don't want to upgrade your MySQL version, you can try restructuring your query like this: select * from ( select * from containers where upload_date 1209208414 and category_id = 120 order by upload_date desc ) as filter limit 175,25 Technically, it's the same query and should return the same results. It will be a little more intensive, since the inner query returns all records, then a limit is imposed. Brent Baisley Systems Architect On Apr 26, 2008, at 7:22 AM, j's mysql general wrote: Hi Guys, Firstly, this is the only time I have ever encountered this problem and searching archives or google shed no luck since yesterday so here I am . I have a table described below: mysql describe containers; +++--+-+- ++ | Field | Type | Null | Key | Default | Extra | +++--+-+- ++ | internal_id| mediumint(20) unsigned | NO | PRI | NULL| auto_increment | | category_id| smallint(20) unsigned | YES | MUL | NULL || | user_id| mediumint(20) unsigned | YES | MUL | NULL || | parts_amount | int(2) | NO | | 0 || | file_name | varchar(64)| NO | MUL | || | file_format| varchar(5) | NO | MUL | || | file_info | text | NO | | NULL || | file_description | text | YES | | NULL || | admin_comments | text | YES | | NULL || | is_approved| tinyint(1) | YES | MUL | 0 || | is_shared | tinyint(1) | YES | MUL | 1 || | is_deleted | tinyint(1) | YES | | 0 || | upload_date| bigint(10) | NO | MUL | 0 || | downloads | int(11)| YES | MUL | 0 || | last_download_date | bigint(10) | NO | MUL | 0 || | rating | decimal(3,1) | YES | MUL | 0.0 || | ftp_site | smallint(6)| NO | MUL | 0 || | total_votes| int(11)| NO | MUL | NULL || | total_dnloads | int(11)| NO | | NULL || | total_votes_ave| float | NO | | 0 || | total_votes_sum| int(11)| NO | | NULL || | file_img | varchar(120) | NO | | NULL || | file_extended_info | text | NO | | NULL || | file_exist | tinyint(4) | NO | MUL | 0 || | post_options | varchar(20)| NO | | NULL || +++--+-+- ++ 25 rows in set (0.00 sec) mysql select count(*) from containers; +--+ | count(*) | +--+ | 9504 | +--+ 1 row in set (0.00 sec) mysql select count(*) from containers where upload_date 1209208414 and category_id = 120; +--+ | count(*) | +--+ | 795 | +--+ 1 row in set (0.01 sec) And I have queries like these: select * from containers where upload_date 1209208414 and category_id = 120 order by upload_date desc limit 0,25 and select * from containers where upload_date 1209208414 and category_id = 120 order by upload_date desc limit 175,25 These queries are dynamically generated and is is being paged for browser display so the second query means I am on the 8th page for 25 items each page. The problem is, offsets 0...150 (LIMIT [0...150],25) will not return any results while 175 onwards will. This happens only when I am filtering category_id 120, all other categories does not yield this odd result. I have no clue whatsoever what is going on, executing the query directly from the server yields the same results. Now, if I omit either the order by or limit clauses I get results all through out. Hope someone can shed some light. Jervin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]