Re: maximum number of tables supported in a mysql database
So you want to mean that i can create (4TB/average_table_size) number of tables in a linux(2.6 kernel) operating system assuming i have that much hard disk space? On 12/11/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: sunaram patir [EMAIL PROTECTED] wrote on 12/11/2005 12:10:52 AM: hi, what is the maximum no of tables supported in a mysql database? That mostly depends on how large your hard drives are... Except for the InnoDB engine (in default mode) and the NDB engine, all other database engines use 1 or more files per table. How many individual files fit on your hard drive? Here is a page describing the maximum sizes of tables based on which operating system you are using: http://dev.mysql.com/doc/refman/4.1/en/table-size.html This article discusses the drawbacks to creating too many tables in the same database: http://dev.mysql.com/doc/refman/4.1/en/creating-many-tables.html This page starts the section about all database engines except InnoDB and NDB: http://dev.mysql.com/doc/refman/4.1/en/storage-engines.html This describes the InnoDB engine: http://dev.mysql.com/doc/refman/4.1/en/innodb.html This describes NDB Cluster: http://dev.mysql.com/doc/refman/4.1/en/ndbcluster.html Somewhere in those articles it may describe the theoretical limits to how many tables you can define but I can summarize them by saying that the actual limits will depend mostly on what type of operating system you have and how big your disks are. I have never heard of any one needing more tables than they could create. I would assume that a few thousand tables wouldn't be too many for most modern hard drives to handle. How many were you worried about? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: from MySQL to MS Excel ...
Thanks JR, Shawn, Scott, ... for your replies. I choose to make use of the SELECT ... INTO OUTFILE. This works fine, but I also want a header-line in the CSV file. So I made the following statement: SELECT `ID`, `Code`, `Name` UNION SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`; But this makes the header-line a trailer-line in the csv file, like this: 11,kg,KiloGrams 12,g,Grams 13,Ton,Tonne ... ID,Code,Name Any idea how to make a sorted csv file with a real header-line ? TIA, Cor - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, December 10, 2005 5:17 PM Subject: RE: from MySQL to MS Excel ... There is at least one other option that JR didn't mention... at least some versions of Excel have the menu option Data-Get External Data which allows you to link through ODBC to run queries directly from within Excel. I have barely used it and I have never tried it with MySQL so I can't really explain how to use it or what it's limitations will be but I know that it works through at least two other ODBC drivers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine J.R. Bullington [EMAIL PROTECTED] wrote on 12/10/2005 10:33:18 AM: Here's the skinny -- YES and NO. NO in that it won't export directly, YES in that you have to do a little leg work in order for it to be done. You have 3 options -- ODBC, Code and CSV. ODBC -- Excel has the ability to use ODBC connections to the MySQL database. Run your MySQL query with the HTML flags turned on and then export to a file so that Excel can read it. (Thanks to SGreen for this info from an earlier post). CODE -- If you code it in ASP or PHP, you can get your code to push directly into Excel with field headers and data, and have formatting options because Excel can interpret HTML code. CSV -- Do your MySQL query from the CLI and then use MySQL to export your results to a CSV file. Then open the CSV file in Excel (using the Excel File Open). See ODBC connection above for another option using the HTML flag. HTH, J.R. -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Saturday, December 10, 2005 9:35 AM To: mysql@lists.mysql.com Subject: from MySQL to MS Excel ... Hi Friends, I am looking for an easy and seamless way to export MySQL query output to MS Excel. At this moment I am using MS Access 2003 as front-end for a MySQL database. With MS Access I can easily send the output of queries on my database to MS Excel. All I need to do is select Tools Office Links Analyze it with Microsoft Office Excel. That's all. This applies to all kinds of MySQL queries, including WITH ROLLUP options. In the manual I found: http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html But this is too much trouble, and does not allow full functionality of MySQL queries. Question: is it possible to create MS Excel files directly from MySQL ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can you tell if a table is corrupted?
Hi all, Say, is there a way to tell if a table has been corrupted? We're having some weird things happening and the only thing I can think of is possible corruption of a table, but is there anything you can do to find out? Jenifer
Re: from MySQL to MS Excel ...
C.R.Vegelin wrote: Thanks JR, Shawn, Scott, ... for your replies. I choose to make use of the SELECT ... INTO OUTFILE. This works fine, but I also want a header-line in the CSV file. So I made the following statement: SELECT `ID`, `Code`, `Name` UNION SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`; But this makes the header-line a trailer-line in the csv file, like this: 11,kg,KiloGrams 12,g,Grams 13,Ton,Tonne ... ID,Code,Name Any idea how to make a sorted csv file with a real header-line ? TIA, Cor I suspect your query is treated as (SELECT `ID`, `Code`, `Name`) UNION (SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ) ORDER BY `ID`; when you want (SELECT `ID`, `Code`, `Name`) UNION (SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`); You see the difference? The former sorts all the rows by id, while the latter only sorts the second query's output. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum number of tables supported in a mysql database
As I read the docs, yes! Is that going to be a limitation for you? Shawn Green Database Administrator Unimin Corporation - Spruce Pine sunaram patir [EMAIL PROTECTED] wrote on 12/11/2005 05:50:58 AM: So you want to mean that i can create (4TB/average_table_size) number of tables in a linux(2.6 kernel) operating system assuming i have that much hard disk space? On 12/11/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: sunaram patir [EMAIL PROTECTED] wrote on 12/11/2005 12:10:52 AM: hi, what is the maximum no of tables supported in a mysql database? That mostly depends on how large your hard drives are... Except for the InnoDB engine (in default mode) and the NDB engine,all other database engines use 1 or more files per table. How many individual files fit on your hard drive? Here is a page describing the maximum sizes of tables based on which operating system you are using: http://dev.mysql.com/doc/refman/4.1/en/table-size.html This article discusses the drawbacks to creating too many tables in the same database: http://dev.mysql.com/doc/refman/4.1/en/creating-many-tables.html This page starts the section about all database engines except InnoDB and NDB: http://dev.mysql.com/doc/refman/4.1/en/storage-engines.html This describes the InnoDB engine: http://dev.mysql.com/doc/refman/4.1/en/innodb.html This describes NDB Cluster: http://dev.mysql.com/doc/refman/4.1/en/ndbcluster.html Somewhere in those articles it may describe the theoretical limits to how many tables you can define but I can summarize them by saying that the actual limits will depend mostly on what type of operating system you have and how big your disks are. I have never heard of any one needing more tables than they could create. I would assume that a few thousand tables wouldn't be too many for most modern hard drives to handle. How many were you worried about? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: maximum number of tables supported in a mysql database
No, thanks! It's, in fact, more than enough! On 12/11/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: As I read the docs, yes! Is that going to be a limitation for you? Shawn Green Database Administrator Unimin Corporation - Spruce Pine sunaram patir [EMAIL PROTECTED] wrote on 12/11/2005 05:50:58 AM: So you want to mean that i can create (4TB/average_table_size) number of tables in a linux(2.6 kernel) operating system assuming i have that much hard disk space? On 12/11/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: sunaram patir [EMAIL PROTECTED] wrote on 12/11/2005 12:10:52 AM: hi, what is the maximum no of tables supported in a mysql database? That mostly depends on how large your hard drives are... Except for the InnoDB engine (in default mode) and the NDB engine,all other database engines use 1 or more files per table. How many individual files fit on your hard drive? Here is a page describing the maximum sizes of tables based on which operating system you are using: http://dev.mysql.com/doc/refman/4.1/en/table-size.html This article discusses the drawbacks to creating too many tables in the same database: http://dev.mysql.com/doc/refman/4.1/en/creating-many-tables.html This page starts the section about all database engines except InnoDB and NDB: http://dev.mysql.com/doc/refman/4.1/en/storage-engines.html This describes the InnoDB engine: http://dev.mysql.com/doc/refman/4.1/en/innodb.html This describes NDB Cluster: http://dev.mysql.com/doc/refman/4.1/en/ndbcluster.html Somewhere in those articles it may describe the theoretical limits to how many tables you can define but I can summarize them by saying that the actual limits will depend mostly on what type of operating system you have and how big your disks are. I have never heard of any one needing more tables than they could create. I would assume that a few thousand tables wouldn't be too many for most modern hard drives to handle. How many were you worried about? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help with a query
I have a table with several columns. The ones of interest are flight_id, angle, and baseline. I want to find the flight_ids for the flights with the maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)). For example, Flights +++---+ | flight_id | angle| baseline | +++---+ | 1 | 37.0 | 100.0 | | 2 | 50.0 | 100.0 | | 3 | 48.0 | 100.0 | | 4 | 40.0 | 100.0 | | 5 | 44.0 | 100.0 | | 6 | 40.0 | 100.0 | | 7 | 45.0 | 100.0 | | 8 | 44.0 | 75.0 | | 9 | 57.8 | 75.0 | +++---+ The result I am looking for are: Maximum altitude: +++ | flight_id | altitude | +++ | 2 | 119.17536 | | 9 | 119.17536| +++ Minimum altitude: +---+---+ | flight_id | altitudeM | +---+---+ | 8 | 72.42666 | +---+---+ Thanks for any help you can provide! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL on Mac OS X - Socket Errors
Hello there, I've searched long and hard, and pestered many people for a solution and for help however I can not get around this problem. I have installed MySQL on my computer which is an Apple iBook G4 which is running Mac OS X 10.4.2 and I wish to use MySQL so that I can learn something and play with it. I have tried to connect and startup the database through the terminal and I've even tried to startup MyPHPAdmin, however I'm getting this message: #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) Does anybody know what it is I can do to start to get around this problem? Thank you very much in advance, Nathan Whitington [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can you tell if a table is corrupted?
If mysql detects corruption it will stop further access to the table until you repair it. It is possible for corruption to happen where mysql can't detect it. If you suspect corruption run a repair on the table. On 12/11/05, Subscriptions [EMAIL PROTECTED] wrote: Hi all, Say, is there a way to tell if a table has been corrupted? We're having some weird things happening and the only thing I can think of is possible corruption of a table, but is there anything you can do to find out? Jenifer -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump: getting it to dump INSERT IGNORE
Hi All, I have read thehttp://dev.mysql.com/doc/refman/5.0/en/ mysqldump.html and can find nothing regarding getting dump to INSERT IGNORE instead of simply INSERT INTO. Is there any way to get INSERT IGNORE to be dumped? Otherwise, I fear I may be forced to parse the dump file and do a few replacements. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: getting it to dump INSERT IGNORE
Hi. Use the --insert-ignore option of mysqldump. You can type 'mysqldump --help' on command line to see all options available. Richard AB. - Original Message - From: Michael Williams [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, December 11, 2005 6:19 PM Subject: mysqldump: getting it to dump INSERT IGNORE Hi All, I have read thehttp://dev.mysql.com/doc/refman/5.0/en/ mysqldump.html and can find nothing regarding getting dump to INSERT IGNORE instead of simply INSERT INTO. Is there any way to get INSERT IGNORE to be dumped? Otherwise, I fear I may be forced to parse the dump file and do a few replacements. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP4 or PHP5?
Dear All, I do not have much experience with PHP or MySql although I have used SQL quite a lot. I am going to set up a relatively small MySQL database (circa 40 tables) and we are expecting a hit rate of about 40,000 visitors per annum mostly browsing a relatively low number of pages each. We plan to launch in March The ISP I have selected runs MySQL release 4.0.14 and offers either PHP4 (Release 4.4.1) or PHP5 (Release 5.0.3) My question is: Is PHP5 (Release 5.0.3) stable enough for this? I know this is really a MySQL forum but will be grateful for any advice. Ch
Re: SQL on Mac OS X - Socket Errors
Nathan Whitington wrote: Hello there, I've searched long and hard, and pestered many people for a solution and for help however I can not get around this problem. I have installed MySQL on my computer which is an Apple iBook G4 which is running Mac OS X 10.4.2 and I wish to use MySQL so that I can learn something and play with it. Great, but what version of mysql? How did you install? Did you follow the post-installation instructions http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html? I have tried to connect and startup the database through the terminal and I've even tried to startup MyPHPAdmin, however I'm getting this message: How did you try to start the server? Did it start? Is there anything in mysql's error log? #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) Error 2002 means the client could not find the socket file to connect to the server. This usually means the server is not running, or the client is misconfigured. As you don't mention any attempts at configuration, I'll guess the server isn't running. Does anybody know what it is I can do to start to get around this problem? * Follow the post-install procedure in the manual. * Make sure mysql's data directory is owned by the mysql user. After that, the most likely problem is that mysql cannot create the socket file due to permissions problems. The default location for the socket file is in /tmp. Several versions of / updates to Mac OS X have set incorrect permissions on /tmp. You should cd /tmp sudo chmod 1777 . to set correct permissions on /tmp. Then try to start mysqld: cd /usr/local/mysql sudo -v sudo bin/mysqld_safe If it doesn't start, look for the .err file in the data directory (the error log) for the reason. If you need more help, include the answers to the above questions, the exact commands you entered, the exact text of any errors rceived, and the contents of the error log in your reply. Thank you very much in advance, Nathan Whitington [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP4 or PHP5?
On Sunday 11 December 2005 23:51, Charles Walmsley wrote: Dear All, I do not have much experience with PHP or MySql although I have used SQL quite a lot. I am going to set up a relatively small MySQL database (circa 40 tables) and we are expecting a hit rate of about 40,000 visitors per annum mostly browsing a relatively low number of pages each. We plan to launch in March [ 8 ]-- Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth are you going to store. Fester -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP4 or PHP5?
Hi Peter, 40 tables seems to be relatively small, I have several databases that I manage that have tables that number in the hundreds. In other databases, I have one site that has 7,500 tables. Have a look at some of the open source ERP systems around eg. Nola or similar, their tables number in the hundreds. It just needs to be managed correctly like anything else that becomes complicated. You have a set of routines and procedures and refine and stick with them. BTW IMHO it would depend on whether you need the features of PHP5 vs 4. PHP4.4.1 is quite stable and works fine. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Peter M. Groen [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 10:44 AM To: mysql@lists.mysql.com Subject: Re: PHP4 or PHP5? On Sunday 11 December 2005 23:51, Charles Walmsley wrote: Dear All, I do not have much experience with PHP or MySql although I have used SQL quite a lot. I am going to set up a relatively small MySQL database (circa 40 tables) and we are expecting a hit rate of about 40,000 visitors per annum mostly browsing a relatively low number of pages each. We plan to launch in March [ 8 ]-- Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth are you going to store. Fester -- 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]
Fw: Need Help with a query
Oops, I meant to copy the list on this reply so that others could benefit Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Mark Phillips [EMAIL PROTECTED] Sent: Sunday, December 11, 2005 6:19 PM Subject: Re: Need Help with a query - Original Message - From: Mark Phillips [EMAIL PROTECTED] To: MYSQL List mysql@lists.mysql.com Sent: Sunday, December 11, 2005 12:38 PM Subject: Need Help with a query I have a table with several columns. The ones of interest are flight_id, angle, and baseline. I want to find the flight_ids for the flights with the maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)). For example, Flights +++---+ | flight_id | angle| baseline | +++---+ | 1 | 37.0 | 100.0 | | 2 | 50.0 | 100.0 | | 3 | 48.0 | 100.0 | | 4 | 40.0 | 100.0 | | 5 | 44.0 | 100.0 | | 6 | 40.0 | 100.0 | | 7 | 45.0 | 100.0 | | 8 | 44.0 | 75.0 | | 9 | 57.8 | 75.0 | +++---+ The result I am looking for are: Maximum altitude: +++ | flight_id | altitude | +++ | 2 | 119.17536 | | 9 | 119.17536| +++ Minimum altitude: +---+---+ | flight_id | altitudeM | +---+---+ | 8 | 72.42666 | +---+---+ Thanks for any help you can provide! I do wish posters to this list would get in the habit of volunteering which version of MySQL they are using, particularly for SQL questions! The answer to almost every SQL question is it depends on which version of MySQL you are using. It's very tedious to give the answer for every version MySQL, as in: If you're using Version 3.x, the answer is A. If you're using Version 4.0.x the answer is B. If you're using Version 4.1.x, the answer is C. etc. [By the way, I don't mean to single you out with this mini-rant; it's just a general observation.] Therefore, I'm going to assume you are using Version 4.1 or higher; in other words, you use a version which supports subqueries. If you are on an earlier version, please reply to the list and explain which version you are on. Perhaps someone will be willing to show you alternatives that will work for you. I should also explain that I am _not_ on a version of MySQL which supports subqueries. However, my main database is DB2 which does support subqueries and the SQL used by DB2 and MySQL is very very similar so this _untested_ answer should be pretty close to what you need. I think the best answer to your question is to use subqueries. I'm going to express the answer in pseudocode first to give you a general sense of the answer, then give you something that should be pretty close to a final answer that will work on your system. Pseudocode (for maximum altitude): select flight_id, baseline*tan(radians(angle)) as max_altitude from Flights where baseline*tan(radians(angle)) in (subquery that gets largest altitude from table) In real SQL, that should end up looking like this: select flight_id, baseline*tan(radians(angle)) as max_altitude from Flights where baseline*tan(radians(angle)) in (select max(baseline*tan(radians(angle))) from Flights) To get the query for the minimum altitude, use the exact same query except replace the max function with the min function in the subquery and change the 'as' for the outer query from 'max_altitude' to 'min_altitude'. For what it's worth, I got slightly different numbers in DB2 so I did not have a tie for maximum altitude: my maximum altitude was for flight 2 at 119.17535 (versus 119.09797 for flight 9). Perhaps the tan() or radians() functions in DB2 are slightly more precise? The 'in' that introduces the subqueries could potentially be replaced by '=' but 'in' is safer. If you use 'in' and there is more than one flight tied for the highest altitude, the query works fine. If you use '=' and there is more than one flight tied for the highest altitude, the query will almost certainly fail - it does in DB2! - because '=' implies that only one row in the outer query can have the maximum altitude; therefore the query fails if more than one row matches. The most tedious part of these queries is typing the 'baseline*tan(radians(angle))' expression. You might expect that there would be some shortcut that would enable you to type the expression only once and then make repeated references to it. Sometimes that is possible but without a suitable version of MySQL handy, I don't want to propose any possible ways to do that since I can't be sure they'll work. Perhaps people with newer versions of MySQL can suggest shortcuts that reduce the amount of typing. Rhino
RE: How can you tell if a table is corrupted?
Hi Jenifer, You can use mysqlcheck to confirm any corruption and also to fix it. See http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html or you can also run statements from the mysql client such as CHECK TABLE, REPAIR and ANALYZE etc. References to this can be found at http://dev.mysql.com/doc/refman/5.0/en/table-maintenance-sql.html These are equally applicable at version 4.1 regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Subscriptions [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 3:01 AM To: mysql@lists.mysql.com Subject: How can you tell if a table is corrupted? Hi all, Say, is there a way to tell if a table has been corrupted? We're having some weird things happening and the only thing I can think of is possible corruption of a table, but is there anything you can do to find out? Jenifer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import Table?
Is there a command that will load in all of the data from a table within a database .sql file on disk? The only import option I am seeing is LOAD DATA INFILE which requires a text file already exported to disk. How about a way to load in the table data directly from the database file? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import Table?
Hi John, If it is a .sql file, with all appropriate SQL statements already in place then you only have to do the following $ mysql -u -p databasename .sql file This will process all appropriate statements in the file. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 11:55 AM To: mysql@lists.mysql.com Subject: Import Table? Is there a command that will load in all of the data from a table within a database .sql file on disk? The only import option I am seeing is LOAD DATA INFILE which requires a text file already exported to disk. How about a way to load in the table data directly from the database file? Thanks, John -- 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: Import Table?
Thanks for the response, David. How about if I want to import all the entries from one specific TABLE within that sql file into a table with identical columns on my MySQL server? Is there a way? Thanks, John On Dec 11, 2005, at 5:29 PM, Logan, David (SST - Adelaide) wrote: Hi John, If it is a .sql file, with all appropriate SQL statements already in place then you only have to do the following $ mysql -u -p databasename .sql file This will process all appropriate statements in the file. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 11:55 AM To: mysql@lists.mysql.com Subject: Import Table? Is there a command that will load in all of the data from a table within a database .sql file on disk? The only import option I am seeing is LOAD DATA INFILE which requires a text file already exported to disk. How about a way to load in the table data directly from the database file? Thanks, John -- 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: Import Table?
Hi John, Personally, I'd just do a cut and paste job on the .sql file unless it is too unmanageable. Not knowing your platform, and being a unixy type person, I would use sed or grep to strip out the lines that I need and then plonk them straight into another file. I don't know how you could accomplish that on a Windows platform. I hope I haven't misunderstood, is the .sql file come from another MySQL database or is this from a SQL server machine or similar? My interpretation of a .sql file is something akin to that created by mysqldump eg. a text file that has a number of SQL statements in it allowing you to recreate the table by using this as input. You can also use Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 12:14 PM To: mysql@lists.mysql.com; Logan, David (SST - Adelaide) Subject: Re: Import Table? Thanks for the response, David. How about if I want to import all the entries from one specific TABLE within that sql file into a table with identical columns on my MySQL server? Is there a way? Thanks, John On Dec 11, 2005, at 5:29 PM, Logan, David (SST - Adelaide) wrote: Hi John, If it is a .sql file, with all appropriate SQL statements already in place then you only have to do the following $ mysql -u -p databasename .sql file This will process all appropriate statements in the file. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 11:55 AM To: mysql@lists.mysql.com Subject: Import Table? Is there a command that will load in all of the data from a table within a database .sql file on disk? The only import option I am seeing is LOAD DATA INFILE which requires a text file already exported to disk. How about a way to load in the table data directly from the database file? Thanks, John -- 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]
Fwd: Re: Need Help with a query
I forgot to copy the list as well Mark -- Forwarded Message -- Subject: Re: Need Help with a query Date: Sunday 11 December 2005 06:47 pm From: Mark Phillips [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Rhino, My apologies for leaving out the version of mysql. I agree 1000% with your rant - it was an oversight on my part. I have mysql 4.0.24 on my development machine and 4.1.11 on my production machine. Thanks for the query - it works very well in 4.1.11. I think in 4.0.24 I need to use a temporary table. Thanks again! Mark On Sunday 11 December 2005 04:19 pm, you wrote: - Original Message - From: Mark Phillips [EMAIL PROTECTED] To: MYSQL List mysql@lists.mysql.com Sent: Sunday, December 11, 2005 12:38 PM Subject: Need Help with a query I have a table with several columns. The ones of interest are flight_id, angle, and baseline. I want to find the flight_ids for the flights with the maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)). For example, Flights +++---+ | flight_id | angle| baseline | +++---+ | 1 | 37.0 | 100.0 | | 2 | 50.0 | 100.0 | | 3 | 48.0 | 100.0 | | 4 | 40.0 | 100.0 | | 5 | 44.0 | 100.0 | | 6 | 40.0 | 100.0 | | 7 | 45.0 | 100.0 | | 8 | 44.0 | 75.0 | | 9 | 57.8 | 75.0 | +++---+ The result I am looking for are: Maximum altitude: +++ | flight_id | altitude | +++ | 2 | 119.17536 | | 9 | 119.17536| +++ Minimum altitude: +---+---+ | flight_id | altitudeM | +---+---+ | 8 | 72.42666 | +---+---+ Thanks for any help you can provide! I do wish posters to this list would get in the habit of volunteering which version of MySQL they are using, particularly for SQL questions! The answer to almost every SQL question is it depends on which version of MySQL you are using. It's very tedious to give the answer for every version MySQL, as in: If you're using Version 3.x, the answer is A. If you're using Version 4.0.x the answer is B. If you're using Version 4.1.x, the answer is C. etc. [By the way, I don't mean to single you out with this mini-rant; it's just a general observation.] Therefore, I'm going to assume you are using Version 4.1 or higher; in other words, you use a version which supports subqueries. If you are on an earlier version, please reply to the list and explain which version you are on. Perhaps someone will be willing to show you alternatives that will work for you. I should also explain that I am _not_ on a version of MySQL which supports subqueries. However, my main database is DB2 which does support subqueries and the SQL used by DB2 and MySQL is very very similar so this _untested_ answer should be pretty close to what you need. I think the best answer to your question is to use subqueries. I'm going to express the answer in pseudocode first to give you a general sense of the answer, then give you something that should be pretty close to a final answer that will work on your system. Pseudocode (for maximum altitude): select flight_id, baseline*tan(radians(angle)) as max_altitude from Flights where baseline*tan(radians(angle)) in (subquery that gets largest altitude from table) In real SQL, that should end up looking like this: select flight_id, baseline*tan(radians(angle)) as max_altitude from Flights where baseline*tan(radians(angle)) in (select max(baseline*tan(radians(angle))) from Flights) To get the query for the minimum altitude, use the exact same query except replace the max function with the min function in the subquery and change the 'as' for the outer query from 'max_altitude' to 'min_altitude'. For what it's worth, I got slightly different numbers in DB2 so I did not have a tie for maximum altitude: my maximum altitude was for flight 2 at 119.17535 (versus 119.09797 for flight 9). Perhaps the tan() or radians() functions in DB2 are slightly more precise? The 'in' that introduces the subqueries could potentially be replaced by '=' but 'in' is safer. If you use 'in' and there is more than one flight tied for the highest altitude, the query works fine. If you use '=' and there is more than one flight tied for the highest altitude, the query will almost certainly fail - it does in DB2! - because '=' implies that only one row in the outer query can have the maximum altitude; therefore the query fails if more than one row matches. The most tedious part of these queries is typing the
Joins on tables with funky data?
Okay, so I haven't been able to find any corrupted tables. Has anyone ever run into problems with joins between tables that contain funky data? I have a table that contains encrypted info and some of the characters being used/stored are definitely odd. I'm still trying to find the answer to our issues. sigh. Jenifer
Re: Joins on tables with funky data?
Subscriptions [EMAIL PROTECTED] wrote on 12/11/2005 10:46:08 PM: Okay, so I haven't been able to find any corrupted tables. Has anyone ever run into problems with joins between tables that contain funky data? I have a table that contains encrypted info and some of the characters being used/stored are definitely odd. I'm still trying to find the answer to our issues. sigh. Jenifer The olde saying goeth: Garbage in, garbage out. If your data is bad, your results will be bad. However, I have heard nothing about problems during JOIN comparisons against BINARY data. Perhaps if you told us what weird things have been happening, we might be able to explain it. So far all you have done is to ask us how to tell if a table is corrupt and if JOINs work with BINARY data. Not much information for us to use to help you form a diagnosis, is it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Select Unique?
I have two tables 'table1', 'table2' with a matching column 'column1'. How can I return all rows from table2 where the entry for table2.column1 does not match any entries in table1.column1? SELECT * FROM table2 WHERE table2.column1 table1.column1 returns all the rows, rather than the unique rows in table2 ... Any ideas? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins on tables with funky data?
Sorry... I thought I had posted the issues, that must have been a support ticket somewhere instead. heh I have a main customer table with a handful of other tables that I join with it via PHP pages. I've written a phone order system that works fine 98% of the time. Every once in a while, the wrong customerid will get joined to these other tables. I have the same join/query throughout the pages in the order process, but randomly the wrong customerid is either selected or joined with the wrong id in the joined table. Kind of confusing... So, if I have this (greatly simplified): customer table: ID = 123, Name = John Doe ID = 124, Name = Henry Harrison customer notes: noteID = 14, customerID = 123 noteID = 15, customerID = 124 query: SELECT tblCustomer.*, tblNotes.note FROM tblCustomer LEFT JOIN tblNotes ON tblCustomer.customerid = tblNotes.noteID WHERE tblCustomer.customerid = 123 The JOIN should always connect John with his notes and Henry with his notes, but every so often a hiccup occurs and the join returns Henry's notes with John instead of John's notes. I've been trying to figure out what could cause the hiccup. The query doesn't change. The data doesn't change other than adding more notes or changing John's phone number. The Notes table does contain a field with encrypted data which is the only lead I have at the moment. I've been running the queries suggested from the other email to find corruption, but the tables check out fine. Jenifer - Original Message - From: [EMAIL PROTECTED] To: Subscriptions Cc: mysql@lists.mysql.com Sent: Sunday, December 11, 2005 11:15 PM Subject: Re: Joins on tables with funky data? Subscriptions [EMAIL PROTECTED] wrote on 12/11/2005 10:46:08 PM: Okay, so I haven't been able to find any corrupted tables. Has anyone ever run into problems with joins between tables that contain funky data? I have a table that contains encrypted info and some of the characters being used/stored are definitely odd. I'm still trying to find the answer to our issues. sigh. Jenifer The olde saying goeth: Garbage in, garbage out. If your data is bad, your results will be bad. However, I have heard nothing about problems during JOIN comparisons against BINARY data. Perhaps if you told us what weird things have been happening, we might be able to explain it. So far all you have done is to ask us how to tell if a table is corrupt and if JOINs work with BINARY data. Not much information for us to use to help you form a diagnosis, is it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
~mysql query log~
Hi, Iam running a mysql server version 5.0.15. My mysql query log is occupying nearly 21 GB of disk space. how do i truncate the same ? Thanks in advance, Abdul. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Unique?
John Mistler wrote: I have two tables 'table1', 'table2' with a matching column 'column1'. How can I return all rows from table2 where the entry for table2.column1 does not match any entries in table1.column1? SELECT * FROM table2 WHERE table2.column1 table1.column1 returns all the rows, rather than the unique rows in table2 ... Any ideas? Thanks, John You need a LEFT JOIN: SELECT table2.* FROM table2 LEFT JOIN table1 ON table1.column1 = table2.column1 WHERE table1.column1 IS NULL; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
