Selecting all minimum values
Hi, I have the following table. How can I select the lowest `place` for each `query` for each `date` (some queries appear twice as they have a different `fullurl`). Thanks for your help. Matt [code] +---+---+++ | place | query | fullurl| date | +---+---+++ | 2 | query 1 | http://yyy.co.uk/zzz.html | 1288051200 | | 2 | query 2 | http://yyy.co.uk/xxx.html | 1288051200 | | 1 | query 2 | http://yyy.co.uk/ | 1288051200 | | 5 | query 3 | http://yyy.co.uk/ | 1288051200 | | 3 | query 1 | http://yyy.co.uk/xxx.html | 1288051200 | | 3 | query 2 | http://yyy.co.uk/zzz.html | 1288051200 | | 1 | query 1 | http://yyy.co.uk/ | 1288051200 | | 2 | query 2 | http://yyy.co.uk/xxx.html | 1287964800 | | 3 | query 2 | http://yyy.co.uk/zzz.html | 1287964800 | | 1 | query 1 | http://yyy.co.uk/ | 1287964800 | | 2 | query 1 | http://yyy.co.uk/zzz.html | 1287964800 | | 3 | query 1 | http://yyy.co.uk/xxx.html | 1287964800 | | 5 | query 3 | http://yyy.co.uk/ | 1287964800 | | 1 | query 2 | http://yyy.co.uk/ | 1287964800 | | 1 | query 1 | http://yyy.co.uk/ | 1287878400 | | 2 | query 1 | http://yyy.co.uk/zzz.html | 1287878400 | | 3 | query 1 | http://yyy.co.uk/xxx.html | 1287878400 | | 5 | query 3 | http://yyy.co.uk/ | 1287878400 | | 1 | query 2 | http://yyy.co.uk/ | 1287878400 | | 2 | query 2 | http://yyy.co.uk/xxx.html | 1287878400 | | 3 | query 2 | http://yyy.co.uk/zzz.html | 1287878400 | [/code] So it returns this [code] | 1 | query 2 | http://yyy.co.uk/ | 1288051200 | | 5 | query 3 | http://yyy.co.uk/ | 1288051200 | | 1 | query 1 | http://yyy.co.uk/ | 1288051200 | | 1 | query 1 | http://yyy.co.uk/ | 1287964800 | | 5 | query 3 | http://yyy.co.uk/ | 1287964800 | | 1 | query 2 | http://yyy.co.uk/ | 1287964800 | | 1 | query 1 | http://yyy.co.uk/ | 1287878400 | | 5 | query 3 | http://yyy.co.uk/ | 1287878400 | | 1 | query 2 | http://yyy.co.uk/ | 1287878400 | [/code]
How to purposely corrupt a table
Hello, This is going to be a very odd question. I'm looking for a way to purposefully corrupt a table. The reason behind this, is that I would like to write a php script that will go through all databases/tables in the environment, and find any tables that are marked as crashed/corrupt, so that I can shoot an e-mail to appropriate support personnel to repair the table in question. So two questions really, 1) Does anyone know a good way to cause this to happen, and 2) is there already something written that would assist in this? Thank you, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Probability Selects
I've tried Googling till my brain is fried and I'm obviously missing something because I'm not finding anything useful. I'm trying to select names at random from a table that contains the name and the frequency at which it is actually used in society. The table is defined as follows: CREATE TABLE `MaleNames` ( `Name_ID` int(11) NOT NULL auto_increment, `Name` char(50) default NULL, `Frequency` decimal(5,3) default NULL, PRIMARY KEY (`Name_ID`) ) Some examples: 1, Aaron, 0.240 3, Abe, 0.006 13, Adrian, 0.069 What's the best way to select names at random from this but still take into account frequency of use? Thanks in advance! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Bug? Distinct AS with Order By
I'm not sure what to search on to see if someone has reported this as a bug or if I'm doing something wrong... Generic code to draw a SELECT element on the screen sometimes it ends up like such... SELECT DISTINCT name AS myvalue,name AS mydisp FROM names WHERE name!= ORDER BY myvalue On 4.1.22 this returns A A B B C C D D On 5.0.22 this returns D D D D D D D D The odd thing is that if I remove the order by clause it works fine... It also works fine if I remove the second copy of the column BUT this is generic code so it might also be doing something like productid as myvalue,productname as mydisp where the values are different. Ultimately I can sort the array I end up with but it seems like this should work. Especially since it did in 4.x. Am I crazy? Doing something wrong? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
VFP to MySQL Query Optimization
I feel like there should be a better way to do this... So I'm hoping someone will be able to advise. We have contacts that belong to organizations. We also have a document tracker. Holding over from VFP you can have up to six organization ids on the document and up to six contact ids. Right now the select to see if a contact has documents looks like this... SELECT * FROM Contacts WHERE (id IN (SELECT contid1 FROM documents) OR id IN (SELECT contid2 FROM documents) OR id IN (SELECT contid3 FROM documents) OR id IN (SELECT contid4 FROM documents) OR id IN (SELECT contid5 FROM documents) OR id IN (SELECT contid6 FROM documents) OR orgid IN (SELECT orgid1 FROM documents) OR orgid IN (SELECT orgid2 FROM documents) OR orgid IN (SELECT orgid3 FROM documents) OR orgid IN (SELECT orgid4 FROM documents) OR orgid IN (SELECT orgid5 FROM documents) OR orgid IN (SELECT orgid6 FROM documents)) Which is UGLY... and I feel like there should be a better way (I know I could break that out into a many-many relationship via a third linking table but I'm not 'able' to do that now.) The only change I can think of is to union the two halves of the select but I'm not sure if that would be better... (IE id in (select contid1 from documents union select contid2 from documents) etc) Any advice or is this the best I'm going to get until I can reorganize the underlying structure? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ALTER TABLE order / optimization
Given table: CREATE TABLE testtab (d_col CHAR(4)); Question 1: It appears that there is no harm in just appending directives onto the alter table command even if the order doesn't make sense. It appears the parser figures it out... For example... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST, ADD COLUMN b_col char(4) FIRST, ADD COLUMN a_col char(4) FIRST; ...does end up with a_col then b_col then c_col then d_col... but does it matter and I doing something wrong? Question 2: Is that any more efficient than doing... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; If it's NOT more efficient then I won't bother rewriting this one app which runs slowly to join them up because it certain is easier to read and debug with each modification on its own line. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump warning or actual error?
My local windows machine has mysql 5.1.33 installed on it. One of my Mac OSX dev servers has some 4.1 flavor of MySQL on it. When I try to do something like the following: mysqldump -h devserver -u me -p somedb dump.sql I get the following: mysqldump: Error: 'Table 'information_schema.files' doesn't exist' when trying to dump tablespaces It looks like it creates the export fine but I've been ssh-ing into the dev box and doing it locally there just in case Should I be worried? Is there some option that would supress that (that i didn't see in mysqldump --help)? Is it truely harmless? Thanks Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a Database
That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. Nope. These are entirely MyISAM tables... So I will probably give this a try then. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a Database
No... I've pretty much avoided those. This WAS a Visual FoxPro app so we're still at the stage where we really aren't leveraging MySQL specific features. We're still pretty much just eliminating VFP specific things. Hence the one time nature of this renaming. Now that we're 5-10 customers in to upgrading we decided to actually define a naming convention for the databases themselves so I've got to fix the 5-10 that are already out there (I could not change it... but then we have to remember that these are special cases) On Wed, Aug 19, 2009 at 10:28 AM, Rolando Edwardsredwa...@logicworks.net wrote: If you have stored procedures, don't forget to update the db column with the new db in mysql.proc as that does not automatically change. Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Wednesday, August 19, 2009 9:27 AM To: Paul DuBois Cc: mysql@lists.mysql.com Subject: Re: Renaming a Database That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. Nope. These are entirely MyISAM tables... So I will probably give this a try then. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Renaming a Database
I know the best way to rename a database is to use mysqldump, extract the database and then reload to the new database. (At least based on what I can find in the 12.1.32. RENAME DATABASE Syntax section of the documentation) That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. I know in the past I've used a similar method with single tables (stop service, create a folder, drop in backups of tables, start service muck with them) and I've had no problems... but I'm hoping wiser minds will confirm I'll be okay OR that I shouldn't even try. All in all, I'm trying to find a way to minimize OUR development time as well as minimizing down time for the client. This would be a one time thing to bring the database name in line with the new product's newly picked conventions. (After we deployed four customers) If it matters two of the installations are on OSX running a stock MySQL 4.x installation and two are on Windows and I'm not certain the version without checking. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Picking Collation Confusion
First off... I've read chapter 9.1.3 on character sets and collations and I'm still confused... :) (that could just be because today is Friday) Our application is installed at several different sites some running on Mac OS, some Windows and a few Linux which I suspect is what led to this situation. To deploy our app we basically do the following... 1. create and test 2. (on test server) mysqldump export.sql 3. (on deployment server) mysql export.sql Now I need to move a set of changes from the test server to the deployment server and I'm using mysqldiff to find the differences. I've noticed that 90% of the changes are simply to align the collation of fields and default collations for tables. Usually it's bouncing between utf8_general_ci and latin1_swedish_ci. 99.99% of the records in our various customers databases will be normal U.S. names and addresses but I know of a few customers that target their base fairly narrowly and might POTENTIALLY need to enter foreign names with accents and the like. Ultimately what it comes down to is... how worried should I be about making collations universal across at least a given customers instances of the application? (If not all copies of the application for all customers) I already have a routine that I call normalize database that makes sure default indexes are applied, etc... so it would be easy to add to that routine to check for and correct collations but then do I need to worry about existing data? Thanks for the advice! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Converting VFP SQL to MySQL
Does anyone have any scripts that will help convert Visual FoxPro 6.0 style WHERE clauses to MySQL... For the most part the problems are converting VFP functions to the equivalent SQL. For example, Visual FoxPro has a function inlist() that is used like inlist(X,1,2,3) which converts to the MySQL query X IN (1,2,3). That's easy enough (relatively speaking) but VFP also has stuff like EMPTY(X) where any of Null, the Empty String (for Char), -00-00 (or the VFP equivalent anyways for dates), False (for Boolean), 0 (for Numeric) are considered empty without needing to know the data type. So that starts getting a lot more complex since I'd need to check the data type of the field in the right table... to be able to convert it to something like (X is null OR X=) or (X is null OR x=0) etc... These are for customer stored queries... I've already manually converted system queries and I'm frustrated to the point of giving up and adding a column untested and let the end user figure it out but that seems bad from the standpoint of lazy and poor customer experience. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Removing Duplicate Records
In our database we have an Organizations table and a Contacts table, and a linking table that associates Contacts with Organizations. Occassionally we manually add to the linking table with information gleaned from outside data sources. This is common enough to be annoying, since it ends up with duplicate linkages, but it's FAR from an everyday occurance. I have three options for dealing with the resulting duplicates and I would appreciate some advice on which option might be best. 1. Attack the horrific spaghetti code that determines the Org and Contact ids and then does the manual add. Creating Orgs and Contacts as needed. Calling this code horrific is a kindness... we're talking evil... We've pretty much ruled this out due to the horror... but I mention that I considered it. 2. Do a create table and populate that new table with the results of a select distinct from the old table then swap the tables. 3. Do... SELECT count(*) AS tCount,OrgID,ContID FROM OrgContLink GROUP BY OrgID,ContID HAVING tCount 1. Then for every record in the result DELETE FROM OrgContLink WHERE OrgID=X AND ContID=Y LIMIT 1. Then repeat until no results are found. I like option 2 in so far as it's more... Atomic? One create...select, one swap and its done. But even though it feels more pure I worry that the overhead of completely creating and destroying entire tables seems like throwing the baby out with the bathwater. IOW: Is rebuilding a whole table for a few (hundred at most) offending duplicate records overkill. I like option 3 in that it leaves everything as is but does require a lot of looping and feels inefficient. However, since we'd be running this only after we do our imports it's not like this looping inefficient code would be running all the time. I know I could probably also put a unique key on both orgid and contid but unless I'm reading the docs wrong I can't add a key in such a way that the duplicate key insertion would silently fail without requiring the offending application to do INSERT ... ON DUPLICATE KEY... which gets back to modifying the spaghetti code from option 1. Thanks in advance for your advice. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Removing Duplicate Records
Ah... Yes. Good point. I like this because I was planning on keeping the output somewhere for a while. (In case we need an accounting at some point) So it will be easy enough to dump what's being deleted to the screen while we loop over our candidates. Thanks! On Tue, Jul 14, 2009 at 10:16 AM, Nathan Sullivannsulli...@cappex.com wrote: If you went with option #3, you could avoid the looping by using (tCount - 1) as the LIMIT in the delete statement instead of always using 1. 3. Do... SELECT count(*) AS tCount,OrgID,ContID FROM OrgContLink GROUP BY OrgID,ContID HAVING tCount 1. Then for every record in the result DELETE FROM OrgContLink WHERE OrgID=X AND ContID=Y LIMIT 1. Then repeat until no results are found. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CONCAT with IF?
I want to store the product version that an article applies to and a comparison operator in my news system. But I can't wrap my head around the where clause... Here's what I've tried... CREATE TABLE test (version char(10), direction char(2)); select concat(6.0,direction,version) as operation from test; +-+ |operation| +-+ | 6.0=6.0 | 6.0=6.0 | 6.06.1 | 6.06.2 +-+ But when I do select if(concat(6.0,direction,version),Y,N) from test I ALWAYS get Y... which means I wouldn't get any meaningful results if I used it in a where clause... Any ideas on ways I can do this? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisleybrentt...@gmail.com wrote: It sounds like you want to use spatial indexes, but they only became available in v4.1 http://dev.mysql.com/doc/refman/5.0/en/create-index.html http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html That feels like the right thing (spatial calculations = spatial indexes?) but I looked at the docs and my head exploded. Can anyone recommend a good book that takes me through it gently? That said I'm intreged by the MBRContains and the Polygon functions... If I read those right I could create a simplified circle (probably just an octogon) to help eliminate false positives in the corners when using a plain square as the enclosure. You don't have to do any re-architecture to change you subquery to a join: SELECT custzip FROM customers JOIN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) AS zips ON custzip=zip Will that work after a where clause? Multiple Times? For example... (pseudo-code...) SELECT * FROM customers WHERE saleslastyear 10 JOIN (SELECT zip FROM etc) AS zips ON custzip=zip JOIN (SELECT MAX(date) FROM phonecalls) AS LastCalledOn ON custid=custid Just from thinking about that... I assume that the only limitation is that in a subselect you can do something like WHERE NOT IN (select etc) but with a JOIN you are assuming a positive relationship? For example using the JOIN methods above there isn't a way to simply do AND custid NOT IN (SELECT custid FROM ordersplacedthisyear) other than doing exactly that and adding this clause to the saleslastyear clause. (In this particular case a column lastorderdate in customer that was programatically updated on ordering would also be useful but I'm thinking examples here... ;) ) I've never seen JOIN used outside of a traditional SELECT t1.*,t2.* FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id=t2.id type of structure so I kinda feel like I have a new toy... Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
SELECT zip FROM zipcodes WHERE
degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
5
Ouch. You might want to calculate the rectange enclosing your target
distance, add an index on lat (and/or long), and add the rectangle check to
your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
ln2 AND ln2. That way mysql can use the index to pare down most of the rows
without having to call all those trig functions for every zipcode.
I like this idea the best (it always bothered me running a query that
involved multiple mathmatical functions).
So... Here's the scratch php code I ended up with... Anyone see any
problems with it? The only problem I see is that I think the old code
was more circular this will be a square (within the limits of a
square on a non-spherical earth... etc.. etc..) ... so there will be
more zip codes included in the corners. If there are too many
complaints about that I might look at some sort of overlapping
rectangle scheme instead of a square.
function ChangeInLatitude($Miles) { return rad2deg($Miles/3960); }
function ChangeInLongitude($Lat, $Miles) { return
rad2deg($Miles/3960*cos(deg2rad($Lat))); }
$Miles = 5;
$OriginalLat = 39.0788994;
$OriginalLon = -77.1227036;
$ChangeInLat = ChangeInLatitude($Miles);
$ChangeInLon = ChangeInLongitude($OriginalLat, $Miles);
$MinLat = $OriginalLat-$ChangeInLat;
$MaxLat = $OriginalLat+$ChangeInLat;
$MinLon = $OriginalLon-$ChangeInLon;
$MaxLon = $OriginalLon+$ChangeInLon;
My only other question is... when I explained the new query... On the
dependent subquery it says possible keys are zip, longitude and
latitude but it used zip. It seems like a better index would be
longitude or latitude? On the primary query, even though there is an
index on custzip it doesn't say it's using ANY indexes. I should
probably leave well enough alone... but I'm curious.
Thanks again!
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
I'm converting a PHP app from using Visual FoxPro as the database backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX 10.4. The end application will be deployed cross platform and to both 4.x and 5.x MySQL servers. This query returned 21 records in .27 seconds. SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5 This query returned 21442 records in 1.08 seconds. SELECT custzip FROM customers This query is still running half an hour later, with a Time of 2167 and a State of Sending Data (according to the mysql process list) SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) When I try to EXPLAIN the query it gives me the following... id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using index 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where If it matters both tables are INNODB and both customers.custzip and zipcodes.zip are indexed. We used a program called DBConvert from DMSoft to convert the data so it's exactly the same on both the VFP side and the MySQL side. With all that in mind... VFP returns the exact same query in 5-10 seconds and that includes render time in the web browser. By comparison... the query WHERE id IN (SELECT id FROM phone WHERE phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns almost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-architecturing than I want to do... (Unless I'm forced) Especially since more than a few of those solutions suggested precalculating the distance between zipcodes which only works if the distances are known (only allow 10, 50 and 100 mile radi for example) Any ideas? Thanks in advance! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Unbiased opinion needed on access control procedure
Hi all, I'm looking to get an unbiased opinion of two possible methods for controlling access to database servers. Here are the specs: * 6 database servers, all firewalled off to the outside world, but accessible to each other on port 3306 unrestricted * 10 web servers total, but only half of those need to connect. Which option? A) GRANT replication slave ON *.* TO [EMAIL PROTECTED]; GRANT replication slave ON *.* TO [EMAIL PROTECTED]; GRANT replication slave ON *.* TO [EMAIL PROTECTED]; GRANT SELECT ON database.* TO [EMAIL PROTECTED]; GRANT SELECT ON database.* TO [EMAIL PROTECTED]; B) GRANT REPLICATION SLAVE ON *.* TO ruser; GRANT SELECT on database.* TO otheruser; A: Pro: Granular control per host, although the permissions are always identical, we still block the webservers that don't need access. Con: Hard to manage, 40 webservers = 40 users with the same permissions, 10 slaves = 10 users with the same permissions B: Pro: Simple to manage. One user allows access from anywhere on the internal network (since the servers are firewalled off to the outside) Con: Any server on the internal network can connect as that specific user to the database. Could cause problems with old code, etc. if passwords aren't changed. Which option do you use in your setup? Which do you see more fit? Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Different max_packet_size sizes
We've found recently that have a max packet size of 8 MB on our master and on our slaves causes problems. For some reason, slaves will error out during replication on packets that come from the master if they are very close to max_packet_size. The solution is to set the max_packet_size higher on the slaves than on the master, but we shouldn't have to do this (I don't think). Does replication add some sort of extra header information, etc. that would increase the packet sizes? If so, is there a formula for calculating what the max_packet_size should be in a master/slave/setup? Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master database server continues to crash
Hi all, We've been having our master database server continually crash about once or twice per week. The most recent output in the error log is contained at the bottom of this message. The funny part is, that not only is mysql crashing often, but we are also seeing some very weird errors in some of our queries... such as: [nativecode=1062 ** Duplicate entry 'aa59442538' for key That error comes from a query where the query actually has an ON DUPLICATE KEY UPDATE clause. Also, we're getting errors with crashed tables: Table '' is marked as crashed and should be repaired A check table on that table and other tables usually results in invalid row count (these are MyISAM tables). There is no pattern to the errors other than that they tend to occur on high traffic tables. We are running mysql version: mysql Ver 14.12 Distrib 5.0.27 We are also running these mysql version elsewhere with no problems. I am beginning to think more and more that this issue is related to the hardware of the master server, not necessarilly the mysql version itself. Although we do need to upgrade, does anyone know of any existing bugs in 5.0.27 that may be causing our issues? Or is this most likely a hardware issue? Thanks, Matt One of the outputs in the Error log (there are many more): Version: '5.0.27-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1073741824 read_buffer_size=131072 max_used_connections=19 max_connections=300 threads_connected=15 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1701373 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x2ac0865b40 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x450c9f78, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x5f7374656c796568 Stack trace seems successful - bottom reached Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac e. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x2ac05362b0 is invalid pointer thd-thread_id=15401 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. mysqld: my_new.cc:51: int __cxa_pure_virtual(): Assertion `Pure virtual method called. == Aborted' failed. Fatal signal 6 while backtracing Number of processes running now: 0 070907 07:21:51 mysqld restarted 070907 7:21:51 InnoDB: Database was not shut down normally! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing a linked list
Hi all,
I've got a table such as the following:
id1 char
id2 char
sample data looks like this:
id1 id2
1 3
2 4
3 5
5 6
6 8
And of course another table has something like:
id info1 info2 info3
1 blahblahblah
2 blahblahblah
I'd like to store paths to specific destinations...
In other words, the path from 1 to 8 is:
1,3,5,6,8
I was thinking of creating a table called relationships
start end path
1 8 {3,5,6}
This would allow me to easily display the path if I know the start and end, but
what it doesn't allow me to do is reuse the data.
IE: say that I calculate the path from 1 to 8 as 1,3,5,6,8, and then I want to
know the path from 3 to 6. even though this is already calculated, I have to
recalculate it as another row... hence
start end path
1 8 {3,5,6}
3 6 {5}
I considered making another table, called hops, such as:
start end relationshipID
1 8 1
table hops:
relationshipID start end
1 1 3
1 3 5
1 5 6
1 6 8
Then I could almost reuse those hops somehow but not sure.
Can anyone recommend a good way to store this data?
Thanks!
-Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Data Vanishing with FireFox
I think it's your PHP application; how did you debug your application?
All this is doing is letting our customer add their contacts to the database.
This is on the quick add form and asks them to enter a first and last name
and an email.
I debugged by re-writing it temporarily to do this...
$SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES ('
.$_POST[ContFirst].','.$_POST[ContLast]
.','.$_POST[ContEmail].','.$MyID.')';
echo $SQL;
$result = mysql_query($SQL,$db);
On the theory that the echo shouldn't change the contents of the $SQL
variable.
Then on the same client machine, I open both browsers and launch the page. In
both browsers I can see the exact same statement (including the value
of $MyID)
on the screen but in IE it works and on FireFox it doesn't.
As soon as I see the SQL on the screen I query directly (not through PHP) to
pull out the records and see that UserID is missing.
What data type is column 'C'? Is it ENUM?
Nope. Nothing compilicated... ContFirst,ContLast are Char(30), ContEmail is
Char(100), UserID (the one failing) is INT(4). The typical values of
UserID (at
least when debugging, and still failing) were numbers from 1-20.
Again... since I'm seeing the exact same statement on the screen in the echo,
theory has it that the exact same statement is being passed into the
mysql_query in the next line.
This is why I'm so baffled. Normally I echo the actual statement and it's
obvious what my error is. (Oh... a comma...) OR I echo the statement and see
that FireFox handles cookies different, or passes form values different, or
truncates $_GET at a different length or some other equally obscure thing but
still that the STATEMENT differs and I have something to start tracing from.
Here the statement is identical on both browsers and one fails and the other
doesn't.
Any other ideas?
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Data Vanishing with FireFox
We recently started getting reports that when users access our PHP based CRM system that it didn't work. After much debugging and tracking down it looks like that ultimately what's happening is that SOMETIMES with a statement like... INSERT INTO Customers (A,B,C) VALUES (One,Two,Three) ...that Three just doesn't make it into the database. So If I... SELECT * FROM Customers WHERE A=One ... I would get One,Two, It seems to happen more often (maybe only) with FireFox. Other tables have no problems that we can see. C is indexed... but we have other indexed fields in the table that don't seem to be affected. It's not our biggest table by far... I'm baffled. Where do I look next? This is on a hosted box so we have limited access to the command line. This is using PHP 4.3.11 with MySQL 3.23.58 on Apache 2.0.51 on a Fedora system (of unknown version) Thanks in advance... Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Hangs, Linux AMD 64, 5.0.22 AB Binaries
On 7/13/06, Jim Winstead [EMAIL PROTECTED] wrote: Sounds like the well-known problem with Debian stable's glibc on x86_64: http://hashmysql.org/index.php?title=Opteron_HOWTO#pthread_rwlock_wrlock_hang_with_nptl Just for the record, this resolved it for us. We applied the patch mentioned to the debian libc6 sources and tested. Apologies for the delay in replying thanks again for a super fast pointer to the cause of the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Hangs, Linux AMD 64, 5.0.22 AB Binaries
On 7/13/06, Jim Winstead [EMAIL PROTECTED] wrote: Sounds like the well-known problem with Debian stable's glibc on x86_64: http://hashmysql.org/index.php?title=Opteron_HOWTO#pthread_rwlock_wrlock_hang_with_nptl Any other ideas or suggestions? We've upgraded to libc 2.3.6 from etch, and we're still seeing the problem (although it is taking a good deal longer / more queries to reproduce it). We also re-compiiled the debian sarge libc6 with the appropriate patch applied, and again no joy. 4333 being the pid for mysqld # grep libc /proc/4333/maps 0040-00905000 r-xp fe:00 51481171 /home/matt/mysql/install/mysql-standard-5.0.22-linux-x86_64-glibc23/bin/mysqld 00a05000-00b68000 rw-p 00505000 fe:00 51481171 /home/matt/mysql/install/mysql-standard-5.0.22-linux-x86_64-glibc23/bin/mysqld 2aee5000-2aeea000 r-xp 08:01 343428 /lib/libcrypt-2.3.6.so 2aeea000-2afe9000 ---p 5000 08:01 343428 /lib/libcrypt-2.3.6.so 2afe9000-2afeb000 rw-p 4000 08:01 343428 /lib/libcrypt-2.3.6.so 2b2b7000-2b3db000 r-xp 08:01 343425 /lib/libc-2.3.6.so 2b3db000-2b4da000 ---p 00124000 08:01 343425 /lib/libc-2.3.6.so 2b4da000-2b4ef000 r--p 00123000 08:01 343425 /lib/libc-2.3.6.so 2b4ef000-2b4f2000 rw-p 00138000 08:01 343425 /lib/libc-2.3.6.so -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Hangs, Linux AMD 64, 5.0.22 AB Binaries
Hi,
Having a most odd problem with random crashes, that appear to be some
kind of deadlocking or blocked process/thread occuring within mysqld
(a wild guess, not any type of assertion).
mysql v4.0.24, ~350Mb DB, running in production for months, no
problems under fairly heavy daily use from multiple users.
Upgraded to 5.0.22 [1], re-importing the data from mysqldump files.
Periodically, a client process will lock up, while holding a lock on
the table. Separate clients can connect and issue queries, but not on
the table that is locked by the hung process. Accessing the locked
table in question hangs the clients, which never appear to return.
Just for the record, killing the client(s) doesn't release the lock.
First observed on our production machine [2]. We took a snapshot, by:
* stopping mysqld
* enabled the query log, copied the db files from /var/lib/mysql
* restarted mysqld, waited for the problem to re-occur,
* stopped mysqld, copied all the files to one side, then downgraded to v4.0.24.
On our dev box [3], we
* installed the v5 mysqld from the same deb package (and later from
MySQL AB binaries, see [4])
* copied the /var/lib/mysql files in that we took from our production box
* parsed the mysql query log with perl, and re-applied the statements
(1 DBI connection per user, as shown in query log, queries applied
in query log order.
DBI does set autocommit=1 on each connection)
Doing this:
* We can reproduce the crash
* It typically always crashes at the same point
(Doing a simple insert into one of a couple of a fairly simple MyISAM tables)
* Sometimes it doesn't crash at that point, but does eventually, while
doing an insert.
I can reduce it to about 40 queries that will crash it 90% of the
time. However, by running some innocent/any query in a separate
command line client immediately after restarting (with reset files in
/var/lib/mysql) and before running the query log commands, the crash
typically won't occur until a lot more queries have run.
Removing a couple of the earlier select queries (40) causes the
lockup not to happen until later on.
We recompiled with the --with-debug and have a couple of the trace
files from when it crashed (it appears to crash less often with the
debug-enabled mysqld). I'm not really up to reading the tracefiles
and making any sense out of them. The last few lines of both do
differ, but the last line is always:
process_alarm: info: sig: 14 active alarms: X
where X might be 1..4 typically.
When this occurs, in almost all cases:
mysql show processlist;
...
| 13 | wwwuser | localhost | our_db | Query | 6 | update | INSERT
INTO `` (`field_1`, `field_2`, `field_3`, `field_4`) VALUES
('', '1 |
...
Occasionally we instead see:
| 266 | wwwuser | localhost | our_db | Query | 29 | Locked | INSERT
INTO `` (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`,
`field_6`, `da |
The time (6 and 29 in the above) would increase, but everything else
remained as shown. It would never complete. Any other client trying
to access that table would be blocked indefinitely. Any further
queries on the relevant table would show up in processlist as Locked
Next, we downloaded the MySQL AB binary [4]. Exactly the same behaviour.
I'm stumped, and wondering if anyone has some ideas on how to proceed?
Thanks,
Matt.
[1] We started from the Debian package for version 5.0.22-2bpo1 found here:
deb-src http://www.backports.org/debian sarge-backports main
and compiled it with --with-openssl
[2] Production : Linux production 2.6.8-11-amd64-generic #1 Wed Jun 1
00:42:47 CEST 2005 x86_64 GNU/Linux
AMD Opteron(tm) Processor 244, 4Gb RAM
Running Debian Sarge (stable), including libc6 2.3.2.ds1-22
[3] Development: Linux dev 2.6.15-1-amd64-k8-smp #2 SMP Tue Mar 7
21:00:29 UTC 2006 x86_64 GNU/Linux
AMD Athlon(tm) 64 X2 Dual Core Processor 4400+, 4Gb RAM
Running Debian Sarge (stable), including libc6 2.3.2.ds1-22
[4]
http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-x86_64-glibc23.tar.gz
We stopped the debian packaged version (/etc/init.d/mysql stop)
and ran this instead.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SNMP monitoring of mysql servers
Hi all, We have an implementation of mysql servers, and are looking for a decent SNMP monitoring package for them. We are looking to monitor replication, queries per second, throughput, and slow queries. Are there any decent packages that easily integrate? Regards, Matthew Juszczak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select the last entry per item
Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. Thanks -Brian
Re: Social Network, linking members
probuly sounds like noobish! or silly but what i would do is have a new
table with 2 colums the first will be a login id and the second will be
a login id
and i would just do like ...
id1 id2
JohnJoe
JohnAlex
JohnFred
Joe Fred
FredAlex
would mean John as Alex, Fred and Joe as his friends
Joe has Fred as a friend
and Fred had Alex as a friend
and would just do like
SELECT `id2` FROM `freiends` WHERE `id1` = 'John';
to get all of Johns friends
and
SELECT `id1` FROM `friends` WHERE `id2` = 'Fred';
to get people that had fred set as their friends
and just work around that, of cause you can have id or account numbers
and not names, can anybody see any problems with that ?
Martin Gallagher wrote:
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.
You've just hit the nail on the head! That's exactly the problem.
I think I might just have to grin and bear what I already have :-(
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1, id2.
So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends with
who. I know 'friend' may not be the best term to use but it is easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or may
not be a problem depending on exactly what kind of a relationship you
are trying to define.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about mysqld
I am having similar issues. I moved a copy of mysqld_safe to init.d and removed the old startup script, this had the effect of lowering the number of processes from about 30 something to 7 or 8. However mysqld_safe now appears as a subprocess of rc and under mysqld_safe is mysqld and under that are the 7 or 8. I know that in some situations there should be a few process however on an exact replica of this machine only mysqld_safe with a single subproccess is running. Bing Du [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, Usually, 'ps -ef|grep mysqld' shows: root 1932 1 ... mysql 1962 1932 ... mysql 1963 1932 ... mysql 1964 1932 ... ... But today I noticed it's like this: root 1932 1 mysql 1962 1932 ... mysql 1963 1962 ... mysql 1964 1963 ... mysql 1965 1963 ... mysql 1966 1963 ... ... So what might have caused 1-1932-1962-1963-1964? Also there are 10 mysqld running. Does that mean there are that many connections established? Would anyboby shed some light or provide pointers? Thanks much in advance. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows - logging into MySQL
Well stupid questions are usually responded to with simple answers like rtfm. You'd think you'd be able to at least be able to install a version of the daemon that's still supported before you get into advanced topics like mysql -h Beauford [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] This is why lists like this get a bad name. First off, these instructions are for 4.1. Secondly. What makes you think I haven't read the instructions or searched extensively on Google etc. Either give a proper answer or just shut your mouth. Never mind anyone responding, I'll find my own damn answers. Thanks for the help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:16 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Beauford [EMAIL PROTECTED] wrote on 11/29/2005 11:31:24 PM: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. You could try following the installation instructions in the manual http://dev.mysql.com/doc/refman/4.1/en/windows-installation.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comma-separated JOINS
I just wanted to thank you for such a thorough response, it has helped me a lot as I've always ignored the existence of joins. Thanks, Matt [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] The sequence you use to JOIN a set of tables is sometimes determined completely by the logic of the JOIN. You should perform all of your CROSS JOINs and INNER JOINs first (a comma is equivalent to a CROSS JOIN but starting with 5.0.12 the comma has a LOWER evaluation priority than an explicit JOIN so watch out!!) then list your LEFT or RIGHT JOINs. It is very difficult to have both LEFT and RIGHT joins in the same query and get a correct result. You can nearly always transform a query containing both LEFT JOIN and a RIGHT JOIN into a query using a CROSS JOIN and a LEFT JOIN. When given the opportunity in a query where several equivalent constructions are available (as in a query that uses nothing but INNER JOINs) I try to list the smallest table first. It may not have the least number of physical records but should have the fewest number of rows returned. JOINs are geometrically expensive operations and the fewer rows you need to evaluate between stages of your JOINs, the more likely you are to have better performance. Try to keep the columns that are references to or are referenced by other tables indexed. The MySQL query engine (at least until 5.0) will use just one index per table per query. Which tables the columns come from in your results does not matter so long as you construct your table reference correctly. Above all else, I strongly discourage the use of comma-joins. The explicit JOIN syntax is not only less ambiguous but is the only way to declare an outer JOIN with MySQL. When you use the explicit JOIN syntax, you are also less likely to form unintentional Cartesian products which can absolutely clobber a query's performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matt Monaco [EMAIL PROTECTED] wrote on 11/29/2005 12:22:45 AM: What would be the most efficient order to join in? Say I have one main table with most columns (I assume this should be the main table of the query) then each table relates to the next, is it as simple as putting them in order? Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Matt, When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? There's no ON clause for a join specified by a WHERE clause, and that's one reason specifiying joins with JOIN ... ON .. is almost always preferable--it entirely disambiguates the join for the writer, readers, and those others who later will have to divine what you meant :-) . PB - Matt Monaco wrote: When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- 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]
comma-separated JOINS
When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why are posts to this list showing up in my inbox?
I am using outlook express to view this mailing list, I do not need the posts actually mailed to my account, how can I address this? Thanks in advance, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are posts to this list showing up in my inbox?
Ahh, I thought I needed to subscribe just to have access to post like the php mailing lists. Didn't realized that was just for emailed copies. Thanks. Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Click the help menu? They show up in your inbox because you subscribed to the list. Perhaps the forums (http://forums.mysql.com) may be a better place for you to check out. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Matt Monaco [mailto:[EMAIL PROTECTED] Sent: Tuesday, 29 November 2005 1:40 PM To: mysql@lists.mysql.com Subject: Why are posts to this list showing up in my inbox? I am using outlook express to view this mailing list, I do not need the posts actually mailed to my account, how can I address this? Thanks in advance, Matt -- 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: comma-separated JOINS
What would be the most efficient order to join in? Say I have one main table with most columns (I assume this should be the main table of the query) then each table relates to the next, is it as simple as putting them in order? Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Matt, When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? There's no ON clause for a join specified by a WHERE clause, and that's one reason specifiying joins with JOIN ... ON .. is almost always preferable--it entirely disambiguates the join for the writer, readers, and those others who later will have to divine what you meant :-) . PB - Matt Monaco wrote: When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL-Max Mandriva
I have MySQL 4.1.12 that came on the Mandriva Linux 2006 RC1 CDs. I think it is probably the Standard rather than the Max version. How can I tell for sure? The reason I am asking is because I have the MySQL Tutorial book, published by MySQL Press and it says that I need the Max version to do all the lessons in the book. Installing MySQL version from Mandriva was a no-brainer because it was already adapted and included in the Mandriva package manager. How complex would it be to get the Max version from the MySQL web site and get it going on my machine with Apache and PHP? If I go that route should I go ahead and get MySQL 5 while I'm at it? Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL install problems on OSX 10.4
I've just installed MySQL 4 4.1.12_1 +server using darwinports but I can't seem to get past this error. After installing it tells me that it cold not resolve my IP. Mattys-iMac-G5:/opt/local/bin matty$ sudo -u mysql ./mysql_install_db Password: WARNING: The host 'Mattys-iMac-G5' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing all prepared tables 050910 15:52:28 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql/ is case insensitive Fill help tables 050910 15:52:30 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql/ is case insensitive I ignore this and try and start mysqld using cd /opt/local ; /opt/local/bin/mysqld_safe but it won't work. It says Mattys-iMac-G5:/opt/local/bin matty$ cd /opt/local ; /opt/local/bin/mysqld_safe [1] 18533 Mattys-iMac-G5:/opt/local matty$ chown: /opt/local/var/Mattys-iMac-G5.err: Operation not permitted Starting mysqld daemon with databases from /opt/local/var STOPPING server from pid file /opt/local/var/Mattys-iMac-G5.pid 050910 16:09:32 mysqld ended Can anyone advise me on what's going on with the the mysqld not starting? Typing mysql at the prompt tells me: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysqld/mysqld.sock' (2) Very confused as to why it isn't working. Any help most appreciated -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searchable/Sortable Encrypted Fields in MySQL?
I need to securely store lots of sensitive contact information and notes in a (MySQL or other freely available) database that will be stored on a database server which I do not have direct access to. This database will be accessed by a PHP application that I am developing. However, I also need to be able to search/sort these data with the database functions (SELECT, ORDER BY, etc) so simple PASSWORD style encryption of specific fields would not work. (For example, I need to encrypt contacts' names, but need to be able to sort results by name). (I realize I could load the entire table into memory with PHP and process/search/sort it there, but that's obviously not a very good solution). Ideally I would like to encrypt entire tables. An encrypted file system is not really an option, because the goal is to prevent loss if the database server is hacked (in addition, I wouldn't be able to install an encrypted file system on the database server). Any suggestions? Thanks much, Matt
RE: Storing huge amount of binary data
Sometimes, the easiest way to do this is to use the file system of the linux machine to store the files, and make reference to them in the DB...storing not data in the DB and getting rid of all your possible problems. Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -Original Message- From: Cabbar Duzayak [mailto:[EMAIL PROTECTED] Sent: Saturday, July 09, 2005 12:01 AM To: mysql@lists.mysql.com Subject: Storing huge amount of binary data Hi Everyone, I will be starting a project for which I will need to store about 1 million image files all of which are about 50-100K in size. I will be using Linux for this project. The database won't be very busy, there will be batch image uploads 1-2 times a day, and around 100-200 concurrent users at most, most of which will be reading from the db and writing only session information type of data, etc... And, I don't really need transaction support (InnoDB)... Adding this up, the image data size will be around 50-100 Gb, and I will need to store a total of 1-2 Gb text information (1K for each image) along with each of these images... First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? 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]
Some query help
Hi Again - I need some more help with a query. I have a list of numbers (bandwidth required)... 2200, 2200, 2200, 400, 320 My data looks like this: Bandwidth | Distance 2250 | 10km 1125 | 10km 622 | 10km 2250 | 20km 1125 | 20km 622 | 20km 2250 | 40km 1125 | 40km 622 | 40km I need the query to look at the list of numbers, and figure out that a certain distance has Bandwidths that are greater than each of the numbers. SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND bandwidth (2200, 2200, 2200, 220) ORDER BY distance ASC So I hope you can see what I am trying to get after. Basically I need to fins a distance that can fit each of the numbers in the list. So if 2200 is in the list, 2250 works. If 400 is in the list, 622 works. Thanks for the help on this! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to SELECT something (CONCAT) and search the field
Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks for the help
Thanks for the help, I'll give some of these examples a try~!!! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.6-beta has been released
' table). (Bug #9492) * `SET @var= CAST(NULL AS [INTEGER|CHAR])' now sets the result type of the variable to `INTEGER'/`CHAR'. (Bug #6598) * Incorrect results were returned for queries of the form `SELECT ... LEFT JOIN ... WHERE EXISTS (SUBQUERY)', where the subquery selected rows based on an `IS NULL' condition. (Bug #9516) * Executing `LOCK TABLES' and then calling a stored procedure caused an error and resulting in the server thinking that no stored procedures exist. (Bug #9566) * Selecting from a view containing a subquery caused the server to hang. (Bug #8490) * Within a stored procedure, attempting to execute a multiple-table `UPDATE' failed with a `Table 'TBL_NAME' was locked with a READ lock and can't be updated' error. (Bug #9486) * Starting `mysqld' with the `--skip-innodb' and `--default-storage-engine=innodb' (or `--default-table-type=innodb' caused a server crash. (Bug #9815) * Queries containing `CURRENT_USER()' incorrectly were registered in the query cache. (Bug #9796) * Setting the `storage_engine' system variable to `MEMORY' succeeded, but retrieving the variable resulted in a value of `HEAP' (the old name for the `MEMORY' storage engine) rather than `MEMORY'. (Bug #10039) * `mysqlshow' displayed an incorrect row count for tables. (Bug #9391) * The server died with signal 11 if a non-existent location was specified for the location of the binary log. Now the server exits after printing an appropriate error messsage. (Bug #9542) * Fixed a problem in the client/server protocol where the server closed the connection before sending the final error message. The problem could show up as a `Lost connection to MySQL server during query' when attempting to connect to access a non-existent database. (Bug #6387, Bug #9455) * Fixed a `readline'-related crash in `mysql' when the user pressed Control-R. (Bug #9568) * For stored functions that should return a `YEAR' value, corrected a failure of the value to be in `YEAR' format. (Bug #8861) * Fixed a server crash resulting from invocation of a stored function that returned a value having an `ENUM' or `SET' data type. (Bug #9775) * Fixed a server crash resulting from invocation of a stored function that returned a value having a `BLOB' data type. (Bug #9102) * Fixed a server crash resulting from invocation of a stored function that returned a value having a `BIT' data type. (Bug #7648) * `TIMEDIFF()' with a negative time first argument and postive time second argument produced incorrect results. (Bug #8068) * Fixed a problem with `OPTIMIZE TABLE' for `InnoDB' tables being written twice to the binary log. (Bug #9149) * `InnoDB': Prevent `ALTER TABLE' from changing the storage engine if there are foreign key constraints on the table. (Bug #5574, Bug #5670) * `InnoDB': Fixed a bug where next-key locking doesn't allow the insert which does not produce a phantom. (Bug #9354) If the range is of type `'a' = uniquecolumn', `InnoDB' lock only the RECORD, if the record with the column value `'a'' exists in a CLUSTERED index. This allows inserts before a range. * `InnoDB': When `FOREIGN_KEY_CHECKS=0', `ALTER TABLE' and `RENAME TABLE' will ignore any type incompatibilities between referencing and referenced columns. Thus, it will be possible to convert the character sets of columns that participate in a foreign key. Be sure to convert all tables before modifying any data! (Bug #9802) * Provide more informative error messages in clustered setting when a query is issued against a table that has been modified by another `mysqld' server. (Bug #6762) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repairing/Restoring a Database
While updating a record in a database, I inadvertantly forgot a where statement; so instead of changing just one record, I changed all 900 records in the database. I've been trying to figure out how to fix this. My latest backup (through mysqldump) was about fifteen days ago. I'm fine with restoring the database to that point, and updating the rest by hand, but I'm having problems doing so. Some tutorials that I've read (such as this one http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/2/ ) say that I should do the following: ___ /usr/local/mysql/bin/mysql -u [username] -p [password] [database_to_restore] [backupfile] ___ But when I execute that command from the shell (starting from the directory in which the backup exists), I get a long message like this: /usr/local/mysql/bin/mysql Ver 12.22 Distrib 4.0.21, for pc-linux (i686) Copyright (C) 2002 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: /usr/local/mysql/bin/mysql [OPTIONS] [database] -?, --help Display this help and exit. --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. etc. etc. What am I doing wrong? And is there an easier way to do this? Thanks in advance for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing/Restoring a Database
One more note for anyone else who runs into this problem: before running the source command, I dropped the database, recreated it, and then ran the mysqldump file into the empty database. Matt On 5/25/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can try doing the same steps, but break it down so that you do them one at a time. FIRST: open the MySQL CLI and attach to the server hosting the database you want to restore: (in a command shell) mysql -h [name or IP address of server] -u [your mysql account] -p Respond with your password when you are prompted. You should now be inside a MySQL client session. SECOND: make your destination database the active database for your session: USE [database name]; Third: process your MySQL dump file: source [fully-qualified path to dump file]; This is the slower, manual way of doing what your command line did all at once. One other idea, on Win32 machines there can be NO SPACE between the -p and the actual password (should you opt to put it in the command). You might try reformatting your command to remove that space. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing/Restoring a Database
Thank you *SO* much, Shawn. Doing it manually did the trick. I'm sure you know how frustrating this can be. I really, really appreciate your help. Whew! best, Matt On 5/25/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can try doing the same steps, but break it down so that you do them one at a time. FIRST: open the MySQL CLI and attach to the server hosting the database you want to restore: (in a command shell) mysql -h [name or IP address of server] -u [your mysql account] -p Respond with your password when you are prompted. You should now be inside a MySQL client session. SECOND: make your destination database the active database for your session: USE [database name]; Third: process your MySQL dump file: source [fully-qualified path to dump file]; This is the slower, manual way of doing what your command line did all at once. One other idea, on Win32 machines there can be NO SPACE between the -p and the actual password (should you opt to put it in the command). You might try reformatting your command to remove that space. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.12 has been released
On 5/16/05 12:57 PM, Dan Bolser [EMAIL PROTECTED] wrote: A new version of MySQL Community Edition 4.1.12 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. How long is it before the Dec OSF downloads are updated usually? Dan, We had to skip Dec OSF for this release due to some unresolved build problems. Sorry. Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.12 has been released
On 5/16/05 1:04 PM, Jan Pieter Kunst [EMAIL PROTECTED] wrote: A new version of MySQL Community Edition 4.1.12 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. The link to the Mac OS X 10.3 version is out of date. It links to 4.1.11. 4.1.12 is downloadable if this: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.11-apple-darw in7.8.0-powerpc.tar.gz/from/pick is manually changed to: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.12-apple-darw in7.9.0-powerpc.tar.gz/from/pick Jan, Yes, sorry about that. The latest update for OS X 10.3 changed the kernel version number again. A request is already in to our web team to update the download page, should be coming up shortly. Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.12 has been released
for an ARCHIVE table failed if the `.arn' file was not present. (Bug #9911) * Fixed an optimizer problem where extraneous comparisons between NULL values in indexed columns were being done for operators such as = that are never true for NULL. (Bug #8877) * SELECT ROUND(expr) produced a different result than CREATE TABLE ... SELECT ROUND(expr). (Bug #9837) * Fixed some awk scriptportabilityproblems in cmd-line-utils/libedit/makelist.sh. (Bug #9954) * Changed metadata for result of SHOW KEYS: Data type for Sub_part column now is SMALLINT rather than TINYINT because key part length can be longer than 255. (Bug #9439) * Fixed some problems with myisampack on 64-bit systems that resulted in segmentation violations. (Bug #9487) * Fixed an optimizer bug in computing the union of two ranges for the OR operator. (Bug #9348) * Fixed an index corruption problem for MyISAM tables that resulted from the 4.1 behavior of padding values with blanks for comparison: Dumping a table with mysqldump, reloading it, and then re-running the binary log against it crashed the index and necessitated a repair. (Bug #9188) * Fixed a segmentation fault in mysqlcheck that occurred when the last table checked in --auto-repair mode returned an error (such as the table being a MERGE table). (Bug #9492) * Fixed the client/server protocol for prepared statements so that reconnection works properly when the connection is killed while reconnect is enabled. (Bug #8866) * INSERT ... ON DUPLICATE KEY UPDATE incorrectly updated a TIMESTAMP column to the current timestamp, even if the update list included col_name = col_name for that column to prevent the update. (Bug #7806) * Starting mysqld with the --skip-innodb and --default-storage-engine=innodb (or --default-table-type=innodb caused a server crash. (Bug #9815) * Queries containing CURRENT_USER() incorrectly were registered in the query cache. (Bug #9796) * A server installed as a Windows service and started with --shared-memory could not be stopped. (Bug #9665) * mysqldump dumped core when invoked with --tmp and --single-transaction options and a non-existent table name. (Bug #9175) * Additional fix for mysql_server_init() and mysql_server_end() C API functions so that stopping and restarting the embedded server will not cause a crash. (Bug #7344) * mysql.server no longer uses non-portable alias command or LSB functions. (Bug #9852) * Fixed a readline-related crash in mysql when the user pressed Control-R. (Bug #9568) * TIMEDIFF() with a negative time first argument and postive time second argument produced incorrect results. (Bug #8068) * Fixed a bug that caused concurrent inserts to be allowed into the tables in the SELECT ... UNION ... part of INSERT ... SELECT ... UNION This could result in the incorrect order of queries in the binary log. (Bug #9922) * Warning message from GROUP_CONCAT() did not always indicate correct number of lines. (Bug #8681) * InnoDB: ENUM and SET columns were treated incorrectly as character strings. This bug did not manifest itself with latin1 collations, but it caused malfunction with utf8. Old tables will continue to work. In new tables, ENUM and SET will be internally stored as unsigned integers. (Bug #9526) * InnoDB: Avoid test suite failures caused by a locking conflict between two server instances at server shutdown/startup. This conflict on advisory locks appears to be the result of a bug in the operating system; these locks should be released when the files are closed, but somehow that does not always happen immediately in Linux. (Bug #9381) * InnoDB: Prevent ALTER TABLE from changing the storage engine if there are foreign key constraints on the table. (Bug #5574, Bug #5670) * InnoDB: Fixed a deadlock without any locking, simple select and update. (Bug #7975) InnoDB now takes an exclusive lock when INSERT ON DUPLICATE KEY UPDATE is checking duplicate keys. * Creating a table did not work for a cluster with 6 nodes. (Bug #8928) Databases with 1, 2, 4, 8, ... (2^n nodes) did not have the problem. After a rolling upgrade, restart each node manually by restarting it with the --initial option. Otherwise, use dump and restore after an upgrade. * Fix undersized array in my_print_defaults that caused crash on Sun Solaris (and maybe strange results on other platforms). (Bug #9136) * Fix for auto-increment not working with INSERT..SELECT and NDB storage engine. (Bug #9675) Enjoy! Matt -- Matt
RE: Field property question!
Yes I do enjoy every day in which the manual makes my life easier. I guess I
didn't work my question specifically enough however. I was actually looking
for a way to 'ALTER' the table so that my State field data is always upper.
In MSSQL you can apply a function to a field, which will then run that
function over the data each time something new gets added. For instance you
could put in Now() in the default, and the default date would appear if you
added a row That's the idea.
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 10, 2005 10:58 AM
To: Matt Babineau; mysql@lists.mysql.com
Subject: RE: Field property question!
[snip]
Can I setup a table so that no matter how data is entered into it (web
form,
command line) The data in one of the columns ALWAYS gets converted to
uppercase? I remeber MSSQL had this feature of being able to apply a
function to a field in its configuration.
[/snip]
The manual, it is amazing no?
http://dev.mysql.com/doc/mysql/en/string-functions.html
UPPER()
INSERT INTO `table` (`colFoo`)
VALUES (UPPER('myData'));
--
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]
Field property question!
Can I setup a table so that no matter how data is entered into it (web form, command line) The data in one of the columns ALWAYS gets converted to uppercase? I remeber MSSQL had this feature of being able to apply a function to a field in its configuration. Thanks! Matt
MySQL 4.1.11 has been released
reset hourly and thus imposed an absolute limit on number of connections per account until the server is restarted or the limits flushed. (Bug #8350) * With a database was dropped with lower_case_table_names=2, tables in the database also were dropped but not being flushed properly from the table cache. If the database was recreated, the tables also would appear to have been recreated. (Bug #8355) * Changed mysql_server_end() C API function to restore more variables to their initial state so that a subsequent call to mysql_server_init() would not cause a client program crash. (Bug #7344) * Fixed a problem with accented letters improperly being treated as distinct with the utf_general_ci collation. (Bug #7878) * ENUM and SET columns in privilege tables incorrectly had a case-sensitive collation, resulting in failure of assignments of values that did not have the same lettercase as given in the column definitions. The collation was changed to be case insensitive. (Bug #7617) * An expression that tested a case-insensitive character column against string constants that differed in lettercase could fail because the constants were treated as having a binary collation. (For example, WHERE city='London' AND city='london' could fail.) (Bug #7098, Bug #8690) * The output of the STATUS (\s) command in mysql had the values for the server and client character sets reversed. (Bug #7571) * If the slave was running with --replicate-*-table options which excluded one temporary table and included another, and the two tables were used in a single DROP TEMPORARY TABLE IF EXISTS statement, as the ones the master automatically writes to its binary log upon client's disconnection when client has not explicitly dropped these, the slave could forget to delete the included replicated temporary table. Only the slave needs to be upgraded. (Bug #8055) * Treat user variables as having IMPLICIT derivation (coercibility) to avoid ``Illegal mix of collations'' errors when replicating user variables. (Bug #6676) * When setting integer system variables to a negative value with SET VARIABLES, the value was treated as a positive value modulo 2^32. (Bug #6958) * Fixed a bug in bundled readline library that caused segmentation fault in mysql when user entered Shift+Enter. (Bug #5672) * Fix conversion of strings - double to get higher accuracy for floating point values that are integers, like: 123.45E+02 (Bug #7840). * Fixed a bug in MATCH ... AGAINST in natural language mode that could cause a server crash if the FULLTEXT index was not used in a join (EXPLAIN did not show fulltext join mode) and the search query matched no rows in the table (Bug #8522). * Platform and architecture information in version information produced for --version option on Windows was always Win95/Win98 (i32). More accurately determine platform as Win32 or Win64 for 32-bit or 64-bit Windows, and architecture as ia32 for x86, ia64 for Itanium, and axp for Alpha. (Bug #4445) * Fixed a rare race condition which could lead to FLUSH TABLES WITH READ LOCK hanging. (Bug #8682) * Fixed a bug that caused the slave to stop on statements that produced an error on the master. (Bug #8412) * If multiple semicolon-separated statements were received in a single packet, they were written to the binary log as a single event rather than as separate per-statement events. For a server serving as a replication master, this caused replication to fail when the event was sent to slave servers. (Bug #8436) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: problem with mysql-max-5.0.3 for Solaris 8 32 bit
It would appear that the package is for the 64-bit OS, even though it is listed as being for the 32-bit one. I see directories, that failed to install, of: /usr/local/mysql-max-5.0.3-beta-sun-solaris2.8-sparc-64bit/sql-bench I had gotten my file from http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.3-beta-sun-solaris2 .8-sparc.pkg.gz/from/http://mysql.mirrors.pair.com/ James, Sorry about this. Somehow the 64bit tarball was used for this package. I've fixed it now and uploaded a new 32bit Solaris 2.8 PKG. Note that it will take a bit to propagate out to the mirrors (probably 8-10 hrs). Thanks for notifying us about this. Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.3-beta has been released
is possible when using --binlog-do-db and --binlog-ignore-db. (Bug #6391) * A sequence of BEGIN (or SET AUTOCOMMIT=0), FLUSH TABLES WITH READ LOCK, transactional update, COMMIT, FLUSH TABLES WITH READ LOCK could hang the connection forever and possibly the MySQL server itself. This happened for example when running the innobackup script several times. (Bug #6732) * mysqlbinlog did not print SET PSEUDO_THREAD_ID statements in front of LOAD DATA INFILE statements inserting into temporary tables, thus causing potential problems when rolling forward these statements after restoring a backup. (Bug #6671) * InnoDB: Fixed a bug no error message for ALTER with InnoDB and AUTO_INCREMENT(Bug #7061). InnoDB now supports ALTER TABLE...AUTO_INCREMENT = x query to set auto increment value for a table. * Made the MySQL server accept executing SHOW CREATE DATABASE even if the connection has an open transaction or locked tables; refusing it made mysqldump --single-transaction sometimes fail to print a complete CREATE DATABASE statement for some dumped databases. (Bug #7358) * Fixed that, when encountering a ``disk full'' or ``quota exceeded'' write error, MyISAM sometimes didn't sleep and retry the write, thus resulting in a corrupted table. (Bug #7714) * Fixed that --expire-log-days was not honored if using only transactions. (Bug #7236) * Fixed that a slave could crash after replicating many ANALYZE TABLE, OPTIMIZE TABLE, or REPAIR TABLE statements from the master. (Bug #6461, Bug #7658) * mysqlbinlog forgot to add backquotes around the collation of user variables (causing later parsing problems as BINARY is a reserved word). (Bug #7793) * Ensured that mysqldump --single-transaction sets its transaction isolation level to REPEATABLE READ before proceeding (otherwise if the MySQL server was configured to run with a default isolation level lower than REPEATABLE READ it could give an inconsistent dump). (Bug #7850) * Fixed that when using the RPAD() function (or any function adding spaces to the right) in a query that had to be resolved by using a temporary table, all resulting strings had rightmost spaces removed (i.e. RPAD() did not work) (Bug #4048) * Fixed that a 5.0.3 slave can connect to a master 3.23.50 without hanging (the reason for the hang is a bug in these quite old masters -- SELECT @@unknown_var hangs them -- which was fixed in MySQL 3.23.50). (Bug #7965) * InnoDB: Fixed a deadlock without any locking, simple select and update (Bug #7975). InnoDB now takes an exclusive lock when INSERT ON DUPLICATE KEY UPDATE is checking duplicate keys. * Fixed a bug where MySQL was allowing concurrent updates (inserts, deletes) to a table if binary logging is enabled. Changed to ensure that all updates are executed in a serialized fashion, because they are executed serialized when binlog is replayed. (Bug #7879) * Fixed a rare race condition which could lead to FLUSH TABLES WITH READ LOCK hanging. (Bug #8682) * Fixed a bug that caused the slave to stop on statements that produced an error on the master. (Bug #8412) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using between
Have you considered just doing a parse on the the IP ranges and having 8 columns in your database, then write your query to work inside the 8 columns Cols = from_zone, from_net, from_subnet, from_node, to_zone, to_net, to_subnet, to_node Then just parse the ip you are looking up and write your query that way. MySQL should beable to reduce the amount of rows it needs to look at pretty quickly this way. Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:05 PM To: 'mos'; 'MySQL list' Subject: RE: using between Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- 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: How to Version Control a database
The method employed by the company I work for is to maintain a version controlled set of scripts, which can be used to create/update a database (many of these scripts are objects such as UDFs, Stored Procedures and so forth, which represent the 'software' layer within the database). A bespoke tool is used to apply these scripts in an appropriate order to a blank (new install) database, or an existing (upgrade) database - in the latter case extra 'update' scripts are used to perform necessary schema adjustments, etc. It depends really on what sort of work is being done - if it is mostly developmental/code/objects, then the above method seems to work well - if the work is mostly represented in data, then it won't really apply! Cheers, Matt -Original Message- From: Will Merrell [mailto:[EMAIL PROTECTED] Sent: 20 February 2005 14:00 To: Mysql Mailing list Subject: How to Version Control a database I have a project that involves several developers working on their own machines. Each has a local copy of the database on their own machine. Since we have some developers who develop while not connected to the network, we cannot use a common database. How can I version control the database so that changes are not lost or stepped on. Right now, we use mysqldump to dump the database and version the dump file, but this still has problems. Is there a better way? -- Will -- 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 4.1.9 - Requesting Optimization Tips
I'm wondering if anyone can provide any tips as to how to conserve resources. Currently, I see 10 instances of mysqld running. Each instance is approximately 18MB. For my application of MySQL, I don't require a large amount of resources allocated to mysqld. 7085 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.05 mysqld 7086 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7087 mysql 20 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7088 mysql 24 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7089 mysql 24 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7090 mysql 20 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7091 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7092 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7093 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7094 mysql 15 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld I found some information on mysql.com. I basically reduced some of the startup options by half. key_buffer_size=32M back_log=25 table_cache=32 net_buffer_length=1M max_allowed_packet=3M query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 read_buffer_size=2M read_rnd_buffer_size=8M -- Regards, Matt Florido -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.9 - Requesting Optimization Tips
Paul DuBois wrote: At 19:21 -0800 2/17/05, Matt Florido wrote: I'm wondering if anyone can provide any tips as to how to conserve resources. Currently, I see 10 instances of mysqld running. Each instance is approximately 18MB. For my application of MySQL, I don't require a large amount of resources allocated to mysqld. If you're running Linux, there's nothing to optimize here. These are threads of the same process, not 10 different processes. Paul, thanks for the fast response. Ah...so this is ignorance on my part. You're saying mysqld is not running separate processes even though Top reports separate PID. Instead of each thread consuming appx. 18MB, the entire process is consuming just 18MB? -- Regards, Matt Florido -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with SELECT SQL_CALC_FOUND_ROWS
Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok here is the code chunk: $rows = is_numeric($_GET['rows']) ? $_GET['rows'] : 5; $stRow = 0; // SEARCH CODE $sql = SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = '$state' limit $stRow, $rows; $search = mysql_query($sql); echo $sql; $sql = SELECT FOUND_ROWS(); $ctTotalResults = mysql_fetch_row(mysql_query($sql)); It errors out on the first $search = mysql_query(); statement. Matt Babineau Criticalcode w: http://www.criticalcode.com http://www.criticalcode.com/ p: 858.733.0160 e: [EMAIL PROTECTED] _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:25 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt Babineau [EMAIL PROTECTED] wrote on 02/01/2005 03:20:49 PM: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] I am by not a PHP expert but it seems that your error message is coming from PHP and not from MySQL (based on the name of the function in the message). Please post the code that surrounds this statement and try to determine and indicate which line is throwing the error, please. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This is very strange behavior! Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Matt Babineau [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:52 PM To: 'Michael Dykman' Cc: 'MySQL General' Subject: RE: Problem with SELECT SQL_CALC_FOUND_ROWS Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [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: Problem with SELECT SQL_CALC_FOUND_ROWS
I do see that the bug was closed over 2 years ago - I guess then it was a persistent problem, but now it is limited to the configuration of the MySQL Server. As I have found out today, Quadrupling the stock memory limits on the MySQL Server solved the problem...not sure what will happen when the database starts growing but, my query is solid, and I am only returning a few rows at a time, so hopefully over a couple hundred queries wont make this thing die! :) Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:55 PM To: Michael Dykman Cc: Matt Babineau; 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still unresolved? Michael Michael Dykman wrote: Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [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]
MySQL + PHP - Search Engine question!
Hi All - I'm building a search engine and what I would like to do is run a search and get the number of results, but still use the LIMIT command so I am not returning a ton of rows all at once. Is this the best way to go about searching? Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
Re: Mysql + PHP - Search
I just answered my own question actually! - snip from php.net - MySQL 4.0 supports a fabulous new feature that allows you to get the number of rows that would have been returned if the query did not have a LIMIT clause. To use it, you need to add SQL_CALC_FOUND_ROWS to the query, e.g. $sql = Select SQL_CALC_FOUND_ROWS * from table where state='CA' limit 50; $result = mysql_query($sql); $sql = Select FOUND_ROWS(); $count_result = mysql_query($sql); You now have the total number of rows in table that match the criteria. This is great for knowing the total number of records when browsing through a list. -- Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
MySQL 4.1.9 has been released
) * Include compression library flags in the output from mysql_config --lib_r. (Bug #7021) * Corrected a problem with mysql_config not producing all relevant flags from CFLAGS. (Bug #6964) * Corrected a problem with mysqld_safe not properly capturing output from ps. (Bug #5878) * Fixed a bug that caused a linking failure when linking both the MySQL client library and IMAP library. (Bug #7428) * Fixed a bug that caused microseconds to be gobbled from the string result of the STR_TO_DATE function, if there is some other specifier in the format string following %f. (Bugs #7458) * Made the MySQL server accept executing SHOW CREATE DATABASE even if the connection has an open transaction or locked tables. Refusing it made mysqldump --single-transaction sometimes fail to print a complete CREATE DATABASE statement for some dumped databases. (Bug #7358) * Fixed that, when encountering a ``disk full'' or ``quota exceeded'' write error, MyISAM sometimes didn't sleep and retry the write, thus resulting in a corrupted table. (Bug #7714) * Fixed that --expire-log-days was not honored if using only transactions. (Bug #7236) * Fixed that a slave could crash after replicating many ANALYZE TABLE, OPTIMIZE TABLE, or REPAIR TABLE statements from the master. (Bug #6461, Bug #7658) * ndb_restore fails to handle blobs and multiple databases (Bug #7379) * ndb_restore enters infinite loop (Bug #7346) * ndb_mgmd is aborted on startup when using SHM connection (Bug #7124) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.2-alpha has been released
) if a VIEW without presence of a key in the underlying table is used in queries with a LIMIT clause for updating. (This is the default value.) + 0 or NO: Prohibit update of a VIEW, which does not contain a key in the underlying table and the query uses a LIMIT clause (usually found in GUI tools). * Reverted output format of SHOW TABLES to old pre-5.0.1 format that did not include a table type column. To get the additional column that lists the table type, use SHOW FULL TABLES now. * The mysql_fix_privilege_tables script now initializes the global CREATE VIEW and SHOW VIEW privileges in the user table to the value of the CREATE privilege in that table. * If the server finds that the user table has not been upgraded to include the view-related privilege columns, it treats each account as having view privileges that are the same as its CREATE privilege. * CHECK TABLE now works with VIEWs (i.e. check view integrity: all underlying tables and columns are present and so on) * Added merge algorithm for join VIEW (view consists of several tables) and UPDATE/INSERT support for one of underlying tables in such VIEWs. Bugs fixed: * Fixed that mysqlbinlog --read-from-remote-server sometimes couldn't accept two binary logfiles on the command line. (Bug #4507) * Fixed that mysqlbinlog --position --read-from-remote-server had incorrect # at lines. (Bug #4506) * Fixed that CREATE TABLE ... TYPE=HEAP ... AS SELECT... caused replication slave to stop. (Bug #4971) * Fixed that mysql_options(...,MYSQL_OPT_LOCAL_INFILE,...) failed to disable LOAD DATA LOCAL INFILE. (Bug #5038) * Fixed that disable-local-infile option had no effect if client read it from a configuration file using mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug #5073) * Fixed that SET GLOBAL SYNC_BINLOG did not work on some platforms (Mac OS X). (Bug #5064) * Fixed that mysql-test-run failed on the rpl_trunc_binlog test if running test from the installed (the target of 'make install') directory. (Bug #5050) * Fixed that mysql-test-run failed on the grant_cache test when run as Unix user 'root'. (Bug #4678) * Fixed an unlikely deadlock which could happen when using KILL. (Bug #4810) * Fixed a crash when one connection got KILLed while it was doing START SLAVE. (Bug #4827) * Made FLUSH TABLES WITH READ LOCK block COMMIT if server is running with binary logging; this ensures that the binary log position can be trusted when doing a full backup of tables and the binary log. (Bug #4953) * Fixed that the counter of an auto_increment column was not reset by TRUNCATE TABLE is the table was a temporary one. (Bug #5033) * Fixed slave SQL thread so that the SET COLLATION_SERVER... statements it replicates don't advance its position (so that if it gets interrupted before the actual update query, it will later redo the SET). (Bug #5705) * Fixed that if the slave SQL thread found a syntax error in a query (which should be rare, as the master parsed it successfully), it stops. (Bug #5711) * Fixed that if a write to a MyISAM table fails because of a full disk or an exceeded disk quota, it prints a message to the error log every 10 minutes, and waits until disk becomes free. (Bug #3248) * Fixed problem introduced in 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, then COMMIT, would cause replication slaves to stop (complaining about error 1223). Bug surfaced when using the InnoDB innobackup script. (Bug #5949) * OPTIMIZE TABLE, REPAIR TABLE, and ANALYZE TABLE are now replicated without any error code in the binary log. (Bug #5551) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.2 is topping out at 1024 threads!
#bdb_max_lock = 10 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Thanks All! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 8:26 AM To: Gleb Paharenko Cc: [EMAIL PROTECTED] Subject: Re: MySQL 4.0.2 is topping out at 1024 threads! In the last episode (Nov 17), Gleb Paharenko said: Help! I can't figure out a way to stop my server from topping out at 1024 threads. This is a very strange behavoir. I have tons of legit use on my database server but I don't think the threads are dying does anyone have any suggestions for this? Similar problems are often found in lists. Usually they are solved by increasing file limits. You likely need to increase open-files-limit. If you're running Linux, you may need to recompile your linuxthreads library also: http://dev.mysql.com/doc/mysql/en/Source_notes-Linux.html -- Dan Nelson [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: MySQL 4.0.2 is topping out at 1024 threads!
Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: From: Matt Babineau [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! Date: Wed, 17 Nov 2004 11:18:04 -0800 (16:18 CLST) This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 25394 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: 63.12.130.192 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 19 hours 40 min 2 sec Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- mysql == # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 connect_timeout = 10 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections = 1024 max_user_connections = 1024 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master
RE: MySQL 4.0.2 is topping out at 1024 threads!
FIXED! Ok Thanks to Eric on this one, the wait_timeout configuration was what fixed my sleepy connection problems! Thanks ERIC! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eric Gunnett [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:37 AM To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! wait_timeout Will set the maximum amout of time a thread will be in the Sleep state before MySQL drops it. Eric Gunnett System Administrator Zoovy, Inc. [EMAIL PROTECTED] Matt Babineau [EMAIL PROTECTED] 11/17/04 11:35AM Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: From: Matt Babineau [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! Date: Wed, 17 Nov 2004 11:18:04 -0800 (16:18 CLST) This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 25394 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: 63.12.130.192 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 19 hours 40 min 2 sec Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- mysql == # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 connect_timeout = 10 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections = 1024 max_user_connections = 1024 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully
RE: Trying to dump from GUI client
I'm not sure what the answer here is but check your user's permissions on the database to make sure it has the necessary items GRANTED to it. Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 12:14 PM To: 'Adam'; 'MySQL General' Subject: RE: Trying to dump from GUI client I am trying MySQL Query Browser, but I get this error... SELECT * INTO OUTFILE 'c:\temp\candidate.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM candidate; ERROR 1045: Access denied for user: 'wowdba'@'%' (Using password: YES) ...even though I am logged in via the client. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 7:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- 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]
Mysql 4.1 and the LIMIT sql statement
Hi all- Has anyone run into problems with this sql syntax? LIMIT -1 I've used this extensively in my code to get back all records rather then specifing a limit. I've done this programmatically with PHP, so all my queries have a limit even if I don't need one, I just have it specify LIMIT -1, but apparently this functionality doesn't seem to work in 4.1??? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com
MySQL 4.0.2 is topping out at 1024 threads!
Help! I can't figure out a way to stop my server from topping out at 1024 threads. This is a very strange behavoir. I have tons of legit use on my database server but I don't think the threads are dying does anyone have any suggestions for this? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com
MySQL 4.0.22 has been released
LOCK TABLES ... LOCAL. (Workaround in 4.0.21: use --quick and --single-transaction. (Bug #5538) * Fixed that if the slave SQL thread found a syntax error in a query (which should be rare, as the master parsed it successfully), it stops. (Bug #5711) * Fixed that if a write to a MyISAM table fails because of a full disk or an exceeded disk quota, it prints a message to the error log every 10 minutes, and waits until disk becomes free. (Bug #3248) * Fixed problem with symlinked databases on Windows being shown with SHOW DATABASES even if the database name doesn't match the given wildcard (Bug #5539) Additional Notes: * Due to a temporary hardware failure the binaries for HP-UX 11.11 are missing from the initial release. They will be provided as soon as this HP-UX build system is online again. Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.7 has been released
Hi, MySQL 4.1.7, a new version of the popular Open Source/Free Software Database Management System has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the first 4.1 production release. Please refer to our bugs database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Changes in release 4.1.7 Functionality added or changed: * InnoDB: Made LOCK TABLES behave by default like it did before MySQL 4.0.20 or 4.1.2: no InnoDB lock will be taken. Added a startup option and settable system variable innodb_table_locks for making LOCK TABLE acquire also InnoDB locks. See section Restrictions on InnoDB Tables in the manual. (Bug #3299, Bug #5998) Bugs fixed: * Fixed a bug with FOUND_ROWS() used together with LIMIT clause in prepared statements. (Bug #6088) * Fixed a bug with NATURAL JOIN in prepared statements. (Bug #6046). * Fixed a bug in join of tables from different databases having columns with identical names (prepared statements). (Bug #6050) * Now implicit access to system time zone description tables (which happens when you set time_zone variable or use CONVERT_TZ() function) does not require any privileges. (Bug #6116) * Fixed a bug which caused the server to crash when the deprecated libmysqlclient function mysql_create_db() was called. (Bug #6081) * Fixed REVOKE ALL PRIVILEGES, GRANT OPTION FROM user so that all privileges are revoked correctly. (Bug #5831). This corrects a case that the fix in 4.1.6 could miss. * Fixed a bug that could cause MyISAM index corruption when key values start with character codes below BLANK. This was caused by the new key sort order in 4.1. (Bug #6151) * Fixed a bug in the prepared statements protocol when wrong metadata was sent for SELECT statements not returning a result set (such as SELECT ... INTO OUTFILE). (Bug #6059) * Fixed bug which allowed one to circumvent missing UPDATE privilege if one had INSERT and SELECT privileges for table with primary key. (Bug #6173) * Fixed a bug in libmysqlclient with wrong conversion of negative time values to strings. (Bug #6049). * Fixed a bug in libmysqlclient with wrong conversion of zero date values (-00-00) to strings. (Bug #6058) * Fixed a bug that caused the server to crash on attempt to prepare a statement with RAND(?). (Bug #5985) * Fixed a bug with handling of DATE, TIME, and DATETIME columns in the binary protocol. The problem is compiler-specific and could have been observed on HP-UX, AIX, Solaris9, when compiling with native compiler. (Bug #6025) * Fixed a bug with handling of TINYINT columns in the binary protocol. The problem is specific to platforms where the C compiler has the char data type unsigned by default. (Bug #6024) * Fixed problem introduced in MySQL 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, then COMMIT, would cause replication slaves to stop complaining about error 1223. Bug surfaced when using the InnoDB innobackup script. (Bug #5949) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.6 has been released
). Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unique key violations
Hi all... I am having a problem with unique key violations in one of my tables. This is the table structure: CREATE TABLE `optionaldata` ( `ForeignID` int(10) unsigned NOT NULL default '0', `FieldID` int(10) unsigned NOT NULL default '0', `Value` char(200) default NULL, UNIQUE KEY `CitizenID_FieldID_Value` (`ForeignID`,`FieldID`,`Value`), ) TYPE=MyISAM; I am getting quite a few rows in the table that are duplicates, ie: 1068715, 60, '[EMAIL PROTECTED]' 1068715, 60, '[EMAIL PROTECTED]' What I want to know is, should this be possible under any circumstances at all? I am using some UPDATE IGNORE calls to this table, but from what I read in the documentation, this still shouldn't happen. Any ideas? Many thanks in advance, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: matching people with projects via resources
Hi Laszlo,
This is sort of a butchery, and might be a little nicer with two queries
and a temp table, but this works in mysql 4.1.3-beta (at least, it did
for me).
SELECT A.name, B.proj
FROM people as A, project as B
WHERE A.rsrc=B.rsrc
GROUP BY A.name, B.proj
HAVING COUNT(*)=(SELECT COUNT(*) FROM project WHERE proj=B.proj);
This counts up the number of rows each (name,project) pair with
resources in common and then checks to see if it's equal to the total
number of resources for that project. This would be pretty slow on a
really huge table (in the tens of thousands, maybe? I don't have a
great sense for how it would scale), in which case you'd want to make a
temporary table with all of the resource counts cached per project.
Hope that helps,
Matt
Laszlo Thoth wrote:
I'm having difficulty constructing a query. I've got two kinds of information:
a table of resources that various people have, and a table of resources that
various projects need.
===
CREATE TABLE `people` (
`name` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('davinci','paint');
CREATE TABLE `project` (
`proj` varchar(11) default NULL,
`rsrc` varchar(15) default NULL
);
INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),('jeans','sewingmachine');
===
I need a query that will tell me which people have the resources required to
complete a given project. Unfortunately all I can get are incomplete matches:
I'm not sure how to express the concept of fully satisfying the requirements
to MySQL.
Restructuring the tables is allowed: I'm not tied to the current schema, I just
need to solve the problem. The only limit is that resources must be arbitrary:
I can't use a SET to define resources because I might want to insert a new
resource at some future point without redefining the column type.
I'm pretty sure this is a good starting point, but that's just matching resource
to resource without excluding Lincoln from building an Ark (no canvas).
mysql SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark | wood| noah|
| ark | wood| lincoln |
| ark | canvas | noah|
| ark | canvas | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas | noah|
| monalisa | canvas | davinci |
| monalisa | paint | davinci |
| jeans| canvas | noah|
| jeans| canvas | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+
It would also be sufficient but less optimal to solve a subset of this problem,
where I only determine who could complete this project for a single project
rather than trying to match all projects to all people in one query.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.21 has been released
`mem_realloc()', whose implementation was incorrect. As a result, InnoDB can incorrectly parse column and table names as the empty string. The InnoDB `realloc()' implementation has been corrected in MySQL/InnoDB-4.0.21. * Fixed a glitch introduced in 4.0.18 and 4.1.2: in `SHOW TABLE STATUS' InnoDB systematically overestimated the row count by 1 if the table fit on a single 16 kB data page. * InnoDB created temporary files with the C library function `tmpfile()'. On Windows, the files would be created in the root directory of the current file system. To correct this behavior, the invocations of `tmpfile()' were replaced with code that uses the function `create_temp_file()' in the MySQL portability layer. (Bug #3998) Enjoy! Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave I/O thread dies, fatal error 1236
Remigiusz Sokoowski wrote: matt ryan wrote: Tobias Asplund wrote: On Tue, 7 Sep 2004, matt ryan wrote: I forgot, did you have multiple slaves on multiple machines? If so, do they have identical hardware/drivers? Multiple slaves on same machine, one works fine Do You tried to distribute replication to other machines? Is it option to You? Remigiusz I will setup a separate server as a slave and see if it works, that would narrow it down to the master being the problem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
I use foxpro to do similar loops I've found that I get 10 queries per second on large tables, when connecting once, and issuing individual select statements via odbc. It is much faster if you can narrow the recordset into an array within php, and spool through that, unfortunatly I deal with 250+ million rows, so hitting the table by a index one at a time is faster Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave I/O thread dies, fatal error 1236
Still have not got this fixed, I'm all out of idea's, the slave has been reloaded again today gerald_clark wrote: We have no idea what you are running, or what you are running it on. matt ryan wrote: 040901 18:36:21 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040901 18:36:21 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040901 18:36:21 Slave I/O thread exiting, read up to log 'FINANCE-bin.186', position 7517914 2 slaves on one box both masters have identical config one slave will not stay running for more than an hour, it seems that it will run until it catches up with the master, then it dies. Any sugestions? All servers have plenty of free drive space Oops sorry windows 2000, mysql 4.0.20d here's the config on the affected server, it is identical to the server that works fine, except for the port number and base/data dir's it's interesting to note, that if I wait a few minutes after the thread dies, and issue start slave it'll usually start right up and run until it's caught up to the master server again. Matt skip-locking set-variable= key_buffer_size=1500M set-variable=join_buffer_size=512M set-variable= max_allowed_packet=300M set-variable= table_cache=512 set-variable=delay_key_write=ALL set-variable= sort_buffer_size=256M set-variable= record_buffer=300M set-variable= thread_cache=8 set-variable=myisam_sort_buffer_size=256M myisam-recover=BACKUP,FORCE set-variable=read_buffer_size=300M set-variable=interactive_timeout=7200 set-variable=wait_timeout=7200 set-variable=thread_concurrency=4 server-id=5 #log-bin master-host=192.168.1.168 master-port=3306 master-user=repl master-password=Daredevil22 master-connect-retry=60 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave I/O thread dies, fatal error 1236
Tobias Asplund wrote: On Tue, 7 Sep 2004, matt ryan wrote: I forgot, did you have multiple slaves on multiple machines? If so, do they have identical hardware/drivers? Multiple slaves on same machine, one works fine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave I/O thread dies, fatal error 1236
040901 18:36:21 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040901 18:36:21 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040901 18:36:21 Slave I/O thread exiting, read up to log 'FINANCE-bin.186', position 7517914 2 slaves on one box both masters have identical config one slave will not stay running for more than an hour, it seems that it will run until it catches up with the master, then it dies. Any sugestions? All servers have plenty of free drive space -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave I/O thread dies, fatal error 1236
gerald_clark wrote: We have no idea what you are running, or what you are running it on. matt ryan wrote: 040901 18:36:21 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040901 18:36:21 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040901 18:36:21 Slave I/O thread exiting, read up to log 'FINANCE-bin.186', position 7517914 2 slaves on one box both masters have identical config one slave will not stay running for more than an hour, it seems that it will run until it catches up with the master, then it dies. Any sugestions? All servers have plenty of free drive space Oops sorry windows 2000, mysql 4.0.20d here's the config on the affected server, it is identical to the server that works fine, except for the port number and base/data dir's it's interesting to note, that if I wait a few minutes after the thread dies, and issue start slave it'll usually start right up and run until it's caught up to the master server again. Matt skip-locking set-variable= key_buffer_size=1500M set-variable=join_buffer_size=512M set-variable= max_allowed_packet=300M set-variable= table_cache=512 set-variable=delay_key_write=ALL set-variable= sort_buffer_size=256M set-variable= record_buffer=300M set-variable= thread_cache=8 set-variable=myisam_sort_buffer_size=256M myisam-recover=BACKUP,FORCE set-variable=read_buffer_size=300M set-variable=interactive_timeout=7200 set-variable=wait_timeout=7200 set-variable=thread_concurrency=4 server-id=5 #log-bin master-host=192.168.1.168 master-port=3306 master-user=repl master-password=Daredevil22 master-connect-retry=60 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Mikhail Entaltsev wrote: You _could_ try adding an identical primary key to the stat_in table as you have on the 321st_stat table. However, since we need all of the rows from stat_in in the results, I am not sure that it will help speed up the join (because it's a left join). Even though I think the index would be ignored, it is worth a try to see if it would make a difference with a new EXPLAIN. Tried this, setup a matching index on the temp table, it took 17 hours to load the input file into the temp stat_in table, so it's definitly not going to save me any time I've used enable/disable keys before, but 2 problems, one it was only 5 % faster, and two, I will have primary key violations when I enable the primary key, it wont enable it, at least that's my understanding of it. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cross server selects?
Hi all. Just a quick syntax question. Is there a way to select rows from a different server database into the one currently in use? In other words, if I had two servers, is there something equivalent to saying (while using the client on server1): SELECT * FROM server2.databasename.tablename WHERE id 5 I couldn't find anything the manual on this. Thanks! -Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Mikhail Entaltsev wrote: Hi, insert into 321st_stat select * from stat_in group by primary key fields from 321st_stat table; did you try to use this query? Best regards, Mikhail. Ran it, it took at least 24 hours, it finished but never gave me the total time, when I checked the server mysql dropped me back to the command prompt, with no time or number of records :( Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Mikhail Entaltsev wrote: Could you execute show create table 321st_stat and show create table stat_in and send results back? I have no key's on the temp table, stat_in, do you think adding keys on the whole primary key would be faster? I wasnt sure if you could join mysql keys, the key is called primary key so would it be a.primary key = b.primary key ? mysql explain select a.* from stat_in a left outer join 321st_stat b on a.don=b.don and a.dic=b.dic and a.niin=b.niin and a.sta=b.sta and a.qty=b.qty and a.fr_ric=b.fr_ric and a.suf=b.suf and a.dte_txn =b.dte_txn where isnull(b.don); | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | | 1 | SIMPLE | a | ALL| NULL | NULL| NULL | NULL| 77269086 | | | 1 | SIMPLE | b | eq_ref | PRIMARY,don,niin,dic,dte_txn | PRIMARY |39 | finlog.a.dic,finlog.a.niin,finlog.a.fr_ric,finlog.a.don,finlog.a.suf,finlog.a.dte_txn,finlog.a.sta | 1 | Using where; Not exists | 2 rows in set (0.11 sec) ---+ | 321st_stat | CREATE TABLE `321st_stat` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '', PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), KEY `don` (`don`), KEY `niin` (`niin`), KEY `stor` (`stor`), KEY `dic` (`dic`), KEY `ctasc` (`ctasc`), KEY `dte_txn` (`dte_txn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=900,000,000 PACK_KEYS=1 | 1 row in set (0.03 sec) | stat_in | CREATE TABLE `stat_in` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=99,999,999 PACK_KEYS=1 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for a query
Hi Claire, On Mon, 23 Aug 2004 14:52:29 -0700 (PDT), Claire Lee [EMAIL PROTECTED] wrote: Hi, Here's a table of mine namedate changeDate n1d1 cd1 n2d1 cd3 n2d2 cd1 n4d1 cd2 n1d2 cd5 n5d1 cd4 n6d2 cd2 I need to select every name for which the changeDate corresponding to d1 is greater than the changeDate corresponding to d2. Any way I can use one statement to do this? Yes, of course. You seem to be suggesting that there will only be two records with the same name in the table. In that case, something like this (although this is untested): select name from mytable a where changedate (select changedate from mytable b where a.name=b.name and a.changedate != b.changedate); or: select name from mytable a where exists (select * from mytable b where a.name=b.name and a.changedate b.changedate); I personally like the latter, form-wise. I suspect it might be marginally faster, too. If my above assumption isn't the case, we need more information on what happens when there are 3 records (do you want any record which has changedate greater than another record of the same name? or only the highest? etc.). Also, mind your NULLs. You might need to edit the above query depending on how you want to handle NULLs in changedate. Good luck, -- Matt Warden Miami University Oxford, OH http://mattwarden.com This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1 day 28 min insert
I think oracle parallel query is calling me 110,832,565 stat records 77,269,086 on weekly update, I get small daily files, but daily sql's dont work very well, and miss records, in this case it missed 563 records. mysql update stat_in set ctasc='321ST'; Query OK, 77269086 rows affected (24 min 17.60 sec) Rows matched: 77269086 Changed: 77269086 Warnings: 0 mysql insert ignore into 321st_stat select * from stat_in; Query OK, 563 rows affected (1 day 28 min 35.95 sec) Records: 77269086 Duplicates: 77268523 Warnings: 0 I just cant deal with speeds this slow, an insert onto a table with a primary key that tosses out almost all records shouldnt take this long to do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
One alternative, since you know you don't have many records to insert, is to pull the IDs of the missing records and insert just those. Do an outer join on the two tables (joining on the primary key), get a list of the IDs of records in stat_in but not in 321st_stat, and add a WHERE id IN (list,of,ids) clause to the end of your INSERT ... SELECT statement. If you're running 4.1, you can use a subquery and embed the first query directly in the INSERT. Running 4.0.x something like, select a.*, b.* from a left outer join b on a.col1=b.col1, a.col2=b.col2, a.col3=b.col3 where b.col1 is null into temptable then insert from temptable into table a I think I tried this once, but it ran all day Is there a way to join on an index, instead of on each column? The primary key is 6-8 columns I forget -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Andrew Pattison wrote: I'm guessing that you have indexes on the 321st_stat table? If this is the case, try dropping them before you do the insert, then rebuilding them. MySQL is known to be slow at doing bulk inserts on indexed tables. Also, updates are much faster than inserts since with inserts there are much more disk IOs required. Cheers Table has a large primary key, to keep duplicates out, so I cant drop the index and remove. Also, reindexing the table takes all day, dropping is not an option Would I be better off doing an insert replace on all 17m new records, or only inserting the 500 new records using insert ignore ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
The table is 9 gig, and the index is 8 gig unfortunately the primary unique key is almost every column, if I were to make it one using concat, it would be huge. I tried making those fields a hash, but it did not work, I had duplicate hashes for non duplicate records!! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help, slave wont stay running!
Still no solution on this anybody have any ideas? It's not network, or hard drive, it's got to be some type of bug in my config files, attached in original email The master is on 4.0.20a and the slave is on 4.0.20a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GROUP BY optimization headscratcher
Michael, and . However, it didn't work. Whole thing still takes about 1 second. On the other hand, I realized I'm an idiot and that the reason it was running so fast when I handled temporary tables myself is that I was using mysqlcc, which truncated the first table to 1000 rows rather than 475,000, which--as one would imagine--sped things up considerably. However, I'm still looking for a way to make this fast. This is an integral part of my application, it'd be a big load off my mind ( my processesor) if I could get it under half a second on my box. I've made the changes Michael suggested, so I was wondering if anyone had suggestions on how to optimize this further. Below please find the query in question, a little background, the create statements and the output of explain: SELECT T2.guid, sum(T2.d+T1.d) AS theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T2.guid; (I grouped by the wrong T last time, sorry). T1 contains one user, and their answers to various questions, so guid actually has only 1 value in this table, and qid has about 65, for a total of 65 rows. T2 contains about 15,000 users, so guid has 15,000 different values and qid has 34 possible values, and the total cardinality comes out to around 475,000. The Create Table statements look like: CREATE TABLE `T1` ( `guid` smallint(5) unsigned NOT NULL default '0', `qid` smallint(5) unsigned NOT NULL default '0', `a` tinyint(2) NOT NULL default '-2', `d` tinyint(2) NOT NULL default '-2', UNIQUE KEY `IX_T1_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM CREATE TABLE `T2` ( `guid` mediumint(8) unsigned NOT NULL default '0', `qid` tinyint(3) unsigned NOT NULL default '0', `a` tinyint(4) NOT NULL default '0', `d` decimal(1,0) unsigned NOT NULL default '0', UNIQUE KEY `IX_T2_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM And the explain is: +---+--+++-++--+-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +---+--+++-++--+-+ | T1| ALL | IX_T1_qid_guid | NULL |NULL | NULL | 65 | Using temporary; Using filesort | | T2| ref | IX_T2_qid_guid | IX_T2_qid_guid | 1 | T1.qid | 4979 | Using where | +---+--+++-++--+-+ Thanks so much! -Matt -Original Message- From: [mailto:[EMAIL PROTECTED] Sent: Saturday, August 14, 2004 3:46 AM To: Matt Eaton Subject: Re: GROUP BY optimization headscratcher Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- 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: Replace delayed locks table
Replace deletes and inserts. ? what do you mean? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help, slave wont stay running!
Anybody else have any ideas? I cant keep the slave up only thing I have not tried is upgrading to 4.0.20, however, nothing changed to cause this problem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
