RE: Can a row be refered using row number?
Why would you want to do this? As data moves around within the table the updates will be in error. Wouldn't it be easier to assign a unique key to each row, search for the key or unique set of information and update the resulting row? Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, August 10, 2006 4:43 AM To: mysql@lists.mysql.com Subject: Can a row be refered using row number? Hi All, Is there any way to refer a row based on the row number or row count? I just the effect as below... E.g. UPDATE TBL_NAME SET COL_NAME = xyz WHERE ROW_NO=4 Or SELECT COL_NAME FROM TBL_NAME WHERE ROW_NO=5 Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL service startup time
Considering there are a number of pieces that are involved in the connection being ready why not create a small program that tries to connect and checks the return value. If it fails sleep for 500ms to 1 sec and try again for up to X-times before aborting altogether? Bob -Original Message- From: Rob Desbois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 11:51 AM To: mysql@lists.mysql.com Subject: MySQL service startup time Hi, I have an application self-installer program which also installs MySQL and sets it up. This is all on Windows. I have a problem in that when the installer runs 'net start MySQL', it returns immediately but the MySQL daemon is not ready for connections immediately. As the next step in the installation is to create the application database, I need to wait until I can connect. What's the best way to achieve this? At the moment I have a rather crude 5 second 'sleep', but that isn't always long enough. Any ideas? --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- 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: New to the group
If you will excuse my ignorance. I have no immediate need for this, but have often asked what the pros/cons there are writing a WEB based interface in PHP vs. say Perl. Do you have any insight into that? Thanks Bob -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, June 22, 2006 3:39 PM To: mysql@lists.mysql.com Subject: Re: New to the group At 08:46 AM 6/22/2006, Nicholas Vettese wrote: Hello, My name is Nick, and I am a new MySQL user. My hope is not to become a PITA, so I will make sure that any question is straight and to the point with the information needed to answer the question. My skill in MySQL is pretty low, and I am looking to build a website for myself that will take information and save it to a database. At this time, I have a login, registration, change/lost password functionality working from a book that I read, but I am looking to expand my knowledge into more robust site. I am not looking to become the master programmer, just someone with enough knowledge and skill to accomplish his goals. Thanks, Nick Welcome Nick, You've come to the right place. There are a couple of books on MySQL that are quite good and I'd like to recommend. MySQL 3rd Edition by Paul Dubois and MySQL Cookbook by Paul Dubois (I think these guys are relatedvbg) If you are using PHP to build your website I found PHP and MySQL for Dynamic Web Sites : Visual QuickPro Guide (2nd Edition) (Visual Quickpro Guide) to be quite good and gets you going quite fast. There's not a lot of reading to do and they have you writing PHP code the first day. If you want a more thorough book on PHP MySQL there is: PHP and MySQL Web Development (3rd Edition) (Developer's Library) (Paperback) by Luke Welling, Laura Thomson There are also PHP/Mysql tutorials on the web but I don't know how good they are. You'll get up to speed faster by getting some of these books. Of course if you're not using PHP, then someone else can jump in with some reading suggestions. 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: Is This A Redundant Info Example?
Bad design that violates basic DB design checks. Redundant information will become a major problem for you going forward. Unless you have hard and fast performance issues they require it, just don't do it:-) Create a single table that contains customer info and reference the information using Foreign keys in your other tables that require customer information. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, May 19, 2006 8:57 AM To: Mark Sargent; mysql@lists.mysql.com Subject: RE: Is This A Redundant Info Example? [snip] Why have customer info in both? Delivery and Billing info makes sense, but why the redundant info in both? Anyone got views on this? Do/would you do it differently, and could you tell us why? Cheers. [/snip] It is bad database design IMHO. -- 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]
Unknown column 'testcase_root.Test' in 'order clause'
I am receiving an error Unknown column 'testcase_root.Test' in 'order clause' I do not understand why the error is pointing to this as an error, nor if it means anything that the name of the column is correctly identified in the query below as testcase_root.TestID. I have checked the DB and the column is present. Can someone help point out what I am missing and or steps to debug the problem. SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE (((testplans.plantriggers_ID_FK)=76530)) OR (((testplans.plantriggers_ID_FK) Is Null)) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED]
RE: Unknown column 'testcase_root.Test' in 'order clause'
Sorry:-\ Meant to say I do not understand why mySQL is pointing to this as an error Bob -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 5:30 PM To: 'mysql' Subject: Unknown column 'testcase_root.Test' in 'order clause' I am receiving an error Unknown column 'testcase_root.Test' in 'order clause' I do not understand why the error is pointing to this as an error, nor if it means anything that the name of the column is correctly identified in the query below as testcase_root.TestID. I have checked the DB and the column is present. Can someone help point out what I am missing and or steps to debug the problem. SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE (((testplans.plantriggers_ID_FK)=76530)) OR (((testplans.plantriggers_ID_FK) Is Null)) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [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 a schema?
A schema is a the database design. Sometimes textual, sometimes visual definition of the database structure (tables, field types, defaults etc). The database is the physical implementation of the schema that holds the data. Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 03, 2005 10:21 AM To: mysql@lists.mysql.com Subject: What is a schema? What is a schema? How is different from a database? -- 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: Phone Number Storage
That may be true, but I don't think the augments provided by Joerg necessitate a single column or multiple columns. His points, leading zeros, sorting, etc go more to the native data type that should be used and are valid in either case. Bob -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 4:23 PM To: Joerg Bruehe; mysql@lists.mysql.com Cc: Asad Habib Subject: RE: Phone Number Storage I guess anywhere we have 3 levels of hierarchies for a phone number. (Country code, Area code and the actual number). The advantage of seperating them into different columns(Either an integer or a string) is that he can group different phone numbers based on area code or country code. sujay -Original Message- From: Joerg Bruehe [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 26, 2005 1:34 AM To: mysql@lists.mysql.com Cc: Sujay Koduri; Asad Habib Subject: Re: Phone Number Storage Hi! Sujay Koduri wrote (re-ordered): -Original Message- From: Asad Habib [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 11:53 PM To: mysql@lists.mysql.com Subject: Phone Number Storage Is it better to store phone numbers as strings or as integers? Offcourse, storing them as integers saves space but this requires extra processing of the user's input (i.e. CPU time). Are there any other advantages/disadvantages of doing it one way or the other? - Asad I think it is better to store the phone numbers as strings only. As phone numbers may also include '-', if you allow entering international numbers, it is good to store them as strings only. Or you can ask the area code and the actual number seperately and store them seperately in two columns as integers. IMO, this is quite an USA-centric view in the answer: In general, phone numbers will also contain a country code. Outside the USA, it is quite common that codes (area or country) may begin with a leading 0 which any numeric type would drop as not significant, so you _must_ use strings for these. Also: A telephone number is no numeric value, arithmetic operations do not make sense on it. Think of extensions: phone numbers 1234-0 and 1234-56 are related, so you would order them (if at all) as strings and not as numeric values. The same applies to postal codes, social security numbers, part numbers etc. While you may use a numeric type for some ID value you want to generate yourself (using autoincrement), IMO this is on the borderline of correct modeling. For phone numbers, you should use strings. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.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: Seriously.. When are we going to get subqueries?!
Folks...please This has really degraded. I seriously think its time everyone sign up for charm school or better yet share these emails with your grandmother who will more than likely crack you all upside your head and knock some minimal level of common decadency into you all. Its an email alias. You're asking for help from people you don't even know. You should therefore present your needs clearly and concisely. You should expect there to be questions. You should expect to not always get timely information. you should expect to get wrong answers from time-to-time. Its the nature of the beast. You should also get a feel if you follow the list that you will also, more often than not get the help you need or at a minimum pointers to help you along. I have and continue to be impressed with the level of help I have received. Its often on par with paid services. If I get called a Bone-head, than I have the choice to clarify my question or move on, but coming back again and again serves no-one. Lets stick to the technical issues and hopefully all become better because of it. For those of you that can't.there's always grandma:-)) Bob Bartis -Original Message- From: George L. Sexton [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 2:26 PM To: mysql@lists.mysql.com Subject: Re: Seriously.. When are we going to get subqueries?! You obviously don't understand the limitations of timestamps. Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. It's not an issue I don't know. It's an issue I'd like to see fixed so that I can list MySQL as a supported database along side PostgreSQL Oracle SQL Server Sybase SQL Anywhere Microsoft Access IBM DB2 That's what I'm after. On Thursday 09 June 2005 10:53, Jeff Smelser wrote: On Thursday 09 June 2005 11:47 am, George Sexton wrote: I'm working in that direction. I first posted to the regular mysql list, and then I posted again to the internals list. I guess the next step is to talk to the MySQL people. We answered you I thought.. Whats the issue you dont know? Jeff -- 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: View
I believe 5.0 does. -Original Message- From: Andreas Ahlenstorf [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 2:55 PM To: Jerry Swanson Cc: mysql@lists.mysql.com Subject: Re: View Am 02.06.2005 um 20:31 schrieb Jerry Swanson: Does Mysql 4 supports views? No. Regards, 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]
Subquery question
I have a problem where I need to use a subquery in combination with a Left Join. The SQL statement below works fine until I introduce the subquery portion. Specifically, WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) Can someone point out where the error is. I double checked the column name/tables names in the above portion and as I said the main body worked prior to introduction of the subquery. I've also tried replacing the IN operand with an = with no luck. Full SQL statement: SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED]
RE: Subquery question
Apologize for not including the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT plantriggers.ID FROM plantriggers WHERE (((plantrigger I'm running MySQL 4.0.2-standard -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 6:29 PM To: 'mysql' Subject: Subquery question I have a problem where I need to use a subquery in combination with a Left Join. The SQL statement below works fine until I introduce the subquery portion. Specifically, WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) Can someone point out where the error is. I double checked the column name/tables names in the above portion and as I said the main body worked prior to introduction of the subquery. I've also tried replacing the IN operand with an = with no luck. Full SQL statement: SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Subquery question
That might explain it:-) The really said part is I remember running into the same issue some months back and completely forgot. Thanks -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 6:54 PM To: Bartis, Robert M (Bob) Cc: 'mysql' Subject: Re: Subquery question In the last episode (May 25), Bartis, Robert M (Bob) said: Apologize for not including the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT plantriggers.ID FROM plantriggers WHERE (((plantrigger I'm running MySQL 4.0.2-standard 4.0 doesn't support subqueries. You'll need to upgrade to 4.1. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
Something like this would make more sense to me and provide greater flexibility; student student_id name age address --- address_id street_name city state zip phone_num -- phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) student_info --- student_id_FK phone_num_id_FK address_id_FK Spent all of 10 mins on this so its not perfect. Bottom line is I would not include the student_id in the address and phone tables. It precludes a student having multiple phones or addresses with out duplicate data. The addition of the student_info table provide the 1:1 or 1:N mapping you're looking for I believe. The only thing you need to ensure is properly set the Cascade on update and restrict on delete options to ensure data integrity. My gut tells me it may be a better implementation to map the student/phone and student/address separately and then create the student_info using keys from these intermediate tables, but it more complicated and it not clear what the constraints on your problem is. Bob Bartis -Original Message- From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 24, 2005 1:34 PM To: mysql@lists.mysql.com Subject: DB design question Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a question about MySQL installation
Suggest you review the MySQL documentation http://dev.mysql.com/doc/mysql/en/installing.html and in particular 2.3.13. Testing The MySQL Installation and 2.3.14. Troubleshooting a MySQL Installation Under Windows Bob -Original Message- From: Terry Leung [mailto:[EMAIL PROTECTED] Sent: Sunday, May 22, 2005 7:12 AM To: mysql@lists.mysql.com Subject: a question about MySQL installation Dear, I have installed MySQL4.1. But why it can not startup when I open my computer? Also, how can I test MySQL can run or not? Thanks for your advice. Best Regards, Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single vs Multiple primary keys
I have a table, see below, that contains a single primary key (SubTestCaseKey )
and a number of foreign keys
* plantriggers_ID_FK ,
* testcase_root_ID_FK
* testcasesuffix_name_FK
What I want to ensure is that there are no duplicate records when considering
the three foreign keys above. Would it be appropriate to remove the single
primary and replace with three multiple primary keys? Is there a performance
impact when doing this. this seems overly complex and wonder if I should be
breaking the table up to simplify? Any suggestions would be appreciated.
Bob
CREATE TABLE testplans (
SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
testcase_root_ID_FK INTEGER NOT NULL,
testcasesuffix_name_FK VARCHAR(20) NULL,
FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
DateMod TIMESTAMP NULL,
tester_list_Name_FK VARCHAR(50) NULL,
PRIMARY KEY(SubTestCaseKey),
INDEX testplans_FKIndex1(tester_list_Name_FK),
INDEX testplans_FKIndex2(testcasesuffix_name_FK),
INDEX testplans_FKIndex3(testcase_root_ID_FK),
INDEX testplans_FKIndex4(plantriggers_ID_FK),
FOREIGN KEY(tester_list_Name_FK)
REFERENCES tester_list(Name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcasesuffix_name_FK)
REFERENCES testcasesuffix(name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcase_root_ID_FK)
REFERENCES testcase_root(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(plantriggers_ID_FK)
REFERENCES plantriggers(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE
)
TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: [EMAIL PROTECTED]
Pgr: [EMAIL PROTECTED]
RE: Write to a mysql table from Excel
One problem I've encountered in the past creating CSV files from Excel is with fields that exceed 256 or 258 characters. The fields end up truncated in the CSV file. A script to directly access the data in Excel and move it to MySQL is appropriate, but can be a lot of work if you only need to do the import one or twice. Although this is somewhat convoluted it works for me. Start MS Access and create linked tables to you MySQL DB. Then import the data from Excel into Access. They're integrated fairly well so the import is basically a couple of button clicks. Then you're done. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- 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: Changed Number
Sounds like the type for the field you're storing the number is not capable of holding a number sized as 16996941. What's the field type you're storing the number in? Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:31 PM To: mysql@lists.mysql.com Subject: Changed Number I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? Ken -- 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: CSV-to-SQL?
Maybe a review of http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html will shed some light? -Original Message- From: Renato Golin [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:48 PM To: Richard Miller; Mysql Subject: Re: CSV-to-SQL? You could use the CSV table type: http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html Or use the LOAD DATA INFILE to import all data: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html --rengolin --- Richard Miller [EMAIL PROTECTED] wrote: I have a dozen, very large CSV files that I would like to put into a MySQL database, with 1 table per file. Does anyone know of a PHP (or other) script that can read the first few lines of a CSV file and create an appropriate CREATE TABLE statement based on the data it finds? (Even better, it could import the file afterwards!) I'm not picky about data types here; I'd simply like to get this data into tables so I can work with it more easily. Thanks, Richard Miller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.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: Changed Number
Maybe a review of http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html will help? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:31 PM To: mysql@lists.mysql.com Subject: Changed Number I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? Ken -- 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: [Q] Database design
Sounds like you need a 1:N relationship table to hold userInfo separate from either the user or group table. Adding a infoIdentifier would allow the number of rows added for a specific user to be sized based on the specific user needs. This is effect would be the key part of a key-value pair, normally associated with associative arrays. The userSpecificInformation would hold the value portion of the information. UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation user table: id (pk) name any other user info only dependant on the user group table: id (pk) name usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation -Original Message- From: Eric Gorr [mailto:[EMAIL PROTECTED] Sent: Saturday, April 02, 2005 2:59 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: [Q] Database design Tom Crimmins wrote: user table: id (pk) name any other user info only dependant on the user group table: id (pk) name any other group info only dependant on the group usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo Yes, if I understand what you are saying here correctly, I considered this. However, the problem is that the columns corresponding to any info specific to individual user/group combo is not guaranteed to be consistent across groups. Well, to be more precise, the type for each column will be the same, but the number of required columns (call this number N) will be different. It is for this reason that it seemed necessary to have a separate table per group. Now, if I could decide what the maximum number of required columns would be, then I could see using this design, but this is simply not possible. I am, of course, limited by the maximum number of columns (call this number X) allowed within a mySQL database. The required number of columns for a particular group could be anywhere between 1 and X. However, it just seemed like a bad idea to use that large of a table when the vast majority of it would go unused and much of it would likely never be used at all. But, perhaps I am wrong and it would simply not be an issue. I suppose it would be possible to dynamically size 'usergroup table' based on the current max N across all groups. Basically, if N changes for a particular group, look at the value of N for all groups, take the max and size 'usergroup table' accordingly. Is this what you would do? -- 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: needs some info about MySql
I am certainly no expert, not even a DB admin:-\ My non scientific algorithm when selection of a DB was a question for a small team was as follows: 1) Go to local book store 2) Find section on DB, browse 3) Look for performance tuning of Oracle 4) Find chair quickly when you realize there are libraries on tuning alone! 5) Look for performance tuning of MySQL 6) Select a book and pay 7) Leave store In all seriousness I was struck by the apparent complexity in tuning Oracle when looking through the available literature. In one case I found a series of 7-books. MySQL had a number of book available, all of which seemed reasonable straight forward and none of which came in a set. As I said, non scientific and I am not a DB Admin, but for a small group such as mine it was critical to find something with low overhead. Bob -Original Message- From: Joan Hsieh [mailto:[EMAIL PROTECTED] Sent: Thursday, March 31, 2005 3:38 PM To: mysql@lists.mysql.com Subject: needs some info about MySql Hi, I'd like to know with a very extensive oracle dba experience, how's the learnig curve for a new MySql server, I don't have any mysql experience or knowledge at all. We have one existing mysql server want us to take over for adminsitrative and maintances. How often mysql needs to be patching, upgrade? and how it could be for the performance tunig compare it to ORACLE tuning? thanks a lot, Joan -- 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: Importing data from MS Access
Its my understanding you need InnoDB table types, but I do not know of the single foreign key per table. I have a DB with multiple foreign keys per table and its seems to work fine. Bob -Original Message- From: Paun [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 11, 2005 5:19 PM To: mysql@lists.mysql.com Subject: Importing data from MS Access I use MySQL-Front 2.5 (freeware) for importing data from MSAccess databaase in MySQL 4.1.7. Of course, there is no possibility to import foreign keys from MSAccess. Question: If I read properly documentation in MYSql is possible to use only one foreing key per table, and fields with foreign key must be on ordered on the same way in booth tables, and only in InnoDB type of tables. I have tables with many foreign key connectons (e.g. members / streets, members/occupations, members/state of membership) between tables, and it was easier way to keep data in database in proper order with less programing. Is that is not possible in MYSQL? Of course, I am very new in MYSQL. Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.9 - Release Date: 1/6/2005 -- 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: synchronizing mysql database in two different places
As suggested by Karam, please refer to http://dev.mysql.com/doc/mysql/en/Replication_Implementation.html for additional information. -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Sunday, October 31, 2004 1:29 PM To: Eko Budiharto; [EMAIL PROTECTED] Subject: Re: synchronizing mysql database in two diferrent places Hello, You might try MySQL's inbuilt replication feature. If you want a more simple approach, might take a look at SQLyog's Data Sync Wizard. More information can be found at http://www.webyog.com I think they have a article on the same subject in SitePoint. You might google it. Karam --- Eko Budiharto [EMAIL PROTECTED] wrote: Hi, I a facing a situation where I have two database server in two different places that has exactly the same database. The thing is if the database is modified in either one of the places, both database has to be updated lively. Can someone tell me who done this already? Please help. I am looking forward to a favorable reply from you. Thank you. __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. 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]
Guest login permissions
I have been working to configure MS Access 2000 as a front-end to an application running MySQL 4.0.20-standard. Our thinking was to have a guest login with minimal privileges (Select only) setup as the default on each users PC. This would allow anyone on the team to access the DB using the front-end (Read-only). We also present a login screen for those power users. Allowing them to login and make changes to the data. The login screen is really to confirm that the user name/password provided is recognized by MySQL (Open/Close connection). From that point forward any operation that results in an update would be done by opening a connection using the userid/password provided and execute the command based on this users privileges. I have shown that if I set the ODBC connection using my ID/password and then login as myself using the front-end everything works as expected. I have also verified that if I login is as guest everything works as expected. The problem comes when I set the ODBC connection up as guest. I use the Test Data Source button to confirm I've provided a valid Guest/password. I then login into the front end as myself or guest and click on a button that results in a small query being run. Instead or the expected results I receive an error that [EMAIL PROTECTED] access denied with password YES. This is followed by the ODBC connector screen being presented at which point if I click the Test Data Source button the test fails, unlike the first time. The permissions for guest are set up as %.lucent.com and with Select privileges. Any idea on what I am doing wrong and or if the approach taken is problematic? Should I be following a different method? I've attached the trace log of from the ODBC connector in which I first tested the data source using guest as a login, then connected via the MS Access front end logged in as guest, which succeeded and then attempted to run a small query. Saw the access denied message and again attempted to test the data source using the ODBC connector screen which failed this time. Not sure where the Admin login request in the trace is coming from. I checked my code and nowhere do I attempt a login in as Admin??? SQL.LOG Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding records not in a set
Thanks for the tip. I'm still facing an issue where I think I have the right syntax and I'm receiving an ODBC failure. Do you have any suggestions on how to go about understanding why the failure and hence how to correct it? Bob -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 9:30 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Finding records not in a set Keep in mind EXISTS/NOT EXISTS are for SQL (Parent) Heavy queries Otherwise you should use IN/NOT IN Martin Gainty 617-852-7822 Man1: In my next life I want to get paid for solving problems Man2: You sound like a misguided capitalist! Man1: But how do I pay my bills?? Man2: You can always beg on the street..problem solved From: Bartis, Robert M (Bob) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Finding records not in a set Date: Sat, 9 Oct 2004 08:53:19 -0400 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc7-f3.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Sat, 9 Oct 2004 05:54:52 -0700 Received: (qmail 20728 invoked by uid 109); 9 Oct 2004 12:53:26 - Received: (qmail 20709 invoked from network); 9 Oct 2004 12:53:26 - Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 192.11.226.161 as permitted sender) X-Message-Info: JGTYoYF78jEAJ70xKNiMjuhlQYGFj9no 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/173887 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] X-Mailer: Internet Mail Service (5.5.2657.72) X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 09 Oct 2004 12:54:52.0865 (UTC) FILETIME=[2B57B310:01C4ADFF] I did find a reference to EXIST/NOT EXISTS clause in the SQL manual, but I get an ODBC Failed call when I run the following simplified query SELECT * FROM main_db WHERE NOT EXISTS (SELECT * FROM featureenable WHERE featureenable.FeatureKey = main_db.FeatureKey); Bob -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 8:48 AM To: [EMAIL PROTECTED] Subject: Finding records not in a set I have a query, call qry_AssociatedFeatures, that finds all features associated with a given plan SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, featureenable.PlanName FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = main_db.FeatureKey WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, or all the features not associated with a plan. Complication here is the feature enable table can have the same feature key associated with multiple plans. I assumed if I took the table containing the unique set of features and query for those records whose feature key is not present in the qry_AssociatedFeatures query I would get what I wanted SELECT DISTINCT main_db.Feature, main_db.FeatureKey FROM main_db, qry_AssociatedFeatures WHERE (((main_db.FeatureKey)[qry_AssociatedFeatures].[FeatureKey])); Unfortunately, I still see records in the qry_UnassociatedFeatures that are also present in the qry_AssociatedFeatures. Any suggestions? -- 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] _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC Call failed - Query appears to be corrupt?
I have a query that I have put together. The query is an attempt to retrieve records from one table, main_db, whose keys are not present in another, featureenable. I am using the NOT EXISTS keywords and continue to receive an ODBC---Call Fail error. I traced the ODBC calls and see something very odd. It appears from the trace that the query being passed from an MS Access front-end to the ODBC connector is being corrupted I've extracted the lines of interest shown below. Please note the MS2 referenced in the 2nd line. These are not in the original query I passed in. Why is the query being passed to the ODBC connector changed? The full queries are also shown. .EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey = featureenable.FeatureKey); .EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey Original Query == SELECT DISTINCT [functionalsubgroup].[Functional], [functionalsubgroup].[SubGroup], [main_db].[Feature], [main_db].[FeatureKey], [functionalsubgroup].[FSKey] FROM featureenable INNER JOIN (functionalsubgroup INNER JOIN main_db ON ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey])) ON [featureenable].[FeatureKey]=[main_db].[FeatureKey] WHERE NOT EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey = featureenable.FeatureKey); ODBC Call Trace STRDB-v2.0b14 83c-218 ENTER SQLExecDirectW HSTMT 08DC1C30 WCHAR * 0x0F271F40 [ -3] SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0 SDWORD-3 STRDB-v2.0b14 83c-218 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 08DC1C30 WCHAR * 0x0F271F40 [ -3] SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0 SDWORD-3 DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-standard]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`M (1064) Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding records not in a set
I have a query, call qry_AssociatedFeatures, that finds all features associated with a given plan SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, featureenable.PlanName FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = main_db.FeatureKey WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, or all the features not associated with a plan. Complication here is the feature enable table can have the same feature key associated with multiple plans. I assumed if I took the table containing the unique set of features and query for those records whose feature key is not present in the qry_AssociatedFeatures query I would get what I wanted SELECT DISTINCT main_db.Feature, main_db.FeatureKey FROM main_db, qry_AssociatedFeatures WHERE (((main_db.FeatureKey)[qry_AssociatedFeatures].[FeatureKey])); Unfortunately, I still see records in the qry_UnassociatedFeatures that are also present in the qry_AssociatedFeatures. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding records not in a set
I did find a reference to EXIST/NOT EXISTS clause in the SQL manual, but I get an ODBC Failed call when I run the following simplified query SELECT * FROM main_db WHERE NOT EXISTS (SELECT * FROM featureenable WHERE featureenable.FeatureKey = main_db.FeatureKey); Bob -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 8:48 AM To: [EMAIL PROTECTED] Subject: Finding records not in a set I have a query, call qry_AssociatedFeatures, that finds all features associated with a given plan SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, featureenable.PlanName FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = main_db.FeatureKey WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, or all the features not associated with a plan. Complication here is the feature enable table can have the same feature key associated with multiple plans. I assumed if I took the table containing the unique set of features and query for those records whose feature key is not present in the qry_AssociatedFeatures query I would get what I wanted SELECT DISTINCT main_db.Feature, main_db.FeatureKey FROM main_db, qry_AssociatedFeatures WHERE (((main_db.FeatureKey)[qry_AssociatedFeatures].[FeatureKey])); Unfortunately, I still see records in the qry_UnassociatedFeatures that are also present in the qry_AssociatedFeatures. Any suggestions? -- 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]
Student request for assistance
A friend of mine, who is not a member of the MySQL email aliases, is working toward her Bachelors degree. She is currently taking a database design course. She mentioned her current assignment to me and I suggested she allow me to forward this request to this email alias. I understand this is not the intent of the alias and I apologize to those who feel its inappropriate. I am simply looking for someone with experience as a database administrator, who is willing to help. The assignment is shown below. The research/assignment must before this coming Saturday. The result of an accelerated program she is enrolled in. Please contact Marlene directly at [EMAIL PROTECTED] if your willing to offer your insights. Assignment: Your goal is to find advice or an anecdote that shows the importance of analyzing business system information requirements before you begin building a database. Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cost of joins?
I have a question that may be similar to the one which Margaret asked recently concerning the Cost of Joins. I have a DB with numerous tables and have inserted keys to relate one table to another. The method minimizes the data I store, but results in me joining multiple tables, sometimes 10 at a time to retrieve information needed to satisfy a given search request. A simple version of my DB would be: Table one oneKey a c b Table two twoKey oneKey-Foreign key e f g Table three threeKey twoKey-Foreign key x y z If I want to collect data concerning x, y, z and its relation to 'a' I need to join tables one, two and three. It seems to me this is the most efficient storage of information. It also, assuming the resulting queries return a large number of records, is the most efficient for end users when moving from record to record. Conversely, it also seems like it will be the most inefficient while waiting for the query results to be calculated? I've noticed another solution proposed by some is to carry forward Foreign Keys. For instance: Table one oneKey a c b Table two twoKey oneKey-Foreign key e f g Table three threeKey oneKey-Foreign key twoKey-Foreign key x y z In this case collecting the same information (x, y, z and its relation to 'a') I need only join tables one and three or just three and do look-ups into table one. Obviously, the issue scales if you add 10 tables into the equation. This method appears less efficient from a data storage perspective and complicates the application. I need to store multiple Foreign keys each time a record is added to a given table. The time to return query results would appear to be very short as each query would only return a single record, but the record to record movement would result in a new query each time. What advantages or disadvantages are there to one method vs. another? Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1005 when adding a Foreign Key
I recently added a column and Index to an existing table. I wanted to also add a Foreign Key. I have done this before defining the commands outside MySQL and souring the file in for new tables, but would prefer to not have to dump the current table just for the modification. I tried to add one based on the users guide, see command/response below, without success. Obviously I am missing something. Any suggestions? mysql alter table runload_list add foreign key (PlanName) references testplans (PlanName) on update cascade on delete restrict; ERROR 1005: Can't create table './mydb/#sql-3ebd_430.frm' (errno: 150) Robert M. Bartis Lucent Technologies ¢ Room HO 1C-413A (HO) / 1B-304 (WH) ( 732.949.4565 (HO) / 973.386.7426 (WH) * [EMAIL PROTECTED]
RE: mysql control center documentation
Had the same issue. I've bookmarked it, but its not clear why its so hard to find. Its good stuff man put it out front:-) -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 12:13 PM To: Chris Stevenson Cc: [EMAIL PROTECTED] Subject: Re: mysql control center documentation The MySQL web site was recently reorganized. For some reason, when they did so, the buried the manual 4 clicks away in the Developer Zone. It's not clear to me how anyone new is supposed to find it there. In any case, the URL is http://dev.mysql.com/doc/mysql/en/index.html. Michael Chris Stevenson wrote: Is there a user guide available anywhere? I can't seem to find anything on mysql.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]
Recommendation on god MySQL books
I'm looking for suggestions on books that would help me to improve my understanding of MySQL operations, admin operations, replication etc. I'm new to MySQL and am about to embark on supporting a database for my team to use in recording test results. Any suggestions and recommendations ones to stay away from? Thanks in advance Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to connect to DB
I have been using a local copy of the current production version of MySQL in a windows environment while we evaluate porting a MS Access front-end to make use of MySQL. We are ready to go prime-time within my team and as part of this exercise installed a LINIX version of MySQL on another machine. We can create a DB, import files etc, etc while on the local LINIX box, but are unable to connect via a remote machine. Any suggestions on how to debug this issue? Networking is not an issue as we can ping the machine. Thanks in advance for you help Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unable to connect to DB
Thanks for the pointers. We can connect from the Linux box to my PC so we're convinced
the network is not an issue, but still cannot connect from my PC to the new MySQL
server installed on the Linux box. When we monitor the packets coming in we can see
the request to connect and to MySQL at port 3306 followed by the request for an ICMP
ping instead of the expected ACK. The ports are enabled in the etc/services file so
we're at a loss. Is there a setting to allow remote connections in Linux?
Still lost:-|
Bob
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, June 11, 2004 3:30 PM
To: Bartis, Robert M (Bob)
Cc: [EMAIL PROTECTED]
Subject: Re: Unable to connect to DB
Check to make sure the linux box has its port open (do a MySQL ping).
From the windows box, telnet to your linux box on port 3306 (or whatever
you set your linux server to listen on in your my.cnf file) you should see
the version# of the server and a bunch of non-text information. If that
fails, your linux box is not listening. Change your server's config file to
open a port.
Another issue about mixing platforms if you are trying to access your
MySQL server through the ODBC driver (the most current version I can find
is 3.x) and your server is version 4.1 or better, you will have to
downgrade your password.
Log into the MySQL with admin permissions (root) and run this command:
update mysql.user set password=old_password('your ODBC pwd here') where
user = 'your ODBC user name here';
Also, make sure you have GRANT-ed the appropriate permission for your user
acct to the new tables.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Bartis, Robert M
(Bob) To: [EMAIL PROTECTED]
[EMAIL PROTECTED]cc:
m Fax to:
Subject: Unable to connect to DB
06/11/2004 03:19
PM
I have been using a local copy of the current production version of MySQL
in a windows environment while we evaluate porting a MS Access front-end to
make use of MySQL. We are ready to go prime-time within my team and as part
of this exercise installed a LINIX version of MySQL on another machine. We
can create a DB, import files etc, etc while on the local LINIX box, but
are unable to connect via a remote machine. Any suggestions on how to debug
this issue? Networking is not an issue as we can ping the machine.
Thanks in advance for you help
Bob
--
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: 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 -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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange behavior in foreign keys
Also, suggest you read http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html and in particular look to see if you're getting error 1005 or 105 returned. That was the purpose of my original question to you. Bob -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Friday, May 28, 2004 11:42 AM To: [EMAIL PROTECTED] Subject: Re: strange behaviour in foreign keys [EMAIL PROTECTED] wrote: I've been running MySQL 4.0.x on a RH9 machine for some months and everything was fine. I could create tables of type InnoDB and define FOREIGN KEY's all was well. A few days ago it does not let me create tables with foreign keys anymore it says something like unable to create /databasename/tablename... If I remove the FOREIGN KEY constraint it accepts de table creation. You have incorrect foreign key definition. Use SHOW INNODB STATUS command to see more detailed error message. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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]
Error 1064 (Syntax error) after adding ENUM or Set types to table definition
I created the dump file from my current DB to make adjustments and also migrate to
Innodb tables. In the process I have tried to add some ENUM types in select table.
Obviously, I am doing something wrong as each one give me a syntax error 1064 when I
try to source in the text file. I've read the manual and do not see where my error is.
Can someone point out what is wrong on the below table?
Thanks in advance
Bob
-- MySQL dump 9.10
--
-- Host: bartis-1Database: stingertrdb4
-- --
-- Server version 4.0.18-max-debug
--
-- Table structure for table `feature_list`
--
DROP TABLE IF EXISTS feature_list;
CREATE TABLE feature_list (
Feature varchar(50) default NOT NULL,
New ENUM('Y','N') NOT NULL,
Owner varchar(50) default NOT NULL,
NewFeatureDescription varchar(255) default NOT NULL,
DateMod datetime NOT NULL default '-MM-DD HH:MM:SS',
PRIMARY KEY Feature (Feature),
INDEX Owner (Owner),
FOREIGN KEY (Owner) REFERENCES tester_list(Tester) ON UPDATE CASCADE ON DELETE
RESTRICT
) TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies ¢
Room HO 1C-413A (HO) / 1B-304 (WH)
( 732.949.4565 (HO) / 973.386.7426 (WH)
* [EMAIL PROTECTED]
RE: Rename database
http://dev.mysql.com/doc/mysql/en/RENAME_TABLE.html -Original Message- From: Ngim Ngau - Kzresults [mailto:[EMAIL PROTECTED] Sent: Thursday, May 20, 2004 4:50 PM To: [EMAIL PROTECTED] Subject: Rename database Hi, Is there a way I can rename a database? or at least copy an old database with existing tables into a new one? 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]
Changing Type value of column in an existing table
How does one go about change the data type of a column in an existing table? Robert M. Bartis Lucent Technologies ¢ Room HO 1C-413A (HO) / 1B-304 (WH) ( 732.949.4565 (HO) / 973.386.7426 (WH) * [EMAIL PROTECTED]
