Re: Database Cleaner
Can you say codefendant? David T. Ashley [EMAIL PROTECTED] (This e-mail was sent from a handheld wireless device.) On Jan 2, 2008, at 5:20 PM, Daevid Vincent [EMAIL PROTECTED] wrote: Do it anyways. Release it as Open Source. IBM is big into the FOSS community, and I seriously doubt they will persue this. Just because they have a patent, you can always build a better mouse trap. You are allowed to improve upon an idea that is patented, or do it a slightly different way the patent states. Honestly, sometimes ignorance is bliss. It might have been a better idea to NOT research patents -- it's not like you were going to build a company around this little script. -Original Message- From: Kugel,Miriam [mailto:[EMAIL PROTECTED] Sent: Saturday, December 29, 2007 2:29 PM To: mysql@lists.mysql.com Subject: Database Cleaner I am thinking to write a database cleanup tool. According to my research they are many application specific database cleaners but not a general one. I found a patent: US Patent Number *07188116* - METHOD AND APPARATUS FOR DELETING DATA IN A DATABASE As far as I got it I cannot write my tool because I am infringing this patent. Just unbelievable Any idea would be welcome. Miriam [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe a bit OT: MySQL forum recommendation?
On 12/3/07, Chris Sansom [EMAIL PROTECTED] wrote: My apologies in advance if this is a bit off topic, but... On a rather old site we have a dreadful old bulletin board system based on Matt Wright's WWWBoard - all horrid text files and ancient Perl code. We want to replace that with a decent forum system based on MySQL and PHP, but there's no money so it'll have to be a free one. We also need to be able to roll it into our existing database by adding more tables, rather than adding a separate database. Naturally, the easier it is to set up the better. And the real icing on the cake would be to find a script for converting between WWWBoard and whichever one we choose, though I suspect that might be too much to ask. I should be able to roll my own converter script if need be. The host we're using has PHP 4.3.10 and MySQL 3.23.56, so that rules out some I've found, but these seem promising from a quick look: FUD Forum miniBB phpBB PunBB SEO-Board Simple Machines Forum (SMF) Vanilla Any recommendations from among these or others? You might check out vBulletin. They might waive the license fee if you ask nicely, or they may also have a policy when it is in support of open-source software.
Re: Incrementing a Private Integer Space
On 11/25/07, Stut [EMAIL PROTECTED] wrote: David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut -- http://stut.net/ Thanks for the help, Stut. Is there any way to modify the query so that it will also work on the first insert where there are no records with the specified p? (I.e. imagine in the table below that I wanted to insert with p=25 ... and I'd want the query to insert 25,1.) Thanks, Dave.
Re: Incrementing a Private Integer Space
On 11/25/07, Chris W [EMAIL PROTECTED] wrote: Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut Auto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT The issue is that unless this behavior is defined, changing autoincrement from the behavior you described to a simpler version that just uses ascending integers with no other context is the kind of thing where the guys at MySQL might reason that it won't affect anyone or wasn't defined in a specific way anyway. Strictly speaking, this feared change wouldn't affect the logical correct operation of my database (there would still be key uniqueness), but the neat n, n+1, n+2 ordering I'm looking for in q would confuse humans. Phrased more compactly: unless MySQL calls out this behavior in the documentation, your solution scares the snot out of me. Thank you sincerely, Dave.
Re: Incrementing a Private Integer Space
Hi Martin, The easiest way to restore context in this conversation is to go to the MySQL home page (www.mysql.com), then go to Community, then Lists, then to look at the archives of the main MySQL mailing list (this one). I believe at this point that Chris and Stut answered my question decisively. They both gave me single-query methods of achieving the behavior that I want. Thanks to all ... Dave. On 11/25/07, Martin Gainty [EMAIL PROTECTED] wrote: Dave is trying to establish an algorithm which would fit your requirement I can see column q is auto-increment which makes sense as you retain the capability to generate a unique row but jumping into the middle of a conversation without knowing the prior discussionWhat is/was/will be the purpose of column p..?Can we denormalise a bit and extrapolate the value of column p based on known value of column q?Martin-__Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. On 11/25/07, Chris W [EMAIL PROTECTED] wrote: Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -StutAuto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT The issue is that unless this behavior is defined, changing autoincrement from the behavior you described to a simpler version that just uses ascending integers with no other context is the kind of thing where the guys at MySQL might reason that it won't affect anyone or wasn't defined in a specific way anyway. Strictly speaking, this feared change wouldn't affect the logical correct operation of my database (there would still be key uniqueness), but the neat n, n+1, n+2 ordering I'm looking for in q would confuse humans. Phrased more compactly: unless MySQL calls out this behavior in the documentation, your solution scares the snot out of me. Thank you sincerely, Dave. _ Put your friends on the big screen with Windows Vista(R) + Windows Live™. http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_102007
Incrementing a Private Integer Space
I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? Thanks.
Designing Table for Both Global and Local Indices
Hi, I am developing a large database where the web interface may be shared among many companies, but the data will generally not be shared. For the purposes of example, let's call it a bug tracking system such as Bugzilla. Each company has their own private software bugs. Many companies may enter bugs that become part of the bugs table. However, depending on how a user is logged in (i.e. is part of which company) only that company's bugs will be queried or visible. If I just index the bugs with an autoincrement long integer and an integer representing the company, it will work fine. The disadvantage, however, is that people generally expect that after they enter Bug #567, Bug #568 comes next (which wouldn't be the case if the index of the bugs table is shared among all companies). What is the most efficient way to tackle this problem so that each company gets their own virtual private space of bug numbers but only one bugs table is used? The most obvious way to handle it is: a)Lock the table. b)Find the maximum bugnumber where company=X. c)Insert the new bug with company=X and bugnumber=max+1. d)Unlock the table. However, is there a better way to think about this? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
On 11/19/07, Mohammad wrk [EMAIL PROTECTED] wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. I think the statement that MySQL is not as powerful as the other products probably is unfounded in the sense that you mean it. On a given platform, searches are typically going to be O(N) or O(log N) depending on how you arrange the indexes and queries. http://en.wikipedia.org/wiki/Big_O_notation What you are trying to do is design your database so that all the queries are O(log N) rather than O(N). It is possible that Oracle can perform more adeptly than MySQL at certain operations (I don't know this, and the stats could easily go the other way). But the difference probably wouldn't exceed 2:1 in favor of either product, and you'll still be left with the O(N) vs. O(log N) design issue. I'd go with the design that does not create new tables. Dave.
Re: secure mysql port
On a *nix box, it is also traditional to configure IPTABLES or similar to restrict TCP/UDP connections based on IP and/or adapter. It seems likely based on your description that the box has two network connections. Dave. On 11/13/07, Michael Dykman [EMAIL PROTECTED] wrote: In my.cnf, you can specify a 'bind-address'. When used it will cause the listener to only be available to host on that same network ie. one of your database host's ip binding is 10.10.10.66/255.255.0.0 # this will list the server to respond only to hosts in the 10.10.x.x range, all other (including localhost!!) will be refused. bind-address=10.10.10.66 On Nov 13, 2007 4:53 AM, David Campbell [EMAIL PROTECTED] wrote: Kelly Opal wrote: Hi Is there any way to restrict access to the tcp port on mysql. I only want my 5 class C's to be able to access the port but it is a public server. Iptables Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupting MySQL on purpose for testing
On 11/6/07, mos [EMAIL PROTECTED] wrote: At 02:18 PM 11/6/2007, Richard Edward Horner wrote: Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Well, there are different types of corruption. Here are a few that come to mind: a)Corruption of a MySQL file, perhaps not repairable (disk hardware problem, OS crash, server power supply failure). b)Corruption in a way that is repairable by a MySQL utility (index file corrupted). c)Application corruption (bad application doesn't handle mutual exclusion correctly, has logical errors, etc.). Hopefully database repair utilities that come with MySQL will map (a) and (b) to be indistinguishable from (c). (But I don't know that--I'm barely a MySQL user.) The most even-handed way to do it would seem to be: a)Write a program that will create a random number of distributed and block corruptions distributed randomly in the MySQL files. (Such a program would take very little time to execute--well under half a second, I'm guessing.) You might want to throw in truncations and blocks added at the end of files, too. b)See how far the MySQL utilities (if there are any) followed by your software will go towards repair. Doesn't seem like an easy thing to test.
Re: Database architecture and security
On 8/23/07, Jason Pruim [EMAIL PROTECTED] wrote: I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Assuming that the web server runs on the same box as the MySQL daemon ... you want to firewall the server so that nobody can connect to the MySQL daemon directly from outside the box. It is also a bad idea to allow the users to have shell accounts on that box unless you have taken additional security precautions (specifically, being sure the MySQL userid/password you're using are secure from all but the web server UID/GID, and that no other userid/passwords have access to the database you're using). Once that is done, all access to the database is controlled by the PHP scripts, and there is no security advantage to having multiple databases. I'm assuming that users have to log in individually (jsmith, bjones, etc.) and that the PHP scripts then carefully control what each user is allowed to modify. I'm also going to assume that you've handled all the obvious technology issues, such as: a)Database transactions/atomic actions. b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave.
Re: Database architecture and security
On 8/23/07, Jason Pruim [EMAIL PROTECTED] wrote: b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave. What do you mean by b? If all the connections come from the local box how could I configure that to make sure it's all set up so it won't leave the database all messed up? I don't know all of the technical details, but if a user uses the STOP button on a browser or the TCP connection is otherwise terminated, there is a feedback mechanism where the PHP script producing the HTML can be terminated (it has no further utility, as it would be feeding a non-existent connection at that point). A worst case is where the PHP script is aborted in a critical section (depending on how transactions are handled in the database) so that the database is left in an inconsistent state. Whether this is possible depends on how you choose to do locking and transactions. The safest approaches I'm aware of are: #1)Form the database results and close the database connection before generating output. #2)Use the PHP function intended for that purpose. Here is the URL for the PHP function: http://us.php.net/manual/en/function.ignore-user-abort.php http://us.php.net/manual/en/features.connection-handling.php Best regards, Dave
Re: Password storage
On 8/18/07, C K [EMAIL PROTECTED] wrote: Friends, I have one question - How to store passwords in MySQL database table in a secure way so that no one can see the password(understand the password string)? It is considered bad security practice to store passwords using reversible encryption. The issue is that users tend to choose the same passwords across different computing systems, as well as personal e-mail and banking accounts. The most common method is to keep a string, known only to the server, that is used to help generate the MD5 or SHA1 hash actually stored. The stored value is then generated using something like: MD5(CONCAT(server_string, user_password, server_string)) In order to be able to mount some kind of an attack other than brute force, an attacker would need to also have the server_string. The disadvantage of using only the user password for the MD5 is that it lends itself to a dictionary attack. So, a bit of randomness thrown in is helpful. http://en.wikipedia.org/wiki/Dictionary_attack As another poster pointed out, the probability of two different passwords having the same hash is remote. Using the SHA1 (160 bits) as an example, and assuming about 64 different characters (6 bits) available for passwords, the SHA1 is about 26 characters of information. Remote. Dave.
Re: recommend a good database book
On 8/13/07, Jonathan Horne [EMAIL PROTECTED] wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. The book at home I have is entitled something like Beginning Databases with MySQL. I'm happy with it. http://www.amazon.com/Beginning-Databases-MySQL-Richard-Stones/dp/1861006926/ref=sr_1_1/105-0224235-8578830?ie=UTF8s=booksqid=1187026358sr=8-1 Also, if you are in the continental US, you might check out www.half.com and search by MySQL. You can get several great used books for the price of one new book. Sometimes you can get lucky and get the books for something like $1 each plus shipping. Dave.
Re: recommend a good database book
On 8/13/07, Jonathan Horne [EMAIL PROTECTED] wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. One more thing. In general, the practical issues (getting simple projects to work) are covered in a book like Beginning Databases with MySQL, but for the theoretical issues, it might be best for you to take a course or two at a local university. For example, understanding the performance impact of indexes requires understanding a little about O(N), O(log N), etc. Here are some things that are relevant: http://en.wikipedia.org/wiki/Database_index http://en.wikipedia.org/wiki/Big_O_notation http://en.wikipedia.org/wiki/Btree In general, you strive to make every database operation perform no worse than O(log N) as the database grows. Understanding how to do this requires a bit of theory. I don't think you'd find that in a book like Beginning Databases with MySQL. Dave.
Re: 2007 MySQL Community Survey - Got Ten Minutes to Spare?
You are more altruistic than I can ever be. Every time I go through a nameless fast-food drive-through (too often, based on my girth), they offer me a chance to win $1,000 if I complete a survey. I never take those surveys. Reasons: a)If, for example, 10,000 people take the survey and the payout is $1,000, that means on average I get $0.10 for taking the survey. Not enough. b)I have no evidence that payouts are ever made. For all I know, the fast food chain offers everybody $1,000 and pays nobody (no way to verify). A chance to win isn't tangible. My stance on those things is always reward first, then I take the survey. Dave. On 8/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Having a moment of altruism, I started doing the survey only to find that it wouldn't let me advance to the next page (from either page 1 or page 2, can't recall). I kept getting an error of an answer is required of this question even when I had provided one. No good deed goes unpunished perhaps... David So I was gonna take this survey (I don't need or care about the book, just wanted to help you out) and honestly, it's more like a quiz -- needless to say I didn't do it. :-| -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Friday, August 03, 2007 2:33 PM To: mysql@lists.mysql.com Subject: 2007 MySQL Community Survey - Got Ten Minutes to Spare? 2007 MySQL Community Survey - Bribe Included I've created a survey on SurveyMonkey that I am hoping to get a bunch of responses for. The survey will help the community team identify how (in)effectively we communicate development and other goals and also what features you, our community users, most want in future versions of MySQL. So, hey, give us ten minutes of your time and help us make MySQL better. A Blatant Bribe for Participating And for those who need a bribe, we'll be giving away two Apress books (each) to 3 random survey takers. The survey is anonymous, but if you would like to go into the drawing for the books, just include your email address in the very last question...otherwise, just leave it blank. Link to the 2007 Community survey: http://www.surveymonkey.com/s.aspx?sm=ONCdTFPxjyq55NAUBUJ_2fvQ_3d_3d Thanks much! Jay Pipes Community Relations Manager, North America MySQL, Inc.
Re: Fwd: auto increment format
You might not need to do this in the way you are suggesting (depending on your application). I'm not sure why you feel you need to combine the autoincrement with the hash into the same field. Does it really do harm if two records have the same hash? It might work as well to have two separate fields, one that contains the AUTOINCREMENT value, and a second field containing the SHA1. The most traditional approach to using cryptographic hashes is to have a bit of randomness (a string with at least 160 bits of information for SHA1), and to form the hash as a function of some known quantity plus the randomness. If + is the concatenation operator, you might use: hashfield = SHA1(randomness + id + randomness); As long as the randomness is known only to you, there is no way for an attacker to make the mapping from id to the hashfield. What do you mean by security? What are you trying to protect against? On 8/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Isn't there a chance that you could get a ID that is the same as one already in use? and if this happens what happens on the insert? Wishing you the best you know you deserve, __ Lucas Heuman Web Developer Ricomm Systems Inc. FAA, WJHTC/Bldg 300, 3nd Fl., L33 Atlantic City Int'l Airport, NJ 08405 Phone 609.485.5401 Olexandr Melnyk [EMAIL PROTECTED] 08/07/2007 08:19 AM To mysql@lists.mysql.com cc Subject Fwd: auto increment format You can do that using a before insert trigger, something like (untested) : CREATE TRIGGER test1bi BEFORE INSERT ON test1 FOR EACH ROW BEGIN NEW.ID = COALESCE( NEW.ID, SHA1(CAST(RAND() AS CHAR))) END; 2007/8/7, shivendra [EMAIL PROTECTED]: Hi, I'm looking for some basic help. I am developing a MySQL database and want to auto increment a field, but I don't want it to just count 1,2,3, etc. I want the field to be a combination of letters and numbers, at least 8 digits long, completely random for security porposes, but do this automatically, everytime a record is added. For example, ord5001, ord5002, ord5003, etc. Does anyone know how to do this in MySQL? -- View this message in context: http://www.nabble.com/auto-increment-format-tf4229677.html#a12032917 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: auto increment format
On 8/7/07, Boyd Hemphill [EMAIL PROTECTED] wrote: Suggestions to use a hash are a problem because once you have a few million rows the likelihood of a collision is quite high if you cannot afford an error stopping your application. This means that if you write a trigger (the obvious way to go) you will need to trap any uniqueness violation and try again with different salt. Also, I think in the original post you cited security. I'd be curious to know what you are protecting against. Using web browser session identifiers as an example, the most common approach is to have a fixed part of the session identifier concatenated with a cryptographic hash. Typically, the hash is formed based on the index of the database row concatenated with some secret state known only to the server, i.e. something like SHA1(secret_state + row_index + secret_state) That elminates an attacker's ability to forge a session identifier, because they can't map from the row index to the hash without possessing the secret state. If your application is similar, uniqueness of the hashes may be a non-issue. It is true that a hash collision _could_ occur, but it would be of no consequence because the possibility of collision doesn't help an attacker. So, it wasn't clear where your uniqueness requirement came from or if the hash really needed to be part of a database key. Dave
Re: error
On 7/10/07, Octavian Rasnita [EMAIL PROTECTED] wrote: I have exported a database using mysqldump from MySQL 5.0.27, and I have tried importing it in a MySQL 5.0.41, but it gives the following error: ERROR 1071 (42000) at line 483: Specified key was too long; max key length is 1000 bytes Hint: Please post line 483 as well as the lines that give the offending table design.
Re: Recursive queries
On 7/8/07, Steffan A. Cline [EMAIL PROTECTED] wrote: I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply As another poster said, there are various ways to represent trees in databases. You may want to consult some of these references. In this particular case, nothing comes to mind that will give you both: a)The ability to represent an arbitrarily-deep hierarchy of responses, AND b)Will let you get the entire result set ordered the way you want in ONE query. I don't immediately see how to get both at the same time. If, for example, you were willing to sacrifice (a), then just set up integers (maybe 3 of them) allowing you to represent a nesting 3 deep then order by these integers on the query. But 3 is not arbitrarily-deep. If you were willing to sacrifice (b), then you could just represent the tree by a table of links that relate parent and child. Finding all the children for a parent is just select * from links where parent=29331 or something like this. Problem is, you would need to issue queries to traverse the tree. I can't immediately see a way to get both (a) and (b) simultaneously. But you can definitely get EITHER (a) or (b). Dave
Full Text Search, Storage Engines
I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up? b)What storage engines are required? c)Are there any restrictions on mixing and matching tables? d)Do table locking and transactions work the same? Thanks.
Full Text Search, Storage Engine Types
I'm sending this again, because the server seems to have been down for several hours, and I'm not sure if it went out. - I'd like to do full text search on some fields of some tables, but I'm a bit confused by the documentation. Questions: a)How do I set that up (i.e. do I need to use a specific storage engine for a table)? b)What storage engine(s) are required? c)Are there any restrictions on mixing and matching tables that use different storage engines in the same database? d)Do table locking and transactions work the same (for example, some of the storage engines are described as not transaction safe--unclear what this means)? Thanks.
Re: zipcode to timezone
On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=pXERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2Bmap%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave.
Re: zipcode to timezone
On 7/6/07, John Trammell [EMAIL PROTECTED] wrote: -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 AM To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone i don't think there is any state got 2 timezones, i could be wrong. Yeah, you are. http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htm One other thought comes to mind. Is there a guarantee that a timezone can't split a zipcode? In other words, is the mapping from zipcode-timezone guaranteed to exist in all cases? I remember a friend in Tennessee who would take her children to school (10 miles away) and the school was in a different timezone than her home, so it got very confusing what time the kids had to be up and to bed and all that. In rural areas where the zipcodes can be rather large, I'm just wondering if there is any guarantee that a zipcode can't span two timzones.
Many:Many Relation Between Database Tables
I'm an old Microsoft Access user, so I just need to double-check on this with MySQL: I want to relate two tables (let's call them users and priveleges) in a many:many way. That is, each user may have multiple priveleges, and each privelege may have multiple users with that privelege. Here are my questions: a)I'm assuming that under MySQL I have to create a third table that maps between them? (Let's call it users2priveleges.) b)I'm assuming that there is nothing special I need to do to get, for example, all the priveleges with a user (just the standard join stuff with x=y and y=z or something like that)? c)I'm assuming that from an optimization point of view, there is nothing I can/should do beyond optimizing the links, i.e. making sure the related fields are indexed? Thanks.
Re: Many:Many Relation Between Database Tables
On 7/5/07, David T. Ashley [EMAIL PROTECTED] wrote: a)I'm assuming that under MySQL I have to create a third table that maps between them? (Let's call it users2priveleges.) b)I'm assuming that there is nothing special I need to do to get, for example, all the priveleges with a user (just the standard join stuff with x=y and y=z or something like that)? c)I'm assuming that from an optimization point of view, there is nothing I can/should do beyond optimizing the links, i.e. making sure the related fields are indexed? On 7/5/07, Dan Buettner [EMAIL PROTECTED] wrote: David, you've hit the nail on the head. Dan Hi Dan, Thanks for the feedback. My recollection is that old versions of Access had some functionality where you would relate the tables many:many and the product would hide the third table from you. Just wanted to be sure there was no such functionality in MySQL (not that I'd want it, anyway). And just wanted to be sure there is nothing further I could tell MySQL in order to optimize this kind of relation. Thanks.
How to Query by First Part of a VARCHAR?
If I have a table with rows like this, all varchar: DOG_LUCY DOG_CHARLIE DOG_LASSIE DOG_XRAY CAT_MR_BIGGLESWORTH CAT_SCRATCHER CAT_WHISTLER what is the form of a query that will return the rows where the first part of the string matches? For example, what if I'd like to return the rows that begin with CAT_, which should give 3 rows? Thanks. P.S.--This example is contrived to illustrate what I'm trying to achieve. My actual application is different. A database of cats and dogs would naturally be best structured differently. Thanks.
Re: How to Query by First Part of a VARCHAR?
On 7/4/07, gary [EMAIL PROTECTED] wrote: SELECT column FROM table WHERE column LIKE CAT\_%; Would it be reasonable to assume that if column is indexed, the query would execute quickly, i.e. I would assume that the indexing would facilitate this kind of query?
Re: How to Query by First Part of a VARCHAR?
On 7/4/07, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 04), David T. Ashley said: On 7/4/07, gary [EMAIL PROTECTED] wrote: SELECT column FROM table WHERE column LIKE CAT\_%; Would it be reasonable to assume that if column is indexed, the query would execute quickly, i.e. I would assume that the indexing would facilitate this kind of query? Yes, but only for prefix checks like in this example. ` LIKE %CAT% ' or ` LIKE %CAT ' can't use an index. Thanks. I was able to confirm the behavior by creating a table with three identical varchars, populating them randomly with a string of 6 digits but setting each varchar within a row the same, and executing queries. s3 is indexed (below). LIKE CAT% was obscenely fast on an indexed column. LIKE %CAT% was obscenely slow. Thanks for the help. - mysql explain stest; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | bigint(20) | | PRI | 0 | | | s1| varchar(200) | YES | | NULL| | | s2| varchar(200) | YES | | NULL| | | s3| varchar(200) | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) mysql select count(*) from stest; +--+ | count(*) | +--+ | 1480131 | +--+ 1 row in set (0.00 sec) mysql select * from stest where s1=123455; +++++ | a | s1 | s2 | s3 | +++++ | 246823 | 123455 | 123455 | 123455 | +++++ 1 row in set (2.16 sec) mysql select * from stest where s3=123455; +++++ | a | s1 | s2 | s3 | +++++ | 246823 | 123455 | 123455 | 123455 | +++++ 1 row in set (0.00 sec) mysql select count(*) from stest where s1 like %; +--+ | count(*) | +--+ | 136 | +--+ 1 row in set (2.10 sec) mysql select count(*) from stest where s3 like %; +--+ | count(*) | +--+ | 136 | +--+ 1 row in set (0.00 sec) mysql select count(*) from stest where s1 like %000%; +--+ | count(*) | +--+ | 5585 | +--+ 1 row in set (2.19 sec) mysql select count(*) from stest where s3 like %000%; +--+ | count(*) | +--+ | 5585 | +--+ 1 row in set (2.78 sec)
Re: How to order a string as integer
On 6/28/07, Magnus Borg [EMAIL PROTECTED] wrote: Got strings in the format integer+(char)^0..1, ex 3456, 2323, 2324A, 2324B, ... I want to order them as they where integers, like: order by cast(STING as unsigned) desc But when using that query resulting in error 1292. And the result are not correct. How can I remove the chars before i casting the string? And if possible take the chars in the order. So that i get a result like: 1000, 1000A, 1000B, 900, 800, 800A. The queries you plan to execute influence the database design--there is no way around that. I would redesign the database in one of three ways: Possibility #1: Separate the problematic columns into an integer field and a character field, then order by a, b. Possibility #2: Add multiple parallel columns (used only for queries). For example, the strings might be 800 and 1000, but the parallel integers are 800 and 1000; ordering by the integers would get the result you want, whereas ordering by the strings would not. Possibility #3: Keep a single parallel column (probably an integer), mapped such that the sort order is what you want. For example, let's assume you have an unusual sort order such that you want numbers with no letters displayed first, then those suffixed by C, then all others with letters in order. You could create a BIGINT assigned as follows: a)Take the base part number, multiply by 40. b)If there is no letter suffix, add nothing. c)If there is a C suffix, add 1. d)If there is an A suffix, add 2. e)... So, the part number 800C would map to (800 * 40) + 1, the part number 800A would map to (800 * 40) + 2, etc. Keep those parallel integers maintained each time you INSERT or UPDATE a record. Then just order by them. #3 is by far the most powerful approach. If you know in advance what the ordering should be, then you can design the mapping from (part number) - (integers) to accommodate it. (part number) - (strings) is also possible if you can make the strings coincide with one of the MySQL collation orders. In general, even if you could manage to phrase the query you're describing, it could never be efficient. The reason is that MySQL can only keep indexes in terms of the ordinal data types and sort orders that are built-in to give you approximately O(log N) query time. Even if you can do what you want, it won't scale well to large tables. You have to map the problem to the the ordinal data types that MySQL supports (integer, string, float). Dave.
Re: Geographic math problem
On 6/28/07, M5 [EMAIL PROTECTED] wrote: Not being very strong at math, I have a little problem that I'm not sure how to solve. Maybe someone can help me. Basically, given a point (latitude, longitude) and a radius (100 meters) (think circle), I need to compute an equivalent square: That is, two points that would correspond to two corners of the square. From: 51, -114100 meters To: 51.005, -114.005NE corner 49.995, -113.995SW corner Now, the above is not really accurate, of course, since the earth is spherical (well, at least most people think so), and I would like this computation to run in MySQL query, e.g.: UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude = (*), sw_longitude = (*) In the above table, there are already three columns with the centre latitude and longitude and radius. Any ideas? Thanks. The math of latitude and longitude ain't too bad. Consulting a globe would help. A degree of latitude is always the same size in terms of the distance along the earth's surface. However, a degree of longitude varies in size--longest at the equator and shortest (actually zero) at the poles. The defining equations come from that. Just a few notes: a)Whatever equations you derive for the corners may break down if the area includes either pole. You will need to guard against that. b)My gut tells me that you can come up with some very simple approximations (sine of this times cosine of that or dimension of the square) that will work so long as the dimensions of the square are much smaller than the diameter of the earth and you're not working too close to the poles. However, if you mark up a spherical surface (such as a basketball or beachball), I think you'll see that the relationships if either of those assumptions break down would have to go to higher-order equations and wouldn't be so simple, even if they can be represented in closed form. If you want the exact relationships (which I don't believe are in the URLs cited), you should probably post to sci.math. Dave.
Re: optimization help
On 6/27/07, Dave G [EMAIL PROTECTED] wrote: Queries on this table when it gets large is slow as molasses. I'm thinking about making a new table for anything with a different test_id any opinions as to whether this is good or bad? Hi Dave G., We need to know how: a)How large the table might grow to, and b)The queries you'll be executing on the table, and c)The anticipated size of the result sets (from the queries). Generally speaking, you are shooting for O(log N) on the queries. You need to be sure that the queries you are issuing combined with the database design will allow that. So, I need to know the specific queries. Dave A.
Re: optimization help
On 6/27/07, Dave G [EMAIL PROTECTED] wrote: select payload_time,HEX(processed_data) from data__ProcessedDataFrames where test_id=18 AND top_level_product_name=DataProduct AND payload_time 11808.74704 AND payload_time 1180564096.24967; What I'm concerned about is with how much data I will eventually have, even scanning over the KEYS will take a long time. Hi Dave, In the case above, you want to be sure that everything involved in the query is indexed or a key (probably the same thing). To give an example, test_id=18 ... if that isn't indexed, it will be an O(N) scan over all records in a table rather than an O(log N) retrieval based on some BTREE or similar. As a first step, be sure that everything involved in a typical query is indexed. For joins, the related columns should also be indexed. Dave A.
Re: Document archiving
On 6/27/07, Eddy D. Sanchez [EMAIL PROTECTED] wrote: Hello Everyone. I want to scan a large quantity of books and documents and store these like images inside or outside a database, I want use mysql, anyone have any experience with this kind of systems, can you suggest me an opensource solution ?? Well, I never figured out exactly what the product DOES (because the website uses terms that are just too modern for me ... from my point of view they are promising to quantum sporkify my enterprise paradigm delivery multiphasic client-heavy empowerment model), but, here goes ... www.alfresco.com Good luck. If you figure what the product does, please post a translation of their website for me. Dave.
Re: Document archiving
On 6/27/07, Eddy D. Sanchez [EMAIL PROTECTED] wrote: Hello Everyone. I want to scan a large quantity of books and documents and store these like images inside or outside a database, I want use mysql, anyone have any experience with this kind of systems, can you suggest me an opensource solution ?? Also, I have to say this to be complete ... You were aware, of course, that nearly every modern copyright for books prohibits digitizing the book and using it in any kind of document retrieval system? In fact, I believe a violation has occured even if it is scanned and the data is never used. I just had to say this. I don't know how U.K. prisons are, but here in the U.S. they are full of large unpleasant men who have done bad things and may do more bad things ... to you, for example.
KEY vs. INDEX
In reading the syntax of the CREATE TABLE statement, I'm a little confused by the two keywords KEY and INDEX. What does one use when one wants MySQL to arrange the column so that it can find a given record WHERE column=whatever in approximately O(log N) time? This is a key, right? MySQL uses some kind of a BTREE arrangement, right? Thanks.
Best Database Representation of a Chain of Command
I'd like to represent our organization's chain of command (i.e. who is whose boss) in a database. The reason is that in some contexts, my database application needs to know who can view whose time and project records (and the rule is that anyone above in the chain of command can, anyone at the same level or below cannot). Conceptually, this is of course a tree. What is the best representation? My notion would be just a single table with a collection of boss-subordinate records. To find person X's boss, query by where X appears as the subordinate. To find person X's first-level subordinates, query by where X appears as boss. Is there a better way to represent a tree? Thanks, Dave.
Re: determining if tables are being used
Just speaking as a newbie who has no idea what he is talking about ... If nobody suggests anything better ... I've noticed that there is a clear correspondence between table names and the files that MySQL keeps, i.e. [EMAIL PROTECTED] dashley]# ls -al /var/lib/mysql/fboprimedevel/ total 7832 drwx-- 2 mysql mysql4096 Nov 5 2006 . drwxr-xr-x 13 mysql mysql4096 Jun 11 01:07 .. -rw-rw 1 mysql mysql 65 Mar 4 2006 db.opt -rw-rw 1 mysql mysql8940 Nov 5 2006 loge.frm -rw-rw 1 mysql mysql 2359444 Jun 11 11:47 loge.MYD -rw-rw 1 mysql mysql 105472 Jun 11 11:47 loge.MYI -rw-rw 1 mysql mysql9322 Nov 5 2006 resv.frm -rw-rw 1 mysql mysql 5146108 Nov 5 2006 resv.MYD -rw-rw 1 mysql mysql 160768 Nov 6 2006 resv.MYI -rw-rw 1 mysql mysql8948 Nov 5 2006 rscs.frm -rw-rw 1 mysql mysql1944 Nov 20 2006 rscs.MYD -rw-rw 1 mysql mysql2048 Nov 21 2006 rscs.MYI -rw-rw 1 mysql mysql9178 Nov 5 2006 sess.frm -rw-rw 1 mysql mysql 904 Jun 8 01:01 sess.MYD -rw-rw 1 mysql mysql2048 Jun 8 01:04 sess.MYI -rw-rw 1 mysql mysql9930 Nov 5 2006 usrs.frm -rw-rw 1 mysql mysql 109464 Jun 5 15:58 usrs.MYD -rw-rw 1 mysql mysql8192 Jun 5 20:02 usrs.MYI The atimes or mtimes of the files may give you some information. Dave. On 6/11/07, Keith Murphy [EMAIL PROTECTED] wrote: I have inherited database servers from a group whose main job was not working on mysql. I am not certain if all the tables on the databases are being used. Is there some efficient way of determining if a table is being accessed or when the last time it was accessed? Thanks, Keith -- Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: maximum number of records in a table
On 6/11/07, kalin mintchev [EMAIL PROTECTED] wrote: hi all... from http://dev.mysql.com/doc/refman/5.0/en/features.html: Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows. that's cool but i assume this is distributed over a few machines... we have a new client that needs a table with 99 000 000 rows, 2 -3 columns. i was just wondering if i have a two dual core 2 processors in a machine with 4 gigs of ram - is that enough to host and serve queries from a table of this size? a few tables on the same machine? more than one machine? what are the query times like? can somebody please share some/any experience s/he has/had with managing databases/tables with that amount of records. i'd really appreciate it... 99 million isn't that large of a number. If you key the database properly, search times should be very modest. I can't speak for insert times, though, especially when keys are involved. This kind of thing is easy enough to do in your favorite scripting language. I would just create a table with a few keys and just for($i=0; $i9900; $i++) it with random numbers. If you have PHP on your system, here is some PHP code (runnable from the command line) that you should be able to hack down. It should answer your immediate questions about which PHP statements to use (if you've never done this from PHP before): http://gpl.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/sw/standalone/dbtestpop.php?rev=1.31content-type=text/vnd.viewcvs-markup http://gpl.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/sw/phplib/usrs.inc?rev=1.11content-type=text/vnd.viewcvs-markup Near the end of it, especially if the software writes output, you should get an intuitive feel for how long each INSERT is taking. You can even do test queries using the barebones MySQL client ... you should see interactively how long a query takes. I would ALMOST do this for you, but it is just beyond the threshold of what I'd do because I'm bored and watching TV. I'm just a little curious myself. I've never messed with a table about 10,000 rows or so. Dave
Arbitrary Boolean Functions as Relational Database Structure?
Hi, I'm implementing a software release database. Users may aribtrarily be members of groups (a many-to-many mapping), and each software release may contain multiple files. I'd like to allow users the maximum flexibility in deciding who may view what software releases. The most obvious approach is to allow specification in the form of Release X may be viewed by Users in Group Y or Group Z, per release. In this case, the database design would be something like: [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files] The many:many relationship between groups and software releases specifies a Boolean function, of the form is in Group X or is in Group Y or Since one knows the user who is logged in (for a web database), one can do an outer join and quickly find all the software releases that the user may view. I believe one can do it in O(log N) time. However, the Boolean function is of a fairly special form (is in Group X or is in Group Y ...). This is the only form where it seems to translate to an SQL query naturally. Here is my question: Is there any interesting way to structure a database so that other forms of permissions can be specified and translate directly into SQL queries? For example, what if, for a software release, one says, to view this software release, a user must be in Group X or Group Y, but not in Group Z and not user Q? Is there a database structure and a corresponding O(log N) query that will quickly find for a given user what software releases may be viewed? Thanks.
Re: Lock Tables Question
On 6/5/07, Brent Baisley [EMAIL PROTECTED] wrote: I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. I knew somebody was going to say this. Here is the relevant prose from my original post. BEGIN One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. END There is no concept that I'm missing. I understand what a transaction is. But I just don't want to bothered. My application is simple enough that bogarting the database until all necessary modifications have been made and the tables are again consistent is good enough. Collisions are handled by serialization. Period. Somebody wins. Everyone else waits. Works for me.
Re: Lock Tables Question
On 6/5/07, Baron Schwartz [EMAIL PROTECTED] wrote:
David T. Ashley wrote:
There is no concept that I'm missing. I understand what a transaction
is.
But I just don't want to bothered. My application is simple enough that
bogarting the database until all necessary modifications have been made
and
the tables are again consistent is good enough.
Collisions are handled by serialization. Period. Somebody
wins. Everyone
else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which
IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That
should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that
begins to involve
more and more things until you are going through *serious* contortions. I
would avoid
it at all costs.
My only concern with GET_LOCK() is that lock is server-global rather than
database-global. This makes attacks possible in a shared setting (some bad
person could disable your database code by going after your lock).
My solution is just to lock all tables in one statement.
The only question I have (and nobody has answered this) is how many tables I
can include in a single LOCK TABLE statement. I thinking anything up to a
few thousand shouldn't be a problem. What is the limit?
Thanks, Dave.
Lock Tables Question
I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. For example: #1)LOCK TABLE x; #2)Make modifications to tables x, y, and z; #3)UNLOCK TABLES; Are there any race conditions in just using one table for this purpose? For example, SQL guarantees that a given SQL statement is atomic. But it is guaranteed that #2 will complete before #3 above? If every process uses the same rule, can anything unexpected happen? One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. Thanks.
Re: Lock Tables Question
On 6/4/07, Gerald L. Clark [EMAIL PROTECTED] wrote: David T. Ashley wrote: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; You could use a string lock for this. Thanks for the suggestion. It looks logically correct. I'd like to stay away from a string lock if possible because other database users could interfere with it (it is server global, and not tied to the database). My original question is still of interest to me ... Thanks.
Re: Lock Tables Question
On 6/4/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Hi Jerry, I really appreciate the good advice. However, my original question is still unanswered. My original question is whether I can lock one table (as a gentleman's rule followed by all processes) to serialize access to the database consisting of many tables. LOCK TABLE x; Manipulate many tables, perhaps not even including table x; UNLOCK TABLES; My question is really whether MySQL might do some strange optimizations ... or somehow buffer the middle query so that it completes after the UNLOCK. Thanks, Dave.
Re: Lock Tables Question
Once you issue a LOCK TABLES command, you may not access any tables not in the LOCK statement. You must lock *ALL* tables you will use, perform your updates, and then UNLOCK TABLES. I didn't know that. I reviewed the documentation. Thanks. OK, then my only remaining question is how many tables I can lock in a single SQL statement. I'm guessing no practical limit (i.e. thousands). What is the maximum length of a MySQL statement, anyway? Thanks.
Re: Data security - help required
On 5/15/07, Ratheesh K J [EMAIL PROTECTED] wrote: Hello all, I have a requirement of maintaining some secret information in the database. And this information should not be visible/accessible to any other person but the owner of the data. Whilst I know that encryption/decryption is the solution for this, are there any other level of security that I can provide to this? Which is the best security technique used in MySQL to store seceret information. PS: Even the database admin should not be able to access anybody else's information mcrypt is the right way to go. The security goals as you've stated them are quite hard to accomplish. For example, if the data is that sensitive and if a traditional block cipher is used, the key has to be hanging around somewhere in order to encrypt the data. Public/private key encryption would solve this issue. I have not investigated whether mcrypt supports this. But in any case the public key would be used to encrypt the data going into the database, and the private key would be used to examine the data.
Re: Memory Problems
On 5/15/07, Mathieu Bruneau [EMAIL PROTECTED] wrote: Hi, yeah, apparenlty you're running into the 32 bits memory liimt. Note thta some memory is allocated for the OS so you don't even have the full 4GB of ram you can technically adressesed. The 64 bits os would increase this limit to 64gb++ (on 64 bits hardware) The the OP: be advised that on a typical x86 system, even with 4GB of memory, the OS is designed not to be able to allocate more than 4GB to an individual process. I know this is counterintuitive, but this is the situation.
Re: Which is a better design?
On 5/9/07, James Tu [EMAIL PROTECTED] wrote: The database server and the web server are on separate machines. Table A contains a record for each user. Let's say Table B contains 'relationship' information. They can be of type 'friend' or 'family'. If a user knows another user, this relationship would be kept in this table, along with the type of relationship. Table B can get big. 10,000's or maybe 100,000's. I'm doing a query in PHP and want to end up with two arrays. One for type friend and one for type family. Which is better: (Method 1) Do ONE query for all the records that meet a certain criteria (let's say 'active'). Then use PHP to loop through the results and put each record into either the friend array or the family array. (Method 2) Do TWO queries. One just for friend. Loop through the records and put into friend array; Then do another query for family...and loop through again. In general, you don't want to introduce arbitrarily large result sets into PHP. PHP is fast, but there are memory limits and speed of iteration limits. In general, you want to structure things so that MySQL returns exactly the results you need, and in the order you need. In general: a)Check your database design to be sure that the queries you are interested in are O(log N). If not, make them that way, by rethinking your database design and/or adding indexes. b)See if you can get all the data you want in one query. In the example you gave, I think the WHERE clause syntax will allow checking for certain of an enumerated type, i.e WHERE (X=3 OR X=5) ... that kind of thing. So, retrieving friends and family in one query shouldn't be a problem. Two queries should not be required. Here is what you need to remember: a)Designs that aren't O(log N) for the queries you are interested in often catch up with you as the database grows. b)There is a speed hierarchy involved. PHP is the slowest of all, so if you loop over records in PHP it needs to be a guaranteed small set. MySQL takes a one-time hit parsing the SQL statement, but after that it can operate on the database FAR faster than PHP can. In general, let MySQL do the work, because it can do the sorting, filtering, etc. FAR faster than PHP. Dave.
Re: Which is a better design?
On 5/10/07, James Tu [EMAIL PROTECTED] wrote: I think b/c of the way the tables are designed, I have to perform multiple queries, unfortunately. Hi James, My suggestion to you would be that if you have a situation you don't believe you can handle in one query, post all the details to the MySQL list and let others take a whack at it. I've not yet encountered a situation where the database can't be designed for one query results. I also believe that MySQL has temporary table functionality: http://www.xaprb.com/blog/2006/03/26/temporary-table-subtleties-in-mysql/ http://www.devwebpro.com/devwebpro-39-20010817Temporary-Tables-With-MySQL.html I don't know how this works (I guess I should read the manual), but I think this would give you the ability in many cases to have MySQL (rather than PHP) do the heavy lifting. It will be much more efficient in MySQL than in PHP. Good luck, Dave.
Re: Which is a better design?
On 5/10/07, James Tu [EMAIL PROTECTED] wrote: David: I definitely can get the result set using one query, but what I do with the result set has me thinking about breaking it up into two queries. Technical Details Omitted Ah, OK, I misunderstood. You want to (get two results, each of which is useful individually) rather than (issue two queries, then iterate in PHP to combine the query results). Two queries seem fine in that case. Just one caution: be aware that another process (such as a web user) can sneak in in between your two queries and modify the database and render the two sets of query results inconsistent with one another. To give you an example, suppose you issue three queries in order (I'm going to botch the syntax here): SELECT COUNT(*) FROM USERS; (call this A) SELECT COUNT(*) FROM USERS WHERE IDX = 10; (call this B) SELECT COUNT(*) FROM USERS WHERE IDX 10; (call this C) It is very possible (in the presence of other simultaneous database activity) that A != B + C. It depends on the application whether this is significant. Table locking is the easiest way to prevent this if it matters. Dave.
Re: FW: MySQL patches from Google
On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. Because of the volume of identical machines, reliability was critical, and Google had a certain flavor of the Linux kernel that they had tested and tuned. I wouldn't be surprised to see Google do the same thing with MySQL. For use internally, they would make some tweaks. What are they using MySQL for? Any massively parallel deployments?
Re: FW: MySQL patches from Google
On 4/25/07, mos [EMAIL PROTECTED] wrote: At 02:36 PM 4/25/2007, you wrote: On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. So you're saying they had a MySQL database on the same machine as the webserver? Or maybe 1 webserver machine and one MySQL machine? I would have thought a single MySQL database could handle the requests from 25-50 webservers easily. Trying to maintain 2000 copies of the same database requires a lot of disk writes. I know Google today is rumored to have over 100,000 web servers and it would be impossible to have that many databases in sync at all times. When I read the article some years ago, I got the impression that it was a custom database solution (i.e. nothing to do with MySQL). If you think about it, for a read-only database where the design was known in advance, nearly anybody on this list could write a database solution in 'C' that would outperform MySQL (generality always has a cost). Additionally, if you think about it, if you have some time to crunch on the data and the data set doesn't change until the next data set is released, you can probably optimize it in ways that are unavailable to MySQL because of the high INSERT cost. There might even be enough time to tune a hash function that won't collide much on the data set involved so that the query cost becomes O(1) rather than O(log N). You can't do that in real time on an INSERT. It may take days to crunch data in that way. My understanding was the Google's search servers had custom software operating on a custom database format. My understanding was also that each search server had a full copy of the database (i.e. no additional network traffic involved in providing search results). As far as keeping 100,000 servers in sync, my guess would be that most of the data is distilled for search by other machines and then it is rolled out automatically in a way to keep just a small fraction of the search servers offline at any one time.
Re: Millisecond time stamp
On 4/17/07, John Comerford [EMAIL PROTECTED] wrote: Hi Folks, A) One character string contain a string I build with the date and milliseconds tagged onto the end b) Two separate fields Field1 TimeStamp Field2 Milliseconds I am leaning towards approach B, but saying that it's more gut instinct than anything else. My questions are: 1) Is there some a better way to achieve what I want ? 2) Which of the two methods above would/do you use ? What you are looking for is the notion of a server-globally-unique-identifier (SGUID). You want something that can't occur again. Milliseconds isn't precise enough. You want microseconds or nanoseconds. You also need to consider these things: a)With PHP (assuming web access), multiple processes can be active at the same time. Thus, even with microseconds, it is possible for two different processes to get exactly the same timestamp (especially since the microseconds counters are typically updated only on hardware timer interrupts, which don't occur every microsecond). Thus, you need more than just time to ensure uniqueness. b)It is helpful if the string sorting order of the field is also the time order. The best approach I've found is to use the following fields, fixed length, concatenated: a)Integer time (seconds). b)Microtime (microseconds or nanoseconds). c)PID of the process. with the provision that the code must execute a spinlock to wait for the microtime to change (otherwise, the next call could get the same identifier, or--although no practical system is this fast--the current process could end and another could run with the same PID and get the same time. Using the three fields above with the spin-lock, the string generated is guaranteed unique for the lifetime of the server (assuming that nobody tampers with the machine time). The result from above is guaranteed unique because no two processes can have the same PID at the same time. Here is some sample code: http://fboprime.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/sw/phplib/sguid.inc?rev=1.4content-type=text/vnd.viewcvs-markup You can figure out how to navigate from the above to find the other include files referenced. If you have any questions and I don't seem to reply to something you post on this list, please write me at [EMAIL PROTECTED] and reply to the server's reply to make it through my SPAM filtering. I don't always watch this mailing list closely. Dave
Re: Millisecond time stamp
On 4/18/07, Tim Lucia [EMAIL PROTECTED] wrote: -Original Message- From: John Comerford [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 10:50 PM To: mysql@lists.mysql.com Subject: Millisecond time stamp Hi Folks, I am putting together a table to hold log entries. I was going to index it on a field with a type of TimeStamp. Unfortunately this will not suit my needs because I could have more than one log entry per second. As far as my (limited) knowledge goes I have two options to get around this: A) One character string contain a string I build with the date and milliseconds tagged onto the end b) Two separate fields Field1 TimeStamp Field2 Milliseconds WHY are you indexing the Timestamp? It should not be your primary key because, as you point out, it might allow duplicates. Even case B is not foolproof. If you're indexing it for searching, then your application should be prepared to handle multiple log records for any value. For uniqueness, add an auto_increment primary key column. That way, you can tell two+ log records apart even when they have the same timestamp. Tim - Dave Ashley's note: Tim's solution is better than the one I proposed if this is your only problem. In my applications, typically the need for unique identifiers comes up in may places, so I'm used to using that style of solution. However, adding an auto-increment primary key column will get the same effect with a lot less work. Also, it is probably more efficient due to the absence of a spin-lock.
PHP 4.X with 64-Bit Integers From MySQL
I have a box where I'm forced to use PHP before 64-bit integers became standard ... If I run a MySQL query where one of the fields returned is 64-bit integer, how do I get this into PHP as a string? My recollection is that in the PHP result sets it auto types so that it is an integer, and 64-bit integers will cause problems if one tries to assign into 32 bits. P.S.--Once into PHP as a string, I would use the bcmath library to manipulate it ... P.P.S.--One solution I would consider acceptable -- although I hope a better one exists -- would be to have a text field in the table that simply holds the string version of the 64-bit integer. If that is the best solution available, I'd be curious about how to populate that in a single operation on an INSERT with the value assigned by AUTOINCREMENT.
Collation Order in MySQL?
I'd like to have a table in MySQL with the key field being a 12-character text string consisting of digits and upper-case letters (minus the vowels and Y, to prevent accidental bad words), i.e. something like: XM39C6B4... When I do queries and get back result sets sorted on this text string, is it always safe to assume that: a)Digits come before letters? b)Letters are in ascending order, i.e. A before B, B before C, etc.? To be conservative, should I set collation order? Thanks.
Re: Best Practice: Timezones and web development
On 2/28/07, Chris McKeever [EMAIL PROTECTED] wrote: Does anyone have any resources, guides, insight into the best practice for storing date/time information when developing a custom web app? I am mainly concerned with how the TZ should be stored? Should it go in as UTC and the code accounts for the user TZ? How does one handle tracking the users Daylight Savings etc Best practice is that all times maintained in a database (or anywhere on the server) are UTC, and are only converted to local timezone and/or adjusted to daylight savings time as required to display data for a specific user. This means, for example, that Randy in California and Sven in Sweden (different users on the same system) will see the same record from a database displayed with different times (because the time is converted to their local timzone before display). Now, as far as the best way to implement the two paragraphs above (especially with DST), I have not a clue.
Re: Global Unique Identifiers
On 1/4/07, Daniel Kiss [EMAIL PROTECTED] wrote: Hi All, I'm designing a database where it is required for each row in a table to have a unique identifier accross a bunch of running MySQL servers on a network. I'm considering two options: 1. Have a two-column primary key, where the first column is a normal auto incremented ID field, the second identifies the database and contains a constant ID per server. Something like this: CREATE TABLE MyTable ( ROWID int unsigned not null auto_increment, DBID int unsigned not null, AnyData varchar(10) not null, PRIMARY KEY (ROWID, DBID) ); INSERT INTO MyTable (DBID, AnyData) VALUES (8, 'Any text'); 2. I would use a traditional one-column binary primary key populated by the built-in uuid() fuction of MySQL. Like this: CREATE TABLE MyTable ( ID binary(36) not null, AnyData varchar(10) not null, PRIMARY KEY (ID) ); INSERT INTO MyTable (ID, AnyData) VALUES (uuid(), 'Any text'); In my view both solutions have their adventages and disadvantages. The first is more optimal in storage space and speed, the second is easier to maintain, administer and query. And there is another aspect, which is needed to be tested, I guess... How fast is the second solution when I execute complex queries based on primary key relations? Does it pay to use the ease of the second solution? Anyone has any experience in similar problems? What is your opinion? Human readability is important in case something goes wrong, and I like the first solution better. I looked at the format of UUID in the MySQL documentation ... not all that human-friendly. In all the database code I've written (admittedly, all on a single server), I've always had a function (written in PHP) that returns server unique identifiers (and a globally unique identifier is along the same lines). They have typically been fairly long character strings that include the Unix time (seconds and microseconds) and the PID. I typically also spin-lock until the microtime changes--that way it is guaranteed that two processes can't have the same PID at the same time. I'd say go with (a)generation that you understand and control, and (b)human-readability in case you have to dissect it. The server-unique identifiers that I described above have the advantage that they double as timestamps. Just an opinion. Dave.
Re: Interview questions?
On 12/18/06, Peter Smith [EMAIL PROTECTED] wrote: Hello MySQLers, Since I'm mildly technical and I know how to spell orakle, I've been asked to write up some technical interview questions for a MySQL position at a startup. Can any of you help me out? Your apparent focus on doing what is best for the company will rob you of the opportunity to have fun. You need to include at least one question that the interview candidate has no reasonable hope of answering, just for your own amusement. How about: How do applications of the latin1_german1_ci and latin1_german2_ci collation differ? Why and how are dictionary sort order and phone book sort order in the German language different? Dave.
Re: Recommended Hardware Configurations
On 12/17/06, Mike Duffy [EMAIL PROTECTED] wrote: I am sure this question has probably been asked in this group before, but I would like to get an updated answer. If you were building your own boxes to run clustered MySQL servers, how would you configure the boxes? (This would of course be for an enterprise level database system.) I am looking for an optimal solution that balances cost and performance; and yes, I realize that is a very subjective standard. I once knew someone who was too cheap to buy a computer case, so he mounted a computer motherboard on a piece of plywood and used it. I don't recommend plywood, but for a cluster you may be able to stack cheap motherboards, power supplies, disk drives, and fans in a single larger metal enclosure (depends on how handy you are at this kind of fab in general). This is at the EXTREME low end of price. Even with 5G of RAM on each one, you may be able to average $600 - $700 per motherboard, all costs included. I'd start with a half-height rack enclosure, and see if you can develop a regular pattern to repeat within. Enterprise-grade servers are EXPENSIVE.
Re: Book Recommendation
On 11/27/06, Nicholas Vettese [EMAIL PROTECTED] wrote: I am looking for a book that will help me understand PHP/MySQL, and the way that they work together. My biggest problem is multi-valued selections, and INSERTING them into the database. A book with great examples like that would be a huge help. Also, any websites that could do the same would be great too. Thanks, nick Beginning Databases with MySQL Beginning PHP4
Compiled C-language CGI-BINs with MySQL
Has anyone ever written C-language CGI-BIN programs (to be executed from Apache) and which use the C-language interface of MySQL? Does it work OK? Thanks.
mysql_server_init(), C API, And Dying Processes
What happens if I use the C API of MySQL and the process dies (maybe a pointer error or something)? a)Will the connection be killed automatically? b)What about LOCK TABLES and similar statements that were issued ... will the locks clear automatically? Thanks.
Searching Fields for a SubString
Hi, I'm a little bit lost on MySQL statements. I am implementing a many:many relationship using a string (wrong way, I know, but I'm trying to keep the design simple and this is a trivial application). In a VARCHAR field, I will store a comma-delimited set of integers corresponding to the indices in another table. It might look like: ,1,2,9, to specify a link to records 1, 2, and 9. I'm not sure how to find, for example, all records where the text of a given field contains ,2,. I'm not interested in an efficient search, just a search. I know it will be O(N) unless indexing of some kind is used. My explanation above might be confusing, so let me give an example that will give the same answer ... Suppose I'm interested in searching a table for all records with last names that contain sh. How would I do that? (Different problem, same answer.) Thanks, Dave.
Re: Searching Fields for a SubString
Thanks for the help. As often happens in these situations, a little searching and experimentation got me a workable answer before any replies arrived. In my case, I was able to use the LOCATE operator, i.e. select idx, lname,schedalonerscs from usrs where LOCATE(,7,, schedalonerscs) != 0; I'm very sensitive to the observations of Peter Bradley. This is the first thing I've done with MySQL, and I didn't know in advance how fast it was or was not. On the page: http://fboprimedevel.e3ft.com the day view scheduler page has to load rather quickly. I was rather cautious to have a simple design for the database. Now that I understand it all a bit better, my next project might use MySQL the right way. Thanks, Dave. On 11/4/06, Dan Buettner [EMAIL PROTECTED] wrote: Dave, you could just use a LIKE statement SELECT * FROM table WHERE mystring LIKE %,2,% Of course if it is at the beginning or end, or the only item, it may look like '2' '2,' or ',2' and not ',2,' so this would work in all cases I think: WHERE (mystring LIKE %,2,% OR mystring LIKE 2,% OR mystring LIKE %,2 OR mystring = 2) Performance will be terrible if this grows much but for a trivial implementation it should work. Dan On 11/4/06, David T. Ashley [EMAIL PROTECTED] wrote: Hi, I'm a little bit lost on MySQL statements. I am implementing a many:many relationship using a string (wrong way, I know, but I'm trying to keep the design simple and this is a trivial application). In a VARCHAR field, I will store a comma-delimited set of integers corresponding to the indices in another table. It might look like: ,1,2,9, to specify a link to records 1, 2, and 9. I'm not sure how to find, for example, all records where the text of a given field contains ,2,. I'm not interested in an efficient search, just a search. I know it will be O(N) unless indexing of some kind is used. My explanation above might be confusing, so let me give an example that will give the same answer ... Suppose I'm interested in searching a table for all records with last names that contain sh. How would I do that? (Different problem, same answer.) Thanks, Dave.
Re: Database design question
On 8/7/06, James Tu [EMAIL PROTECTED] wrote: If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? Hi James, There are really two elements to this problem. The first element is how quickly MySQL can extract the messages you want from a large table. This requires that you know in advance the type of queries you're going to do (all messages by one user? all messages in a certain time window?) and be sure that these queries are approximately O(log N) rather than O(N) or worse. You will need to change your database design to fit the queries that you'll be doing. O(log N) queries would generally be characterized by the fields you're searching or sorting on being key fields (i.e. MySQL makes an index or BTREE or whatever it makes rather than having to go through the entire table linearly). The second element is data presentation. In developing web applications at least, if the first data on a page is displayed by the browser while the rest of the data is loading, the user perceives the load as being faster than it really is because the user is looking at the first data while the rest is loading. So, to make things more snappy, you might do more than one query to avoid large result sets. Dave.
Re: SELECT with Result Ordered by Minimum of Fields
On 8/6/06, Chris W [EMAIL PROTECTED] wrote: David T. Ashley wrote: Can I just write something like: SELECT * FROM mytable WHERE fieldofinterestvalue ORDER BY MIN(field1, field2) ASC; I think this will work.. SELECT *, IF(a-b 0,a, b) as SortField FROM table WHERE whatever ORDER BY SortField Question: Is there any way to get SortField (or a similar per-selected-record field) included with the SELECT output? The reason for this inquiry is that my PHP script that uses the SELECT results will also have to calculate SortField as MySQL did as part of the query. If MySQL has done it already, no need to do it a second time in the PHP script. Thank you.
SELECT with Result Ordered by Minimum of Fields
Hi, I have a table and I'd like to do a SELECT query with the result ordered by the minimum of two fields. I might also like to query by the minimum (but that is an easier problem, as I can just rephrase it as an OR). Can I just write something like: SELECT * FROM mytable WHERE fieldofinterestvalue ORDER BY MIN(field1, field2) ASC; or is it more complicated or impossible? Thanks, Dave.
RE: Newbie Locking Question
Nigel wrote: mod_php will persist the MySQL connection holding open any lock or syncronisation token obtained through any of the three methods : begin/commit, lock/unlock tables or get_lock/release_lock. PHP does ensure that even in the event of timeouts or fatal errors any shutdown handlers registered are still executed so it is possible to clean up properly whichever method is used. http://uk.php.net/manual/en/function.register-shutdown-function.php If you use php's pdo with transactions it perform a rollback for you on abort or completion. What is a pdo? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Locking Question
Hi, I'm doing a PHP application, and there are just a few instances where I need to do atomic operations on more than one table at a time and I can't express what I want to do as a single SQL statement. What I'm trying to guard against, naturally, is race conditions when more than one process is making modifications at a time, i.e. multiple simultaneous page hits. Because my application is so non-demanding in terms of server resources, what I decided to do is use only one simple locking schema throughout the code that locks every table at the same time, i.e. -- LOCK TABLE widgets WRITE, thingamabobs WRITE ... Make multiple interrelated table changes. UNLOCK TABLES -- In my estimation, what this should do is cause every other process to sleep briefly until the first one is through to the UNLOCK TABLES. I can't see going to a more complex locking model with such a simple application. Will the lock every table approach work as I intend? Anything I should watch out for? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Locking Question
Nigel wrote: If you can't or won't do this properly by using a transactional table and begin/commit at least look at using get_lock() based guard conditions which only lock a string leaving the database accessable. Whatever you do if you client is php install a shutdown handler to clean up any locks. Hi Nigel, Just one question: the documentation of get_lock() says that the lock is freed when a process terminates. Does this also occur with mod_php and persistent connections if a PHP script dies (say, due to fatal error or CPU limit timeout)? It isn't clear how mod_php works and why with persistent connections the LOCK TABLES locks wouldn't be freed but the get_lock() lock would. You were suggesting that I replace the LOCK/UNLOCK TABLES critical section with one implemented using get_lock()??? Or maybe you are suggesting something else ... Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Find Most Recent Autoincrement Index Assigned???
I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Working out Square Footage with Feet and Inches
There is a technique called unit cancellation that may serve you well. The technique is essentially to treat each unit as an orthogonal vector (orthogonal to all other units), so one unit can't be mixed with any other unit. The only way to convert is to multiply by various forms of 1. 1 is a fraction like (2.54 cm)/(inch) or (1 inch)/(2.54 cm), where the same thing is in the numerator and denominator. You can cancel (i.e. cross out) the same units appearing in the numerator and denominator. The rules free you from having to remember conversion factors. You can just multiply by various forms of 1. For example, if one has 1000 square inches and wishes to know how many square feet: (1000 inch * inch) (1 foot / 12 inch) (1 foot / 12 inch) = 6.944 foot * foot. Notice that the inch x 2 in the numerator cancel with those in the denominator. Because you have to cancel units (by crossing them out on paper), it would be impossible to use 12 alone as the converstion factor, as the units would come out to (foot * inch) rather than (foot * foot) if you did this. It is a system that helps to keep your head straight and prevent human mistakes. I hope there is a web page somewhere that describes this with illustrations. It is hard to do in a text e-mail. You can go surprisingly far with this technique if you forget conversion factors and need to get them again. For example, suppose I've forgotten how many kilometers per mile, but I do remember that there are 2.54 cm/inch ... (1 mile)(5,280 foot/mile)(12 in/1 foot)(2.54cm/1 inch)(1m/100cm)(km/1000m) = 1.6093 km. Dave. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 10:46 AM To: Shaun; mysql@lists.mysql.com Subject: RE: Working out Square Footage with Feet and Inches [snip] I have measurements of rooms stored in a table for each house in feet and inches, does anyone know how can I work out the square footage? [/snip] Convert to inches, multiply length * width, divide by 144 (one square foot) -- 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: Should we petition GoDaddy?
I am slowly considering leaving GoDaddy, who has a very good bandwidth and ok tech support (I have seen better but much much worse) and acceptable prices, but unfortunately does not support MySQL 5 and PHP 5 either. SNIP Who think we could make them make the right move and pretty quickly? I'm guessing that you are young and haven't had enough experience with idiots. Simply change vendors. GoDaddy will not bend to accommodate you. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[NEWBIE] How To Trim Database To N Records
Hi, I'm a beginning MySQL user ... I have a table of log entries. Over time, the entries could grow to be numerous. I'm like to trim them to a reasonable number. Is there a query that will, say, trim a table down to a million rows (with some sort order, of course, as I'm interested in deleting the oldest ones)? The sorting isn't a problem. I've just never seen an SQL statement that will drop rows until a certain number remain ... Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number Searches
On Wed, February 22, 2006 4:52 pm, CodeHeads wrote: I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? I'm potentially introducing confusion here, because I've never actually _used_ MySQL, but can't you do one of the following things: a)Store the number as a string with guaranteed uniqueness, then search by a string (which I think MySQL will do). In the case of an IP address, to make the (IP) - (STRING) mapping have no collisions, I think all you need to do is prepend with zeros to reach length three on each number, i.e. 192.168.0.10 would become 19216810. b)Store the IP as a 64-bit integer. I think MySQL will key on strings, right? Dave. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Complexity Limit Question [Newbie Question]
Hi, I have several tables linked in various ways so that an inner join is possible. However, at the same time and in the same SQL query, I'd also like to query by some field values in one of the tables. Two quick questions: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Thanks, Dave. --- P.S.--It might seem that I'm a lazy bum and unwilling to just try it. My situation is that I'm working on design documentation for the database and working through in my head whether everything can be done in approximately O(N) time. The book I have doesn't mention joins on more than two tables. I will get the database set up and try it soon. Thanks for your patience. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity Limit Question [Newbie Question]
On Tue, February 7, 2006 10:46 am, [EMAIL PROTECTED] wrote: David T. Ashley [EMAIL PROTECTED] wrote on 07/02/2006 14:03:04: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Yes, you can do multi-way joins, and people often do. My biggest is 3-way, but some people do at least 5-way. Beware that it is easy to specify operations which will heavily load the system if you are not careful. The constraints in the WHERE statement are *logically* and syntactically done on the huge table produced by the joins. However, the MySQL optimiser is not stupid and will perform the filter upstream of the JOIN where possible. Some experimentation and use of the EXPLAIN statement may be necessary to find the best ordering for queries. My thought process was that if all the fields involved--both in the JOIN and limits on any fields--were key fields, then the operation should be approximately O(log N). But, now that I think about it: a)A simple limit operation or search operation on a KEY field should be O(log N) (i.e. doesn't MySQL build index tables or something on key fields to get approximately that behavior?), BUT b)I think you are right about the caution ... just thinking about it, it isn't clear that a JOIN will be O(log N) when the only condition is something like (table1.a = table2.b). It seems that a limiting condition (sex=MALE or datebirthday1 and datebirthday2 or whatever) would have to be applied first to a key field (by the optimizer?) or else the JOIN would be something like O(N) or maybe even worse. Thanks for pointing out the EXPLAIN keyword. That may be helpful. I'd be curious on any perspective ... what computational complexity is an inner join with no other conditions (i.e. the only condition is table1.a = table2.b)? I'd guess it isn't O(log N). Well that concludes my newbie questions. Thanks for the help. I'll now begin using MySQL ... --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AUTOINCREMENT / UNIQUE Behavior [Newbie Question]
I remember in MySQL that you can define an integer table field as AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong, but everyone will know what I mean). In the life of a database where there are frequent additions and deletions, 2^32 isn't that large of a number. When the integer field reaches 2^32-1 or whatever the upper limit is, what happens then? Will it try to reuse available values from records that have been deleted? Or is it always an error? Thanks, Dave. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
