Re: Need help in recreating .MYD files
Hi, please tell me the server uptime and also the master logs as show master logs; in mysql prompt. Yes u can restore data from the binlog if you have the binlogs. balaraju mandala wrote: Hi Dilip, it means i loosed the data, correct Dilip. is there any other way to gain that data, any binary logs etc? regards, bala -- Thanks Regards, Dilipkumar DBA Support ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in recreating .MYD files
Hi Dilip, I got two binary logs in Server. I don't know how to find server uptime? mysql show master logs; +--+ | Log_name | +--+ | localhost-bin.08 | | localhost-bin.09 | +--+ 2 rows in set (0.00 sec) if i ask for structure it is giving error message mysql desc userdetails; ERROR 1016 (HY000): Can't open file: 'userdetails.MYI' (errno: 145) if i create .MYD file using vi editor, MySql giving following message mysql desc userdetails; ERROR 1105 (HY000): File './everest/userdetails.MYD' not found (Errcode: 13) but the file is exist. tell me how i can get the data. regards, bala
Re: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
I believe the answer is that there isn't a way to fix the syntax for 3.23, because 3.23 simply doesn't support multi-table deletes. An alternative solution is needed. One option would be to do this programmatically. Collect the sectionids with a SELECT, then delete them with a separate query. Another option would be to select the good rows into a new table, then use this new table to recreate table A. Something like CREATE TABLE A2 SELECT A.* FROM A JOIN B ON A.sectionid = B.id; then DELETE FROM A; INSERT INTO A SELECT * FROM A2; or RENAME TABLE A TO A_old, A2 TO A; Michael it's work, thanks mike! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Certification examples
Hi I have bought the MySQL certification study guide (v 5.0) and am almost ready for the exams, does anyone know if the questions in the self study guide are very similar to the ones that you get in the exam? and is there anywhere else that i can get more questions like this for practice purposes? TIA Ade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case confusion
On 9 May 2006, at 02:22, Chris wrote: I would run this query: SELECT * FROM mytable WHERE LOWER(emailaddress) IN (SELECT LOWER(emailaddress) FROM mytable GROUP BY 1 HAVING COUNT(emailaddress) 1) This would show all duplicate emails, I would use the info this displays to choose which records to change/keep/delete. May not be the best way, but it would work. I'm giving it a go, but it's been running for about 4 hours at 60% CPU so far! Would it be quicker to tell it to use a case insensitive collation to locate the duplicates? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching problem
Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do. Any help is very appriciated ^_^ Many thanks for any replies Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case confusion
On 9 May 2006, at 13:12, Marcus Bointon wrote: I'm giving it a go, but it's been running for about 4 hours at 60% CPU so far! Would it be quicker to tell it to use a case insensitive collation to locate the duplicates? I managed to come up with a variation on my original attempt that got me what I needed. Thanks, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Barry schrieb: Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do. Any help is very appriciated ^_^ Many thanks for any replies Barry Ok found a solution: WHERE concated_field REGEXP '(^|)87682(|$)' Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Case confusion
Though I do not know what your schema is, I think you may try to decompose it into several BCNFs which can erase the redundancy of emailaddress. - Original Message - From: Marcus Bointon [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Chris [EMAIL PROTECTED] Sent: Tuesday, May 09, 2006 8:12 PM Subject: Re: Case confusion On 9 May 2006, at 02:22, Chris wrote: I would run this query: SELECT * FROM mytable WHERE LOWER(emailaddress) IN (SELECT LOWER(emailaddress) FROM mytable GROUP BY 1 HAVING COUNT(emailaddress) 1) This would show all duplicate emails, I would use the info this displays to choose which records to change/keep/delete. May not be the best way, but it would work. I'm giving it a go, but it's been running for about 4 hours at 60% CPU so far! Would it be quicker to tell it to use a case insensitive collation to locate the duplicates? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk
Re: Matching problem
Barry wrote: Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 WHERE concated_field LIKE '%87682%' See: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html BTW: 1. The performance of LIKE is not that good :-S 2. This doesn't sound like a good DB-Design, why don't you use two seperated fields for both numbers, or a m:n table if there are more possible entries? Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do. Any help is very appriciated ^_^ Many thanks for any replies Barry HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
try this: WHERE concated_field LIKE '87682%' or WHERE concated_field LIKE '87682next_' it is based on SQL-99, not using PHP - Original Message - From: Barry [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 8:49 PM Subject: Matching problem Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do.
Re: Matching problem
Wolfram Kraus schrieb: Barry wrote: Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 WHERE concated_field LIKE '%87682%' It would also give me Fields that have 987682 or 876825. That's not what i looked for but thanks anyway ;) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
彭一凡 schrieb: try this: WHERE concated_field LIKE '87682%' or WHERE concated_field LIKE '87682next_' would give me 876825 what i am not looking for. And i were also looking for next87682. So this doesn't work. But thanks anyway :) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
customer id - made the worst possible way
hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
On 9 May 2006, at 14:27, Wolfram Kraus wrote: WHERE concated_field LIKE '%87682%' No, because that would also match numbers that contain that sequence like '187682next32876825'. 2. This doesn't sound like a good DB-Design, why don't you use two seperated fields for both numbers, or a m:n table if there are more possible entries? Definitely. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to solve this problem?
彭一凡 schrieb: Hi There is a schema example below: (From A first course in database system) Product (maker, model,type) Pc (model, speed, ram, hd, rd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) The statement below seems wrong based on MySQL 5.0, though it is from the Solutions: (SELECT maker, model, type AS productType FROM Product) RIGHT NATURAL OUTER JOIN ((PC FULL NATURAL OUTER JOIN Laptop) FULL NATURAL OUTER JOIN Printer); Dose MySql support this statement? And how I can solve it? As far as i know you can only use JOINs with ON. So this would likely not work. If you tell what you want to be outputted we might help find a solution. barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
Hi Cor, Don't know if that function exists in MySQL... If you by any chance is using PHP you can do it by using ucfirst(str) But I quote the User Comment at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html from Tom O'Malley: quote Posted by Tom O'Malley on April 18 2006 1:16am An example of how to make the first letter in a string uppercase - analogous to UCFIRST SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName /quote /Johan C.R.Vegelin wrote: Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Marcus Bointon schrieb: On 9 May 2006, at 14:27, Wolfram Kraus wrote: WHERE concated_field LIKE '%87682%' No, because that would also match numbers that contain that sequence like '187682next32876825'. 2. This doesn't sound like a good DB-Design, why don't you use two seperated fields for both numbers, or a m:n table if there are more possible entries? Definitely. Well not my one though :P But have to work with it ^^ -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Marcus Bointon wrote: On 9 May 2006, at 14:27, Wolfram Kraus wrote: WHERE concated_field LIKE '%87682%' No, because that would also match numbers that contain that sequence like '187682next32876825'. WHERE concated_field LIKE '87682%' OR concated_field LIKE '%87682' Still poor performance ;-) [...] Marcus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to solve this problem?
First I would advice you to take a closer look at: http://dev.mysql.com/doc/refman/5.0/en/join.html It will answer your question. /Johan ??? wrote: Hi There is a schema example below: (From A first course in database system) Product (maker, model,type) Pc (model, speed, ram, hd, rd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) The statement below seems wrong based on MySQL 5.0, though it is from the Solutions: (SELECT maker, model, type AS productType FROM Product) RIGHT NATURAL OUTER JOIN ((PC FULL NATURAL OUTER JOIN Laptop) FULL NATURAL OUTER JOIN Printer); Dose MySql support this statement? And how I can solve it? BJUT Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
I'd be surprised if things actually turned out to be quite as simple as you describe. For example, let's say that your column actually contained book titles or names of people. Would you really want to see any of the following in your column: - A Diplomatic History Of The Un? (more likely: A Diplomatic History of the UN) - A.b. Mcdonald? (more likely: A. B. McDonald) - The Life And Times Of King George Iii? (more likely: The Life and Times of King George III) In any case, I don't think a simple SQL UPDATE will do what you want to do, at least not very easily. You'll almost certainly want some real programming statements to do the string manipulation that you need. You haven't said whether your data is already in tables or whether you are planning to load the data into new tables. You also haven't said whether the data is in all upper case, all lower case or in some form of mixed case. If the data is not already in tables, I'd be inclined to change the case of the data with a scripting language that was appropriate for your operating system and then load the corrected data into the tables. For instance, on Linux, I might write a bash shell script to reformat the data, which is presumably sitting in a flat file somewhere, then load the reformatted data into the tables. This gives you the option of choosing from several different scripting languages, some of which you may already know fluently. That could save you a lot of time. If the data is already in tables, you could unload it to a flat file, fix it with a shell script, and then reload it to the database. Or, you could write a User Defined Function (UDF) or Stored Procedure (SP) in order to update the existing values. Then you could call the procedure or function to do the necessary work at any time you found data with the wrong case. If you write an SP, you could pass the table name and column name to the procedure. Then, the procedure could do a loop that operated on every value in that column of the table. For each row, it could read the existing value, create a revised value using string manipulation techniques, then update the current value with the revised value. Creating the revised value would likely be the only tricky part and even that might not be very hard if it really were only necessary to convert the first letter of each word to a capital. If the code actually had to handle more complex cases like the ones I put at the beginning of this note, the code would be more complicated; it might even be impossible if the language you were using for the SP or UDF didn't have many string manipulation techniques. In that case, you might need to choose a different language or you could go back to unloading the data from the database, manipulating it outside MySQL, and then reloading it. That's all I'm going to say for the moment but if you decide to try a UDF or SP and can state what programming languages you are willing to use for the code - and whether the conversion is really as simple as capitalizing just the first letters of the words - I might be able to give you more specific suggestions. Unfortunately, I don't have a current version of MySQL and can't really install one so I can only talk hypothetically, based on UDFs and SPs that I've written in DB2 and on what I've seen in the MySQL manuals. I can't actually write you a simple UDF or SP for MySQL that would do at least the basic parts of the conversion you want. Maybe someone else on this mailing list has an example that you could have which is actually known to work in MySQL. Otherwise, you might only have hypothetical guidelines and manual articles to guide you as you try to write your UDF or SP. That can be time-consuming if you've never done any coding like that before. But it could be fun too if you are in the right frame of mind! -- Rhino - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 1:41 AM Subject: How to convert strings to 'proper case' ? Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
My God! Rhino, that was a very long and very good answer!! Impressive!! /Johan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by leads to an empty set.
Hi all, I have a strange problem and I can't really understand what's going on! mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; Empty set (0.00 sec) mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1); +-+++ | nid | sticky | created| +-+++ | 73 | 0 | 1141048224 | | 75 | 0 | 1141736038 | . | 93 | 0 | 1145039899 | | 97 | 0 | 1145189131 | +-+++ 51 rows in set (0.00 sec) I'm using a standar drupal installation: mysql desc node; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | nid | int(10) unsigned | | PRI | NULL| auto_increment | | type | varchar(32) | | MUL | || | title| varchar(128) | | MUL | || | uid | int(10) | | MUL | 0 || | status | int(4) | | MUL | 1 || | created | int(11) | | MUL | 0 || | changed | int(11) | | MUL | 0 || | comment | int(2) | | | 0 || | promote | int(2) | | MUL | 0 || | moderate | int(2) | | MUL | 0 || | sticky | int(2) | | | 0 || | vid | int(10) unsigned | | | 0 || +--+--+--+-+-++ 12 rows in set (0.00 sec) | version | 4.1.12 CentOS release 4.2 (Final) Any idea ? Am I doing something wrong ? Did I hit a bug ? Many thanks, -- GNU/Linux registered user #224950 Proud Egyptian GNU/Linux User Group www.eglug.org Member. Life powered by Debian, Homepage: www.foolab.org -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.gnu.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Pooling
Has anyone implemented connection pooling in C# .NET? On 5/8/06, romyd misc [EMAIL PROTECTED] wrote: What i meant by implementing connection pooling i meant if i need to do any code changes other than changes in connection string. Thanks, Romy On 5/8/06, Tim Lucia [EMAIL PROTECTED] wrote: I don't hear you need to implement connection pooling. Maybe, but I think you might still have errors under load, as you approach the maximum connection count in the pool. Tim -Original Message- From: romyd misc [mailto:[EMAIL PROTECTED] ] Sent: Monday, May 08, 2006 2:37 PM To: mysql@lists.mysql.com Subject: Connection Pooling Hi Everyone, I'm developing an application using C# .NET and mysql as database. It's a multithreaded application, we open a mysql database connection at the very beginning when the application is started and all the database requests use the same connection. But under stress or when more than one request try to access database, i get object reference errors. I don't get this error when frequency of database calls is low. Does it sounds like i need to implement connection pooling? I tried to lookup online, but couldn't find any help under mysql documentation. Can someone help me setting up mysql connection pooling with C#.NET. Thanks in advance, Romy
Re: Case confusion
Marcus, I just noticed that a key field (emailaddress) in my db is case sensitive when it should not have been, so now I've got a bunch of what are effectively duplicate records. I'm having trouble picking them out so I can manually merge/delete them before changing the collation on the field to be case insensitive. SELECT * FROM mytable group by lower(emailaddress) having count(emailaddress) 1 This is ok, but it only shows me the records with lower case addresses (I can't tell which case version is the correct one without looking at them) when I want to see the records with all cases, that is all of '[EMAIL PROTECTED]', '[EMAIL PROTECTED]' and '[EMAIL PROTECTED]'. I'm confusing myself with the case sensitivity and self-references! I think there are about 45 duplicates out of about 200,000. How can I find these pesky things? If you need to inspect the dupes, rather than eliminate them on some criterion without inspection, this will fetch them: SELECT LOWER(emailaddress), ... FROM mytable GROUP BY LOWER(emailaddress) having COUNT(*) 1; PB thanks, Marcus --Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 5/5/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in recreating .MYD files
On 5/9/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Dilip, I got two binary logs in Server. I don't know how to find server uptime? mysql show master logs; +--+ | Log_name | +--+ | localhost-bin.08 | | localhost-bin.09 | +--+ 2 rows in set (0.00 sec) if i ask for structure it is giving error message mysql desc userdetails; ERROR 1016 (HY000): Can't open file: 'userdetails.MYI' (errno: 145) if i create .MYD file using vi editor, MySql giving following message You don't need some file with that name. You need a special MySQL created data file with lots of headers and content. mysql desc userdetails; ERROR 1105 (HY000): File './everest/userdetails.MYD' not found (Errcode: 13) but the file is exist. Exists, but its invalid in all aspects (size 0, no headers, no content). tell me how i can get the data. You must understand that the .MYD file IS the data. So, to recover it, you'll have to just use another source (backup, binlog). A backup is usually an sql file, you'll have to recreate your table and populate it with the data on the backup, or, if your backup has the structures, you'll just have to use it to recreate the whole database. The binlog files logs all queries to the server, this way, it can recreate the changes on the database, but that requires a snapshot of the state where the binlog started. Another option would be to try and recover the lost files from the filesystem, but all the commands you issued and the simple fact that your system is running may have already overwritten the free space that your files used to take. And besides that is an expensive and tecnical solution. In simple terms, if you have no backups, your binlogs are outdated or rotated to an extend (old changes lost) and you can't recover the files from the filesystem. You're pretty much f up (don't get me wrong, It happened to me once, and now I have daily backups, a replicated server and binlogs saved daily too). -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting server time into mysql
Hi everyone, I am trying to capture my server time automatically using php and insert it in a mysql table. Can the timestamp or time data type capture this information automatically without having me code anything else?
Re: customer id - made the worst possible way
If you really want to change the customer ID, then you can always copy the entire table to another table with a primary key set. Then simply reference that primary key field and forget the prior one. -- Steve - Web Applications Developer http://www.sdwebsystems.com On Tue, May 9, 2006 9:33 am, [EMAIL PROTECTED] said: hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in recreating .MYD files
Thank you Daniel for reply. Just in curiocity i want ask u, how u r taking daily backups, just using 'mysqldump' or using any Tools. regards, bala
Sum of counts
Here comes a newbie question... I want to get a total of entries from four tables which all match a particular id. The result for the id I'm testing (21) should be 233. In my naivety, I thought something like this would work: select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 ...but no - I get about eight and a half million! I tried fiddling about with joins but got no better luck. In fact if I replace those +'s with commas I get four equal values of about 2.12 million. In the end I got the correct result like this: select (select count(*) from table_a where id = 21) + (select count(*) from table_b where id = 21) + (select count(*) from table_c where id = 21) + (select count(*) from table_d where id = 21) as total Two questions: 1 Is this the best way to do it? If not, what is? 2 This is fine in MySQL 5 (on my development platform), but 3.23 (on the live platform until the host upgrades us) doesn't support subqueries, so as an interim measure is there any better way than doing four separate queries and adding up the total in the PHP script? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If Yoda so strong in Force is, why he words not right order in put can? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by leads to an empty set.
- Original Message - From: Mohammed Sameer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 9:56 AM Subject: Order by leads to an empty set. Hi all, I have a strange problem and I can't really understand what's going on! mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; Empty set (0.00 sec) mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1); +-+++ | nid | sticky | created| +-+++ | 73 | 0 | 1141048224 | | 75 | 0 | 1141736038 | . | 93 | 0 | 1145039899 | | 97 | 0 | 1145189131 | +-+++ 51 rows in set (0.00 sec) I'm using a standar drupal installation: mysql desc node; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | nid | int(10) unsigned | | PRI | NULL| auto_increment | | type | varchar(32) | | MUL | || | title| varchar(128) | | MUL | || | uid | int(10) | | MUL | 0 || | status | int(4) | | MUL | 1 || | created | int(11) | | MUL | 0 || | changed | int(11) | | MUL | 0 || | comment | int(2) | | | 0 || | promote | int(2) | | MUL | 0 || | moderate | int(2) | | MUL | 0 || | sticky | int(2) | | | 0 || | vid | int(10) unsigned | | | 0 || +--+--+--+-+-++ 12 rows in set (0.00 sec) | version | 4.1.12 CentOS release 4.2 (Final) Any idea ? Am I doing something wrong ? Did I hit a bug ? If the two queries really are identical except that one has an ORDER BY added to it, this would appear to be a bug, although it would be a VERY strange one! But a bug isn't the _only_ possibility; in fact, I can think of three things that are probably much more likely. 1. Is there any possibility that a DELETE took place between the first query and the second? If the query without the ORDER BY returned 51 rows, then a DELETE executed by you - or someone unknown to you - removed all the rows, then the query WITH the ORDER BY executed, this would explain the behaviour you saw without any bug being involved. 2. Is there any possibility that the two queries took place against different tables or databases or systems? If the query without the ORDER BY ran against a version of the table that had 51 rows in it (perhaps the production version of the table) and the query with the ORDER BY ran against a different version of the table, maybe one that was empty (perhaps a test version of the table), this would explain the behaviour you saw without any bug being involved. This kind of thing could happen if you had two command prompts open and each was pointed at a different version of the table. If you were juggling several things at once, you might forget that the two prompts pointed at different systems and not realize that the queries had been done against different tables. 3. Is there any possibility that the query with the ORDER BY which you have given us in your email is not the one which returned 0 rows and that it is not identical to the other query that lacks the ORDER BY? The best approach for reporting query problems is to copy and paste the query from your MySQL environment into your email but some people simply type the query directly into the email. That opens the possibility that you typed the query inaccurately and may explain the problem. Frankly, I find it quite unlikely that ORDER BY would fail so I would strongly recommend that you consider the alternate scenarios I have suggested and rule those out first. If you can rule them out, then you may have encountered a real bug. Naturally, you should report that bug if you can satisfy yourself that it really IS a bug. -- Rhino Another -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customer id - made the worst possible way
Well whatever you do to fix the problem, I would practice on a copy of the database first, just to make sure that any alterations to the tables are exactly what you want to happen. When you are 100% sure you know how to solve the problem, then you know it is safe to make the changes to the live database itself. Do you have the show create table xyz /G output for the tables you want to alter please? Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Tue, 9 May 2006 [EMAIL PROTECTED] wrote: To: mysql@lists.mysql.com From: [EMAIL PROTECTED] Subject: customer id - made the worst possible way hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- 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: Need help in recreating .MYD files
On 5/9/06, balaraju mandala [EMAIL PROTECTED] wrote: Thank you Daniel for reply. Just in curiocity i want ask u, how u r taking daily backups, just using 'mysqldump' or using any Tools. I'm using mysqldump plus bzip2 to compress data for about 2 years now. Tried many tools, mysqldump scripted in bash (linux) or even in .bat (windows) was always better than any other tools. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customer id - made the worst possible way
I would drop the primary key off of your cust_id and add a new field like customer_id and relink using the old fk. I'd probably do this by creating a new table and doing an INSERT INTO SELECT FROM to populate the autoincrement and rename the table.. From there, add the fk's to your other tables and update those records. I'd probably keep the cust_id in your primary customer table for tracking purposes.. On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting server time into mysql
In the last episode (May 09), Alla-amin said: I am trying to capture my server time automatically using php and insert it in a mysql table. Can the timestamp or time data type capture this information automatically without having me code anything else? You can use the 'timestamp' type to automatially insert the current date/time when inserting or updating, or you can insert now() into a regular 'datetime' field. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html http://dev.mysql.com/doc/refman/5.0/en/datetime.html http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: novice on SQL
Hi John, right the problem boils down to this: sitetable tasktable ID taskidtaskid Changes ---- 11010100 21110120 SELECT sitetable.siteid, tasktable.prices FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: IDChanges 1 100 1 120 but what I need is the following format siteidprices1 prices2 (limits of 5) --- 1 100120etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). I'm beginning to accept that comment :( Thanks John Tony John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hi John, tried your suggestion but I can't get it to work. This is because I don't know how to set conditions in the following clauses (because there isn't any) and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] What values do you want for Changes1, Changes2, etc.? (How are you selecting for them.) Post your SQL here if you need further help. --J the result I've got was similar to the following (note the ID is pkey of another table) IDChanges1Changes2Changes3 - 1 10.010.0same as 1 10.310.3 1 12.212.2 2 31.031.0 3 1.021.02 3 4.94.9 thanks for your help anyway Tony tony yau [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo. Cheers John Hicks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] tony yau wrote: Hello, I can get a select result like the following: (SELECT ID,Changes FROM mytable WHERE somecondition;) IDChanges - 1 10.0 1 10.3 1 12.2 2 31.0 3 1.02 3 4.9 how can I get the above result sets into the following format (columns 'Changes1','Changes2',... are all from 'Changes') IDChanges1Changes2Changes3 (limits of 5) 1 10.010.312.2 2 31.0 3 1.024.9 I have got a method that works (I think) by first do a SELECT getting DISTINCT id values and then foreach of these ID I do another SELECT to get the Changes values and then just massage the display. Is there another way of doing this by using a single SQL query? There may be a simpler way, but this should work: select Table.ID, Table1.Changes as Changes1, Table2.Changes as Changes2, Table3.Changes as Changes3 from Table, Table as Table1, Table as Table2, Table as Table3 where Table.ID = Table1.ID and Table.ID = Table2.ID and Table.ID = Table3.ID and Table1.[condition for Changes1] and Table2.[condition for Changes2] and Table3.[condition for Changes3] order by table.ID --J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customer id - made the worst possible way
and this would be, in other words, the solution 2, right? If you really want to change the customer ID, then you can always copy the entire table to another table with a primary key set. Then simply reference that primary key field and forget the prior one. -- Steve - Web Applications Developer http://www.sdwebsystems.com On Tue, May 9, 2006 9:33 am, [EMAIL PROTECTED] said: hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- 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: customer id - made the worst possible way
Yeah, pretty much, but I would keep cust_id around and start over with a true autoincrement from 1. On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: and this would be, in other words, the solution 2, right? If you really want to change the customer ID, then you can always copy the entire table to another table with a primary key set. Then simply reference that primary key field and forget the prior one. -- Steve - Web Applications Developer http://www.sdwebsystems.com On Tue, May 9, 2006 9:33 am, [EMAIL PROTECTED] said: hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- 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: inserting server time into mysql
Thanks for your help,
How can I capture this information from a php form into a mysql table.
This is what I did:
1. I created the table and the user to access the database the table is in
create table staffs (
id int not null auto_increment primary key,
firstname varchar(20) not null,
lastname varchar(20) not null,
signin datetime not null
);
2. I created a php form to insert data into this table. The
form works but the datetime field isn't populated
html
head
titleStaff Detail/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
/head
body
?php
if(isset($_POST['add']))
{
include 'config.php';
include 'opendb.php';
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$signin = $_POST['signin'];
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', '$signin');
mysql_query($query) or die('Error, insert query failed');
include 'closedb.php';
echo New staff detail added;
}
else
{
?
form method=post
table width=400 border=0 cellspacing=1 cellpadding=2
tr
td width=100First Name/td
tdinput name=firstname type=text/td
/tr
tr
td width=100Last Name/td
tdinput name=lastname type=text/td
/tr
tr
td width=100Sign-In Time/td
tdinput name=signin type=text/td
/tr
tr
tr
td width=100nbsp;/td
tdnbsp;/td
/tr
tr
td width=100nbsp;/td
tdinput name=add type=submit id=add value=Submit/td
/tr
/table
/form
?php
}
?
/body
/html
Am I doing something wrong?
You mentioned that I can insert the now() function into a regular datetime
field, how can I do that?
In the last episode (May 09), Alla-amin said:
I am trying to capture my server time automatically using php and
insert it in a mysql table.
Can the timestamp or time data type capture this information
automatically without having me code anything else?
You can use the 'timestamp' type to automatially insert the current
date/time when inserting or updating, or you can insert now() into a
regular 'datetime' field.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
Re: customer id - made the worst possible way [SOLVED]
Yup! Got it.. Thanks guys, to all of you, for REALLY fast help! :) -afan Yeah, pretty much, but I would keep cust_id around and start over with a true autoincrement from 1. On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: and this would be, in other words, the solution 2, right? If you really want to change the customer ID, then you can always copy the entire table to another table with a primary key set. Then simply reference that primary key field and forget the prior one. -- Steve - Web Applications Developer http://www.sdwebsystems.com On Tue, May 9, 2006 9:33 am, [EMAIL PROTECTED] said: hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting server time into mysql
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', NOW());
I think if you alter the table and set a default value on signin to
NOW()
ALTER TABLE staffs CHANGE signin signin DATETIME DEFAULT 'now()' not
null';
then you could just to :
$query = INSERT INTO staffs (firstname, lastname) VALUES ('$firstname',
'$lastname');
-Original Message-
From: Alla-amin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 09, 2006 12:41 PM
To: mysql@lists.mysql.com
Subject: Re: inserting server time into mysql
Thanks for your help,
How can I capture this information from a php form into a mysql table.
This is what I did:
1. I created the table and the user to access the database the table is
in
create table staffs (
id int not null auto_increment primary key,
firstname varchar(20) not null,
lastname varchar(20) not null,
signin datetime not null
);
2. I created a php form to insert data into this table. The
form works but the datetime field isn't populated
html
head
titleStaff Detail/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
/head
body
?php
if(isset($_POST['add']))
{
include 'config.php';
include 'opendb.php';
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$signin = $_POST['signin'];
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', '$signin');
mysql_query($query) or die('Error, insert query failed');
include 'closedb.php';
echo New staff detail added;
}
else
{
?
form method=post
table width=400 border=0 cellspacing=1 cellpadding=2
tr
td width=100First Name/td
tdinput name=firstname type=text/td
/tr
tr
td width=100Last Name/td
tdinput name=lastname type=text/td
/tr
tr
td width=100Sign-In Time/td
tdinput name=signin type=text/td
/tr
tr
tr
td width=100nbsp;/td
tdnbsp;/td
/tr
tr
td width=100nbsp;/td
tdinput name=add type=submit id=add value=Submit/td
/tr
/table
/form
?php
}
?
/body
/html
Am I doing something wrong?
You mentioned that I can insert the now() function into a regular
datetime field, how can I do that?
In the last episode (May 09), Alla-amin said:
I am trying to capture my server time automatically using php and
insert it in a mysql table.
Can the timestamp or time data type capture this information
automatically without having me code anything else?
You can use the 'timestamp' type to automatially insert the current
date/time when inserting or updating, or you can insert now() into a
regular 'datetime' field.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting server time into mysql
The now() function would be used in the INSERT/UPDATE statement, not the
form field.
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', now());
mysql_query($query) or die('Error, insert query failed');
See the change to your variable $signin. Change that to now() and then
remove the form field signin.
To make your life a lot easier, you really should use the TIMESTAMP field.
ALTER TABLE staffs MODIFY `signin` `signin` timestamp default
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
This will ensure that you have the server's timestamp, not the client's
(timezones are a b**ch sometimes...)
J.R.
-Original Message-
From: Alla-amin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 09, 2006 12:41 PM
To: mysql@lists.mysql.com
Subject: Re: inserting server time into mysql
Thanks for your help,
How can I capture this information from a php form into a mysql table.
This is what I did:
1. I created the table and the user to access the database the table is in
create table staffs ( id int not null auto_increment primary key, firstname
varchar(20) not null, lastname varchar(20) not null, signin datetime not
null );
2. I created a php form to insert data into this table. The form works but
the datetime field isn't populated
html
head
titleStaff Detail/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
/head
body
?php
if(isset($_POST['add']))
{
include 'config.php';
include 'opendb.php';
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$signin = $_POST['signin'];
$query = INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', '$signin');
mysql_query($query) or die('Error, insert query failed');
include 'closedb.php';
echo New staff detail added;
}
else
{
?
form method=post
table width=400 border=0 cellspacing=1 cellpadding=2 tr td
width=100First Name/td tdinput name=firstname type=text/td
/tr tr td width=100Last Name/td tdinput name=lastname
type=text/td /tr tr td width=100Sign-In Time/td tdinput
name=signin type=text/td /tr tr tr td width=100nbsp;/td
tdnbsp;/td /tr tr td width=100nbsp;/td tdinput name=add
type=submit id=add value=Submit/td /tr /table /form ?php }
? /body /html
Am I doing something wrong?
You mentioned that I can insert the now() function into a regular datetime
field, how can I do that?
In the last episode (May 09), Alla-amin said:
I am trying to capture my server time automatically using php and
insert it in a mysql table.
Can the timestamp or time data type capture this information
automatically without having me code anything else?
You can use the 'timestamp' type to automatially insert the current
date/time when inserting or updating, or you can insert now() into a regular
'datetime' field.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
smime.p7s
Description: S/MIME cryptographic signature
Re: customer id - made the worst possible way [SOLVED]
I don't know what your application is like, but I generally recommend keeping your old table structure in tact as much as you can as to not break application functionality. If the old ID field was never referenced from within your application, then this might not be a problem. But to avoid major code re-writes, I suggest keeping the old ID field for pre-existing records and relying on the new primary key ID field for new ones -- at least until you decide to change all necessary application functionality to reference the new ID. -- Steve - Web Applications Developer http://www.sdwebsystems.com On Tue, May 9, 2006 12:47 pm, [EMAIL PROTECTED] said: Yup! Got it.. Thanks guys, to all of you, for REALLY fast help! :) -afan Yeah, pretty much, but I would keep cust_id around and start over with a true autoincrement from 1. On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: and this would be, in other words, the solution 2, right? If you really want to change the customer ID, then you can always copy the entire table to another table with a primary key set. Then simply reference that primary key field and forget the prior one. -- Steve - Web Applications Developer http://www.sdwebsystems.com On Tue, May 9, 2006 9:33 am, [EMAIL PROTECTED] said: hi to all, I have to redo a web site of one company and the structure of the current db is a little mess. one of them is customer id number. right now, customer table use as primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I really have no idea why previous developer made cust_id with letter C on the beggining of a number, and the number is made from date, (mdyHis) ?!?! What do you suggest to do: 1. take off letter C and keep the numbers, change cust_id to integer NOT NULL, add one customer with number 2000 and then apply auto_increment? 2. replace current Cxx with INT numbers and replace the cust_id in every other table where cust_id is foreign key? 3. something else? Thanks for any help! -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
- Original Message -
From: Chris Sansom [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 11:47 AM
Subject: Sum of counts
Here comes a newbie question...
I want to get a total of entries from four tables which all match a
particular id. The result for the id I'm testing (21) should be 233. In my
naivety, I thought something like this would work:
select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21
...but no - I get about eight and a half million! I tried fiddling about
with joins but got no better luck. In fact if I replace those +'s with
commas I get four equal values of about 2.12 million.
In the end I got the correct result like this:
select
(select count(*) from table_a where id = 21) +
(select count(*) from table_b where id = 21) +
(select count(*) from table_c where id = 21) +
(select count(*) from table_d where id = 21)
as total
Two questions:
1 Is this the best way to do it? If not, what is?
2 This is fine in MySQL 5 (on my development platform), but 3.23 (on the
live platform until the host upgrades us) doesn't support subqueries, so
as an interim measure is there any better way than doing four separate
queries and adding up the total in the PHP script?
The reason you are getting so many rows has nothing to do with the way you
are using the count(*) function and adding the different count() results
together. The problem is that you are doing your joins incorrectly. Whenever
you join tables, you need to specify what the tables have in common by
writing joining predicates; the number of joining predicates you usually
write is the number of tables being joined minus one. Therefore, since you
are joining four tables, you need three joining predicates: one to join the
first table to the second, one to join the second table to the third, and
one to join the third table to the fourth.
A joining predicate looks like this:
table1.col4 = table2.col3
In other words, there are two column names with an equal sign in the middle.
I think you are attempting to get the same result by saying a.id. = 21 and
b_id = 21 (etc.) but this is not having the effect that you want. In your
case, I think you need to change the original query to this:
select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = b.id
and b.id = c.id
and c.id = d.id
and a.id = 21
and b.id = 21
and c.id = 21
and d.id = 21
This says that:
- a row in table_a is joined to a row in table_b when the 'id' value in
table_a is identical (and non-null) to the 'id' value in table_b
- a row in table_b is joined to a row in table_c when the 'id' value in
table_b is identical (and non-null) to the 'id' value in table_c
- a row in table_c is joined to a row in table_d when the 'id' value in
table_c is identical (and non-null) to the 'id' value in table_d
- the final result should only have rows where the id columns in each of the
four tables contain 21.
The conditions you wrote, namely 'a.id = 21', 'b.id = 21', 'c.id = 21' and
'd.id = 21' are called local predicates, i.e. conditions that affect only
one table. In your case, you've said that out of all the rows in the result
set after the joins have been done, you only want rows where a.id, b.id,
c.id, and d.id are 21.
Unfortunately, you don't have any joining clauses so MySQL joins EVERY ROW
of the first table to EVERY row of the second table and EVERY ROW of the
second table gets joined to EVERY row of the third table and EVERY row of
the third table gets joined to EVERY row of the fourth table. This is called
a Cartesian product and is usually considered very bad news because it gives
you huge result sets in which most of the rows are joined to rows to which
they shouldn't be joined. I think you wrote this query on the assumption
that your local predicates would ensure that only the correct rows were
joined but, as you can see, that isn't the case.
If you try the query I gave you, you should see that it only joins rows when
the ids are equal; then, the local predicates ('a.id = 21', etc.) ensure
that you only get the joined rows you want, namely the ones that contain
21.)
This is a very common beginner mistake. Unfortunately, the MySQL manual
doesn't yet explain how to do joins very well; this is something that is
badly needed, in my opinion.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to restart mysql and apache
hi again, I have to restart as soon as possible mysql and apache on our web server (mandrake cooker 10) - since our admin is out of office for today. if someone can ive me some instructions, please? 1. what I have to restart first apaceh or mysql - or desn't matter? 2. I have sudo access (if I understand it correct, I have root privilages even I loged in as regular user) to web server - enough or I have to have root access to do restart? Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to restart mysql and apache?
hi again, I have to restart as soon as possible mysql and apache on our web server (mandrake cooker 10) - since our admin is out of office for today. if someone can ive me some instructions, please? 1. what I have to restart first apaceh or mysql - or desn't matter? 2. I have sudo access (if I understand it correct, I have root privilages even I loged in as regular user) to web server - enough or I have to have root access to do restart? Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
At 13:28 -0400 9/5/06, Rhino wrote: The reason you are getting so many rows has nothing to do with the way you are using the count(*) function and adding the different count() results together. The problem is that you are doing your joins incorrectly... In your case, I think you need to change the original query to this: select count(a.id) + count(b.id) + count(c.id) + count(d.id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = b.id and b.id = c.id and c.id = d.id and a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 Hi Rhino Many thanks for the very full and frank response, but sadly it didn't work. I do understand exactly what you said, and I even took it further, adding in: and a.id = c.id and a.id = d.id and b.id = d.id ...so that every table is thus related to every other one, but I'm /still/ getting that damned eight and a half million instead of the 233 I expect! I'm baffled by this, though the version I did with subqueries works very nicely (and it's simple enough to do four separate queries and add them together in the script for the older MySQL). -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Outside of a dog a man's best friend is a book. Inside of a dog it's too dark to read. -- Groucho Marx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting server time into mysql
Thanks guys, It worked - thank you all so very much.
Re: how to restart mysql and apache?
found this: /etc/rc.d/init.d/mysqld restart /etc/rc.d/init.d/httpd2 restart I think it should work? -afan On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi again, I have to restart as soon as possible mysql and apache on our web server (mandrake cooker 10) - since our admin is out of office for today. if someone can ive me some instructions, please? 1. what I have to restart first apaceh or mysql - or desn't matter? 2. I have sudo access (if I understand it correct, I have root privilages even I loged in as regular user) to web server - enough or I have to have root access to do restart? Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to restart mysql and apache?
On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi again, I have to restart as soon as possible mysql and apache on our web server (mandrake cooker 10) - since our admin is out of office for today. Are you sure you MUST restart those services? AFAIK you run Linux exactly because you don't wanna do that ;) if someone can ive me some instructions, please? 1. what I have to restart first apaceh or mysql - or desn't matter? Anyway: /etc/init.d/mysql restart /etc/init.d/apache2 restart (or apache, depends on your version) 2. I have sudo access (if I understand it correct, I have root privilages even I loged in as regular user) to web server - enough or I have to have root access to do restart? If you have enough privileges using sudo (all privileges to be exact), you can run sudo su and become root, but that's just a security flaw and you should blame your admin for that! Try it: sudo su -c /etc/init.d/mysql restart or simply sudo su and performe the commands listed for your first question. If it succeed, you restart the server, but then you have an insecure and bad configured system. Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to restart mysql and apache?
On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: found this: /etc/rc.d/init.d/mysqld restart /etc/rc.d/init.d/httpd2 restart I think it should work? Yeah, different systems, different locations, but the same purpose... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to restart mysql and apache?
On 5/9/06, Edward Vermillion [EMAIL PROTECTED] wrote: On May 9, 2006, at 1:50 PM, Daniel da Veiga wrote: On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi again, I have to restart as soon as possible mysql and apache on our web server (mandrake cooker 10) - since our admin is out of office for today. Are you sure you MUST restart those services? AFAIK you run Linux exactly because you don't wanna do that ;) Um... you run linux because you *can* do that. IE. you don't have to reboot the server to restart the services... ;) From another point of view, Yeah, true! :) -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to restart mysql and apache?
Thanks Daniel! -afan On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: found this: /etc/rc.d/init.d/mysqld restart /etc/rc.d/init.d/httpd2 restart I think it should work? Yeah, different systems, different locations, but the same purpose... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- 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]
comparing postgis with mysql
hello, I am relatively new in this field. I am designing a database to store the events extracted from the oceans. This project later demands of publishing data on web. I am not able to decide which database to use. Mysql with spatial extension or the postgis one. I would prefer to use windows platform. Can someone suggest or can provide me with some links that compare the two databases. Do you think Mysql has a better spatial elements handling capacity in comparison to postgis.? please reply soon. Thanks and Regards Parang Saraf [EMAIL PROTECTED]
#1191 - Can't find FULLTEXT index matching the column list
It looks like today is my day! :)
I FULLTEXT indexed my table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_name` (`prod_name`),
FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;
When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list
What am I doing wrong?
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by leads to an empty set.
On Tue, May 09, 2006 at 12:13:41PM -0400, Rhino wrote: - Original Message - From: Mohammed Sameer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 9:56 AM Subject: Order by leads to an empty set. Hi all, I have a strange problem and I can't really understand what's going on! mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; Empty set (0.00 sec) mysql SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1); +-+++ | nid | sticky | created| +-+++ | 73 | 0 | 1141048224 | | 75 | 0 | 1141736038 | . | 93 | 0 | 1145039899 | | 97 | 0 | 1145189131 | +-+++ 51 rows in set (0.00 sec) I'm using a standar drupal installation: mysql desc node; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | nid | int(10) unsigned | | PRI | NULL| auto_increment | | type | varchar(32) | | MUL | || | title| varchar(128) | | MUL | || | uid | int(10) | | MUL | 0 || | status | int(4) | | MUL | 1 || | created | int(11) | | MUL | 0 || | changed | int(11) | | MUL | 0 || | comment | int(2) | | | 0 || | promote | int(2) | | MUL | 0 || | moderate | int(2) | | MUL | 0 || | sticky | int(2) | | | 0 || | vid | int(10) unsigned | | | 0 || +--+--+--+-+-++ 12 rows in set (0.00 sec) | version | 4.1.12 CentOS release 4.2 (Final) Any idea ? Am I doing something wrong ? Did I hit a bug ? If the two queries really are identical except that one has an ORDER BY added to it, this would appear to be a bug, although it would be a VERY strange one! But a bug isn't the _only_ possibility; in fact, I can think of three things that are probably much more likely. Then I did hit a bug, I tried various combinations of queries! It's very strange: mysql SELECT n.nid FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; Empty set (0.00 sec) mysql SELECT COUNT(n.nid) FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; +--+ | COUNT(n.nid) | +--+ | 53 | +--+ 1 row in set (0.00 sec) I thought it might be a bug in the mysql client, I tried to connect remotely but it failed too. 1. Is there any possibility that a DELETE took place between the first query and the second? No! 2. Is there any possibility that the two queries took place against different tables or databases or systems? No! 3. Is there any possibility that the query with the ORDER BY which you have given us in your email is not the one which returned 0 rows and that it is not identical to the other query that lacks the ORDER BY? The best approach for reporting query problems is to copy and paste the query from your MySQL environment into your email but some people simply type the query directly into the email. That opens the possibility that you typed the query inaccurately and may explain the problem. I did a copy and paste from my terminal! Frankly, I find it quite unlikely that ORDER BY would fail so I would strongly recommend that you consider the alternate scenarios I have suggested and rule those out first. If you can rule them out, then you may have encountered a real bug. Naturally, you should report that bug if you can satisfy yourself that it really IS a bug. Looks like it is really a bug and honestly, I'm shocked! I know it's FLOSS as I'm a FLOSS developer myself and nothing is perfect but order by!! Thanks for your reply, I'll report it. -- GNU/Linux registered user #224950 Proud Egyptian GNU/Linux User Group www.eglug.org Member. Life powered by Debian, Homepage: www.foolab.org -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.gnu.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing postgis with mysql
There used to be a page at the MySQL site which showed comparison between MySQL and its competitors. For example, one page contrasted MySQL with DB2. These pages were lengthy and compared the product feature for feature. I just had a look but I'm having trouble finding the comparison pages. I'm not sure if they have been removed or whether the site has been redesigned so that you can find things more easily - which usually results in it being _harder_ to find things, in my experience :-) Have a look for yourself; maybe you can find those comparisons. I'm not sure if postgis was one of the databases compared to MySQL though. -- Rhino - Original Message - From: Parang Saraf [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 4:06 PM Subject: comparing postgis with mysql hello, I am relatively new in this field. I am designing a database to store the events extracted from the oceans. This project later demands of publishing data on web. I am not able to decide which database to use. Mysql with spatial extension or the postgis one. I would prefer to use windows platform. Can someone suggest or can provide me with some links that compare the two databases. Do you think Mysql has a better spatial elements handling capacity in comparison to postgis.? please reply soon. Thanks and Regards Parang Saraf [EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing postgis with mysql
Parang Saraf wrote: hello, I am relatively new in this field. I am designing a database to store the events extracted from the oceans. This project later demands of publishing data on web. I am not able to decide which database to use. Mysql with spatial extension or the postgis one. I would prefer to use windows platform. Can someone suggest or can provide me with some links that compare the two databases. Do you think Mysql has a better spatial elements handling capacity in comparison to postgis.? MySQL's current OpenGis implementation is of a part of the "simple" OpenGis spec. PostGis doesn't do speherical geometry either, AFAIK. There is a MySQL-PostGres comparison at http://monstera.man.poznan.pl/wiki/index.php/Mysql_vs_postgres. PB please reply soon. Thanks and Regards Parang Saraf [EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow query
I have a very simple table that looks like this: CREATE TABLE `contacts` ( `id` int(11) NOT NULL auto_increment, `fullname` varchar(100) default NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `user_id_2` (`user_id`,`fullname`), CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 It's a bit of a lopsided table in that of the 1,000,100 records in the db, 1,000,000 of them belong to user_id 1. But I wouldn't expect this to skew my results. I am writing a little paging server that retrieves pages of data using LIMIT and OFFSET. I'm really surprised by how slowly my queries are running on a relatively fast desktop machine. Records near the top of the list are fine: mysql SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 0; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab | ++--+-+-+--+ 1 row in set (0.03 sec) But as I move down the list, queries run slower and slower: mysql SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 10; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 726543 | Benny Abbott | [EMAIL PROTECTED] | 1 | bennyab | ++--+-+-+--+ 1 row in set (2.94 sec) mysql SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 50; ++---+--+-+--+ | id | fullname | email| user_id | nickname | ++---+--+-+--+ | 309543 | Jimmie Abbott | [EMAIL PROTECTED] | 1 | jimmieab | ++---+--+-+--+ 1 row in set (12.75 sec) EXPLAIN says: ++-+--+--+---+---+-+---++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+---+-+---++-+ | 1 | SIMPLE | contacts | ref | user_id,user_id_2 | user_id_2 | 4 | const | 506222 | Using where | ++-+--+--+---+---+-+---++-+ In other words, it *is* using an index for this query. Anyone have any advice for me? Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
