Re: Mysql - Tables Export to Excel!
Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
Note : if you table name it will export all tables from given database; eg:* mysqldump -u user -ppassword wordpress --no-data Dumpdata.txt * It will export all tables from db wordpress. On Mon, Apr 19, 2010 at 8:57 AM, Prabhat Kumar aim.prab...@gmail.comwrote: use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
The MySQL ODBC driver? / Carsten On Thu, 8 Apr 2010 10:42:28 +0530 (IST), Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A !DSPAM:451,4bbd65f933049495715525! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
Hi Vikram, You can use toad for mysql ( It's a free tool) to export table structure or data. Even you can use mysqldump also. Krishna On Thu, Apr 8, 2010 at 10:42 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A
RE: Mysql - Tables Export to Excel!
You can also use HeidiSQL, another free tool to visually manage and export structure/data etc (and a whole host of other stuff) I use it to manage windows and linux hosted databases. George -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: 08 April 2010 08:18 To: Vikram A Cc: MY SQL Mailing list Subject: Re: Mysql - Tables Export to Excel! Hi Vikram, You can use toad for mysql ( It's a free tool) to export table structure or data. Even you can use mysqldump also. Krishna On Thu, Apr 8, 2010 at 10:42 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
Or MySQL Connector :) On Apr 8, 2010 10:11 AM, Carsten Pedersen cars...@bitbybit.dk wrote: The MySQL ODBC driver? / Carsten On Thu, 8 Apr 2010 10:42:28 +0530 (IST), Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any... !DSPAM:451,4bbd65f933049495715525! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: MySQL Tables
Hi list, If I am converting an access db to mysql what will the tables be MyISAM? Thanks That depends on your tool, your settings and what not. Ask a better question if you want a better answer. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Tables
Unless you are specifying some other type in your table creation statements, your tables will be created as MyISAM. -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/11/04 11:30 AM Subject: MySQL Tables Hi list, If I am converting an access db to mysql what will the tables be MyISAM? 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: MySQL Tables
Thanks Victor - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'EWAGW ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 11, 2004 11:38 AM Subject: RE: MySQL Tables Unless you are specifying some other type in your table creation statements, your tables will be created as MyISAM. -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/11/04 11:30 AM Subject: MySQL Tables Hi list, If I am converting an access db to mysql what will the tables be MyISAM? 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: MySQL Tables Load Slowly
--- Emmett Bishop [EMAIL PROTECTED] wrote: David, It sounds like you need some indexes on your table. Do this... put the word EXPLAIN in front of the select statement to you use to get your data. For more info, check out this link http://dev.mysql.com/doc/mysql/en/EXPLAIN.html on the MySQL site. Email me the output of the EXPLAIN statement and I'll take a look. Take a look at the above page and you should be able to get a pretty clear idea of how well your query is optimized. It's a good idea to run EXPLAIN on all but the simplest of queries to see what MySQL is going to do with them. Yes, I would have designed the db with more tables. Generally, tables should represent distinct things or the relationships between things. Countries, states, or counties to use your example. But each table should represent one kind thing. Do a google search on Normalize Database and you'll find some good information on approaches to database design. They might give some of the theoretical background that you're looking for. Aha! I added EXPLAIN, like this... $res = mysql_query (EXPLAIN SELECT Name, Residents, Pop, Capital FROM basics where Capital like '%VOLCAN%') or die (mysql_error()); but none of my data displays at all. However, I did indeed strip out all my keys so I could get my tables published online. I probably only needed to get rid of the foreign keys, but I didn't want to take any chances. Anyway, I added a primary key, but the table still loads slowly. However, I'm guessing that it isn't enough to have a key on a table - that key also has to be cited in your query/select statements. Is that right? Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Tables Load Slowly
INDEXes grasshopper, INDEXes. --On Sunday, June 13, 2004 11:26 -0700 David Blomstrom [EMAIL PROTECTED] wrote: I have a MySQL table with about 3,500 rows and 30 columns. There are rows for each of the world's nations, each of the 50 states, some 3,000 U.S. counties, Canada's provinces, etc. Obviously, I can't display the entire table on a web page. But even when I do an operation that displays just a single cell, it takes a long, long time to load. So I'm thinking of splitting the table into three tables - Nations, States and Counties. But I wanted to make sure I'm not doing something wrong first. I thought dynamic tables were supposed to process very quickly and that the loading time was associated primarily with the amount of data you want to display on a page. Am I wrong? __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Tables Load Slowly
--On Sunday, June 13, 2004 12:56 -0700 David Blomstrom [EMAIL PROTECTED] wrote: Aha! I added EXPLAIN, like this... Please read the docs. And use the mysql CLI/monitor tool. Explain does just that, it EXPLAINs to you, the database programmer, what the MySQL engine will do when it goes about satisfying your query. In the case below I can tell you that only a full text search will help (please also look at reference documentation available at http://dev.mysql.com/ for that). In simple terms an index is exactly the same as an index for an encyclopedia. You give have a key, say the name of a city, and you want to know where you can find more information on that city. You look that city up in the index, and it says go to page 3127, so you then flip to page 3127 and read all about say, Portugal. Now say you wanted to know all cities with a population greater than 5000. The encyclopedia doesn't have an index for this lets say. So what do you do? You read each and every single entry for every city. Throwing out the ones that don't match and writing down the ones that do, very time consuming. But if the encyclopedia had an index that listed (and ranked) each city by its population you could look there and quickly find them, and what pages they are on. A database index does PRECISELY the same thing for your database server. IT tells it where (on the disk/in the MYD data file) it can find a record. IF one doesn't exist for the KEY you're asking for it does what you'd do, it reads the whole book! A database also needs a little more information than that to do somethin intelligent, like in my example above with the populations, this is where *CORRECT* column types come in. For gods sake if it's an int, store it as one. It'll store smaller, and indexes will work as you expect on them. A sickeningly common mistake of many beginners is to use CHAR/VARCHAR or BLOB (TEXT, TINYTEXT...) for everything, and not to use indexes. In your case because of the leading % and trailing % wildcards a full table scan is inevitable. What you want is a full text index, not a normal index, this allows you to look very efficiently for keywords and pull them out of the database with great speed. It's akin to an index that lists every word in a book, and what pages that word occurs on (what records or tuples in database speak). Try rewriting your query like this (note how I add LIMIT, this tells the database not to send us rows we're not going to use or display, this is another tool you should read up on): SELECT Name, Residents, Pop, Capital FROM basics WHERE MATCH (Capital) AGAINST ('volcan') LIMIT 10; but first execute this on your database: CREATE FULLTEXT INDEX CapitalFTSIDX (Capital); Or restrict your searching to things ending in %'s and use a standard index/key. HTH! $res = mysql_query (EXPLAIN SELECT Name, Residents, Pop, Capital FROM basics where Capital like '%VOLCAN%') or die (mysql_error()); but none of my data displays at all. However, I did indeed strip out all my keys so I could get my tables published online. I probably only needed to get rid of the foreign keys, but I didn't want to take any chances. Anyway, I added a primary key, but the table still loads slowly. However, I'm guessing that it isn't enough to have a key on a table - that key also has to be cited in your query/select statements. Is that right? Thanks. -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL tables performance question
If you are going to be needing to add columns and/or tables, you should probably rethink your data model. I'm not sure what data you are tracking, but perhaps you can consolidate it a bit more. Make your columns into rows with a row type field. A simple example would be tracking phone numbers. Instead of having separate columns for home, work, and mobile, break it out into another table so you can have unlimited phone numbers. Have a descriptor field to indicate what type of phone number it is. You can then add other phone type with ease, like beeper, fax, car, etc. This also has the added advantage of being able to search on all phone numbers in one query, yet also being able to search on just home phones. On Wednesday, July 23, 2003, at 04:26 AM, Marek Lewczuk wrote: Hello, I have a table where misc data are stored. Right now this table has about 30 columns, but for sure it will be more in the near future. So I wonder how the big number (50-100) of table's columns affect for MySQL DB performance. Maybe it's better to create more tables rather than more table's columns ?? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL tables named 'column' cause problems.
Hello, He didn't name a column 'column', he named a table 'column'. This may have seemed reasonable given his application. In any case, bad idea or not, his point is well-taken. It worked in one place but not another. While column is a reserved word, the directions at http://www.mysql.com/doc/L/e/Legal_names.html clearly state you can use it as a table name anyway, as long as you quote it properly. So, it seems to me, either Tozz did something wrong when he invoked mysqldump, or there's a bug which breaks mysqldump on tables named 'column' (or any other reserved word, I'd guess). I suppose it would be useful to know which version of mysql he is using and how he's calling mysqldump so we can figure out which is the case. This is the mysqldump command I use: mysqldump -A -pmysecrethere sql_backup.sql And it fails :(. So this looks like a bug in mysqldump then. Bye, Tozz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL tables named 'column' cause problems.
Column is a mysql reserved word. Just like desc or asc or tons of others. See here: http://www.mysql.com/doc/R/e/Reserved_words.html Dan -Original Message- From: Tozz [mailto:[EMAIL PROTECTED]] Sent: Saturday, March 16, 2002 8:58 PM To: [EMAIL PROTECTED] Subject: MySQL tables named 'column' cause problems. Hello, While reading my cron messages I found out that the mysqldump returns with an error mysqldump: Can't get CREATE TABLE for table 'column' (You have an error in your SQL syntax near 'column' at line 1) The table column is: CREATE TABLE `column` ( `id` int(11) NOT NULL default '0', `name` text NOT NULL, `user` text NOT NULL, `text` longtext NOT NULL ) TYPE=MyISAM; Anyway, I was unable to a 'DROP column;'. This returns in an error value. Then I change the above snipped into: CREATE TABLE `kolom` ( `id` int(11) NOT NULL default '0', `name` text NOT NULL, `user` text NOT NULL, `text` longtext NOT NULL ) TYPE=MyISAM; (kolom is dutch for column), there is no problem and my backup exists without errors. So, in short it comes down too: MySQL buggs when there is a table named 'column' Please mail any replies to [EMAIL PROTECTED] as I am not a member of this list. Bye, Tozz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL tables named 'column' cause problems.
Hey Column is a mysql reserved word. Just like desc or asc or tons of others. See here: http://www.mysql.com/doc/R/e/Reserved_words.html Dan Then, imho I think its stupid that MySQL lets you create tables with reserved words, but it stops you from making dumps. Still seems like a bug to me. Bye, Tozz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL tables named 'column' cause problems.
On Sun, Mar 17, 2002 at 05:31:52AM +0100, Tozz wrote: Hey Column is a mysql reserved word. Just like desc or asc or tons of others. See here: http://www.mysql.com/doc/R/e/Reserved_words.html Dan Then, imho I think its stupid that MySQL lets you create tables with reserved words, but it stops you from making dumps. Still seems like a bug to me. Sir, naming a column column is generally considered a bad idea, regardless of the DBMS being used. It's like naming a variable variable. Bob Hall -- All my databases are named database, datafile, datafil, etc. The tables are all named table, tabell, datasett, etc. Every one who has to do maintenance on the databases hates me. It's MySQL's fault for letting me set them up that way. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL tables named 'column' cause problems.
He didn't name a column 'column', he named a table 'column'. This may have seemed reasonable given his application. In any case, bad idea or not, his point is well-taken. It worked in one place but not another. While column is a reserved word, the directions at http://www.mysql.com/doc/L/e/Legal_names.html clearly state you can use it as a table name anyway, as long as you quote it properly. So, it seems to me, either Tozz did something wrong when he invoked mysqldump, or there's a bug which breaks mysqldump on tables named 'column' (or any other reserved word, I'd guess). I suppose it would be useful to know which version of mysql he is using and how he's calling mysqldump so we can figure out which is the case. Michael On Sun, 17 Mar 2002, Bob Hall wrote: On Sun, Mar 17, 2002 at 05:31:52AM +0100, Tozz wrote: Hey Column is a mysql reserved word. Just like desc or asc or tons of others. See here: http://www.mysql.com/doc/R/e/Reserved_words.html Dan Then, imho I think its stupid that MySQL lets you create tables with reserved words, but it stops you from making dumps. Still seems like a bug to me. Sir, naming a column column is generally considered a bad idea, regardless of the DBMS being used. It's like naming a variable variable. Bob Hall -- All my databases are named database, datafile, datafil, etc. The tables are all named table, tabell, datasett, etc. Every one who has to do maintenance on the databases hates me. It's MySQL's fault for letting me set them up that way. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables
On Wed, 21 Feb 2001, Rolf Hopkins wrote: Have you tried checktable? Are there many numbers giving weird results or just one? Are you running an old version of Mysql? Any of the columns auto inc by any chance. in the database there's only one auto_increment field in another table. I haven't tried checktable, is it available in MySQL-3.20.32? Yes, I KNOW it's old. But upgrading is not an option. BTW you really shouldn't "cut" your original message unless it isn't really important. This is in case someone knows the answer but didn't get your first posting. Yes, true. BTW, you should CC to me if you're sending to the list, as I'm subscribed to it :) I'm getting the mails twice. * MP3's EN VIVO de INFUSION: http://members.xoom.com/sysfork/ * MP OnLine? EL BBS? FeedBack? - System Fork!!! 4799-2510 TLD 24hs * El sexo es como el Hacking. Entras, salis y esperas no haber dejado * nada por lo que puedan rastrearte. * Panic? My kernel doesn't panic! We are doomed! DustDustDust - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables
- Original Message - From: "Arturo Busleiman" [EMAIL PROTECTED] To: "Rolf Hopkins" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 21, 2001 20:38 Subject: Re: MySQL Tables On Wed, 21 Feb 2001, Rolf Hopkins wrote: Have you tried checktable? Are there many numbers giving weird results or just one? Are you running an old version of Mysql? Any of the columns auto inc by any chance. in the database there's only one auto_increment field in another table. I haven't tried checktable, is it available in MySQL-3.20.32? Yes, I KNOW it's old. But upgrading is not an option. Ouch, that is old. I don't think even the MySQL team support that version anymore but I could be wrong. Don't know if it existed in that version or not. I'm sure the manual will have something about it. Why is upgrading not an option? There should be ismchk but I can't say for sure BTW you really shouldn't "cut" your original message unless it isn't really important. This is in case someone knows the answer but didn't get your first posting. Yes, true. BTW, you should CC to me if you're sending to the list, as I'm subscribed to it :) I'm getting the mails twice. That's true to but how do I know whose subscribed and whose not, so I reply all. O | O \/ * MP3's EN VIVO de INFUSION: http://members.xoom.com/sysfork/ * MP OnLine? EL BBS? FeedBack? - System Fork!!! 4799-2510 TLD 24hs * El sexo es como el Hacking. Entras, salis y esperas no haber dejado * nada por lo que puedan rastrearte. * Panic? My kernel doesn't panic! We are doomed! DustDustDust - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables
I think you'll get better results if you don't quote your numbers. Quotes should be used for text and dates (depending) but not numbers. - Original Message - From: "Arturo Busleiman" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 21, 2001 11:54 Subject: MySQL Tables These are the commands I'm currently using to build the MySQL tables used/needed by a program I'm doing: What is wrong here that an INSERT ('2','2','1','test',NULL) generates: | 33554432 | 33554432 | 1 | test| 20171221080222 | the 33554432 shouldn't be 2's? anything other than INT works WELL (and the 3rd column's a TINYINT, whereas the other two are INT's) why the third column is OK? CREATE TABLE msg ( f INT UNSIGNED, t INT UNSIGNED, mt TINYINT UNSIGNED, md BLOB, mdt TIMESTAMP, INDEX (t) ); * MP3's EN VIVO de INFUSION: http://members.xoom.com/sysfork/ * MP OnLine? EL BBS? FeedBack? - System Fork!!! 4799-2510 TLD 24hs * El sexo es como el Hacking. Entras, salis y esperas no haber dejado * nada por lo que puedan rastrearte. * Panic? My kernel doesn't panic! We are doomed! DustDustDust Yahoo! Groups Sponsor -~-~ eGroups is now Yahoo! Groups Click here for more details http://us.click.yahoo.com/kWP7PD/pYNCAA/4ihDAA/stAVlB/TM -_- To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables
On Wed, 21 Feb 2001, Rolf Hopkins wrote: I think you'll get better results if you don't quote your numbers. Quotes should be used for text and dates (depending) but not numbers. tried, but same results. * MP3's EN VIVO de INFUSION: http://members.xoom.com/sysfork/ * MP OnLine? EL BBS? FeedBack? - System Fork!!! 4799-2510 TLD 24hs * El sexo es como el Hacking. Entras, salis y esperas no haber dejado * nada por lo que puedan rastrearte. * Panic? My kernel doesn't panic! We are doomed! DustDustDust - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables
Have you tried checktable? Are there many numbers giving weird results or just one? Are you running an old version of Mysql? Any of the columns auto inc by any chance. BTW you really shouldn't "cut" your original message unless it isn't really important. This is in case someone knows the answer but didn't get your first posting. - Original Message - From: "Arturo Busleiman" [EMAIL PROTECTED] To: "Rolf Hopkins" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 21, 2001 12:10 Subject: Re: MySQL Tables On Wed, 21 Feb 2001, Rolf Hopkins wrote: I think you'll get better results if you don't quote your numbers. Quotes should be used for text and dates (depending) but not numbers. tried, but same results. * MP3's EN VIVO de INFUSION: http://members.xoom.com/sysfork/ * MP OnLine? EL BBS? FeedBack? - System Fork!!! 4799-2510 TLD 24hs * El sexo es como el Hacking. Entras, salis y esperas no haber dejado * nada por lo que puedan rastrearte. * Panic? My kernel doesn't panic! We are doomed! DustDustDust - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Tables over Multiple Drives
Sam, Thanks for the reply.. Is that a stable way of doing things? Is there a performance hit when using the --with-raid option? I assume I will need to move the files manually and link them manually as well? --- Jason H. Frisvold Senior ATM Engineer Engineering Dept. Penteledata CCNA Certified - CSCO10151622 [EMAIL PROTECTED] --- "I love deadlines. I especially like the whooshing sound they make as they go flying by." -- Douglas Adams -Original Message- From: Sam Wong [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 30, 2001 5:41 AM To: [EMAIL PROTECTED] Subject: Re: MySQL Tables over Multiple Drives Compile mysql with --with-raid option, then use symbolic link to link it to other drive - Original Message - From: "Jason Frisvold" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 30, 2001 6:33 PM Subject: MySQL Tables over Multiple Drives I looked through the manual briefly and did not see this addressed anywhere.. Is it possible to have a table span over multiple drive without a raid? Or perhaps possible to specify a directory where a table will reside other than the default directory? Thanks, --- Jason H. Frisvold Senior ATM Engineer Engineering Dept. Penteledata CCNA Certified - CSCO10151622 [EMAIL PROTECTED] --- "I love deadlines. I especially like the whooshing sound they make as they go flying by." -- Douglas Adams - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, Jan 30, 2001 at 12:44:43PM +0100, Tonu Samuel wrote: On Tue, 30 Jan 2001, Jason Frisvold wrote: Thanks for the reply.. Is that a stable way of doing things? Is there a performance hit when using the --with-raid option? Very small if at all. RAID does some additional syscalls sometime but they should be enough rare to not sense this. So why isn't the --with-raid option set in the binary download version? Tim [who would like it to be]. I assume I will need to move the files manually and link them manually as well? Yes and no. When you create table using CREATE TABLE RAID_TYPE=RAID0 then MySQL creates directories 00/, 01/ and so on into data directory and creates tables into them. You can create symlinks to toher disks named 00, 01, 02 and MySQL does not rewrite them. Tonu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, 30 Jan 2001, Sam Wong wrote: Date: Tue, 30 Jan 2001 19:32:05 +0800 From: Sam Wong [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: MySQL Tables over Multiple Drives Yes, you've to recreate the tables with RAID option (read the manual for details) and move and link the directoris manually. I think there may be a performance gain, instead of performance hit...Because as the file spread on diff discs, the seek time gain. The code should has been stablize already, I think. BTW, why you want it to span over multiple drive? Please excuse me for being show but "RAID" turns up no hits on the index page of the doc's. What is it under and on what versions support it? Thanks. Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, Jan 30, 2001 at 05:08:46PM +, Tim Bunce wrote: On Tue, Jan 30, 2001 at 10:57:16PM +0800, Sam Wong wrote: So why isn't the --with-raid option set in the binary download version? The file will be bigger and slower in result I doubt it would be significantly bigger. I believe --with-raid support is a very small and simple layer between mysqld and the file i/o system calls. At the open source database summit, I asked Monty why it wasn't enabled in the binaries they provide, and he said that it was for performance reasons. On each table open, MySQL has to see if it is a RAID table and do a bit of extra work. I'm not sure what you mean by "slower in result". He has estimated it as a performance hit of a "few percent", but if your tables stay open (because you don't have many, or you have a good-sized table cache) it really shouldn't be an issue. He said that MaxSQL would have many of the compile-time options enabled for folks who still wanted to use a binary release. But it doesn't appear to have materialized... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, Jan 30, 2001 at 01:13:43PM -0800, Jeremy D. Zawodny wrote: On Tue, Jan 30, 2001 at 05:08:46PM +, Tim Bunce wrote: On Tue, Jan 30, 2001 at 10:57:16PM +0800, Sam Wong wrote: So why isn't the --with-raid option set in the binary download version? The file will be bigger and slower in result I doubt it would be significantly bigger. I believe --with-raid support is a very small and simple layer between mysqld and the file i/o system calls. At the open source database summit, I asked Monty why it wasn't enabled in the binaries they provide, and he said that it was for performance reasons. On each table open, MySQL has to see if it is a RAID table and do a bit of extra work. Why not determine it by checking only if the open of the non-raid fail failed? That way there'd be no penalty for those not using the raid feature. I'm not sure what you mean by "slower in result". He has estimated it as a performance hit of a "few percent", but if your tables stay open (because you don't have many, or you have a good-sized table cache) it really shouldn't be an issue. I can't see where a "few percent" hit would come from unless tables were being opened at the rate of many per second. Anyone smart enough to be using the raid feature would also know how to tune mysql to keep the files open. Those not using the feature should see no hit. Anyway, even if there is a hit, why not allow a runtime config option to disable the raid feature? Checking a global in C is basically free. He said that MaxSQL would have many of the compile-time options enabled for folks who still wanted to use a binary release. But it doesn't appear to have materialized... I wish it would. Monty, can you give us an update? Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, Jan 30, 2001 at 06:51:01AM -0500, Jason Frisvold wrote: Well, there are a few reasons.. We have a system with 4 8G drives.. If we use one for root and one to mirror that root, we have 2 drives left. Now, if we raid those 2 remaining drives (RAID5) we get about 12 Gigs of space available... I'm confused; what does RAID5 on just two drives get you? One drive, and you're still dead. I would have thought that (in this case) just striping would have been the way to go... --- Jason H. Frisvold Senior ATM Engineer Engineering Dept. Penteledata CCNA Certified - CSCO10151622 [EMAIL PROTECTED] --- "I love deadlines. I especially like the whooshing sound they make as they go flying by." -- Douglas Adams -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php