structure of whole table...
THANKS ALL for the tremendous response. It is really helping me a lot I have another query. Can the output of the DESCRIBE be stored somewhere ( in file)? I suppose the DESCRIBE query doesn't return the result set. Or is there any way to get the structure of whole table? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: structure of whole table...
Can the output of the DESCRIBE be stored somewhere ( in file)? You can store it where-ever you like. Mysql stores it in a binary file which you can't read (ie it's not like the my.cnf file). I suppose the DESCRIBE query doesn't return the result set. What are you after exactly? Using 'explain' doesn't return the results, it only shows you what mysql is going to do (whether it will use indexes or not for example). Or is there any way to get the structure of whole table? show create table 'tablename'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: structure of whole table...
On Friday 11 August 2006 08:25, [EMAIL PROTECTED] wrote: > Can the output of the DESCRIBE be stored somewhere ( in file)? mysql mydb 'describe table' > table.describe.txt ? > Or is there any way to get the structure of whole table? show create table? -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with updating table
I have a site with horse racing results and the results table consists of: Date, Time, Course, Horse, Odds, Result, Profit I have about 6 months results in there and obviously there are no unique fields. Each morning I imprt a new spreadsheet with the days racing selections without the odds and result field filled in. As the day goes on I use a page to update the results but when I update a race with horse that has run in the past it updates the new record and the old record as well. I now have 2 identical records as the data updates has overwritten the old record for the horses race before. Any ideas!?!?!?!? -- View this message in context: http://www.nabble.com/Problem-with-updating-table-tf2089249.html#a5758339 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with updating table
Sounds like you will have to change the structure of your table. I would add an auto-increment column at the beginning of the row and use that as the primary key. You can still select on the horse, course etc. like so SELECT * FROM horse_and_courses WHERE ORDER BY new_primary_key DESC LIMIT 1; This would return you the most recent record. The auto_increment column would be updated automatically during the import. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: wizard007 [mailto:[EMAIL PROTECTED] Sent: Friday, 11 August 2006 5:30 PM To: mysql@lists.mysql.com Subject: Problem with updating table I have a site with horse racing results and the results table consists of: Date, Time, Course, Horse, Odds, Result, Profit I have about 6 months results in there and obviously there are no unique fields. Each morning I imprt a new spreadsheet with the days racing selections without the odds and result field filled in. As the day goes on I use a page to update the results but when I update a race with horse that has run in the past it updates the new record and the old record as well. I now have 2 identical records as the data updates has overwritten the old record for the horses race before. Any ideas!?!?!?!? -- View this message in context: http://www.nabble.com/Problem-with-updating-table-tf2089249.html#a575833 9 Sent from the MySQL - General forum at Nabble.com. -- 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 naming convention
Hello everyone! I am looking for a standard naming convention for databases. For example: is it good to use tablenames in column names like: table => tb_id,tb_text,tb_name and such. Probably there is some kind of overall naming convention out there, looked on google and such but only found conventions that people personally liked but no standards. Thanks for any help :) 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]
problem with decimal part
Hi, I have a client that want's to store in a table the exact number that he get's from a file, i've used a DOUBLE data type (MySQL 3.2x), but I have the following problem: If the number is for example 9.0 mysql truncates and only stores 9 I tried to use the (M,D) for example as (6,5) but the problem is that mysql stores the number as 9.0 and I don't want that, I want the number to be the exact number I read from the file. Is there any way to fix that? I thought of using a VARCHAR data type. What do you think? Thanks -- Com os melhores cumprimentos Jorge Martins - Wemake, Tecnologias de Informação, Lda. Tel. 223744827 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
The example you give is a form of hungarian notation, wherein an abbreviation representing the type of the variable is the first part of the variable name. Hungarian notation is not generally considered good practice for a variety of reasons; it is usually unnecessary, it interferes with the readability of the code, and since there is no actual connection between the variable name and the type, thus there is no guarantee that the notation will be correct. (For example, if you declare a variable as one type in a C++ program and then later change the declaration, the type given in the variable name will then be incorrect. This happens a lot in practice.) Table names, column names, and database names all exist in distinct namespaces in MySQL. Thus, if you give a table name "Users" there is no chance of ambiguity if you also name a column in that or another table "users" so there is no advantage to be had by including "tbl" in the name. I like to name tables and columns so as to make the queries read most like plain English. Thus, for example, I might name a table "Users" which would contain columns for "username," "address," "telephone," etc. I got into a long argument with a good programmer I know about whether or not to name tables in the singular or plural, e.g. "Users" or "User." I was arguing for the plural and he insisted you should never name a table in the plural. I don't think it really makes much difference. Here is an explanation of how I like to name tables and columns. I'd like to hear what other people think of this. I don't like to include the table name in the column name. Thus, in the "Users" table you might have columns named "Address," "City," and "State" but it isn't good practice to name these columns "Users_Address," "Users_City" etc. If you do it this way your queries will look like "SELECT Name, Address, City FROM Users" or "SELECT Users.Name, Users.Address FROM ..." Compare that to "SELECT Users.User_Name, Users.User_City FROM ..." For tables which exist just to represent many-to-many relationships I like to name the tables with the names of the tables which are related joined by an underscore. For example, if I have a table "Users" and another table "Permissions" (storing perhaps different things a user can do, like "insert into accounts" or "update transactions") then the table showing which users have which permissions I would name "Users_Permissions." There is a good argument to be made for including the table name in the id field, such as "Users.User_ID" instead of "Users.ID." If the key columns are the same you can do a join with simpler syntax: SELECT Users.Name, Permissions.Name FROM Users LEFT JOIN Users_Permissions USING (User_ID) LEFT JOIN Permissions USING (Permission_ID); However, I still prefer to name primary key id columns just "id" and then name the corresponding foreign key columns in related tables after the singular form of the names of tables they are related to. E.g.: mysql> create table Users (id int(6) unsigned primary key, name varchar(32)); Query OK, 0 rows affected (0.08 sec) mysql> create table Permissions (id int(6) unsigned primary key, name varchar(32)); Query OK, 0 rows affected (0.01 sec) mysql> create table users_permissions (user int(6) unsigned default NULL, user int(6) default NULL, UNIQUE KEY x (user, permission)) SELECT Users.Name, Permissions.Name FROM Users LEFT JOIN Users_Permissions ON Users.id=user_permissions.user LEFT JOIN Permissions on permissions.id=user_permissions.permission; If you've never read Donald Knuth going on about Literate Programming you might check this out: http://www-cs-faculty.stanford.edu/~uno/ lp.html Just like with parenthesis styles, you can name database objects whatever you want and it will still work. But good nomenclature makes it all so much more clear. Douglas Sims [EMAIL PROTECTED] On Aug 11, 2006, at 4:08 AM, Barry wrote: Hello everyone! I am looking for a standard naming convention for databases. For example: is it good to use tablenames in column names like: table => tb_id,tb_text,tb_name and such. Probably there is some kind of overall naming convention out there, looked on google and such but only found conventions that people personally liked but no standards. Thanks for any help :) 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]
PHP script to simply manage tables
Hi. I've got three tables (users, books and news), and I would like to crete a web interface to manage their data (create items, modify items, delete items). Is there any PHP script to create it automatically (in the same way phpMyAdmin does, but with less functionalities)? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
Barry wrote: > Hello everyone! > > I am looking for a standard naming convention for databases. > > For example: is it good to use tablenames in column names like: > table => tb_id,tb_text,tb_name I've for a long time been using an uppercase notation for databases. Probably isn't the best system, but it's what I've worked with so far. That and naming the primary key on a table with the suffix ID. Foreign Keys have the same name as they do on primary keys. Simple, strong, ugly, and dignified -- John Meyer http://pueblonative.wordpress.com http://pueblonative.110mb.com/board -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert/replace question...
REPLACE is a special INSERT/UPDATE combination where you dont specify a filter, it uses the primary key. If no existing record exists, it INSERTs a new one, otherwise it UPDATEs an existing one. What you are looking for is the UPDATE command. - Original Message - From: "bruce" <[EMAIL PROTECTED]> To: Sent: Friday, August 11, 2006 12:28 AM Subject: insert/replace question... hi. i have the following that works... in that it does a replace on the row. replace into appTBL (appName, universityID) values(%s,%s)... i'm trying to figure out how to do the replace on the row if a given column is not equal to a certain value.. i've tried... replace into appTBL (appName, universityID) values(%s,%s) where foo != 66 and various combinations but i'm getting an error regarding the 'where' clause... i've searched the mysql site, as well as google, and can't quite figure out how to accomplish this.. is there another, more efficient/better way to resolve this... any thoughts/comments... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with decimal part
Unfortunately i think you ought to use a varchar string, i read from the mysql manual and do not found a numeric type that can have different decimals number on each register. "Jorge Martins" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] > Hi, > > I have a client that want's to store in a table the exact number that he > get's from a file, i've used a DOUBLE data type (MySQL 3.2x), but I have > the following problem: > > If the number is for example 9.0 mysql truncates and only stores 9 > > I tried to use the (M,D) for example as (6,5) but the problem is that > mysql stores the number as 9.0 and I don't want that, I want the > number to be the exact number I read from the file. > > Is there any way to fix that? I thought of using a VARCHAR data type. What > do you think? > > Thanks > > -- > Com os melhores cumprimentos > Jorge Martins - Wemake, Tecnologias de Informação, Lda. > Tel. 223744827 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade returning an error
Hi, I upgraded one slave server from 4.0.23 to 5.0.24, and when I run mysql_upgrade I got the following error : ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' what can I do ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: insert/replace question...
Hi, > REPLACE is a special INSERT/UPDATE combination where you > dont specify a filter, it uses the primary key. If no existing record > exists, it INSERTs a new one, otherwise it UPDATEs an existing one. Just a quick note - REPLACE does not do any UPDATE. It is a combination of DELETE (if the record exists) and INSERT. Also, it does not have to be the primary key - any UNIQUE index will do. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with decimal part
On Fri, 2006-08-11 at 10:30 +0100, Jorge Martins wrote: > Hi, > > I have a client that want's to store in a table the exact number that he > get's from a file, i've used a DOUBLE data type (MySQL 3.2x), but I have > the following problem: > > If the number is for example 9.0 mysql truncates and only stores 9 > > I tried to use the (M,D) for example as (6,5) but the problem is that > mysql stores the number as 9.0 and I don't want that, I want the > number to be the exact number I read from the file. Technically 9, 9.0 and 9.0 are exacatly the same _number_. > Is there any way to fix that? I thought of using a VARCHAR data type. > What do you think? Would be the way to go as what your after is the origional string value. Why do you need exactly the same string? MySQL is pretty good at auto casting so you can still treat the field as a number in most cases and see http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html for functions to use in the other cases. mark > Thanks > > -- > Com os melhores cumprimentos > Jorge Martins - Wemake, Tecnologias de Informação, Lda. > Tel. 223744827 > MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access mySQL database across Internet
At the moment our mysql server hosted by an ISP, is updated every second by a program running on our local Internet connection from our own office network. We have seen no performance issues from our websites hosted by the same ISP running our mySQL server. My question is because of this remote update, would we see a problem if we queried the mysql database from webpages from another ISP ? > Date: Thu, 10 Aug 2006 16:09:29 -0500> From: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> Subject: Re: Access mySQL database across Internet> CC: > mysql@lists.mysql.com> > A good point to make here is that averages are > generally a poor> calculation to use when planning for load, since your > systems have to> handle the peaks as well as the average. I know this, > fought a number> of fights over it at my last job - don't know why I just > quoted you> averages!> > If 99% of your 1000 hits occur between 7:59 AM and > 8:01 AM (some sort> of workday login validaton system perhaps ) then > obviously you need to> handle as many as 500 a minute or more. So it may > vary from average> quite a bit based on your peak times - that will be good > information> to research as part of your planning process.> > Dan> > > On > 8/10/06, Dan Buettner <[EMAIL PROTECTED]> wrote:> > No, 1000 hits / day isn't > terribly high traffic, if you're serving> > pretty normal "stuff". That > averages out to a hit every 32 seconds or> > so during the course of an 8 to > 5 business day, a hit every 86 seconds> > or so in a full day. No problems > at that level.> >> > When you get to 1000 hits / minute, then you're talking > high traffic!> >> > Dan> >> > On 8/10/06, Neil Tompkins <[EMAIL PROTECTED]> > wrote:> > > What would you assume to be high traffic ? 1000 hits per day or > more ?> > >> > > > Date: Thu, 10 Aug 2006 13:05:11 -0500> From: [EMAIL > PROTECTED]> To: [EMAIL PROTECTED]> Subject: Re: Access mySQL database across > Internet> CC: mysql@lists.mysql.com> > Neil, in theory, this should work > fine, given sufficient bandwidth> between the two sites.> > In practice, if > it is a high traffic site generating a large number of> connections, or a > high traffic site pulling large pieces of data> (BLOBs or TEXT) from the > remote database, you may find performance to> be unacceptable.> > Latency to > establish a connection will be slightly higher, on a "busy> internet day" > possibly noticeable to end uers.> > You'll have to be sure you open up any > firewalls and also MySQL's own> access control to your remote server.> > Some > ISPs prohibit connections like this, so check with yours - would> be > disappointing for sure to build it all then get a call saying> "cease and > desist; read our acceptable use policy".> > Dan> > > On 8/10/06, Neil > Tompkins <[EMAIL PROTECTED]> wrote:> > Hi> >> > We have a mysql database > hosted with a IIS server on the same network accessible over the Internet. > Now we want to set-up a new website with another ISP therefore located in a > different datacenter.> >> > Has anyone had any experience of this. What > performance issues would I get if I access the database from the other > datacenter across the Internet ?> >> > Thanks> > Neil> >> >> > -- > MySQL > General Mailing List> For list archives: http://lists.mysql.com/mysql> To > unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]> _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: replace/temp tbl question...
Bruce, seems like this ought to work just fine. Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote: hi... can someone tell me if this should work in theory... basically, i'm looking to get a bunch of rows from my initial table, based on the value of a given column/field. i'd then like to do a complete replace/update on the values in the temp table. finally, i'd like to write everything in the replaced/updated tmp table back to the orignal tbl... i'm essentially using 3 steps to get this accomplished... create tmp table temp select a.appName, a.universityID from appTBL a where a.complete = 0 replace into temp (appName, universityID) values(%s,%s) replace into appTBL select t.appName, t.universityID from temp t thoughts/comments/etc... -thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access mySQL database across Internet
If it's a simple update, then it's doubful a once-a-second update would cause any problems. Databases are built to handle exactly that sort of work. On 8/11/06, Neil Tompkins <[EMAIL PROTECTED]> wrote: At the moment our mysql server hosted by an ISP, is updated every second by a program running on our local Internet connection from our own office network. We have seen no performance issues from our websites hosted by the same ISP running our mySQL server. My question is because of this remote update, would we see a problem if we queried the mysql database from webpages from another ISP ? > Date: Thu, 10 Aug 2006 16:09:29 -0500 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Re: Access mySQL database across Internet > CC: mysql@lists.mysql.com > > A good point to make here is that averages are generally a poor > calculation to use when planning for load, since your systems have to > handle the peaks as well as the average. I know this, fought a number > of fights over it at my last job - don't know why I just quoted you > averages! > > If 99% of your 1000 hits occur between 7:59 AM and 8:01 AM (some sort > of workday login validaton system perhaps ) then obviously you need to > handle as many as 500 a minute or more. So it may vary from average > quite a bit based on your peak times - that will be good information > to research as part of your planning process. > > Dan > > > On 8/10/06, Dan Buettner <[EMAIL PROTECTED]> wrote: > > No, 1000 hits / day isn't terribly high traffic, if you're serving > > pretty normal "stuff". That averages out to a hit every 32 seconds or > > so during the course of an 8 to 5 business day, a hit every 86 seconds > > or so in a full day. No problems at that level. > > > > When you get to 1000 hits / minute, then you're talking high traffic! > > > > Dan > > > > On 8/10/06, Neil Tompkins <[EMAIL PROTECTED]> wrote: > > > What would you assume to be high traffic ? 1000 hits per day or more ? > > > > > > > Date: Thu, 10 Aug 2006 13:05:11 -0500> From: [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> Subject: Re: Access mySQL database across Internet> CC: mysql@lists.mysql.com> > Neil, in theory, this should work fine, given sufficient bandwidth> between the two sites.> > In practice, if it is a high traffic site generating a large number of> connections, or a high traffic site pulling large pieces of data> (BLOBs or TEXT) from the remote database, you may find performance to> be unacceptable.> > Latency to establish a connection will be slightly higher, on a "busy> internet day" possibly noticeable to end uers.> > You'll have to be sure you open up any firewalls and also MySQL's own> access control to your remote server.> > Some ISPs prohibit connections like this, so check with yours - would> be disappointing for sure to build it all then get a call saying> "cease and desist; read our acceptable use policy".> > Dan> > > On 8/10/06, Neil Tompkins <[EMAIL PROTECTED]> wrote:> > Hi> >> > We have a mysql database hosted with a IIS server on the same network accessible over the Internet. Now we want to set-up a new website with another ISP therefore located in a different datacenter.> >> > Has anyone had any experience of this. What performance issues would I get if I access the database from the other datacenter across the Internet ?> >> > Thanks> > Neil> > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > Be one of the first to try Windows Live Mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script to simply manage tables
Thomas >Is there any PHP script to create it automatically (in the same way >phpMyAdmin does, but with less functionalities)? Mebbe theUsual will be helpful http://www.artfulsoftware.com/theusual.html, code at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html#theusual_php. PB - thomas Armstrong wrote: Hi. I've got three tables (users, books and news), and I would like to crete a web interface to manage their data (create items, modify items, delete items). Is there any PHP script to create it automatically (in the same way phpMyAdmin does, but with less functionalities)? Thank you very much. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.10.9/416 - Release Date: 8/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with decimal part
mark addison wrote: On Fri, 2006-08-11 at 10:30 +0100, Jorge Martins wrote: Hi, I have a client that want's to store in a table the exact number that he get's from a file, i've used a DOUBLE data type (MySQL 3.2x), but I have the following problem: If the number is for example 9.0 mysql truncates and only stores 9 I tried to use the (M,D) for example as (6,5) but the problem is that mysql stores the number as 9.0 and I don't want that, I want the number to be the exact number I read from the file. Technically 9, 9.0 and 9.0 are exacatly the same _number_. Not if you have to count the number os significant digits. I just think it's strange that in a DOUBLE(6,5) the number 9.0 stores 9.0, I think that this should only happen if you choose ZEROFILL option. 5 should be the max number of digits of the decimal part not the mandatory number of digits...or maybe not, i'm not very good at math Is there any way to fix that? I thought of using a VARCHAR data type. What do you think? Would be the way to go as what your after is the origional string value. Why do you need exactly the same string? MySQL is pretty good at auto casting so you can still treat the field as a number in most cases and see http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html for functions to use in the other cases. mark Thanks -- Com os melhores cumprimentos Jorge Martins - Wemake, Tecnologias de Informação, Lda. Tel. 223744827 MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
FWIW a couple of weeks ago I read of a good reason not to use uppercase letters in table column names. I can't remember what it was now, but it seemed like sense at the time! My personal preference is to always use lowercase and separate any words that need it with an underscore. Apart from that I agree with Douglas Sims that what is most readable is best. select user_email, user_address, user_postcode from users where user_id = %d; # Hmm select email, address, postcode from users where user_id = %d; # Less typing and more readable. James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
On Fri, 11 Aug 2006 17:00:35 +0100, James Harvard wrote > > need it with an underscore. Apart from that I agree with Douglas > Sims that what is most readable is best. > > select user_email, user_address, user_postcode from users where > user_id = %d; > # Hmm > > select email, address, postcode from users where user_id = %d; > # Less typing and more readable. Good thing nobody is grading readability of the code samples submitted here:-) Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access mySQL database across Internet
On Fri, 11 Aug 2006 15:02:12 +, Neil Tompkins wrote > At the moment our mysql server hosted by an ISP, is updated every > second by a program running on our local Internet connection from > our own office network. We have seen no performance issues from our > websites hosted by the same ISP running our mySQL server. That's fairly impressive, but I hope you mean that you only *consider* updating once per second, if actually necessary. It would be an awful lot of wasteful traffic if you actually had the two machines talking every seconed to transmit nothing. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
On Aug 11, 2006, at 6:00 PM, James Harvard wrote: FWIW a couple of weeks ago I read of a good reason not to use uppercase letters in table column names. I can't remember what it was now, but it seemed like sense at the time! My personal preference is to always use lowercase and separate any words that need it with an underscore. Apart from that I agree with Douglas Sims that what is most readable is best. select user_email, user_address, user_postcode from users where user_id = %d; # Hmm Not so readable, I agree, but great when you want to find all references to a particular database column in 1000s of lines of code! I have found this more important than readability (although there is no excuse for sloppy formating). select email, address, postcode from users where user_id = %d; # Less typing and more readable. In fact I would go a step further. Call the table tab_users (or something similar), so that it is easy to find all references to this particular object in your program. Also make sure structure and class members have unique names (prefix the names with a short form of the structure name). For example, think of how many fields are called 'flags' in a C program. It is extremely useful to be able to quickly find all references to a particular flags field. This also makes a program for outsiders easier to understand because it is possible to answer questions like "How is this field/column used?", but just doing a global search. - Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace/temp tbl question...
thanks for the reply dan... but is there a better/more efficent way...> thanks -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, August 11, 2006 8:13 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: replace/temp tbl question... Bruce, seems like this ought to work just fine. Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote: > hi... > > can someone tell me if this should work in theory... > > basically, i'm looking to get a bunch of rows from my initial table, based > on the value of a given column/field. i'd then like to do a complete > replace/update on the values in the temp table. finally, i'd like to write > everything in the replaced/updated tmp table back to the orignal tbl... > > i'm essentially using 3 steps to get this accomplished... > > create tmp table temp > select a.appName, a.universityID > from appTBL a > where a.complete = 0 > > replace into temp > (appName, universityID) > values(%s,%s) > > replace into appTBL > select t.appName, t.universityID > from temp t > > > thoughts/comments/etc... > > -thanks > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace/temp tbl question...
Well that's a different question! ;) Given a set of values, iterating through and updating the values in the original table would also work fine, eliminating the use of a temp table. As to whether that's more efficient - hard to say. Will depend on the size and structure of your original table, as well as the number of entries you're changing. Updates/replaces of single rows by unique keys are generally fairly inexpensive and quick. Doing the select back from the temp table could cause a table lock for a while, depending on number of rows. Upside is it could be transaction compliant. In your case if 'appname' is a unique value and indexed as such, it should be quick to update one record at a time. Looping through hundreds of records and updating each make take time but should keep the database reasonable responsive for other queries while that is happening. Downside is concurrency issues while your updates are occuring. Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote: thanks for the reply dan... but is there a better/more efficent way...> thanks -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, August 11, 2006 8:13 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: replace/temp tbl question... Bruce, seems like this ought to work just fine. Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote: > hi... > > can someone tell me if this should work in theory... > > basically, i'm looking to get a bunch of rows from my initial table, based > on the value of a given column/field. i'd then like to do a complete > replace/update on the values in the temp table. finally, i'd like to write > everything in the replaced/updated tmp table back to the orignal tbl... > > i'm essentially using 3 steps to get this accomplished... > > create tmp table temp > select a.appName, a.universityID > from appTBL a > where a.complete = 0 > > replace into temp > (appName, universityID) > values(%s,%s) > > replace into appTBL > select t.appName, t.universityID > from temp t > > > thoughts/comments/etc... > > -thanks > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More than 4 CPUs?
It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! -- Ed Pauley II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
At 7:05 pm +0200 11/8/06, Paul McCullagh wrote: >Not so readable, I agree, but great when you want to find all references to a >particular database column in 1000s of lines of code! Personally that's not something I've found myself wanting to do, but I can see some people may need to do that. I suppose it just proves that, when it comes to coding practices, Your Mileage *Will* Vary. :-) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than 4 CPUs?
Ed, I don't recall that being an issue, though you will likely need to tweak the values of your my.cnf file (thread_concurrency for example) to ensure you're taking advantage of all the cores. This is highly dependent on your situation - but as you increase number of CPUs, where you may start to see an issue is with the other subsystems on your host, such as disk and network. This is really more an issue if you're serving many different kinds of data from the same physical server - with lots of CPUs, you have the power to crunch, but your disks and network may not be able to keep the CPUs fed. To generalize (a lot), 8 CPUs and highly varied databases could introduce this as a problem for you - in this situation I would really load it up on RAM and have some very fast disks (good guidelines for database servers anyway). If you are setting up more of a single-purpose database server, then you can take advantage of multiple CPUs serving from the same cached data. Pretty general but hopefully helpful. Another thoughts would be to buy 2 smaller physical servers instead of 1 big one, and use MySQL replication for redundancy and backups. Could do load-balancing as well but that can be tricky. Dan On 8/11/06, Ed Pauley II <[EMAIL PROTECTED]> wrote: It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! -- Ed Pauley II -- 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: More than 4 CPUs?
From the articles I've read recently, 8 CPU's seems to be the point where the scalability ratio starts dropping. See the following articles for examples: http://developers.sun.com/solaris/articles/mysql_perf_tune.html http://corporateclub.mandriva.com/xwiki/bin/download/Main/Technology/mysql-performance-whitepaper.pdf I know there were some presentations at this years MySQL Conference that went over this (MySQL Performance Landscape comes to mind). You might be able to find a presentations on the mysql site. Regards, Scott Tanner On Fri, 2006-08-11 at 14:44 -0400, Ed Pauley II wrote: > It seems like I once read that you don't get any performance gains in > MySQL when you go above 4 CPUs per server. Is this correct? I was > considering a 4 dual-core CPU machine. Should I go with a 2 dual-core > machine instead? > Thanks! > > -- > Ed Pauley II > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than 4 CPUs?
--On August 11, 2006 2:44:34 PM -0400 Ed Pauley II <[EMAIL PROTECTED]> wrote: It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! WellThat may not be entirely true with Opteron's but with Intel's it is. IT's not just a MySQL thing, past the 4th CPU there just isn't much/any gain because you're bottlenecked on RAM and FSB. I've not personally run such a large MySQL machine but I don't know why there'd be a scaling issue assuming your application is designed properly and does it's best to not lock tables. I'd check to make sure you're CPU bound before investing in so much CPU. More likely you're I/O bound. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changed?
Hi, I recently updated to version 4.1.2 from a slightly older one like 4.1.11 or something. But I am now having a problem. Before,this query would work fine: Select distinctrow * from mytable order by mydatecolumn. mydatecolumn is a column of type date. This used to return the records in order of date, as it should. After the update however, it now returns them in random order. If i use a int type column to order by, then it works fine, it seems ony the date columns will no longer work. If i take distinctrow out of the query then it should work, but i have way to much to change to do that, and i still need to be able to select distinct rows when making a join. Why does it no longer work? Thanks. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.10.9/416 - Release Date: 8/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can a row be refered using row number?
The reason for this is probably because the original poster inherited a database without a primary key, and now needs to make edits to it. That happened to me with Oracle once, and I was lucky enough to be able to use Oracle's rowid, which is a unique reference to the row that is independent of the primary key. Unfortunately, MySQL has no such allowance. As others have stated, the best way to accomplish this is to add a primary key to the table, and hope you don't break any code downstream. It may be worth a look at the table description to make sure one doesn't already exist. On 8/10/06, Bartis, Robert M (Bob) <[EMAIL PROTECTED]> wrote: Why would you want to do this? As data moves around within the table the updates will be in error. Wouldn't it be easier to assign a unique key to each row, search for the key or unique set of information and update the resulting row? Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replace question...
hi... i've got an issue that i can't figure out... i'm trying to do a replace, where i only replace information from one tbl, into the targeted tbl, if the targeted tbl 'valid' item = 0; if the tbls are: fromTBL cat dog mouse tgtTBL cat dog mouse valid data from external app (fromTBL): foo1 = ['a1','b1','c1'] foo2 = ['a2','b2','c2'] foo3 = ['a3','b3','c3'] foo4 = ['a4','b4','c4'] foo5 = ['a5','b5','51'] data in the db/tbl (tgtTBL): db1 = ['a1','b1','c1',1] db2 = ['a2','b2','c2',0] db3 = ['a5','b5','51',1] foo represents the rows from the app. db represents the rows in the tbl. so the foo rows may or may not be in the db. the idea is to be able to do a 'replace' for the foo rows and to replace those who have a valid==0, and to insert any foo that isn't already in the tbl. the db/tbl is setup to automatically set valid=0 so if there's a way to create a list of the foo lists, and then to do a large replace using the executemany, then i could possibly do this in one step... i've tried to do this: rSQL = """replace into trgtTBL (cat, dog) select t.cat, t.dog from fromTBL t, trgtTBL where trgtTBL.valid = 0""" this approach should get all the rows of the fromTBL, and insert/replace them into the trgtTBL where the 'valid' item is 0... or at least that's what i'm trying to accomplish. this however, seems to update the rows regardless of the value of 'valid' there appears to be something subtle that i'm missing. any help would be seriously appreciated. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP script to simply manage tables
I know you asked for PHP, but you might look into Ruby on Rails. 'scaffold' can make a whole lot of stuff like that almost trivial... http://www.rubyonrails.org/ DÆVID > -Original Message- > From: thomas Armstrong [mailto:[EMAIL PROTECTED] > Sent: Friday, August 11, 2006 4:02 AM > To: mysql@lists.mysql.com > Subject: PHP script to simply manage tables > > Hi. > > I've got three tables (users, books and news), and I would like to > crete a web interface > to manage their data (create items, modify items, delete items). > > Is there any PHP script to create it automatically (in the same way > phpMyAdmin does, but > with less functionalities)? > > Thank you very much. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace question...
Bruce, looks like you're missing join criteria, and so you're selecting a Cartesian product into your trgtTBL ... this this on for size: replace into trgtTBL (cat, dog) select t.cat, t.dog from fromTBL t, trgtTBL where trgtTBL.valid = 0 AND t.cat = trgtTBL.cat AND t.dog = trgtTBL.dog Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote: hi... i've got an issue that i can't figure out... i'm trying to do a replace, where i only replace information from one tbl, into the targeted tbl, if the targeted tbl 'valid' item = 0; if the tbls are: fromTBL cat dog mouse tgtTBL cat dog mouse valid data from external app (fromTBL): foo1 = ['a1','b1','c1'] foo2 = ['a2','b2','c2'] foo3 = ['a3','b3','c3'] foo4 = ['a4','b4','c4'] foo5 = ['a5','b5','51'] data in the db/tbl (tgtTBL): db1 = ['a1','b1','c1',1] db2 = ['a2','b2','c2',0] db3 = ['a5','b5','51',1] foo represents the rows from the app. db represents the rows in the tbl. so the foo rows may or may not be in the db. the idea is to be able to do a 'replace' for the foo rows and to replace those who have a valid==0, and to insert any foo that isn't already in the tbl. the db/tbl is setup to automatically set valid=0 so if there's a way to create a list of the foo lists, and then to do a large replace using the executemany, then i could possibly do this in one step... i've tried to do this: rSQL = """replace into trgtTBL (cat, dog) select t.cat, t.dog from fromTBL t, trgtTBL where trgtTBL.valid = 0""" this approach should get all the rows of the fromTBL, and insert/replace them into the trgtTBL where the 'valid' item is 0... or at least that's what i'm trying to accomplish. this however, seems to update the rows regardless of the value of 'valid' there appears to be something subtle that i'm missing. any help would be seriously appreciated. thanks -bruce -- 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]
