NaNs
Greetings. If I have a key on a float and attempt to insert a NaN I get ERROR 1034: Incorrect key file for table: 'foo'. Try to repair it if I attempt to update/delete that row. If I remove the key from the float it seems to behave when I try to update/delete the row. Is this a bug / known limitation in mysql-3.23.52? Thanks for any insight. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Massive Research Data
Some background... We have Objects r1, r2 ... r1600 Which each have (~1600) regions d1, d2 ... d500 Which each have (~500) datapoints So, roughly we have around 786,000 datapoints on a given object. Typically, we might take 15 or so regions and take data from each of their datapoints (~7500) at ~1300 "steps" or environmental variations. So, each test will generate about 9.75M floats. A curve can be extracted from the 1300 steps (floats) for each datapoint. We then run these tests hundreds of times a day - sometimes on different objects, sometimes on the same ojbects (so you can analyse the curves through time) We have to date stored this raw data within compressed text files indexed by MySQL. However, as you can imagine, querying this data is a pain. We have to ask MySQL for all of the tests of a given object, then analyse the files to extract the appropriate curves - often opening up 100s of datafiles. The question is whether anyone has any more intelligent ways of storing this data within MySQL without busting MySQLs file size limits, or reasonable CPU/RAM contraints. Storing the data raw in rows doesn't seem like the most sane technique. Nor does throwing it into a char for each datapoint. What I'm looking for is a _lossless_ technique to store the curves within the database for each datapoint (we need to be able to extract and analyse data for each datapoint). My guess is that someone out there is doing scientific analysis similar to this using MySQL as their backend. The environment is R&D so queries will be fairly limited. We obviously don't want queries to take down the DB server, but, at the same time this isn't the backend data to Yahoo! serving millions of requests every minute. Thanks! Seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: columns no more
> is there a way to delete a column from a table, without droping the > whole table? Yep! ALTER TABLE DROP [COLUMN] col_name http://www.mysql.com/doc/A/L/ALTER_TABLE.html > If not is can I get sql to replicate the command to re create the table? Yep! mysqldump -qd -u http://www.mysql.com/doc/m/y/mysqldump.html > can I transfer information to a dummy table while > I create the table I want? Yep! Just do a select into outfile http://www.mysql.com/doc/S/E/SELECT.html then a load data infile http://www.mysql.com/doc/L/O/LOAD_DATA.html Take care, seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing Raster Data
I'm curious if anyone has any recommendations for storing large multi-layered raster data within mysql beyond simply using blobs or text fields. Although not a GIS application; the idea is similar. We are simply modeling 3D surfaces. Queries on the actual mapping structure stored within the raster won't be frequent - reading the data (or large segments of it) is more important. But, I figured I would check to ensure there isn't some level of scholarship out there for mapping raster data to the relational model beyond just storing the rasters in full within blobs/text. Thanks in advance for any insight. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Representing Object Oriented Data in a RDBMS
Greetings! I've been using MySQL for years now and love it's flexibility, scaleability, and general useability. However, I think I might be beginning to bump into a wall in cleanly and efficiently representing some data structures within merely a relational system. My hope is that someone on the list has had to deal with similar data and might be able to offer some insight into how to effectively and efficiently represent it within a relational, as compared to an object oriented DB structure so I don't have to start looking around for an alternative DB option. I need to draft a mechanism for storing _user defined_ Objects (users define characteristics, components of that object, events that occur to those objects etc.). So, a very simplified example might be that someone is defining what cut up pieces of sandwiches are and how they came about from their original Raw Materials (RM). RM1 -- \ RM2 > ObjectTop / \ RM3 -- \ \ --> ObjectWidget1 \ / > ObjectSandwich --> ObjectWidget2 RM1 -- / \ \ / --> ObjectWidget3 RM4 > ObjectBottom / RM5 -- Where, (not programmatically or by an arbitrary table) a user would define say: ObjectTop as requiring RMS1 - 3, define it has having a name as being of size etc. etc. and so on in a similar fashion for ObjectBottom. The user might create an object ObjectSandwich which is composed of 1 ObjectBottom and 1 ObjectTop and have a characteristic of a name of along with nth other characteristics. Finally, s/he might create multiple ObjectWidgets which descend from ObjectSandwich (think chopping up the sandwich into multiple pieces). This would be a two step process obviously. The user would DEFINE a generic ObjectSandwich for example (think, creating a class in code), and then would create specific instances of that generic object (think, create instances of your class and constructing your specific characteristics of that instance). The database would thus have no idea it was being told to store data about a sandwich, or, more specifically, pieces of that sandwich UNTIL the user DEFINES those "objects" for it. Each level would have to inherit the characteristics, events (you might heat the sandwich while it's still a sandwich; you might not), composition etc. of it's parents (all the way up the chain). So, you should be able to ask ObjectWidget what kind of and which specifically ObjectTop he had. However, I don't have the option of having a "Bottoms" table, and a "Sandwiches" table or a "Widgets" table since I won't know as a programmer what objects might be created by users. There would just have to be tables describing the compositions of any specified objects, the values of those specifications for each particular instance of that object (think creating instances of classes in an OO language). To add further complexity to the project I need to be able to reference external data into specific objects. For example, I might want to take pictures of an ObjectTop and associate that picture with that specific ObjectTop. I may want add multiple notes (not just a 1:1 relationship) about specific ObjectSandwiches and so on. This seems fairly intuitive within an OO model, but, it's more difficult to visualize how a strictly relational model would effectively and efficiently handle it (particularly traversing up inheritances defined by users without overrunning your database). Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Project + MyODBC
Has anyone successfully saved Microsoft Projects within MySQL? When I attempt to save a project I get a MyODBC error on the syntax, on, what I presume is one of their table creation SQL statements. Anyone have any insight? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Uploading files using PHP to database.
> Could anyone shed some light on how to go about uploading a text file > with a web interface using php into a mysql database. wow, that was a > mouthfull. I would much appreciate it. Well, that depends. Do you want to store the native text file fully intact say like you might store an image file or a word document, or, instead are you only interested in just the text. Secondly, what do you wish the the input UI to look like? Do you just want them to enter in a filename, or, is pasting into a text box sufficent? If you objective is merely for them to paste into a textbox the task is simple enough.. just create a table with a blob/text element and use your form to populate (standard PHP usage of forms/variables at work here of which I won't go into detail here on). If you goal is to merely accept filenames and go from there might I suggest you taking a look here: http://www.php.net/manual/en/features.file-upload.php Once you have your data from the user the question returns to whether you want to store the native file format (applies more if you are intending to handle formatted text such as in word, rtf, or some other format), or just the text. Depending on how large your text/data files are you might consider using your database as merely an indexing agent. This could be as simple as creating a database managed directory structure on one of your servers, placing your text files in there and then having the database point to them either by ID or store the location (the prior is probably ideal). Or, you could do the prior and add in some full text word indexing such as with a B-tree index. The point here is that your harddisk makes for a fairly good blob storage device.. no use not making use of it when compared to loading up your database with tons of data which doesn't add to the queryability of the data while limiting your database's ability to scale. Regards, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql/table structure question.
s is why chars become difficult to deal with. Sounds like the job of a recursive select.. but, perhaps there exists some additional table hierarchies that I'm not thinking about to easily compare multiple levels of data. Your brilliance is requested and thanked in advance, Seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Got timeout reading communication packets
Hi! > How do you know that row is truncated ?? > Filesizes for one. With just this single query the database is ca. 65K, the update log with just this query in it is ~600k. > If you have binary data in the blob field, when you retreive a result > set, you will get it back in the binary, unescaped form. > I can't imagine there is any binary data in there. See the php script in the previous email.. it just increments a counter and stores in a sring. It also reacts the same way with a text field. Also, it's truncating right in the middle of one of the incremented numbers... 12770 12771 12772 12773 1 | > Query the lengths returned to make sure that data is truncated. > mysql> select length(data) from measurement_extended_data; +--+ | length(data) | +--+ |65535 | +--+ 1 row in set (0.05 sec) [internal mysql]# wc /tmp/update.002 4 100046 589227 /tmp/update.002 > If you are sure, send me CREATE TABLE statement and INSERT instead of > REPLACE statement, so that I can test it. > # here's the create statement.. the only thing really abnormal # is the size of the primary key (compound) CREATE TABLE measurement_extended_data ( content_typeID tinyint(3) unsigned NOT NULL default '0', contentID int(10) unsigned NOT NULL default '0', arrayID smallint(5) unsigned NOT NULL default '0', positionID mediumint(8) unsigned NOT NULL default '0', testID smallint(5) unsigned NOT NULL default '0', iteration smallint(5) unsigned NOT NULL default '0', test_locationID smallint(5) unsigned NOT NULL default '0', date_added int(10) unsigned NOT NULL default '0', added_by mediumint(8) unsigned NOT NULL default '0', data text NOT NULL, PRIMARY KEY (content_typeID,contentID,arrayID,positionID,testID,iteration), KEY test_locationID (test_locationID), KEY testID (testID) ) TYPE=MyISAM; Update log attached in seperate email. Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Got timeout reading communication packets
Re: large replaces truncating... I'm still trying to figure out what's going on here.. I've now tried it in PHP and am getting the same response... Ie, given this simple script: The data field in the table truncates like this: | 12750 12751 12752 12753 12754 12755 12756 12757 12758 12759 12760 12761 12762 12763 12764 12765 12766 12767 12768 12769 12770 12771 12772 12773 1 | It always truncates in the exact same location. I can drop the table and recreate but it still truncates in the exact same location. The query, when echoed out is sane ending indeed on 9. Here's the table again: mysql> desc measurement_extended_data; +-+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+---+ | content_typeID | tinyint(3) unsigned | | PRI | 0 | | | contentID | int(10) unsigned | | PRI | 0 | | | arrayID | smallint(5) unsigned | | PRI | 0 | | | positionID | mediumint(8) unsigned | | PRI | 0 | | | testID | smallint(5) unsigned | | PRI | 0 | | | iteration | smallint(5) unsigned | | PRI | 0 | | | test_locationID | smallint(5) unsigned | | MUL | 0 | | | date_added | int(10) unsigned | | | 0 | | | added_by| mediumint(8) unsigned | | | 0 | | | data| blob | | | | | +-+---+--+-+-+---+ (Note.. I've even tried changing data to a text NOT NULL).. exact same truncation location). The php script above reports no error. ** The UPDATE log (when turned on) shows the ENTIRE query (ie.. the query is at least making it to the database long enough to be logged). Again.. the my.cnf: # MySQL Configuration File # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] port= 3306 socket = /tmp/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=7M # INCREASED FOR MORE DATA set-variable= thread_stack=128K set-variable= back_log=50 set-variable= max_connections=2250 set-variable= tmp_table_size=15M set-variable= table_cache=6000 set-variable= sort_buffer=3584K set-variable= join_buffer=512K set-variable= connect_timeout=2 set-variable= record_buffer=1M set-variable= flush_time=900 set-variable= wait_timeout=300 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [myisamchk] set-variable= key_buffer=32M set-variable= sort_buffer_size=48M Any new ideas? Seth > Seth Northrop writes: > > > > > > 010624 19:44:23 Aborted connection 231 to db: 'unconnected' user: 'root' > > host: `localhost' (Got timeout reading communication packets) > > > > > The above only implies that your program exited without calling > mysql_close(). Nothing to do with your problem. > > But, as I told you , you did not quote string constants. > > And shoot out your resulting string to some file for inspection. That > might help you find your error. > > > --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Got timeout reading communication packets
> The above only implies that your program exited without calling > mysql_close(). Nothing to do with your problem. > But.. why would the console be reporting this? Ie, I'm getting the same result (same error in the error log, same truncation) when I manually pipe the query to the mysql console. (Note that the errors are coming from localhost; the actual script is run from a client). Shouldn't a direct console query terminate the connection to the database after proper completion? > But, as I told you , you did not quote string constants. > > And shoot out your resulting string to some file for inspection. That > might help you find your error. > See previous emails.. I've been doing this.. the query looks sane; no \0 characters, no odd binary data, nothing out of whack where it truncates (ie, no renegade ' etc.). Because running the query through the console with no intervention of the c api causes the same result I'm finding it difficult to believe it's an issue with the code itself unless it has something to do with the string itself (ie.. there is some unknown character in there). But, that seems unlikely since the code to generate the query is fairly straight forward.. just dump a bunch of tab delimited ints groups of which comma delimted into a string. The query just looks like: REPLACE into reflectivity.measurement_extended_data set content_typeID = 7, contentID = 121529, arrayID = 0, positionID = 1, testID = 1, iteration = 0, test_locationID = 1, date_added = 993165848, added_by = 18, data = '396 601 12011801,0 191 191 191 191 190 189 188 188 188 188 188 187 191 191 191 191 192 "; It just happens to be several MB large. Or... are we just having a huge disconnect here? Thanks! Seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Got timeout reading communication packets
Slight correction in the query...
> The query just looks like:
>
> REPLACE into reflectivity.measurement_extended_data set content_typeID =
> 7, contentID = 121529, arrayID = 0, positionID = 1, testID = 1,
> iteration = 0, test_locationID = 1, date_added = 993165848, added_by = 18,
> data = '396 601 12011801,0 191 191 191
> 191 190 189 188 188 188 188 188 187
> 191 191 191 191 192 '
--
---
Seth Northrop
Manager of Information Technology
Reflectivity, Inc.
3910 Freedom Circle, Suite 103
Santa Clara, CA 95054
voice: 408-970-8881 x147
fax:408-970-8840
http://www.reflectivity.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Got timeout reading communication packets
> At a small glance, your code looks OK. Try looking at resulting string > sql in gdb or just print it out to stdout. That might catch your bug. > Thanks again for the reply. The query looks sane.. As I mentioned, I spit it out to a file and piped it directly to mysql (after inspecting it). At least where it truncates there isn't anything abnormal (the blob is merely a tab delimited string of ints) and the same truncation occurs. > MySQL commands lenghts are limited only by max_allowed_packet, which > can be extended up to 16 Mb in 3.23 and 4 Gb in 4.0. > Nodz.. that's what I thought. This is our setting: set-variable= max_allowed_packet=7096128 Confirmed in variables: | max_allowed_packet | 7095296 It's definately not that big, and, we aren't getting the standard expected errors when you exceed packet size (such as mysql server has gone away). The only clue we have is this: 010624 19:44:23 Aborted connection 231 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Got timeout reading communication packets
>
> You are possibly not escaping binary fields. You should use
> mysql_escape_string() function on binary objects prior to inserting it
> in the INSERT command, or use load_file() function.
>
Thanks Sinisa for the reply!
There really isn't any binary data.. and, we are calling
mysql_escape_string.. here is the snippet of code:
// START SNIP
int state;
unsigned int encoded_str_length = 0;
MYSQL_RES *result;
MYSQL_ROW row;
char *sql, *escaped_data;
escaped_data = new char[2*strlen(data)+1]; //required by mysql
sql = new char[2*strlen(data)+600];// +1
encoded_str_length = mysql_escape_string(escaped_data,data,strlen(data));
sprintf(sql,"%s%d%s%d%s%d%s%d%s%d%s%d%s%d%s%d%s%d%s%s%s",
"REPLACE into reflectivity.measurement_extended_data set
content_typeID = ",content_typeID,
", contentID = ",contentID,
", arrayID = ",arrayID,
", positionID = ",positionID,
", testID = ",testID,
", iteration = ",iteration,
", test_locationID = ",test_locationID,
", date_added = ",date_added,
", added_by = ",added_by,
", data = '",escaped_data,"'");
state = mysql_query(connection,sql);
delete [ ] sql;
delete [ ] escaped_data;
if (state != 0)
{
Application->MessageBox(mysql_error(connection),NULL,MB_OK);
return 0;
}
// END SNIP
And, as I mentioned, if I do something like:
bin/mysql <-u & -p> < foo.sql
where foo.sql contains the query checked for sanity the exact same result
occurs.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Got timeout reading communication packets
Hi! I posted this last week, but, haven't seen any replies yet, and, generally, there seems to be a lack of replies in the archives on the "Got timeout reading communication packets" related issues. Here is the issue again: We have started doing some rather large replace intos and inserts (~3MB) using a client written in C. However, these queries are truncating at around 100k - meaning, it inserts about 100k or so of the blob field.. but, stops and truncates out the rest of the query. There is no error returend by the connection, but, the error log does have errors such as these: 010323 11:15:19 Aborted connection 45 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) The same occurs if I pipe the query directly to mysql from the command line. No error, but the data field only has about 100k of the data there before it just stops. The table looks like this: # MySQL dump 8.13 # # Host: localhostDatabase: reflectivity # # Server version3.23.35 # # Table structure for table 'measurement_extended_data' # CREATE TABLE measurement_extended_data ( content_typeID tinyint(3) unsigned NOT NULL default '0', contentID int(10) unsigned NOT NULL default '0', arrayID smallint(5) unsigned NOT NULL default '0', positionID mediumint(8) unsigned NOT NULL default '0', testID smallint(5) unsigned NOT NULL default '0', iteration smallint(5) unsigned NOT NULL default '0', test_locationID smallint(5) unsigned NOT NULL default '0', date_added int(10) unsigned NOT NULL default '0', added_by mediumint(8) unsigned NOT NULL default '0', data blob NOT NULL, PRIMARY KEY (content_typeID,contentID,arrayID,positionID,testID,iteration), KEY test_locationID (test_locationID), KEY testID (testID) ) TYPE=MyISAM; Smaller queries work fine. It's just when they seem to be over 1 or 2MB. /etc/my.cnf looks like this: # MySQL Configuration File # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] port= 3306 socket = /tmp/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=7096128 set-variable= thread_stack=128K set-variable= back_log=50 set-variable= max_connections=512 set-variable= tmp_table_size=15M set-variable= table_cache=6000 set-variable= sort_buffer=3584K set-variable= join_buffer=512K set-variable= connect_timeout=2 set-variable= record_buffer=1M set-variable= flush_time=900 set-variable= wait_timeout=300 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [myisamchk] set-variable= key_buffer=32M set-variable= sort_buffer_size=48M As you can see we bumped up the max_allowed_packet because we were getting "server has gone away" errors. - Box is a 1Ghz XEON /w Redhat 7.2 and 1GB RAM - MySQL version is: 3.23.35 - Query is a fairly straight forward insert or replace into and is verified as clean. Am I missing a configuration directive? Is something else up? Thanks! Seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to prevent inserting duplicate rows?
> how do I OVERWRITE the previous entry in the > table? ie. is there a SQL command to do like INSERT, but if duplicate > found, overwrite with the new value. See REPLACE into http://www.mysql.com/doc/R/E/REPLACE.html Take care, seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RAID advice :> (fwd)
Sorry for the delayed reply.. the list marked my reply as spam ! ;) -- Forwarded message -- Date: Fri, 22 Jun 2001 02:48:14 -0700 (PDT) From: Seth Northrop <[EMAIL PROTECTED]> To: Wouter de Jong <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] Subject: Re: RAID advice :> > Let's say your OS crashes (Linux...bad libraries for example, that are > not resolvable, for example :>), then you'll have to format all your disks > including your \ > data to replace the OS. I'm missing the link here between OS crashing and having to reinitalize and rebuild the RAID array. Ultimately, you want to avoid single points of failure. Having the OS on a none redundant disk seems like a pretty big one. If that disk goes bad (a much higher probability than linux crashing and destroying your disks in a flaming explosion) then your database is down. If it's on the RAID array then you swap a new disk in and have zero downtime (assuming you can hot swap). You could certainly keep your / partition seperate.. this is generally a good idea anyways; but, I see no advantage to keeping the OS off the RAID array. --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: what does this mean please?
> ERROR 2002: Can't connect to local MySQL server through socket > '/tmp/mysql.sock' (2) > > and how you fix it? It means you have no local socket for mysql to connect through. Meaning, something/someone either deleted or mangled /tmp/mysql.sock or mysqld isn't running to begin with. Try to connect with the -host flag to connect (so it won't use the socket) and shutdown and try to restart the mysqld - hopefully this will recreate mysql.sock Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Knowing if a table exits
desc does the trick from the console or via a query. Checking for the datafiles is another way if you are local to the server; though, that's not really a sane technique since you have to usually have the permissions of the database to see the datafiles.. privs you probably don't your script to have. --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database MySQL: myisamchk and isamchk error message
> I was running myisamchk and ismchk on my databases and I got the > following error: > > "warning: 1 clients is using or hasn't closed the table properly" You aren't running (my)isamchk with mysqld running are you? If so, you shouldn't be.. or at least running on tables that are being used/open and not flushed and locked. --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help with index
Just make sure you have a key on any field you do a query on. Given that there isn't a whole lot of correlation between your columns (logically speaking.. ie, age doesn't really match up with city) I wouldn't worry about compound keys.. they'll actually just slow you down since you need to maintain larger keys that don't offer much advantage. The only exception to this might be if you commonly have a query which takes the form you can represent with a compound key (ie.. you always grab a row by age and city).. but, even then I don't think you will gain much over two individual keys. Take care, seth On Fri, 22 Jun 2001, Jaime Teng wrote: > >> whats the difference between: > >> alter table tablename add index (name); > >> alter table tablename add index (age); > >> alter table tablename add index (birthdate); > >> alter table tablename add index (city); > > > >These are four INDIVIDUAL keys.. helpful if you want to search by name, > >age, birthday, _OR_ city > > Will I get any performance if I were to do some complex > query like: > > select * from tablename where name = 'BILL' and city = 'new york'; > select * from tablename where age > 30 and city = 'seatle'; > select * from tablename where age > 30 and name = 'JOHN'; > > or do you suggest that I add the following on top of the > previous index? > alter table tablename add index (name,city); > alter table tablename add index (age,city); > alter table tablename add index (age,name); > etc... > > thanks > Jaime > > > > database,mysql > > --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Repairing Tables
> It there a way around this error occuring > after every outage?. Eeek! Are you running myisamchk -r? - obviously, there isn't really much of a way to avoid having to repair tables after a hard OS crash. I would highly recommend trying to get ahold of something like an APC battery supply which you can monitor on the serial port - then you can safely shut down mysql and shut down the server before you run out of battery power. This of course assumes you can get a hold of a good quality UPS there at a reasonable price. --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: change table name?
> how do i change a table's name without recreating it? See: http://www.mysql.com/doc/A/L/ALTER_TABLE.html ALTER TABLE RENAME TO Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help with index
> whats the difference between: > alter table tablename add index (name); > alter table tablename add index (age); > alter table tablename add index (birthdate); > alter table tablename add index (city); These are four INDIVIDUAL keys.. helpful if you want to search by name, age, birthday, _OR_ city > compared to: > alter table tablename add index (name,age,birthdate,city); This is a compound key. Helpful usually if you want to span a UNIQUE entity across multiple columns. Ie, if you wanted to have a unique key (say a primary key) without having a distinct unique column (like a counter). The key would be creating essentially is nameagebirthdatecity. Since you can query on the prefix of a key, a query which would query: (name) OR (name && age) OR, (name && age && birthdate) OR, (name && age && birthdate && city) would be optimized. HOWEVER, a query on age, birthday, or city alone (or any combination which doesn't follow the prefix order of the key) would NOT. > What I want is performance/speed in doing the following: > select * from tablename where name = 'bill'; > select * from tablename where age > 30; > select * from tablename where city = 'new york'; You would thus want the prior, individual keys. Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: passing variables to/from flash to/from php
> Does anyone know how to pass a variable from a page embedded with a = > .swf (flash) object to a .php file for processing, and pass back the = > results to the same page containing the .swf file? > Thanx in advance! Perhaps not the most topical discussion for the mysql list, but, alas, I would recommend checking out the "Ming" swf functions in PHP. http://www.php.net/manual/en/ref.ming.php Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Startup Question
> in this directory is the ./mustang-bin.index and also ./mysql/most.frm > however I am getting the error messages below when I try and start it. I > have never had this problem before. Does anyone have any ideas. Your mysqld doesn't know where your files are (though, that was probably obvious ;). Try setting the -h (--datadir) flag to force to that location and see if that works. If that doesn't you have a permissions issue I would presume. As for the log, check the path I suppose in /etc/my.cnf and verify that it is correct - else, again it could be a permissions issue. Personally though, I would install the binary distro on linux. I believe, (correct me if I'm wrong others), it at least use to be noticably faster than trying to compile it yourself absent given you couldn't match Monty's compiler configurations) - not to mention it's a whole lot less of a pain to manage then the source distro. Just untar and go. --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there any file .frm, .MYD, .MID on mySQL in HPUX Plateform?
Yes. Though having never installed mysql on HP-UX my presumption is that it acts much like all other unices and stores them in a ./data directory. For example, in the binary distribution this is typically usually in something like /usr/local/mysql/data. (or, unpacked directory/data) Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help me HOw to load Images or pictures into MYSQL database
> > Please inform me how to load IMAGES INTO MYSQL TABLES. Having created several similar applications in the past I would recommend NOT storing these images IN the database; your filesystem makes for a nice blob storage device. I would instead store pointers of some sort to the files to minimize DB I/O and storage requirements. But, if you must, just write the data to a blob field. Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Large Replace/Inserts Truncating.
Hi! We have started doing some rather large replace intos and inserts (~3MB) using a client written in C. However, these queries are truncating at around 100k. There is no error returend by the connection, but, the error log does have errors such as these: 010323 11:15:19 Aborted connection 45 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) The same occurs if I pipe the query directly to mysql from the command line. No error, but the data field only has about 100k of the data there before it just stops. The table looks like this: # MySQL dump 8.13 # # Host: localhostDatabase: reflectivity # # Server version3.23.35 # # Table structure for table 'measurement_extended_data' # CREATE TABLE measurement_extended_data ( content_typeID tinyint(3) unsigned NOT NULL default '0', contentID int(10) unsigned NOT NULL default '0', arrayID smallint(5) unsigned NOT NULL default '0', positionID mediumint(8) unsigned NOT NULL default '0', testID smallint(5) unsigned NOT NULL default '0', iteration smallint(5) unsigned NOT NULL default '0', test_locationID smallint(5) unsigned NOT NULL default '0', date_added int(10) unsigned NOT NULL default '0', added_by mediumint(8) unsigned NOT NULL default '0', data blob NOT NULL, PRIMARY KEY (content_typeID,contentID,arrayID,positionID,testID,iteration), KEY test_locationID (test_locationID), KEY testID (testID) ) TYPE=MyISAM; Smaller queries work fine. It's just when they seem to be over 1 or 2MB. /etc/my.cnf looks like this: # MySQL Configuration File # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] port= 3306 socket = /tmp/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=7096128 set-variable= thread_stack=128K set-variable= back_log=50 set-variable= max_connections=512 set-variable= tmp_table_size=15M set-variable= table_cache=6000 set-variable= sort_buffer=3584K set-variable= join_buffer=512K set-variable= connect_timeout=2 set-variable= record_buffer=1M set-variable= flush_time=900 set-variable= wait_timeout=300 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [myisamchk] set-variable= key_buffer=32M set-variable= sort_buffer_size=48M As you can see we bumped up the max_allowed_packet because we were getting "server has gone away" errors. - Box is a 1Ghz XEON /w Redhat 7.2 and 1GB RAM - MySQL version is: 3.23.35 - Query is a fairly straight forward insert or replace into and is verified as clean. Am I missing a configuration directive? Is something else up? Thanks! Seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Borland C++ Builder (reading data).
Caveat: I'm not a seasoned Borland C++ builder programmer so this may very
well be just plain stupidity on my part (I'm not even a really good C
programmer either !).
However, givem some code that looks similar to this:
int state;
MYSQL_RES *result;
MYSQL_ROW row;
char sql[250];
sprintf(sql,"%s%s%s%s","SELECT positionID
FROM reflectivity.positions
where position_row = ",position_row,
" && position_column = ",position_column);
state = mysql_query(connection,sql);
if (state != 0)
{
printf(mysql_error(connection));
return 0;
}
result = mysql_store_result(connection);
// while ((row = mysql_fetch_row(result)) != NULL)
row = mysql_fetch_row(result);
if (mysql_num_rows(result) > 0)
{
// free some memory
mysql_free_result(result);
return (int)atoi(row[0]);
}
else
{
// free some memory
mysql_free_result(result);
return 0;
}
in C using gcc on linux it works. I return a valid int which corresponds
to the database entry, no warnings, no errors that I can see.
In Borland C++ Builder, although I connect to the database (ie, I can
WRITE fine to the databsae, and, there are no complaints by my connection
to the database), and "if (mysql_num_rows(result) > 0)" returns true
(meaing it sees the row(s)), I get:
raiased EXCEPTION CLASS EAccess Violation with message Access Violation at
address 3256EEFF in module cc3250mt.dll read of address FEEFEE. Process
stopped.
row[0] however does NOT match against NULL or \0 (though if I print it out
it shows "")
When I attempt to reference or work with row[0]. This has been the case
whenever I try to read an element in row under Borland (various other
functions as well) whereas the exact code (in all instances) works fine
when compiled with gcc. Again though, any WRITES to the database work
great under Borland (so I don't think it's a connection issue).
Am I just totally missing some boat here?
Thanks for any help!
Seth
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C API; queries within functions.
Hi!
This is probably more of a C inadequacy than a MySQL problem. I'm playing
around with C with MySQL (I have to date only interfaced with it in
PHP) and I was curious if anyone had any example code which illustrates
how you would write functions which return mysql data.
For example, in PHP I might write a simple function like this:
function get_specific_db_column($dbname, $tblname, $pk,
$pk_val, $colname)
{
$sql = "SELECT $colname as val
FROM $dbname.$tblname
WHERE $pk = '$pk_val'";
$r = mysql_query($sql);
while ($row = mysql_fetch_array($r))
$array[] = $row;
return $array;
}
But, in C I haven't figured out how to do things like navigate passing the
MYSQL *mysql init pointer into the function for mysql_query etc.
I can however get a program WITHOUT functions (ie, hardcode the
query) compiled and working:
// Simple test application
// for the C Mysql API
#include
#include
#include
#define def_host_name NULL
#define def_user_name "root"
#define def_password NULL
#define def_db_name "reflectivity"
int main (char **arg)
{
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL *connection, mysql;
int state;
char colsize=10, rowsize=10;
// connect to the mysql database on internal
mysql_init(&mysql);
connection = mysql_real_connect(&mysql, def_host_name,
def_user_name,
def_password,
def_db_name,
0, /*port defaut*/
NULL, /*socket default*/
0);/*flag*/
if (connection == NULL) // check for a connection error
{
// print the error message
printf(mysql_error(&mysql));
return 1;
}
state = mysql_query(connection,"SELECT * from reflectivity.accounts");
if (state != 0)
{
printf(mysql_error(connection));
return 1;
}
// you must call mysql_store_result before we can issue anything else
result = mysql_store_result(connection);
printf("Rows: %d\n", mysql_num_rows(result));
// process each row in the result set
while ((row = mysql_fetch_row(result)) != NULL)
printf("%s - %s - %s - %s - %s\n",row[0],row[1],row[2],row[3],row[4]);
// free some memory
mysql_free_result(result);
// close the mysql connection
mysql_close(connection);
printf("Done.\n");
}
Ny guess is that if I just see a couple of examples which mimic some of
the functionality of the above PHP function in C I'll be able to write
them without a problem.
Any pointers on where to look?
Thanks!
Seth
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Large scale statistical analysis
We are performing some rather extensive data collection (measurements) and are attempting to come up with the most sane storage mechanism to facilitate offline (Igor, MatLab, Custom Apps) and online (less complex web based solutions) analysis tools. The problem resides in the amount of data collected; by rough estimates we anticipate collecting 3-5 billion rows of data (presently, this can be represented in 2D plots; (ie, x,y data).. though, eventually data will be collected in more complex fashion, but, the above example makes our storage problems a bit easier to digest) per data collection cycle. Of course, that number could fluctuate up or down. The key here is that it is a lot of data. The question resides in the best way to store this data. The data itself is associated to objects (ie, there aren't 3 billion random measurements; 1k, 2k, 10k etc. of them could be associated with an object - with 10's of 100's of 1,000's of objects being measured). The result is a graphable curve to represent the various measurements of an object. I've outlined 4 possible options, which, I'd love to get feedback on: 1) Use mysql, try to store the data in its above form. Pray it can handle it. However, I've noticed in the past MySQL begins to bog down between 50-500M rows depending on your table architecture and the machine it's running on. The fact that the data isn't static (ie, we add to it) complicates the issue since MySQL seems to slow down (understandable) trying to build indexes on inserts over a certain row count. Thus, this doesn't really seem like an option. 2) Reference flat files with rows in the database. Ie, store measurements for objects, or chunks therein within flat files on the filesystem and then reference those files with rows in a mysql table; similar to how I store and track binary data. I don't like this option simply because it requires file handlers to open up and compare data for objects.. it also is a bear to organize and maintain all of those flat files, and, slows down the application layer. 3) Use something other than mysql (cringe).. recommendations welcome. (Oracle, DB2 etc, not a database or the standard filesystem). 4) Use data compression, trend analysis, curve reduction algorithms to trim down the data but still not lose the significance of the raw data trends. If this is the best option, recommended table and data structures along with pointers on where to learn more about data reduction techniques would be helpful (ie, how to effectively store the data within the context of rows and columns). Of course, the best solution is probably not among those 4. Basically, any insight on how large (enterprise) level data collection and analysis is conducted and implemented would be a great help, whether through personal experience or through textual reference would be much appreciated! I just can't imagine that people out there DON'T use databases to handle large quantities of raw data like this. Thanks for any help! Seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 http://www.reflectivity.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
getting rows with max values.
This may have been covered in the past; but, marc.theaimsgroup isn't responding so I can't run my normal search. I'm trying to extract rows with max values within unique groups - NOT just the max values. In very watered down terms: Given: CREATE TABLE test_table ( number tinyint(1) unsigned NOT NULL default '0', value char(1) NOT NULL default '', seq tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (number) ); With data like this: mysql> select * from test_table; ++---+-+ | number | value | seq | ++---+-+ | 1 | a | 1 | | 2 | b | 1 | | 3 | c | 1 | | 4 | a | 2 | | 5 | a | 3 | | 6 | b | 2 | ++---+-+ 6 rows in set (0.00 sec) I'd like to get rows, grouped by value, with a maximum seq. For example; data that looks like this: ++---+-+ | number | value | seq | ++---+-+ | 5 | a | 3 | | 3 | c | 1 | | 6 | b | 2 | ++---+-+ However, queries such as; SELECT number, value, max(seq) from test_table group by value; produce something like: ++---+--+ | number | value | max(seq) | ++---+--+ | 1 | a |3 | | 2 | b |2 | | 3 | c |1 | ++---+--+ Or, thinking I can force an order within the groups: SELECT number, value, max(seq) from test_table group by value order by seq DESC; still produce: ++---+--+ | number | value | max(seq) | ++---+--+ | 1 | a |3 | | 2 | b |2 | | 3 | c |1 | ++---+--+ 3 rows in set (0.00 sec) But, of course, that's ordering the result set so the group by is beating it to the punch. I noted in the manual the quote "Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results" I also tried using havings (something somewhat silly like having seq = max(seq)) but, that doesn't produce anything for the same reasons I presume as the order by. So, that leaves two questions: Is there a way to replicate using max() within a WHERE (ie, WHERE foo = max(foo)) OR Is there a way to order within a GROUP BY? Ultimately, I'll be joining this data to other tables; so, it would be a pain if I have to break this up into multiple queries. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
