MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again
Hi list, I've got a query coming out of sugarCRM that is generating this error: MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' I recently converted the entire database to utf8 - made sure all the connections are utf8 etc -- made php use utf8 - set the doctype on the page to utf8 -- when I run the same query in the mysql monitor it runs fine - when apache/php run it it fails to deal with the collation. the data was converted via mysqldump to text file and reimporting changing all tables/database to utf. fwiw: the query looks like this: ( SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , meetings.date_start , meetings.parent_id , meetings.parent_type , meetings.time_start , 'meetings' panel_name FROM meetings where ( meetings.parent_id= '63301596-6175-1b89-75df-431283170495' AND meetings.parent_type='Opportunities' AND meetings.deleted=0 AND (meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(CONCAT(jt0.first_name , ' '), CONCAT(jt0.last_name , ' ')) contact_name, tasks.contact_id , tasks.date_due , tasks.parent_id , tasks.parent_type , tasks.time_due , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 where ( tasks.parent_id= '63301596-6175-1b89-75df-431283170495' AND tasks.parent_type='Opportunities' AND tasks.deleted=0 AND (tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , calls.date_start , calls.parent_id , calls.parent_type , calls.time_start , 'calls' panel_name FROM calls where ( calls.parent_id= '63301596-6175-1b89-75df-431283170495' AND calls.parent_type='Opportunities' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 ) and in this case it doesn't return anything - which is correct given the data. we are using: mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i386) Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 I've seen some stuff that versions before 4.1.11 suffered from collation issues - is this likely to my case or can anyone see some other path through this - we have a single production database that we are very reluctant to update at this time. should i just switch back to latin1 ? thanks mysql list - you guys rock, lost in translation Jeff
Re: zip code search within x miles
On 4/16/05, Scott Gifford [EMAIL PROTECTED] wrote: Jeff Kolber [EMAIL PROTECTED] writes: How are sites doing the search by zip and coming up with results within x miles? Is there some OSS zip code download that has been created for this? The ones I'm familiar with use this: http://www.census.gov/geo/www/tiger/zip1999.html [...] I would be interested, anyone have insights into doing with Canadian Zip Codes or otherwise optimizing a database of Canadian Zip Codes? I looked into this a few months back (for a very similar project, actually) and found many places offering Canadian postal code databases, but none for free. If you already have the database, I don't see why optimizing it would be any different than for American postal codes. ScottG. Just that there are supposedly 650,000+ canadian codes. Makes you want to do whatever your doing in an optimal way. Jeff Kolber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zip code search within x miles
How are sites doing the search by zip and coming up with results within x miles? Is there some OSS zip code download that has been created for this? I have to do this on a high volume realestate website - we use the great circle concept to calculate the limits on longitudes and latitudes to be within distance n. We then select the zip codes within these limits and join on the zip code to the property info. We also calculate the distance from the center of the search to the found property. As our traffic grew I started storing the longitude and latitude in radians as well, which relieved the database from doing some math. I have also seen that we've had to update the database about twice a year to keep up with the USPS - mainly in terms of which counties are in which zip codes - which may or may not change more frequently in the period before an election. I would be interested, anyone have insights into doing with Canadian Zip Codes or otherwise optimizing a database of Canadian Zip Codes? Jeff Kolber http://www.ForSaleByOwner.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innoDB alter table and locking issues
Hello List, Recently I needed to alter a large innodb table (~200k rows) to add a column on a live server. The alter table ran fine on the development server with close to the same number of rows in the table so I didn't anticipate too much trouble. The list archive and the documentation indicate that alter table has some inneficenties - like rebuilding the entire table, indexes and all and that the table would be locked as read only while the alter table command was executing -- and the the alter table was likely to take A LONG TIME. (one poor guy posted that he gave up on altering his table after it was still running for 24 hours!) What we found is that the process list quickly filled up with SELECT statements that were 'Waiting for table' -- The connections maxed out (at 500) since the webservers were still sending queries, which I figured would be okay since they are reads, but apparantly these queries were locked out. I found myself locked out , unable to issue more commands and had to kill mysqld. Since all ALTER TABLE commands will probably exhibit suimilar locking - I am weary of even creating a new table with another name with the new structure, populating it with data from the table I want to alter, dropping the old table and renaming the new one back to the old one's name. So I'm wondering a few things if people can chime in: - does ALTER TABLE really just lock read only or does it just flat out LOCK? - might there be some configuration setting that influences this locking? - what strategies do people use to alter live busy tables ? Thanks for any and all input! Jeff Kolber ps: we are using: mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
