RE: Weeding out duplicates
At 10:25 -0600 on 07/09/2004, Jonathan Duncan wrote about Re: Weeding out duplicates: Lachlan, I want to identify the entries in the table where the email addresses are the same as another entry. Whatever else is in the record does not matter to me. However, a second requirement for the query is that it show me the last duplicate instead of the first. This way I keep the first entries and remove subsequent ones. Thanks, Jonathan Duncan If you are willing to go with a PHP/MySQL solution as opposed to a pure MySQL one, try this: 1) Use that query to get a list of what Email Addresses are duplicated. 2) Now do a query with a WHERE Email=Duplicate-Email-Address and fetch the Primary Key. 3) Do a Delete by Primary Key for all rows fetched in 2 EXCEPT for those keys fetched in 1. The sequence is that you do step 1, loop though the results one at a time (step 2) doing step 3 in that loop. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weeding out duplicates
At 17:25 -0600 on 07/09/2004, Jonathan Duncan wrote about Re: Weeding out duplicates: For the information of someone who may need it in the future. I used Jeffrey's idea for determining duplicates. Then I created a temporary table, and used insert...select to put the id's of the duplicates in the temporary table. Then it was a simple delete from table where temp.id=table.id. Thanks for the help. Jonathan Duncan I responded to your query earlier with a PHP/MySQL solution that was equivalent to this. I only saw your reply after sending my suggestion. Sorry for giving you an answer you had already discovered. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
At 15:30 -0400 on 06/23/2004, Michael Stassen wrote about Re: INDEX DESC: The manual http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html says An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order. Something that is VERY easy to fix and implement. So long as you KNOW that the Index is being defined as DESC, just STORE the Index value as Field XOR xFF..FF (FF..FF being the length of the Keyed Field). The Index is then AUTOMATICALLY in Descending order and you just need to XOR to recover the actual key value if/when you need it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
At 16:38 -0400 on 06/23/2004, Michael Stassen wrote about Re: INDEX DESC: For example, I want to read ranges of values from my db in the opposite order of how they are currently being indexed without using an extra ORDER BY in my select. This ORDER BY forces an internal sort to put the result set in DESC order which slows my SELECT. You cannot count on rows being returned in any order unless you explicitly use ORDER BY. True. What you are ignoring is that if you ORDER BY an INDEXED column, the Index is used to read the records directly without an intervening Sort (so long as both the ORDER BY and the INDEX are ASC). If the Index were defined (and stored) as DESC, then an ORDER BY DESC would likewise just use the Index without the need for the sort that currently must get used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you deal with URL's?
At 22:38 -0300 on 06/21/2004, Sergio Salvi wrote about Re: How do you deal with URL's?: Separate data from how it's displayed. I mean, create a table called states with the fields state_id, state_name and state_url. Put the data in the according field: state_id state_name state_url 1 Alabama http://www.alabama.gov 2 Washington http://access.wa.gov ...and so on Instead of a auto_increment state_id, go with the USPS 2 letter code (AL, WA, etc). That way you can pull up the state name from an address. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
At 13:37 -0400 on 06/19/2004, Michael Stassen wrote about Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a: Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. It goes to 16+ Million if you say UNSIGNED. Why lose half the range by allowing the negative numbers that will never be used? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Full Text Index on Large Tables - Not Answered
At 19:02 -0700 on 06/18/2004, Paul Chu wrote about Re: Full Text Index on Large Tables - Not Answered: Appreciate any help at all Thanks, Paul -Original Message- From: Paul Chu [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 10:16 AM To: [EMAIL PROTECTED] Subject: Full Text Index on Large Tables Hi, If I have a table with 100 - 200 million rows and I want to search For records with specific characteristics. Ex. Skills varchar(300) Skill id's 10 15 Accounting finance etc. Is it advisable to created a field with skill ids and then use the Skills column in a full text index Thanks for your help, Paul The best way of doing this is to create 2 additional tables for the skills. One table has as its PK a skill number and as a separate indexed column a text description. The second table has as its PK (and total content) the Skill Number and the UserID (in that order). To search for the skills, you just search the 2nd table on Skill Number (after getting it from the first table) and Join the result to the User Table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to monitor that slave is not working because of user rights?
At 09:10 -0500 on 06/17/2004, gerald_clark wrote about Re: How to monitor that slave is not working because of use: The master will be trying to serve binlogs to the old address, but the slave won't know this. You could monitor the IP address, and stop and start the slave when it changes, but how will the master know about the address change? There are free DNS services that will assign a domain name to a dynamic address (all you need to do is run a daemon on your slave that monitors its current IPN and tells the DNS Server when it changes). The master would just use the domain name to talk to the slave (or it can periodically monitor the A record to tell when to change IPNs for the slave's address). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
At 17:16 -0700 on 06/18/2004, Kevin Brock wrote about AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one: We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. You are wasting half your range by not saying UNSIGNED. Not your problem (I think) but still an error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
At 12:22 +0100 on 06/11/2004, Andrew Dixon - MSO.net wrote about Query Help: Hi Everyone. I have the following a table with a varchar column that contains a comma delimited list of id's from another table that relates the item keywords in the other table. The table keywords contains keyword_id (int/auto increment/primary key) Keyword (varchar/normal key) The galleries table contains: gallery_id (int/auto increment/primary key) gallery_name (varchar) keywords (varchar) I didn't design the database and I know it is not a good design, but I'm stuck with it and I need a query to get the gallery_id when I have a certain keyword_id For example: gallery_id | gallery_name | keywords 1 | test | 1,2,3,4 2 | test2| 3,4,5,6 And I won't to get all the galleries with where the have the keywords 2, which in this case would be record 1 or keyword 4 which would be both record. SELECT gallery_id, gallery_name FROMgalleries WHERE keywords Hope that makes sense, thanks in advanced. Best Regards, Andrew Dixon. I have two suggestions for your problem. You can remove the Keywords Column and create a table with gallery_id and keyword as its content (IOW: A table with 1/1, 1/2, 1/3, 1/4, 2/3, 2/4, 2/5, 2/6 rows) with the Primary key as Keyword, gallery_id [so it can use the index to read only the correct group). As an alternative, you can alter the keywords from Varchar (or Char) to SET. To do this, just do a dump of the table, alter the keywords definition in the CREATE TABLE to SET(1,2,3,4,5,6) and then feed the commands back in. It will parse the 1,2,3 in the INSERT into the correct bits. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not unique table/alias
At 14:40 -0400 on 06/07/2004, Jack Tanner wrote about not unique table/alias: I have two complex subqueries that I need to join. I suspect this problem is due to using aliases instead of table names, but I don't know how to work around it (temporary tables?). Please help. SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2 JOIN t1 ON t2.col = t1.col; Not unique table/alias: 't1'. SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2 WHERE t2.col = t1.col; Works fine! (But this query is different, because it doesn't get the rows that are NULL in one of the tables.) Using MySQL 4.1.2. Thanks in advance for your help. I think it is screaming about a syntax error since you do not have two tables for the Join. Try: SELECT col FROM (subquery-1) AS t1 JOIN (subquery-2) AS t2 ON t2.col = t1.col; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Schema Design
At 15:53 +1000 on 06/07/2004, Ligaya Turmelle wrote about Re: Advice on Database Schema Design: I think I get what you are trying to do and it sounds good (though I am still a beginner). The only thing I can think that might cause a problem is since you have more then a couple of tables you MAY have to write joins for all of them . Only you would know if you can do that and/or be comfortable doing it. To get a stronger idea though of your design I would need to see either a logical ER diagram or all of the creates (I am very visual so I like pictures). *shrugs* whether or not you want to pass those along is up to you. Respectfully, Ligaya Turmelle Thanks for the reply. I'm still at the stage where I am deciding what I want the tables to look like before doing the CREATE TABLE statements. I do not think I will need JOINS since it is going to be more of a WHERE t1.x=t2.x (and SELECT t2.y [from the t2.x selected row]) type situation where I am selecting the fields form the support tables based on the link (Foreign Key) field not actually merging/matching tables. Robert A. Rosenberg [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I am converting a form that was originally designed to be Email Submitted into a Submit-to-PHP-Page Form (which will then insert the data into a MySQL Database). I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data. I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with. Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them). There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing. There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName. There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column. To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot QuickDirty PHP page, I will read the table and recreate the Option Statements which would the be CutPasted into the original HTML code replacing the old versions of the tags. The states go though the same Turn into INSERT Commands but there is no need for post processing or HTML Tag replacement. Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity. Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DELETE from more than one table
At 14:01 + on 06/07/2004, shaun thornburgh wrote about Re: DELETE from more than one table: Hi Paul, Thanks for the reply. I actually need to delete data from about 10 tables, is this possible? If the field in the tables is defined as a Foreign Key (with ON DELETE CASCADE), deleting the root key will do this (you can then insert it if you did not want to delete it but only the records that pointed at it). I do not know how helpful this is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Errors from Extra Spaces in Spreadsheets
At 22:33 -0700 on 06/06/2004, David Blomstrom wrote about OT: Errors from Extra Spaces in Spreadsheets: I've been having a tough time importing comma-delimited files into my database tables. I just discovered that most of the errors are similar to this one: 060 is not a valid integer value I checked my spreadsheet - Microsoft Works - and discovered that there's a space after many of my numerals. I'm not sure how to remove these spaces. I didn't see anything in the Help files. I could do a search and replace, but that would also wipe out spaces between words - like... TrippspaceCounty I just wondered if there's a simple trick anyone knows of for getting rid of spaces like these or preventing them in the first place. Thanks. Assuming that these are not the last fields in the record a simple Search/Replace operation in a Text Processor would handle this: Find = space, - note that space is an actual space not the word Replace = , I note you mention S/R but you need to be more specific on what you are searching for (as I listed above) and not just look for spaces. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advice on Database Schema Design
I am converting a form that was originally designed to be Email Submitted into a Submit-to-PHP-Page Form (which will then insert the data into a MySQL Database). I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data. I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with. Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them). There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing. There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName. There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column. To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot QuickDirty PHP page, I will read the table and recreate the Option Statements which would the be CutPasted into the original HTML code replacing the old versions of the tags. The states go though the same Turn into INSERT Commands but there is no need for post processing or HTML Tag replacement. Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity. Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where do exported SQL files go?
At 22:17 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:13 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) Might it be doing this due to PHPAdmin being a WebPage application and thus being served by your Web Server making the file need to be a FTP Download g? Well, that's as good an explanation as any. :) It isn't that big a deal; at least I know where I can find everything I download, and I can always copy and rename them and move them somewhere else if necessary. It just confused me this time around. I apologize if I seemed to have been flippant in my wording instead of just saying Here's Why. I am 100% sure of the correctness of my solution (ie: PHPMyAdmin IS a Web Application running on the Web Server not an application running on the user's machine [even if the Server is running on the user's machine it us still the Server not the user who is running the program]). I phrased it that way since it is one of those Can't see the Forest for the Trees situations where the answer is obvious once you look at the problem the correct way and want'ed to inject some humor to downplay an offence at pointing out the obvious. If it had been mysqladmin, the file would just be saved since you would be on your machine doing the query not using a Web Browser to issue it to a Web Application. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
At 21:54 -0700 on 06/02/2004, David Blomstrom wrote about Re: Exporting/Importing Databases: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases: Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. OK, is this something I can do in phpMyAdmin or another software program, or do I have to open the MySQL file itself? Yes you need to edit the dumped file. BTW: As of 4.1, mysqldump will automatically add these statements. I am thinking of filing a bug report against PHPMyadmin to add the statements. I've scarcely touched MySQL files, but I found the file counties.frm, which I assume I would open with Notepad, right? And then I can make SET FOREIGN_KEY_CHECKS = 0; the very first line and SET FOREIGN_KEY_CHECKS = 1; the very last line, after which I would EXPORT my database, then import it, right? They do into the EXPORTED file after you create it. And since I like to plan ahead, is it OK to insert these two lines in ALL my MySQL documents, just to be prepared for this error? If I inadvertently stick these lines in a file that doesn't have a foreign key, will it cause a problem? No it will not cause a problem. You are just turning off the check for RI (just like the If Exits clause on the DROP TABLE command turns off the check to see if there is a table to delete before doing the CREATE TABLE). Finally, after I've imported my database online, do I have to go back and remove these two lines, or can I just leave them there indefinitely as a safeguard? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases: Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where do exported SQL files go?
At 20:13 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) Might it be doing this due to PHPAdmin being a WebPage application and thus being served by your Web Server making the file need to be a FTP Download g? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reloading Database with Foreign Keys
I just tried to reload a mysqldump created dump to a new server and got rejected due to lack of referential integrity. I remember the existence of a command I can insert in the file that will turn off the checking of the Foreign Keys while the recreation is being done but I can not locate it in the Docs. Can someone help me by supplying me with the correct command (which I remember as setting some switch to False/Off at the start of the recreation and resetting it to True/On [or vice-versa] at the end of the recreation)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reloading Database with Foreign Keys
I just tried to reload a mysqldump created dump to a new server and got rejected due to lack of referential integrity. I remember the existence of a command I can insert in the file that will turn off the checking of the Foreign Keys while the recreation is being done but I can not locate it in the Docs. Can someone help me by supplying me with the correct command (which I remember as setting some switch to False/Off at the start of the recreation and resetting it to True/On [or vice-versa] at the end of the recreation)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reloading Database with Foreign Keys
At 18:01 -0400 on 06/01/2004, Bartis, Robert M (Bob) wrote about Re: Reloading Database with Foreign Keys: See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE dump_file_name; mysql SET FOREIGN_KEY_CHECKS = 1; Bob Thanks - that was it and it fixed my problem. -Original Message- From: Robert A. Rosenberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 01, 2004 5:56 PM To: [EMAIL PROTECTED] Subject: Reloading Database with Foreign Keys I just tried to reload a mysqldump created dump to a new server and got rejected due to lack of referential integrity. I remember the existence of a command I can insert in the file that will turn off the checking of the Foreign Keys while the recreation is being done but I can not locate it in the Docs. Can someone help me by supplying me with the correct command (which I remember as setting some switch to False/Off at the start of the recreation and resetting it to True/On [or vice-versa] at the end of the recreation)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate
At 12:38 -0700 on 05/27/2004, Daevid Vincent wrote about Re: Feature Request: UPDATE 'error codes' or mysql_affected: REPLACE INTO won't work, as I need the previous record (hence the update). I store the first and last time I saw a node, amongst other info. REPLACE would delete that data. REPLACE would (might?) also fail if the Primary Key is some other table's Foreign Key (although this may be a permissible deletion since the record is not actually getting deleted but just the fields getting updated). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATETIME question
At 11:01 +0300 on 05/26/2004, Egor Egorov wrote about Re: DATETIME question: John Mistler [EMAIL PROTECTED] wrote: Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT statement that will: select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND 'HH:MM:SS', but whose (DATE) is anything? SELECT .. FROM t1 WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'; or from version 4.1.1 you can use TIME() function for this purpose. Description of DATE_FROMAT() and TIME function you can see at: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Note: This is the correct solution for your current setup. One minor caveat on going this route - Since you are looking at something past the start of the field, you must read every row for the check and can not use an index over that column. Depending on the number of rows and how often you do the select (and how hard it would be to alter the code that inserts new rows), you might want to look into adding a new column that has only the time in it and make it an Index. Then you can use the index column in your select. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a complicated join
At 14:07 -0500 on 05/25/2004, [EMAIL PROTECTED] wrote about need help with a complicated join: I am trying to come up with a query that takes two tables, one with non-split-adjusted historical stock prices, and one with information on splits, for instance: CREATE TABLE quotes ( symbol VARCHAR(127)NOT NULL, dateDATENOT NULL, quote FLOAT NOT NULL, PRIMARY KEY (symbol, date), INDEX (date), ); INSERT quotes VALUES (A, 2004-01-01, 3); INSERT quotes VALUES (A, 2004-01-02, 3); INSERT quotes VALUES (A, 2004-01-03, 3); INSERT quotes VALUES (A, 2004-01-04, 3); INSERT quotes VALUES (A, 2004-01-05, 2); INSERT quotes VALUES (A, 2004-01-06, 2); INSERT quotes VALUES (A, 2004-01-07, 2); INSERT quotes VALUES (A, 2004-01-08, 1); INSERT quotes VALUES (A, 2004-01-09, 1); CREATE TABLE splits ( symbol VARCHAR(127)NOT NULL, dateDATENOT NULL, split_from INT UNSIGNEDNOT NULL, split_toINT UNSIGNEDNOT NULL, PRIMARY KEY (symbol, date), ); INSERT splits VALUES (A, 2004-01-05, 2, 3); INSERT splits VALUES (A, 2004-01-08, 1, 2); I need to be able to pull out split-adjusted quotes, like this: SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = A ORDER BY date; +++---++ | symbol | date | quote | adjusted_quote | +++---++ | A | 2004-01-01 | 3 | 1 | | A | 2004-01-02 | 3 | 1 | | A | 2004-01-03 | 3 | 1 | | A | 2004-01-04 | 3 | 1 | | A | 2004-01-05 | 2 | 1 | | A | 2004-01-06 | 2 | 1 | | A | 2004-01-07 | 2 | 1 | | A | 2004-01-08 | 1 | 1 | | A | 2004-01-09 | 1 | 1 | +++---++ Split-adjusting means that on a split date all previous prices are multiplied by split_from/split_to ratio. In my example two splits took place, one on 2004-01-05, which multiplied all previous prices by 2/3 and another one on 2004-01-08, which multiplied all previous prices (including those already affected by first split) by 1/2. Any help would be appreciated. Question: Are you doing this direct in MySQL or is it being done as a Web Inquiry that is doing the MySQL Select Under the Covers and then displaying the result? If the latter, then you can do it by first building a Temp Table of all records where symbol=A (fill in the requested symbol from the user query) AND date=as-of-date (again supplied by user) creating an adjusted field equal to the quote. The temp table now has only the requested table rows and ends at the as-of-date. You then read the splits table for all records dated before or on the as-of-date and do the updates to the adjusted field for each adjusted row (you can have the loop update the factor as needed so you only need to run the table once). Then just read and display the temp table. One additional question. From your definition when you say non-split-adjusted historical stock prices I assume that you mean that on the day a split occurs, that day's quote HAS BEEN adjusted for the split and all subsequent quotes are based on the status of the latest split. What you are trying to do with the adjusted column is adjust for the splits and have the quotes reflect the quote in terms of a block of stock quoted on day one in the table. IOW: If the first quote in the table was for 1 share and due to splits that block is now 5 shares, you want to multiple the todays (1-share) quote by 5 to get a constant based price and do the same on the other day by using the then current block size as the adjustment factor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing on OS X without installer
At 08:30 +1000 on 05/24/2004, Chris Curnow wrote about Installing on OS X without installer: Hi, I've just downloaded the latest version of MySQL (4.0.20) for OS X. They seem to have omitted the installer - there's no .dmg file to download. The manual only covers installing from the .dmg for OS X. Can anyone help me on how to install without the installer. You can just drag the folder to /user/local and then update the SoftLink for mysql. Or you can just go back to the mysql site and do the download again since they fixed their error and now have a dmg installer. In either case, do not forget to drag the data folder from your old mysql folder to the new one. best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running more than one level of MySQL
At 15:09 -0500 on 05/18/2004, Paul DuBois wrote about Re: Running more than one level of MySQL: At 15:46 -0400 5/18/04, Robert A. Rosenberg wrote: I have a site that is being hosted by an ISP which is running version 3.23.52. When I questioned why that downlevel version and not a 4.0 version (such as 4.0.18 or the just released 4.0.20), I was told Unfortunately, when versions change on MySQL, they also drop features and change security settings. This can cause many problems system wide. Before I go further with my discussion and renew my request for a 4.0 Database, I would like to know if it is even possible to have more than one level active (and if so, what is involved in the set-up). It's perfectly possible. I have dozens of versions installed, though not all necessarily running at the same time. :-) http://dev.mysql.com/doc/mysql/en/Multiple_servers.html Thank you for your reply. I am passing it on to the ISP in the hope that they will use it to provide an option to offer current level support to those who need/want it (by just adding the new port number to the Host Name in MySQL connection request). I have one suggestion to be added to the base code to assist in running multiple levels - Allow the --port=port_number parameter to take a list as opposed to only one number (so the server monitors more than one port and treats them as if they were the same). This would allow a migration to the newer versions to proceed by having the base port number as well as the alternate one be available for the use after the migration while those who were using the alternate port number in their calls are removing the number so as to again use the standard port number. Initially, the downlevel version would have ownership of the standard port as well as a private port (for those who do not want to be migrated after the move) which would be reassigned on Roll Day to the newer version. The alternate port could then be scheduled to be removed after a period of time to allow for its removal from the Host Name calls by those who were using the newer version prior to the Roll. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: check for certain characters
At 23:51 -0400 on 05/11/2004, Michael Stassen wrote about Re: check for certain characters: Then you could add NOT to Paul's query: SELECT * FROM your table WHERE sequence NOT REGEXP '^[atcg]+$'; or, equivalently, SELECT * FROM your table WHERE sequence REGEXP '[^atcg]'; I suspect the latter may be faster, but you'd have to try them to be sure. Note that pattern matching in mysql is case-insensitive by default. If that matters to you, then you would need to add the BINARY keyword to the WHERE clause: WHERE sequence NOT REGEXP BINARY '^[atcg]+$'; or WHERE sequence REGEXP BINARY '[^atcg]'; Michael The need to go BINARY to detect case also requires that sequence be a BLOB not a TEXT field (I might have the case-sensitive/case-insensitive types reversed), -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blocking Selects with LOCK TABLES
At 12:40 -0400 on 05/10/2004, Lou Olsten wrote about Blocking Selects with LOCK TABLES: x-charset iso-8859-1According to the docs (http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html) : If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can read from or write to the table. Other threads are blocked. So, I've got two threads going (T1, T2). T1 issues LOCK TABLES transtest WRITE; But when I go to T2, I can still issue: SELECT * FROM transtest; and retrieve all the data. I CANNOT update, so I know the command is at least partially working. As I understand it, I'm supposed to see a message from T2 that says something about This table has been locked with the LOCK TABLES command. Did T2 issue a LOCK TABLES transtest READ; or did you just try doing the read without trying to get a READ Lock? If the latter, then that is your problem. Once you start locking a table, EVERYONE needs to get locks on that table. It is an InnoDB table, if that matters. Thanks, Lou /x-charset -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This thing called MOD
At 18:27 +0200 on 05/09/2004, Thomas Nyman wrote about This thing called MOD: Hi All I'm a bit perplexed..perhaps its a language thing,,but the MYSQL reference manual says that MOD ..Returns the remainder of N divided by M... and gives an example SELECT MOD(234,10) -- 4 This I do not understand. remainder of N divided by M - isn't that simply division? I mean 234 divided by 10 does not equal 4 Yes it does. It means 23 Tens and a remainder of 4 (ones). On my own machine..if I do SELECT MOD(23,6) I would expect 3,8333 as the result and not 5. SInce MOD is returning something other than I expect there must be something I am missingin other words...what is MOD returning?? Thomas As others have explained to you, MOD has to do with INTEGER Arithmetic. Thus MOD(234,10) returns 4 since after you have taken always the 10s, you have 4 left over. As a real world example, you have to make up some amount from 1 cent to 999 cents (such as $2.34) using ONLY Dimes and Pennies (with the rule that you have 9 Pennies and 99 dimes to use). MOD tells you how many pennies are required after you have used enough (ie: 23) dimes to get to $2.30. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers (or too-many-crappy-questions)
At 12:22 -0700 on 04/30/2004, Eric wrote about Re: triggers (or too-many-crappy-questions): Hi, Can we have list for people who just like to give newbies shit? How about [EMAIL PROTECTED] How much have you guys paid for your support hmm? This is a free mailing list and I don't really see why people have to moan about a few simple questions every once in a while. The people who moan about RTFM crap are much more of a bother to me than a poor guy who doesn't know his way around mysql yet, doesn't get how useful and complete the manual is, and needs some basic help. Thanks, Eric I've been on mailing lists that have attempted to TRY TO solve this problem by sending a Top 10 FAQS Message along with a Welcome - You're now Subscribed one to all new subscribers. This seems to help keep the RTFM type of questions to a lower level. Putting a mail-to link to request a new copy of the Top 10 message in the list signature (for those lists that add a footer signature) keeps the address visible once the user is on the list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar 4 = char 4? Why?
At 13:34 -0400 on 04/18/2004, Stormblade wrote about varchar 4 = char 4? Why?: This has been puzzling me. At first I thought it was something that Navicat was doing but I also tried in EMS MySQL and it does the same. If I set the type of a field to varchar and set the length to anything less than 4 it will get converted to a char type of the same length. Now I know that char is faster. I read it can be up to 50% faster but I am curious why I am prevented from having a varchar of length less than 4? Is it prohibitively expensive to do this in MySQL? Is it such a bad idea that they simply don't allow you to do it? Inquiring minds want to know. Taking a wild guess here so I might be wrong. Varchar needs a length to be appended to the start of the character data so MySQL can tell how long it is. This length field is probably 2 bytes long. Thus if the max length of the string is 1-3 bytes long, the field will be from 3-5 bytes so you might as well bite-the-bullet and just declare the field as char(4). If the length field is 4 bytes, then ALL Varchars under max=3 will be 5-8 bytes for max=1-3 so again char(4) is shorter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query about MySQL and Access Permissions to MySQL Database FILES
I am talking a PHP+MySQL course at my local community college and since this is the first time the course is being offered there are some teething problems with the curriculum. I am posting this query at the request of the instructor. We are using a Linux Server and each Student has their own set of directories on the Server. The MySQL Databases are created on local PCs and then FTP'ed to the user's directory on the server. The files get User=RW and Group/World=R permissions due to the FTP being used having no way to set some other set of default Permissions and no way to update them once uploaded. The Databases are made known to MySQL by using a Softlink in the MySQL data folder that points to the actual copy in the user's directory. We would like to avoid the need to constantly go in and update/correct the permissions to G/W=RW after each upload of new copies of the Database Folder or 3 Files that comprise a database. Now that the background has been covered, here is my question. Since to gain access to a Database from the PHP Code, a mysql login request is required, it seems to me that this login can provide the MySQL Server the information needed to switch to the respective user's UID when accessing the database for update (as opposed to just read) purposes (thus getting RW Permission to the files). Does MySQL have the setuid authority so it can do so (and if so, does there code exist there to do so)? If not, is there some other way to allow Update Access to the respective databases based on the Login UserID other than go Group/World Writable on the 3 files that comprise the database? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
