RE: How to Version Control a database
The method employed by the company I work for is to maintain a version controlled set of scripts, which can be used to create/update a database (many of these scripts are objects such as UDFs, Stored Procedures and so forth, which represent the 'software' layer within the database). A bespoke tool is used to apply these scripts in an appropriate order to a blank (new install) database, or an existing (upgrade) database - in the latter case extra 'update' scripts are used to perform necessary schema adjustments, etc. It depends really on what sort of work is being done - if it is mostly developmental/code/objects, then the above method seems to work well - if the work is mostly represented in data, then it won't really apply! Cheers, Matt -Original Message- From: Will Merrell [mailto:[EMAIL PROTECTED] Sent: 20 February 2005 14:00 To: Mysql Mailing list Subject: How to Version Control a database I have a project that involves several developers working on their own machines. Each has a local copy of the database on their own machine. Since we have some developers who develop while not connected to the network, we cannot use a common database. How can I version control the database so that changes are not lost or stepped on. Right now, we use mysqldump to dump the database and version the dump file, but this still has problems. Is there a better way? -- Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT DISTINCT?
This is certainly the first step. Also, if you want to insert only those rows which are not already present, you can use something akin to: INSERT INTO table1 SELECT * FROM table2 t2 LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for your data) WHERE t1.XYZ IS NULL (obviously put in the appropriate column names etc for your data structure!) Cheers, Matt -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 22:22 To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- 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: Parent-Child Relationship Question
Essentially, I think, you are asking about organizing hierarchical data. This is something which I always find tricky to wrap my head around, but, one approach I seen used successfully is to use (initially) two tables: 1. A table such as 'Places' which contains the actual data (or Locations, any suitable name really) 2. A second table which contains the 'meta-data' - in this case, the organization of the hierarchy. So. Places might be: PlaceID, Type, Name, Description Places_Hierarchy might be: PlaceHierarchyID, PlaceID, ParentID Thus, for the example below.. Places: 1 state Arizona .. stuff .. 2 country USA .. stuff .. 3 country Japan .. stuff .. Places_Hierarchy: 1 1 2 2 2 NULL 3 3 NULL Then, you can join the two together and organize things that way. There are a number of other things you can try - like moving the 'type' out into a lookup table and storing the ID in the hierarchy (allowing you to retrieve all places of a certain type, for instance). This is a situation in which views are (for me, anyway) sorely missed! Cheers, Matt -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: 02 July 2004 03:13 To: [EMAIL PROTECTED] Subject: Re: Parent-Child Relationship Question And here's a follow up question... After looking at my database from a fresh perspective, I'm now thinking of combining tables area and family into a single table. If I do that, it would make life so much simpler if I had TWO name fields, like this: ID | Name | ParentID | Parent Name az |Arizona|us| United States us | United States |kna | North America jpn | Japan |keu | Eurasia I could then slap a $mycode = 'az on a page and easily fill in its name and the name of its parent without fiddling with queries, joins, unions, etc. I know that duplicating names in two fields isn't the most elegant solution, but would create any major problems? Thanks. __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- 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: Optimising SQL Statements
Hi Martin, Speaking more from a general standpoint (I mostly work with MS SQL, but my home projects are MySQL - these days there is precious little time for those, though).. Assuming you have appropriate indexes on tables y and y2: 1. Truncate WILL be faster than delete, as it is a non-logged operation. Deleting is comparatively slow. If the goal is to remove 'old' records no longer in the Y set from X, it may be better to just delete those rows no longer in Y (see below), rather than the whole lot. 2. It may be better to do a left join to tabley (on y_id, and y_id IS NULL), although I do not know quite how mysql handles these things - for MS, where not exists / left join where null is quicker than not in. 3. If X is indexed, it may be faster to drop those indexes, bulk-insert data, and recreate them. This is often better when dealing with large sets of data, since it is SO much quicker to insert into an un-indexed table and then create an index, compared with inserting into the table and updating the index for each row. Hope this helps! Matt -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: 25 June 2004 17:43 To: [EMAIL PROTECTED] Subject: Optimising SQL Statements Hello All: I have a very simple test procedure PROCEDURE TEST_PROCEDURE AS BEGIN DELETE FROM X; COMMIT; INSERT INTO X (column1) SELECT Y.y_id FROM Y_TABLE Y WHERE Y.y_id NOT IN (select Y.y_id FROM TABLEY2); COMMIT; END; this very simple procedure takes 5 min 30 sec to complete its very basic delete and insert operations Any ideas on how I can optimise (I used truncate instead of delete and that helped big time) Vielen Danke, -Martin Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relating to the official business of Laconia Data Systems (LDS) is proprietary to the company. It is confidential, legally privileged and protected by law. LDS does not own and endorse any other content. (cell) 617-852-7822 (e) [EMAIL PROTECTED] (http)www.laconiadatasystems.com From: Ron McKeever [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Location of files Date: Fri, 25 Jun 2004 07:47:18 -0700 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc8-f18.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 07:50:32 -0700 Received: (qmail 11035 invoked by uid 109); 25 Jun 2004 14:47:44 - Received: (qmail 11013 invoked from network); 25 Jun 2004 14:47:43 - Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 207.217.120.74 as permitted sender) X-Message-Info: JGTYoYF78jH0d9Gs+XXJZ4+neItA1A7m Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Archive: http://lists.mysql.com/mysql/167906 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 25 Jun 2004 14:50:35.0116 (UTC) FILETIME=[C575A2C0:01C45AC3] Is there any benefit to having the .MYD files on one drive, and the .MYI on its own dedicated hard drive?? rm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- 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: Returning updated rows
There are a number of approaches which you could take to this. One option would be to 'pre-assign' rows to a server - so that each server has a distinct set of rows to work through. Another would be to use transactions to handle this, so that only one set of updates actually occur at a time (locking should prevent the processes from getting the same data, since the objects/rows held in the transaction would be locked out). Difficult to say without a better idea of what you are trying to achieve, though! Cheers, Matt -Original Message- From: Thomas Schwanhaeuser [mailto:[EMAIL PROTECTED] Sent: 19 June 2004 23:40 To: [EMAIL PROTECTED] Subject: Returning updated rows Is it possible that one can return the actual rows, which where affected by an update statement? What I want to do: I have n rows in a table which symbolize some work, which have several servers to do. For this, the table has a column called INPROCESS. In order that multiple servers can work on the transactions, I'ld like that each of them requests 1 row, which is not currently processed - and set's INPROCESS to YES. I have to avoid that two server grab the same row... My ideas was now something like UPDATE ... INPROCESS=YES WHERE INPROCESS=NO ... LIMIT 1 - but of course the application would also have to know which item it should process know. Thank you in advance for your help. Thomas -- 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: WHERE clause problem
Hmm. Bit Odd. However, I suspect the problem is that your 'where' isn't explicit enough: Where ( month(date) = month(now()) ) or ( month(date) = month(now())-1 ) Bear in mind that if month(now()) = 1 you will be looking for records in month 0! A better way to do this might be: WHERE month(date) BETWEEN month(now() - interval 1 month) AND month(now) Cheers, Matt -Original Message- From: mayuran [mailto:[EMAIL PROTECTED] Sent: 03 May 2004 16:15 To: [EMAIL PROTECTED] Subject: WHERE clause problem This is my table: mysql desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) 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: simplifying OR clauses
As others have said, you can use 'IN'. You could also use UNION (although I don't think I would, personally, for quite what you want!): SELECT word FROM word_table WHERE id = 1 UNION SELECT word FROM word_table WHERE id = 2 Etc. Assuming your version of MySQL supports the UNION operator! Another option (although less elegant than 'IN') is to create a temporary table with one column, 'word_id' or similar, and insert all of the IDs you wish to search for in there. You can then INNER JOIN to that table: SELECT word FROM word_table wt INNER JOIN id_table it ON it.word_id = wt.id It all depends on how you're doing this, and exactly what you want. :) Cheers, Matt -Original Message- From: Matthias Eireiner [mailto:[EMAIL PROTECTED] Sent: 26 April 2004 23:00 To: [EMAIL PROTECTED] Subject: simplifying OR clauses hi there, I have a basic question: how can I simplify multiple OR statements in a WHERE clause where I have only one column to which I refer? e.g. SELECT word FROM word_table WHERE id = 1 OR id = 34 OR id = 78 OR id = 8787 OR ... I thought I once read over something like this but I can't find it right now. Would be great if somebody could help me out! Thanks a lot in advance! regards Matthias _ Matthias Eireiner email: [EMAIL PROTECTED] www.bvcapital.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]
RE: Standard of Column Names
To me, this is entirely a matter of personal choice - and the important thing is to pick a standard and stick to it. :) I usually end up with a table called 'People' for arguments sake, which will have an abstract PK (auto increment int) called PeopleID (I always use the table name). I also capitalize each word (and all abbreviations), which is a habit from MSSQL programming - MySQL is case sensitive, which is worth remembering. I use underscores to indicate that a table is a 'glue' table - e.g. If each row in People can correspond to multiple rows in the table Jobs, and vice versa, I would create People_Jobs to describe the relationship between the two. There are a number of different methods that have been published, including 'Norwegian', I believe - and a bit of googling should turn up some info on these. :) Cheers, Matt -Original Message- From: Ronan Lucio [mailto:[EMAIL PROTECTED] Sent: 27 April 2004 15:46 To: [EMAIL PROTECTED] Subject: Standard of Column Names Hello, I´m doing the planing for an application that will use MySQL as database. So, I´d like to know your opinions about the standard for the column names. Supposing that I should create a table named car. Is it better to have either the column names (cod, name, description) or (car_cod, car_name, car_description)? Thanks, Ronan -- 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: What is your mysql debugging strategy?
I do something similar in my PHP applications - using an object wrapper to the SQL connection. That way, when an error occurs, the object automatically outputs the query, along with any error which was returned. Cheers, Matt -Original Message- From: Richard Bryson [mailto:[EMAIL PROTECTED] Sent: 27 April 2004 22:26 To: [EMAIL PROTECTED] Subject: Re: What is your mysql debugging strategy? I only use mysql with php so all I need is php code: $result = mysql_query($sql) or die(mysql_error()); This always tells me what I did wrong in the query. You could easily put together a very short script into which you just drop you query. THis would output the problem to the page. Dead simple and quick. Rich Joshua J. Kugler wrote: On Tuesday 27 April 2004 04:26 am, zzapper said something like: Even though I solved the following problem myself, I'd like to know what debugging strategy people use to solve problems when they get the dreaded Error in Mysql look in the manual Fire up MySQL CC and paste the SQL in there, and see what error it gives me. As in 'You have an error near' type messages. j- k- -- 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: Compound Primary Key question
As Jeremy says - it depends totally on what you want to do. If you have tables where there is no logical, unique way to identify that column (or the only way to do so is via a column you do not want to use for this purpose), then assigning a separate ID column as a PK makes sense. E.g: If you have a lookup table 'ItemDescription' which contains a list of description fields for items, it would make sense to make the table (ItemID, Description) with ItemID being an autoincrement primary key. However, in some other cases, a compound key will make more sense - for instance if you have a 'glue table' such as 'Item_Shop' which lists the items that are available in each shop: (ItemID, ShopID), then clearly, you cannot have a PK on either column alone (since there is a many to many relationship), so a compound PK is the only way to actually put a PK on the table (and uniquely identify a given row). One rule of thumb is: If there are two or more columns within a given table which together are the logical way to identify that row (and the way you would always join to the table), then use those as a compound key, otherwise assign a separate autoincrement column as a PK. Cheers, Matt -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 23 April 2004 23:51 To: Emmett Bishop Cc: [EMAIL PROTECTED] Subject: Re: Compound Primary Key question On Fri, Apr 23, 2004 at 03:40:43PM -0700, Emmett Bishop wrote: Quick question. In general, is it better to create compound primary keys or use an auto increment field to uniquely identify each record? Yes. It depends on your application and your data. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.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]
LAST_INSERT_ID() and Stored Procs
Hi all. Another hiccup along the happy road with MySQL 5.0! The last bit of a stored procedure I have just put together does this: -- insert cluster row INSERT INTO clusters (Name) VALUES (sName); SELECT LAST_INSERT_ID() INTO iNewClusterID; -- insert map row INSERT INTO map (X, Y) VALUES (iX,iY); SELECT LAST_INSERT_ID() INTO iNewMapID; -- insert map_clusters row INSERT INTO map_clusters (MapID, ClusterID) VALUES (iNewMapID, iNewClusterID); The last table mentioned, map_clusters has an FK on either column - each pointing to one of the other two tables. The procedure always fails on this insert, citing that there has been an FK violation. I've returned the values of iNewClusterID and iNewMapID out as parameters, and they always seem to be 0. However, I tried this: Create procedure id_test (out id int) Begin Select last_insert_id() into id; End And this correctly returns the last insert_id for the current connection. Most puzzling - I saw a closed bug from March on mysql.com which would have explained this, however, then, the above short procedure would have failed as well! Has anyone out there run into similar troubles? Cheers, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LAST_INSERT_ID() and Stored Procs
Oh. If only I'd tried ONE more thing before mailing that out! If anyone does have the same problem, the vital (missing) piece of information is that I was using MySQLCC. It seems to have problems with SPs unless you open a new query window after changing the contents of a procedure.. Thanks, Matt -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: 23 April 2004 02:08 To: 'MySQL List' Subject: LAST_INSERT_ID() and Stored Procs Hi all. Another hiccup along the happy road with MySQL 5.0! The last bit of a stored procedure I have just put together does this: -- insert cluster row INSERT INTO clusters (Name) VALUES (sName); SELECT LAST_INSERT_ID() INTO iNewClusterID; -- insert map row INSERT INTO map (X, Y) VALUES (iX,iY); SELECT LAST_INSERT_ID() INTO iNewMapID; -- insert map_clusters row INSERT INTO map_clusters (MapID, ClusterID) VALUES (iNewMapID, iNewClusterID); The last table mentioned, map_clusters has an FK on either column - each pointing to one of the other two tables. The procedure always fails on this insert, citing that there has been an FK violation. I've returned the values of iNewClusterID and iNewMapID out as parameters, and they always seem to be 0. However, I tried this: Create procedure id_test (out id int) Begin Select last_insert_id() into id; End And this correctly returns the last insert_id for the current connection. Most puzzling - I saw a closed bug from March on mysql.com which would have explained this, however, then, the above short procedure would have failed as well! Has anyone out there run into similar troubles? Cheers, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query help
I suspect you want 'IS NULL' rather than '= NULL'. :) I always find it best to think of NULL as undefined value rather than no value - which is why you need to check for it especially (using IS rather than = or other operators). Cheers, Matt -Original Message- From: Yonah Russ [mailto:[EMAIL PROTECTED] Sent: 21 April 2004 14:47 To: MySQL List Subject: Re: query help I got a response off the list suggesting writing a function to go over the query results- it's not hard but I'd rather do this in sql if possible. I came up with this: select books.bookid,books.title,copies.copyid from books left join copies on books.bookid=copies.bookid where copies.copyid=NULL; this didn't work even though without the where clause I got exactly what I wanted- the left join filled in the entries that didn't have copies with a null copyid. what did I do wrong? thanks yonah Yonah Russ wrote: Hi, I have two tables- books and copies every book has an id in the books table every copy of a book has the books id and a copy id in the copies table (1 row per copy) I want a list of all the books that don't have any copies meaning all the book id's in books that don't match any book id's in copies. how can I do this? thanks yonah -- 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: Slow Connection from PHP to MySQL 5.0
Yep - both the Win2k box and Linux box now identify each other by IP (access control and any host references in code) - but the problem persists. And yep again, I agree that the presence of windows is the other variable! However, I don't see why it would be so slow to connect! Perhaps it's a peculiarity of the MySQL 5 windows build currently available - but then I was hoping someone else would have seen the same issue! I'm going to try building MySQL 5 on another linux box at some point (then I can throw a backup of the database on there and try it), but would really like to keep it installed on Windows at the moment - because it's more convenient to debug (and restart)! Cheers, Matt -Original Message- From: [EMAIL PROTECTED] [mailto:mysql-list- [EMAIL PROTECTED] Sent: 15 April 2004 20:29 To: Matt Chatterley Subject: Re: Slow Connection from PHP to MySQL 5.0 do you have (mysql) access control on the mysql5 box that's based on hostname, rather than ipnumber? if so, how quickly does the inverse-map address of the linux box resolve? from a testing perspective, you have two variables with your setup. the first is mysql5 (vs. 4) but you also have windoz vs unix. part of the issue may simply be that windoz is that much slower setting up the connection. -- Original Message -- From: Matt Chatterley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Thursday, April 15, 2004 07:58:20 PM +0100 Subject: Slow Connection from PHP to MySQL 5.0 Hi Folks. I've seen similar questions asked both here, and via a google search - but I haven't found an answer which has helped yet - so heres hoping!! I run a Linux webserver which has PHP 4 installed, and am currently prototyping a design using MySQL 5.0-alpha on Windows 2K professional. The two servers are on different subnets of a LAN, but are able to talk to each other unrestricted (all TCP services I have tried work perfectly, e.g. SMTP, FTP). The problem is that connections from PHP to MySQL seem to take 4-5 seconds (after which any queries within the connection go through in normal lengths of time - only the connection time itself is long). Although I have a fully functional internal DNS server, I thought name resolution might be an issue. I amended my PHP pages (they inherit a global 'data connection' object which is used as a wrapper) to connect to the Win2k box by IP address - and the problem persists. Has anyone else encountered this, and are there any ideas? Connections from the same Linux box with PHP to another Linux box on the same subnet as the Win2k box running MySQL 4 work perfectly - so is it possible this is a MySQL 5 issue? I am using the 'old connections' flag (but have tried both with it off and on, since I am not actually using a password for the connection at present). Any suggestions will be most gratefully received! Cheers, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- End Original Message -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Connection from PHP to MySQL 5.0
Hi Folks. I've seen similar questions asked both here, and via a google search - but I haven't found an answer which has helped yet - so heres hoping!! I run a Linux webserver which has PHP 4 installed, and am currently prototyping a design using MySQL 5.0-alpha on Windows 2K professional. The two servers are on different subnets of a LAN, but are able to talk to each other unrestricted (all TCP services I have tried work perfectly, e.g. SMTP, FTP). The problem is that connections from PHP to MySQL seem to take 4-5 seconds (after which any queries within the connection go through in normal lengths of time - only the connection time itself is long). Although I have a fully functional internal DNS server, I thought name resolution might be an issue. I amended my PHP pages (they inherit a global 'data connection' object which is used as a wrapper) to connect to the Win2k box by IP address - and the problem persists. Has anyone else encountered this, and are there any ideas? Connections from the same Linux box with PHP to another Linux box on the same subnet as the Win2k box running MySQL 4 work perfectly - so is it possible this is a MySQL 5 issue? I am using the 'old connections' flag (but have tried both with it off and on, since I am not actually using a password for the connection at present). Any suggestions will be most gratefully received! Cheers, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I specify a primary key to be primary, unique and index ?
As I discovered recently, thanks to another user on this list, there is at least one situation where you WILL need to also create a KEY index on a PRIMARY KEY column - If you have a composite primary key such as (col1, col2) and you wish to place a foreign key on col2, you will ALSO have to add a KEY on col2 to be able to do so. Cheers, Matt. -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 08:53 To: [EMAIL PROTECTED] Subject: Re: Do I specify a primary key to be primary, unique and index ? From the MySQL documentation: * A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. KEY is a synonym for INDEX. So, specifying PRIMARY KEY implies UNIQUE and INDEX.. You don't have to specify them yourself. At 01:11 am 4/11/2004, you wrote: I learned that there are three types of indexes (PRIMARY, UNIQUE, and INDEX). Now assuming I create a performance-critical PRIMARY key, will I better have to specify UNIQUE and INDEX for this column also !? It should be obvious that a primary key is unique anyway, and an index as well, shouldnt it !? Please note, I am not after saving disk space here, performance is all I am after, and such a three-fold indexing exercise just seems redundant to me in the best case scenario, or harmful even, am I right there !? -- 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: MSSQL to MYSQL
Yep. Theres no reason at all why this sort of thing won't work for MSSQL too. Use SQL Enterprise Manager to generate a create script for all objects in the database, and also tell it to script referential integrity (FKs, etc). Then add anything MySQL specific, such as Type=InnoDB (which you will need for transactions/FKs, although I believe BDB works as well?). The only possible problem you will run into is with any code that is embedded into the database - stored procedures shouldn't be too bad, as the syntax in MySQL is fairly similar, you'll just end up changing some function names and tweaking (unless you have very complicated MS SPs). Remember that there are no table variables in MySQL, and that the syntax to create a temporary table is 'create temporary table xyz' not 'create #xyz'. Views of course, are a different matter. In terms of the database structure itself, without embedded code though, it should work perfectly... Cheers, Matt -Original Message- From: David Carlos Brunstein [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 05:23 To: 'Rodrigo Galindez' Cc: [EMAIL PROTECTED] Subject: RE: MSSQL to MYSQL Hi Rodrigo. I'm facing a similar task but from Informix to MySQL. What I do is: 1. Obtain a SQL script to create the logical database (an Informix tool give it). You can use Erwin, with reverse engineer and the save the script. 2. Add the Type=INNODB clause for each CREATE sentence. 3. Divide the scritp into tow: one for the tables creation (with its primary key) (CreateDB.sql) and another one for the alter tables to create the foreing keys (AlterDB.sql). 4. Create a script for loading data from TXT files. First you have to save every table data from SQL Server into TXT files, then load them into MySQL tables (LoadDB.sql). 5. Run the AlterDB.sql script (step 3). It works fine to me. Regards, David. == David Carlos Brunstein System Analyst / Software Developer Buenos Aires, Argentina Mail to: David _ Brunstein @ Yahoo . Com . ar IM: DavidBrunstein @ Hotmail . Com -Original Message- From: Rodrigo Galindez [mailto:[EMAIL PROTECTED] Sent: Saturday, April 10, 2004 3:38 AM To: [EMAIL PROTECTED] Subject: MSSQL to MYSQL Hello list, I have to move a database in MSSQL to MYSQL, with the table structures and all the respective data. I tested one product to do this, SQLyog, and it works fine, except for some little problems with indexes and primary/secondary keys. I want to know if anyone have been dealing with the same problem to recommend me some suggestions/tips/tricks. Do you know another program/script/ways to do this migration ? I want to migrate everything from the original MSSQL database, like indexes, relationships, and so on. Can you guys recommend me some actions or tips to take ? Thanks in advance, -- Rodrigo Galindez Information Management Assistant Center for Human Rights and Environment (CEDHA) Gral Paz 186 10 A 5000 - Cordoba - Argentina Tel/fax 54-351-4256278 [EMAIL PROTECTED] www.cedha.org.ar -- 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: sql join help
Hmm. SELECT o.* FROM orders o INNER JOIN orderitems oi ON o.orderid = oi.orderid INNER JOIN products p ON p.productid = oi.productid AND p.productparentid 2 WHERE o.orderstatus =2 Not sure why you are checking for a NULL ordered in orderitems? That would suggest you get back only items that have no associated order? The above should do what you state below, though - I think! Cheers, Matt -Original Message- From: Michael Collins [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 05:14 To: [EMAIL PROTECTED] Subject: sql join help I suppose this would be easier with subselects but I am using MySQL 4: I want all orders that are of orderStatus 2 and whose orderitems contain a product that is in a productparent category greater than 2. An orderitem can only have one product, and that product has a single certain product parent (defined in the products table). This is how the tables are related: members - orders - orderitems - products - productparents I have tried the following, but I know it is not correct: SELECT count(*) FROM orders AS o LEFT JOIN members AS m USING (memberId) LEFT JOIN orderItems AS oi ON (o.orderId=oi.orderId) LEFT JOIN products AS p ON (oi.productId=p.productId) AND (p.productParentId 2) WHERE (oi.orderId IS NULL) AND (o.orderStatus=2); -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- 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]
5.0.0a stored procedure crash
Hi folks. I've just submitted the following as a 'bug' via the MySQL website, and was wondering if anyone out there had experienced the same problem? It only seems to cause a crash when a nested IF is put into a stored procedure, so the work-around is obvious - don't nest 'em! As posted to mysql.com: Description: Using mysqld-opt on Windows 2000 Professional (5.0.0a-alpha), and the stored procedure shown below is created and called, the error: The instruction at 0x referenced memory at 0x. The memory could not be read Is displayed, and the server shuts down 'unexpectedly'. I have tried different permutations of the procedure, and it is the addition of a 'nested' if statement which triggers the problem - if this is removed, the code will execute with no problems and give the expected result. All tables referenced are InnoDB, and are simple tables with two columns (an auto increment primary key and a unique indexed value column of type VARCHAR(6)). How to repeat: From the command line: delimiter // CREATE PROCEDURE name_test (OUT sFragment VARCHAR(6), OUT iRand INT) BEGIN DECLARE bContinue INT; --DECLARE iRand INT; SELECT 1 INTO bContinue; WHILE bContinue = 1 DO SELECT CAST((RAND() * 100)/33 AS UNSIGNED) INTO iRand; IF iRand = 0 THEN SELECT Fragment INTO sFragment FROM namefragmentvowel ORDER BY RAND() LIMIT 1; ELSE IF RAND = 1 THEN SELECT Fragment INTO sFragment FROM namefragmentconsonant ORDER BY RAND() LIMIT 1; END IF; END IF; SELECT 0 INTO bContinue; END WHILE; END // Then from MySQLCC: call name_test(@sTest, @iRand); select @sTest AS frag, @iRand AS rng; Cheers, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIKE search with different orders
Hmm. You might be best off using the FIND_IN_SET function (check the manual at mysql.com for more information). In short, if you replace all spaces in your string to search with commas, you should be able to do something like: SELECT * FROM xyz WHERE FIND_IN_SET(test_column, your string here) 0 Not 100% sure, as I haven't tried quite this approach! Another, more long winded way might be to explode your string out into a temporary table and compose a query which will bring back all matching rows (by multiple joins to the temporary table). FIND_IN_SET looks like a better alternative! Cheers, Matt -Original Message- From: Tarik ANSARI [mailto:[EMAIL PROTECTED] Sent: 10 April 2004 14:51 To: [EMAIL PROTECTED] Subject: LIKE search with different orders Hello again, To follow my previous message, the CONCAT method does works, but now my problem is to make a search where the order doesn't count : then to find members whose firstname is john, lastname smith and vice-versa. I would like to use an operator or a function for this, but I cannot enumerate all possible combinations (in this case yes, because the query only has 2 words, but with a query with 6 words it would make 6! combinations then a very long query !). Thank you -- 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: Database design question
I'm not 100% sure as to what you are trying to do, however, the relationship you describe could, I believe, be modeled as: Candles (candleid, description, price) Waxes (waxid, name/description) Candle_Waxes (candleid, waxid) Thus one table holds the description and price of each candle, another table holds the name of each wax, and a third table connects the two - as a candle can have multiple waxes, the logical way to do this (to me, anyway) is via this third table - glueing the other two together. You'll need to be a bit clever when querying, as simplying joining all three together will bring back multiple rows for candles which contain more than one wax - this could be eliminated by not bringing back the wax details (and using distinct), or in a number of other ways. One other way might be to come up with a way to combine all of the wax names into one field (tricky - can't think how to do this in mysql, off the top of my head). What precisely are you trying to achieve, though - this might be completely wrong for you! Thanks, Matt -Original Message- From: JOHN MEYER [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:39 To: [EMAIL PROTECTED] Subject: Database design question Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: can't call a stored proc from another stored proc?
Morning :)
1. You sure can, e.g:
CREATE PROCEDURE `user_authenticate`(IN sUserName VARCHAR(25), IN sPassword
CHAR(32), OUT sUserCookie CHAR(32))
BEGIN
DECLARE iUserID INT;
DECLARE iLogID INT;
SELECT MD5(CONCAT(UserID,NOW())) INTO sUserCookie FROM users WHERE UserName
= sUserName AND Password = sPassword;
IF LENGTH(sUserCookie) = 32 THEN
UPDATE users SET Cookie = sUserCookie, LoggedOnAt = NOW() WHERE UserName =
sUserName AND Password = sPassword;
SELECT UserID INTO iUserID FROM users WHERE Cookie = sUserCookie;
CALL processlog_write(NULL, 'user_authenticate', CONCAT('User authenticated
successfully (', sUserName, ').'), iUserID, iLogID);
ELSE CALL processlog_write(NULL, 'user_authenticate', CONCAT('User
authentication failed (', sUserName, ')'), 0, iLogID);
END IF;
END
2. I believe this is planned for the future - I read something about it in
the documentation not long ago.
Sounds like an oddness either with 5.0.1-alpha, or with your build - I am
using the pre-compiled binary version of 5.0.0a-alpha on Win2k, and the
above procedure executes with no problems at all.
As a side note, does anyone know if it is now confirmed that views will be
in 5.1 rather than 5.0? :) I know, I know.. I keep harping on about views...
Regards,
Matt.
-Original Message-
From: Michael Pheasant [mailto:[EMAIL PROTECTED]
Sent: 03 April 2004 11:57
To: [EMAIL PROTECTED]
Subject: can't call a stored proc from another stored proc?
Hi,
1) Can a stored procedure call another stored procedure?
Ie, can you do 'call someproc()' from within a stored procedure?
2) Also, will a function ever be able to issue a SELECT query?
I am using mysql-5.0.1-alpha (built froms ource) , winXP win2k.
The mysql daemon crashes without an error message when I try (1)
Cheers,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: two tables with same field names into one table
To select the contents of both into one table, you most likely want to use the 'UNION' operator: SELECT * FROM desktops UNION SELECT * FROM laptops If you create the computers table before hand (you can see how you would create either of the others with SHOW CREATE tablename), then you can just do one INSERT into the new table, using a select similar to the one above. Thanks, Matt -Original Message- From: Brad Tilley [mailto:[EMAIL PROTECTED] Sent: 03 April 2004 21:00 To: [EMAIL PROTECTED] Subject: two tables with same field names into one table Hello, I am a mysql newbie. Recently, I've been given the task of joining two tables within the same DB into one table. Currently, the tables are named 'desktops' and 'laptops'... ultimately, I would like one table named 'computers' Both tables have the exact same fields... they fields even have the same names. I tried this: create table computers select * from desktops, laptops where desktops.field_1 = laptops.field_1 ... ... ... But I got an error about duplicate field names. Any suggestions on how to do this? Thanks, Brad -- 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]
5.0 InnoDB problem - ADD CONSTRAINT
Hi folks. I have a problem creating a foreign key constraint which I just don't seem to be able to figure out. There are three tables, for which the 'show create' output is given below: CREATE TABLE `users` ( `UserID` int(11) unsigned NOT NULL auto_increment, `ContactID` int(10) unsigned NOT NULL default '0', `UserName` varchar(25) NOT NULL default '', `Password` varchar(32) NOT NULL default '', `LoggedOnAt` datetime default '-00-00 00:00:00', `Cookie` varchar(100) NOT NULL default '', PRIMARY KEY (`UserID`), UNIQUE KEY `UserName` (`UserName`), KEY `ContactID` (`ContactID`), KEY `Cookie` (`Cookie`), CONSTRAINT `0_34` FOREIGN KEY (`ContactID`) REFERENCES `contact` (`ContactID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `usergroups` ( `UserGroupID` int(10) unsigned NOT NULL auto_increment, `Code` varchar(20) NOT NULL default '', `Description` varchar(100) NOT NULL default '', PRIMARY KEY (`UserGroupID`), KEY `CodeLookup` (`Code`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `user_usergroups` ( `UserGroupID` int(11) unsigned NOT NULL default '0', `UserID` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`UserID`,`UserGroupID`), CONSTRAINT `0_75` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 As you can see, there is one FK on user_usergroups, which references the users table (UserID). However, when I do this, to create a similar constraint on UserGroupID to the usergroups table: ALTER TABLE user_usergroups ADD CONSTRAINT FOREIGN KEY `usergroupid_ref_usergroups` (UserGroupID) REFERENCES usergroups (UserGroupID); I get this error: [Dorfl] ERROR 1005: Can't create table '.\space\#sql-340_b.frm' (errno: 150) The online documentation indicates that this is because my FK constraint is 'badly formed' (I looked up innodb error codes 1005 and 150 in the manual). I have tried recreating the usergroups table with usergroupid as an int(11) (I am unsure as to why it is length 10, rather than 11, to be honest - I created the tables via MySQLCC, and other similar columns are length 11), but this makes no difference. Both columns are unsigned and NOT NULL, and although the documentation states that both parent and child columns must be indexed - they are, because they are both a part (or the whole) of the primary keys. It cannot be because user_usergroups.UserGroupID is part of a combined primary key - because UserID is too! This leaves the only reason I can envisage as: It is because UserGroupID is not the FIRST column referenced in a combined Primary Key - meaning I would have to create a secondary index on it. Is this the case, or have I missed something obvious? Before I forget, I am using (please forgive me), 5.0a-alpha on Windows 2K. Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting around 2 queries
One option would be to 'union' the two queries (assuming the columns are the same type and length), allowing you to run one query string: Select serial from blacklist where serial = x Union Select serial from seriallist where serial = x Would return 1 or 2 rows, depending on whether rows are found in one table or both. You wouldn't know which table though (but from your message, I guess that is unimportant). I suppose there are a number of things you could do, really... Regards, Matt -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: 30 March 2004 07:39 To: MySql Subject: Getting around 2 queries I currently run this, which is 2 hits to the database. Select serial from blacklist where serial = '23' Select serial from seriallist where serial = '23' I only desire to know if either of the 2 has at least one row, I am only testing for existence here. Is there some way I can get around 2 queries and do this as one? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
From SQL Server: Jobs
Hi all, I hope I've chosen a suitable list for this post. If not, please feel free to redirect me! :) By day, I am a SQL Server (and .NET) programmer. By night, my current project (a space-based trading/strategy game to be delivered on the web, should anyone be interested in contacting me off-list) is being constructed in PHP (primarily - some light javascript is bound to work its way in, and there are plans for a java applet chat client to be integrated into the pages), with a MySQL 5.0 back-end. I'm currently prototyping, and trying out a few things which I wish to implement in the long run - hence why I'm using MySQL 5, to try it out (and because I want to house data-related logic IN the database itself - so Stored Procedures are a must). In-between lamenting the lack of views, one of the things I most miss from SQL Server, and which I need, is the Job System. For those unfamiliar with the concept from SQL Server, it is a way to set up one-shot or scheduled tasks to run within the database - these execute SQL statements when run, and can be started manually, from a procedure call, or from a schedule. My current prototype involves creating a small schema, with associated stored procedures, and a PHP script which runs a simple loop, detecting which jobs are cached for execution and then, based on the job-code, loading an XML definition file, creating and executing the required SQL statements. For the most part these will be parameterized Stored Procedures - the 'cache data' will dictate the parameters to be passed in. A future implementation (if the project ever reaches fruition and opens to the public) will likely be based in VB or Java (since those are the two application languages I am most comfortable with -- most likely Java, as then I can run it on both Linux and Windows) and will be dual-mode - running either as a monitoring console, or an 'authoritative instance' which actually provides the loop and executes the queries. To the point. My question: Has anyone out there attempted (or seen attempted) such a thing for MySQL? The need has arisen from the fact that I will need to run a number of regular maintenance jobs (such as a map-expansion routine, and various statistical updates) as well as some ad-hoc processes which I would prefer to handle outside of page requests (these would be 'one shot' jobs). I'm really fishing for comments and suggestions as to this implementation - particularly if there are any fatal flaws in my theory, or if it has already been done - Wheel reinvention is not one of my favourite pastimes!! Many thanks, Matt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get around lack of views?
The only idea which presents itself (be warned, this is ugly) is to maintain a set of tables which hold the same data, partitioned out by privilege, and to grant access on those tables to appropriate users. These tables could be maintained (or recreated) from the source data at regular intervals. Leaves a lot to be desired though, since data consistency (and age) can become issues if updates are frequent... If the data is relatively static, it's just a bit messy. Regards, Matt -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 21:58 To: [EMAIL PROTECTED] Subject: How to get around lack of views? How would I do this? Let say I have an employee table with Name varchar(64) Dept int(11); Salary int(11); I want to grant select on Salary to a mysql user but only where dept = 1 let's say. Normally I would create a view to do something like this. But I was wondering if there would be another way around this until views are implemented that someone has thought of. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT ... SELECT question
Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: INSERT ... SELECT question
Oh, if only there were views!! That would make this easy. Maybe soon (*please*). :) Another approach (more cumbersome) might be to insert the rows you need to duplicate into a temporary table, update the id_col adding max(id_col) from the original table to each, and then to insert from the temporary table back into the original. I don't like that at all, though. It seems messy. Hopefully someone here can come up with a better idea! Cheers, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 20:12 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question There is only 1 table. I want to replicate a record in a table except the AUTO_INCREMENT column. Your solution would work, but I'd prefer to not have to maintain a list of columns in the application. Worst case I'll have the application generate the query based on the table definition, but I was hoping that MySQL had something built in to make this easier. Thanks, Eric -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 2:11 PM To: 'Eric J. Janus'; 'MySQL' Subject: RE: INSERT ... SELECT question Importance: Low Can you roughly outline the schema of the two tables? If the key value doesn't have to match from the input to the final result, you can always insert into a list of fields, skipping the auto_increment/key column, and they will continue to be generated.. Assuming you have two tables with id_col, col2, col3: INSERT INTO table (col2, col3) SELECT col2, col3 FROM table2 WHERE id_col=1; Regards, Matt -Original Message- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: 29 March 2004 19:37 To: MySQL Subject: INSERT ... SELECT question I have a table with just about 100 columns, and I would like to duplicate a row exactly, except for one column, which is the AUTO_INCREMENT column. Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' doesn't work, because it produces the following error: 'ERROR 1062: Duplicate entry '1' for key 1'. Because I'd like the application using this database to be simpler to maintain, I'd prefer to not have to change the code each time a field is added...so is there a way to duplicate a row, but still have it automatically assigned an value for the AUTO_INCREMENT column? Thanks, Eric -- 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: Speeding up MySQL server
Hmm. Taking a stab in the dark here, but.. If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row? Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed? Depends on what you're doing, and what you're trying to achieve, really! Thanks, Matt. -Original Message- From: Jim Richardson [mailto:[EMAIL PROTECTED] Sent: 25 March 2004 03:17 To: [EMAIL PROTECTED] Subject: Re: Speeding up MySQL server On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL statement
Very straightforward if the new column is NULLable OR has a deterministic value, e.g. INSERT INTO newtable (col1, col2) SELECT col1, 1 FROM oldtable WHERE ID = xxx Or if NULLable: INSERT INTO newtable (col1) SELECT col1 FROM oldtable WHERE ID = xxx Equally you could populate 'col2' from an expression or similar, calculating the value from data already in oldtable. Is this what you meant? If not, what exactly are you trying to do? Thanks, Matt -Original Message- From: Vinay [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 18:09 To: [EMAIL PROTECTED] Subject: SQL statement Is their a way of using insert into newtablename select * from oldtablename where ID=xxx But with an aditional column in newtablename V!nay -- 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: multiple databases: design question
The not very useful answer would be: It depends on what you are trying to achieve. More usefully (I hope): I work with a complex web application which is also strongly modularized. This system is generally delivered using several databases, residing on different servers, to allow us to cope with more user activity. The whole set-up relies on quite complex replication, since some of the data in the system is required by all of the constituent parts. A large cluster, scaled to cope with thousands of users might theoretically consist of four database servers: Web, Master, Reporting, Processing. The first two are strongly paired, with the former serving all direct requests from web page users (e.g. viewing and submitting data). The second feeds 'centrally controlled' information (e.g. product information) to all of the other databases, and is the entry point for maintenance personnel who maintain the information within the system. The use of a reporting server is fairly standard - it allows us to shift the processing requirements of report generation onto a separate server, lowering the load on the web-facing one. Requests for reports are passed to this server which generates the required data and passes it to a separate system responsible for displaying/serving the finished report to the user. Finally the use of a 'processing' box, would theoretically allow any number crunching to be done away from the web-facing box - data requiring processing (e.g. an order which the user has submitted) is churned here, and the results are replicated back to other databases which require them. I don't know if it is technically possible to run cross-database transactions, but I would suspect not. However, I'm sure you can work around this without too much trouble - the use of replication so that all data 'originates' from one place and is controlled there can help to simplify this. To summarise, before I get carried away - if your application/system can be split down into logical sections, it may be advantageous both from the organizational point of view (as you say) and scalability pov too, to split it up into multiple databases. Thanks, Matt -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of TO Sent: 21 March 2004 15:14 To: [EMAIL PROTECTED] Subject: multiple databases: design question What are the advantages and disadvantages of using multiple databases, versus placing all tables in one uber-database? I understand and appreciate the organizational value of multiple databases, but what other issues are involved? I ask this because I'm considering moving from tables across multiple DBs (on one server) to all tables in one DB (on one server.) I don't want to do this, but may need to given the constraints of perl's Class::DBI and Ima::DBI. Specifically, I am stuck on successfully handling transactions and rollback across multiple databases, because Ima::DBI creates a different handle (eg connection) to each database, which stymies rollback (as the work is happening thru different cxns). I'd welcome any suggestions about structuring databases -- one vs. many -- and if anyone has advice about the perl issues, that'd be great too. 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: Newbie Can't Add a User to MySQL
Hi. Bit off-topic (ish) I know, however: You are missing a semi-colon in the 'forum snippet'. The line: $conn = mysql_connect(localhost, forums, forumuser, somepass) or die(mysql_error()) should be: $conn = mysql_connect(localhost, forums, forumuser, somepass) or die(mysql_error()); As to the connection problem, you are putting the password into a variable called '$dbpassword' and passing a different (presumably null) variable '$dbpasswd' into mysql_connect. Thanks, Matt -Original Message- From: Lee Zelyck [mailto:[EMAIL PROTECTED] Sent: 21 March 2004 18:10 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Newbie Can't Add a User to MySQL Greetings Michael, Daniel, et al, Good news, I can get in! As evidenced below, however, my php forum script will not authenticate forumuser to insert data. Further, I tried to run a php/mysql authorization test, and still an error. I know this is not a php mailing list, but please find the problematic php snippets below, as well as the forumuser creation and authentication process. If somone could please point out my mistakes, that would be awesome! Thanks again, Lee TESTER ?php //Database connect script $dbhostname = localhost; $dbuser = forumuser; $dbpassword = somepass; $dbname = forums; $link = mysql_connect($dbhostname, $dbuser, $dbpasswd) or die(Connection to server: $dbhostname FAILED!!!); mysql_select_db($dbname) or die(Connection to database: $dbname FAILED!!!); ? ERROR: Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /var/www/dbtest.php on line 7 Warning: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /var/www/dbtest.php on line 7 Connection to server: localhost FAILED!!! FORUM SNIPPET: //Connect to server and selectdatabase $conn = mysql_connect(localhost, forums, forumuser, somepass) or die(mysql_error()) mysql_select_db(forums,$conn) or die(mysql_error()); ERROR: Parse error: parse error in /var/www/do_addtopic.php on line 13 For anyone that cares, forumuser was create and tested from the following commands: mysql GRANT SELECT,INSERT,UPDATE ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'somepass'; Query OK, 0 rows affected (0.00 sec) Spider:/usr/bin# mysql -u forumuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 44 to server version: 3.23.49-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; +--+ | Database | +--+ | forums | | mysql| | test | +--+ 3 rows in set (0.00 sec) mysql use forums; Database changed mysql show tables from forums; +--+ | Tables_in_forums | +--+ | forum_posts | | forum_topics | +--+ 2 rows in set (0.00 sec) __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- 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: retrieving last record for all distinct users
Making the assumption that you are running a version of MySQL which supports subqueries, I believe you could use: SELECT Login, TimeStamp, IP FROMSessions S INNER JOIN ( SELECT MAX(TimeStamp) TimeStamp, Login FROMSessions GROUP BYLogin ) Latest ON Latest.Login = S.Login Or something very similar - using a subquery (and joining to it), to ensure you only look at the latest records. I've made the assumption that 'Login' is your way to uniquely identify a user! Thanks, Matt -Original Message- From: motorpsychkill [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 01:18 To: mysql Subject: retrieving last record for all distinct users I have a table SESSIONS with the following fields: SESSION_ID LOGIN IP TIMESTAMP I am trying to select the last login record for all distinct users. The closest I can get to is: select distinct LOGIN, TIMESTAMP, IP from SESSIONS group by LOGIN order by TIMESTAMP desc This kind of works but it does not get the correct IP for the last TIMESTAMP. This seems easy enough, but I can't seem to figure this one out today. Can anybody see what I'm missing? Thanks! -m -- 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: retrieving last record for all distinct users
Had my brain been in gear, I would have typed the 'AND Latest.TimeStamp = S.TimeStamp' which you will also need on that join.. Cheers, Matt -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 19:51 To: 'motorpsychkill'; 'mysql' Subject: RE: retrieving last record for all distinct users Making the assumption that you are running a version of MySQL which supports subqueries, I believe you could use: SELECT Login, TimeStamp, IP FROMSessions S INNER JOIN ( SELECT MAX(TimeStamp) TimeStamp, Login FROMSessions GROUP BYLogin ) Latest ON Latest.Login = S.Login Or something very similar - using a subquery (and joining to it), to ensure you only look at the latest records. I've made the assumption that 'Login' is your way to uniquely identify a user! Thanks, Matt -Original Message- From: motorpsychkill [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 01:18 To: mysql Subject: retrieving last record for all distinct users I have a table SESSIONS with the following fields: SESSION_ID LOGIN IP TIMESTAMP I am trying to select the last login record for all distinct users. The closest I can get to is: select distinct LOGIN, TIMESTAMP, IP from SESSIONS group by LOGIN order by TIMESTAMP desc This kind of works but it does not get the correct IP for the last TIMESTAMP. This seems easy enough, but I can't seem to figure this one out today. Can anybody see what I'm missing? Thanks! -m -- 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: Stored Procs and Commit/Rollback Transactions
Possibly veering off topic, but I have a strong urge to comment on this, and shall! I am a M$ .NET developer (primarily ASP.NET with SQL Server), and have recently embarked on a project at home, and wished to apply the same sort of principles that I use at work - for example, keeping all 'system logic' embedded within the database itself. I have begun prototyping using MySQL 5.0.0a-alpha on win32 (possibly the most unstable combination you can imagine), and it is excellent. I have the odd crash, or strange glitch (such as procedures not being recognized, requiring a restart before they can be called), but this is fine - it's the first alpha, after all! Now all I need to be truly content is views Cheers, Matt -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 22:57 To: Laphan; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Stored Procs and Commit/Rollback Transactions Hi Laphan, (I'm sending this to the general list too, since this isn't Windows specific and more people will see it.) MySQL 5.0, which is an early Alpha, does now support stored procedures. http://www.mysql.com/doc/en/Stored_Procedures.html And MySQL also supports transactions with the InnoDB (most common) and BDB table types. MyISAM doesn't. Hope that helps. Matt - Original Message - From: Laphan Sent: Thursday, March 18, 2004 5:19 PM Subject: Stored Procs and Commit/Rollback Transactions Hi All OK I admit it I'm a complete MSV (MySQL Virgin), so I might be asking some very stupid questions!!! I'm used to SQL Server so I think I should have a basic understanding, but I'm sure you'll tell me different!! Basically I just want to confirm that the latest release of MySQL doesn't offer stored procs or commit/rollback functionality - right? How does a MySQL-er get round this? I'm wanting to develop my ASP/Cart with MySQL as the back-bone and I'm trying to find out what the generic do's and dont's are when using this collaboration. Any feedback would be very much appreciated. Rgds Laphan -- 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: Guru's advice needed ........[Security: SQL injection]
This reply has two purposes.
Firstly, a small suggestion (modest compared to the others which will
follow, no doubt!) - check out mysql_escape_string() - this may be useful to
you.
Secondly, a further question:
In addition to protecting against SQL Injection, has anyone here
experimented with detecting and recording attempts at injection?
I've been pondering checking strings which come directly from user input for
sql keywords (and possibly using regexps to check for potential SQL Syntax
fragments), but before I begin, I thought asking would benefit me, if
someone with more experience has already tried this...
Thanks,
Matt
-Original Message-
From: Tariq Murtaza [mailto:[EMAIL PROTECTED]
Sent: 19 March 2004 18:41
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Guru's advice needed [Security: SQL injection]
*Dear Friends!*
Can someone shed some light on how SQL injection attack occurs when
*magic_quotes_gpc *isON and how it prevents when its OFF. To my
understanding apostrophise are escaped automatically in POST/GET/COOKIE
when its ON, so how it tends towards SQL Injection.
Someone suggested to keep magic_quotes_qpc OFF through .htaccess file
and use following line of codes to prevent attacks at start of the file...
?php
/**
* Checks for magic_quotes_gpc = On and strips them from incoming
* requests if necessary
*/
if (get_magic_quotes_gpc()) {
$_GET= array_map('stripslashes', $_GET);
$_POST = array_map('stripslashes', $_POST);
$_COOKIE = array_map('stripslashes', $_COOKIE);
}
?
But unfortunately it does not work for nested POST requests. do anyone
have better idea?
Secondly why we have to stripslashes while DB (mysql for example) is
doing it for us on execution and another question arises doesn't it
prevent from SQL injection attack when apostrophise are escaped in query.
*What is the best practices handling 'quotation marks' in input string
and how to prevent SQL injection.
*Looking forward for some advice from panel of experts on forum.
Thanks and have a nice day!*
*Cheers!
*Tariq*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
