Help Table Types Causing Issues
Hello, I have created a table for items. And I needed some of the columns to be searchable, so I created the table as: a MyISAM for full text search. CREATE TABLE `item` ( `parent_id` int(11) NOT NULL default '0', `id` int(11) NOT NULL auto_increment, `manufacturer_id` varchar(50) default NULL,, `limited_qty` char(1) NOT NULL default 'N', `link_name` varchar(50) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_id` (`id`), FULLTEXT KEY `description` (`description`), FULLTEXT KEY `name` (`name`), FULLTEXT KEY `name_2` (`name`), FULLTEXT KEY `name_3` (`name`,`description`) ) TYPE=MyISAM; The above table item has a primary key of ID. since this is an 'item' I have 2 other tables that have references to this item: item_cart_ref and item_category_ref. I needed to create foreign keys on those two tables, but they are type InnoDB because of the foreign key. CREATE TABLE cart_ref_items ( cart_id varchar(50) not null, item_id int not null, usertype varchar(20) NOT NULL DEFAULT 'anonymous', quantity int default 1, key(item_id), FOREIGN KEY (item_id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB But I cannot mix types, it errors: So how can one use foreign key references to their main table when it needs to have full-text search? Help Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help Table Types Causing Issues
Scott Purcell [EMAIL PROTECTED] wrote on 07/29/2005 04:44:10 PM: Hello, I have created a table for items. And I needed some of the columns to be searchable, so I created the table as: a MyISAM for full text search. CREATE TABLE `item` ( `parent_id` int(11) NOT NULL default '0', `id` int(11) NOT NULL auto_increment, `manufacturer_id` varchar(50) default NULL,, `limited_qty` char(1) NOT NULL default 'N', `link_name` varchar(50) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_id` (`id`), FULLTEXT KEY `description` (`description`), FULLTEXT KEY `name` (`name`), FULLTEXT KEY `name_2` (`name`), FULLTEXT KEY `name_3` (`name`,`description`) ) TYPE=MyISAM; The above table item has a primary key of ID. since this is an 'item' I have 2 other tables that have references to this item: item_cart_ref and item_category_ref. I needed to create foreign keys on those two tables, but they are type InnoDB because of the foreign key. CREATE TABLE cart_ref_items ( cart_id varchar(50) not null, item_id int not null, usertype varchar(20) NOT NULL DEFAULT 'anonymous', quantity int default 1, key(item_id), FOREIGN KEY (item_id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB But I cannot mix types, it errors: So how can one use foreign key references to their main table when it needs to have full-text search? Help Scott Fulltext searching in InnoDB is on the TODO list. Until then you can design your data in Innodb (so that you can create and use foreign keys) with one exception. A simple table (two columns is usually enough, I think you will need one with five) will need to be created in MyISAM to contain the TEXT field(s) you want to FT search. One column contains the PK value of the table you maintain in InnoDB while the other column(s) contains the TEXT. Yes it's a bit of an overhead but since none of the BIG MONEY is paying to make FT+InnoDB a priority, we lowly community users have to work around the lack of funcitonality until it makes it through the development pipeline. I think that it is a fair compromise. One straggler table to be able to do FT searches is not too much to ask or maintain with your application code outside of the FK protections. Nobody said MySQL was perfect but it is REALLY GOOD, ya know? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
table types
hi list, im a newbie around here. i'v gone through some parts of the mysql documentation. however, i'd like to know the meaning of spatial, clustered and unique table types. or is it column types? - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
show table types
SHOW TABLE TYPES became available in MYSQL 4.1. What is the equivalent for MYSQL 3.23.49? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table types
Hello. SHOW TABLE STATUS SHOW CREATE TABLE sol beach [EMAIL PROTECTED] wrote: How do I find out what table type is associated with each of the tables in MYSQL? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: table types
SHOW TABLE STATUS (it includes type/storage engine as one of the returned columns) --bemansell Brian E. Mansell MySQL Professional On Wed, 15 Dec 2004 19:21:24 -0800, sol beach [EMAIL PROTECTED] wrote: How do I find out what table type is associated with each of the tables in MYSQL? -- 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]
table types
How do I find out what table type is associated with each of the tables in MYSQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote: As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Yes, if you use both table types within a single database then you will have to split up the memory usage. However, in many databases there are just one or two tables that use 90% of the disk/memory space. If this is your situation then you just allocate most of the memory for the table type these tables use (assumign they use the same type), and you won't have any performance problem because the others don't need much memory. However, if your data is evenly split and evenly accessed between the two table types then splitting the memory may present some performance degredation. Of course the solution is buy more memory. John On Mon, 2004-10-11 at 09:49 -0700, Benjamin Arai wrote: Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote: As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Diffrences in Table Types
Greetings: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Diffrences in table types
(SORRY FOR THE BLANK MESSAGE WRONG BUTTON PUSHED) Greetings: I am trying to create referential intergrity... I was reading that mySql does not support that on the Database Level and that you have to do it manually. However i am now seeing some posts that talk about it... i.e. Foreign Keys and such... with INNODB can someone please fill me in Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Diffrences in table types
It's pretty much just how you said it. MyISAM tables (the default mysql table type) do not support foreign key constraints. InnoDB tables do support them. Here is a breakdown of the pros and cons of each as I see it (others please feel free to tell me I've got it all wrong :)). MyISAM pros: 1)fast 2)default 3)smaller disk footprint MyISAM cons: 1)Table level locking creates poor performance in high concurrency situations 2)No foreign key constraints 3)No transation support InnoDB pros: 1)Transactions 2)Row level locking for good performance in high concurrency situations 3)Foreign Key Contstraints InnoDB cons: 1)Higher disk footprint 2)Slightly slower in non high concurrency situations due to transaction overhead key constraint checking etc I've had a very positive experience with using InnoDB tables in a production environment with a several gigabyte database. John McCaskey From: GH [mailto:[EMAIL PROTECTED] Sent: Sat 10/9/2004 5:29 PM To: [EMAIL PROTECTED] Subject: Diffrences in table types (SORRY FOR THE BLANK MESSAGE WRONG BUTTON PUSHED) Greetings: I am trying to create referential intergrity... I was reading that mySql does not support that on the Database Level and that you have to do it manually. However i am now seeing some posts that talk about it... i.e. Foreign Keys and such... with INNODB can someone please fill me in Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Debating over table types
I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Stuart
Re: Debating over table types
InnoDb doesn't support full text indexes on text columns so if you need to search text fields you may want ot use MyISAM. It is perfectly ok to have a mixture of InnoDB and MyISAM in a single database so you can make your decision on a table by table basis. Joe Stuart Felenstein [EMAIL PROTECTED] wrote: I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Stuart [EMAIL PROTECTED] http://www.joeaudette.com http://www.earworkout.com
Re: Debating over table types
I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. Well, how about transaction support? I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Foreign keys luxury? Well, if this aint a read-only database, I wouldn't call that a luxury, but rather a requirement. 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: Debating over table types
Right, I have set up some tables with text columns that will need full text searching, In those cases I chose myisam. Is there any danger in changing table types on the fly ? Not including the loss of any foreign keys that may have been set up (innodb to myisam). I'm wondering more about data corruption ? Stuart Joe Audette [EMAIL PROTECTED] wrote: InnoDb doesn't support full text indexes on text columns so if you need to search text fields you may want ot use MyISAM. It is perfectly ok to have a mixture of InnoDB and MyISAM in a single database so you can make your decision on a table by table basis. Joe Stuart Felenstein wrote: I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Stuart [EMAIL PROTECTED] http://www.joeaudette.com http://www.earworkout.com
Re: Debating over table types
In the last episode (Aug 24), Stuart Felenstein said: Right, I have set up some tables with text columns that will need full text searching, In those cases I chose myisam. Is there any danger in changing table types on the fly ? Not including the loss of any foreign keys that may have been set up (innodb to myisam). I'm wondering more about data corruption ? During testing I've converted tables from MyISAM to Innodb and back with no problems. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Storage Engines and Table Types.....
Hi! How do I know which of the storage engine am I using or running, or whatever that use the databases/tables? Thanks, Scott F.
RE: MySQL Storage Engines and Table Types.....
Do a show variables from the MySQL monitor. show variables; -Original Message- From: Scott Fletcher To: [EMAIL PROTECTED] Sent: 6/28/04 10:27 AM Subject: MySQL Storage Engines and Table Types. Hi! How do I know which of the storage engine am I using or running, or whatever that use the databases/tables? Thanks, Scott F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Storage Engines and Table Types.....
You can see a list of the available storage types with SHOW ENGINES To see which engine is in use for any table you can SHOW TABLE STATUS or SHOW CREATE TABLE tablename All of these commands, and more, are documented at : http://dev.mysql.com/doc/mysql/en/SHOW.html Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: Fax to: 06/28/2004 11:27 Subject: MySQL Storage Engines and Table Types. AM Hi! How do I know which of the storage engine am I using or running, or whatever that use the databases/tables? Thanks, Scott F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Storage Engines and Table Types.....
Show table status\G Will give you a list of tables the Type: field for each table is the storage engine. Your installation probably defaults to MyISAM -Eric On Mon, 28 Jun 2004 11:51:40 -0400, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can see a list of the available storage types with SHOW ENGINES To see which engine is in use for any table you can SHOW TABLE STATUS or SHOW CREATE TABLE tablename All of these commands, and more, are documented at : http://dev.mysql.com/doc/mysql/en/SHOW.html Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: Fax to: 06/28/2004 11:27 Subject: MySQL Storage Engines and Table Types. AM Hi! How do I know which of the storage engine am I using or running, or whatever that use the databases/tables? Thanks, Scott F. -- 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: Converting table types
Greg Macek [EMAIL PROTECTED] wrote: I was wondering what happens when you change table types. Does MySQL automatically get rid of the old table files? Yes, MyISAM files are deleted. As for converting InnoDB - MyISAM, then of course, InnoDB tablespace is not deleted, but freed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Converting table types
Hello, I was wondering what happens when you change table types. Does MySQL automatically get rid of the old table files? I'm looking into changing out some tables from MyISAM to InnoDB. I'm not tight on space, but I wasn't sure how it handled the conversion, since the storage location changes with this. -- Greg Macek | Senior IT Manager Marketing Resources, Inc. Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table types
Hi, Is it wise to have a database with hybrid table types? In other words: if I have a table that wouldn´t have many INSERT/DELETE/UPDATE queries, may I create it as MyISAM type and even interact (make JOINs) with other InnoBD and MyISAM tables? Or is it better (faster) to create all columns with the same type (InnoDB)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table types
Hi Ronan, Yes, it's fine to mix table types in databases and queries. Matt - Original Message - From: Ronan Lucio Sent: Thursday, June 03, 2004 2:44 PM Subject: Table types Hi, Is it wise to have a database with hybrid table types? In other words: if I have a table that wouldn´t have many INSERT/DELETE/UPDATE queries, may I create it as MyISAM type and even interact (make JOINs) with other InnoBD and MyISAM tables? Or is it better (faster) to create all columns with the same type (InnoDB)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mixed table types in transactions?
Hi all, Here's one of those damned annoying what if questions for everyone. If I have a transaction that performs INSERTs or UPDATEs on a bunch of tables, some InnoDB and some MyISAM, and after performing a bunch of modifications on each I issue a ROLLBACK, does the fact that a MyISAM table is involved in the process cause any problems with the ROLLBACK at all? Any definitive answer either way would be appreciated, as I have just inheritted the codebase from hell to either maintain or redo parts of (redo would be appreciated, so all bagging of the above approach is invited!) Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mixed table types in transactions?
At 16:18 +1000 8/18/03, Chris Nolan wrote: Hi all, Here's one of those damned annoying what if questions for everyone. If I have a transaction that performs INSERTs or UPDATEs on a bunch of tables, some InnoDB and some MyISAM, and after performing a bunch of modifications on each I issue a ROLLBACK, does the fact that a MyISAM table is involved in the process cause any problems with the ROLLBACK at all? MyISAM isn't transactional, so operations performed on the MyISAM tables during the transaction won't be rolled back. Any definitive answer either way would be appreciated, as I have just inheritted the codebase from hell to either maintain or redo parts of (redo would be appreciated, so all bagging of the above approach is invited!) Regards, Chris -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Referential integrity, FULLTEXT and table types
I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order to support referential integrity. If I create the child tables as INNODB tables, will referential integrity still work with the MyISAM parent table? Many thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Referential integrity, FULLTEXT and table types
I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order to support referential integrity. If I create the child tables as INNODB tables, will referential integrity still work with the MyISAM parent table? Hello Tom, Currently you can't use InnoDB tables and Full-Text search, also you can't use MyISAM (which support Full-Text) with foreign keys (it's planned to implement foreign keys in MyISAM tables in MySQL 5.0). So my suggestion: use InnoDB MyISAM together - maybe it isn't referential safe but what can we do... Good luck, Marek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Referential integrity, FULLTEXT and table types
At 16:20 +0100 28-07-2003, Tom Gazzini wrote: I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order to support referential integrity. If I create the child tables as INNODB tables, will referential integrity still work with the MyISAM parent table? Many thanks, Tom If You can do a join between InnoDB MyIsam You can put your text in one table and other data in another table, then link the tables with ids. I'm not sure You can mix InnoDB MyIsam tables in a join. Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Hi Everybody, our server was down for 8 hours. I applogize in case anybody did try to access the table chart. Its up and running and of course I would appreciate any feedback. http://www.knowd.co.jp/staff/nils/mysql-ttf.html Best regards Nils Valentin Tokyo/Japan 2003 5 30 17:34Nils Valentin : I created a little table chart showing the features of the different table types. Its not complete and might even contain wrong info. The information is taken from the the mysql manual and several books. http://knowd.co.jp/staff/nils/mysql-ttf.html As I dont have (yet) had the time to try all features of each table type I would appreciate if anybody with experience could take a sharp look and point out to me anything that is obvious wrong in the chart. I want this to be a practical chart, meaning as live is there will be a difference between the documentattion and the real life. Please also let me know why it is wrong and on which system you made the experience. Perhaps you are using features which are not even mentioned on the chart ? Also in this case let me know please, I will update the chart. Of course I will make the final chart available so the everybody can use it. Note: The Gemini table was available only from Nusphere and is not sold or used anymore. I know this, but I want to create a chart which shows the feature of all possible table types on a technical base, to fully understand the advantages/disadvantages each type has or had. ( You will also find enclosed the ISAM table type which is probably not even used anymore ;-) Best regards and thank you for any feedback Nils Valentin Tokyo/Japan 2003 5 29 16:35Nils Valentin : Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Hi Edward, I expected this question to come up once more, so I think that this would have happened anyway, so let's just forget it shall we ? ;-) Probably it's only you and me reading it ;-) Best regards Nils Valentin Tokyo/Japan On Fri, 30 May 2003 09:07:17 -0400, Becoming Digital [EMAIL PROTECTED] wrote: It was my desire to post off-list but Jeremy's reply-to address duped me. It wasn't until after I sent that I realized this. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Becoming Digital [EMAIL PROTECTED] Sent: Friday, 30 May, 2003 08:13 Subject: Re: Practical samples for table types Hi Edward, Thank you for the reply. I am only interested in any technical details, not if it was good or bad what a certain company did. Please be so kind and leave the legal matters out of this. Thank you for understanding. Best regards Nils Valentin Tokyo/Japan 2003300:50ecoming Digital was on questionable legal ground. In fact, *I'm* convinced it was illegal. Care to elaborate on this? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 29 May, 2003 10:57 Subject: Re: Practical samples for table types On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 82-0024 6-1 Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
I created a little table chart showing the features of the different table types. Its not complete and might even contain wrong info. The information is taken from the the mysql manual and several books. http://knowd.co.jp/staff/nils/mysql-ttf.html As I dont have (yet) had the time to try all features of each table type I would appreciate if anybody with experience could take a sharp look and point out to me anything that is obvious wrong in the chart. I want this to be a practical chart, meaning as live is there will be a difference between the documentattion and the real life. Please also let me know why it is wrong and on which system you made the experience. Perhaps you are using features which are not even mentioned on the chart ? Also in this case let me know please, I will update the chart. Of course I will make the final chart available so the everybody can use it. Note: The Gemini table was available only from Nusphere and is not sold or used anymore. I know this, but I want to create a chart which shows the feature of all possible table types on a technical base, to fully understand the advantages/disadvantages each type has or had. ( You will also find enclosed the ISAM table type which is probably not even used anymore ;-) Best regards and thank you for any feedback Nils Valentin Tokyo/Japan 2003 5 29 16:35Nils Valentin : Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
was on questionable legal ground. In fact, *I'm* convinced it was illegal. Care to elaborate on this? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 29 May, 2003 10:57 Subject: Re: Practical samples for table types On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- 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: Practical samples for table types
Hi Edward, Thank you for the reply. I am only interested in any technical details, not if it was good or bad what a certain company did. Please be so kind and leave the legal matters out of this. Thank you for understanding. Best regards Nils Valentin Tokyo/Japan 2003 5 30 20:50Becoming Digital : was on questionable legal ground. In fact, *I'm* convinced it was illegal. Care to elaborate on this? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 29 May, 2003 10:57 Subject: Re: Practical samples for table types On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Dear Edward, Thank you for the reply. I appreciate your informaton. Do you have by chance also any practical samples ? f.e Until which table size might it be better to use f.e MyISAM and when would you use another format (Heap or a TST table ? f.e When you have have mostly read only access to data and the amount fits into the memory easily than you could think about using a HEAP type etc. How big should the query cache be compared to the table size ? I believe that what I am looking for is practical samples - or some rough guidelines whats known to work good. Best regards Nils Valentin Tokyo/Japan - MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. InnoDB is the more popular and stable transaction-safe table type in open-source MySQL and was designed specifically for high performance with large volumes of data, as well as overall CPU efficiency. Like BDB, support for it did not appear until v3.23.34. Gemini tables are available only in NuSphere's Enhanced MySQL and not in the open source version of MySQL. All of the above is paraphrased from SAMS Teach Yourself MySQL in 24 Hrs. Who knew that book would actually come in handy. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 27 May, 2003 03:58 Subject: Practical samples for table types Hello mysql fans ;-), This time I have a question which sounds simple, but I believe the answer might not be so easy. I would appreciate to hear from anybody some really good samples of which table type would be good for which occasion. I tried to look for some information perhaps a case study or something like this on the internet but I did not find anything like this. Either I looked for the wrong information or it doesnt exist yet. I understand that ISAM tables are OS dependent and obsolete since MyISAM replaced them. That still leaves 7 formats open. (MyISAM, Merge Tables, HEAP, BDB, InnoDB, GEMINI) I would appreciate a omparison chart or soemthing similar showing the features for each table type etc. I want to figure out f.e. if I can modify HEAP tables or perhaps even use them as slaves in a replication system. What will happen when transformin Innodb tables into BDB or Gemini format. Would that be a good idea or not (and why) ? While this is a complex topic I am approaching I dont expect a simple answer or anybody to know everything I am trying to get a more complete understanding of the tables disadvantages and advantages. While the documentation lists up all options for each table I believe it doesn't necessary describe for which purpose they are useful. Perhaps anybody around who was just thinking the same ? -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 82-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
In the meanwhile I found a bit information which goes into the direction what I look for. http://www.mysql.de/newsletter/2002-12/a91.html www.nusphere.com/products/library/gemini.pdf (page 11) I still would appreciate any feedback or additonal information from experienced MySQL users. Best regards -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Field \ Table Types - Beginner
Hi Well I searched the web for answers first but there is so much its hard to know were to start I'm creating a Genealogical Database and want to start right and ensure my basic table and fields are correct (I see that I can a large variety of table types) My data is made of Names, Dates and occasionally images. From the web side the vast majority of queries will be made on the surname so I'm thinking that I should create a full text index on the surname field ? as that will enable fast searching on the field ? Any advice on what field types I should use for names \ dates and Images would be appreciated - oh and table type. Regards John - 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: Field \ Table Types - Beginner
John, My data is made of Names, Dates and occasionally images. From the web side the vast majority of queries will be made on the surname so I'm thinking that I should create a full text index on the surname field ? as that will enable fast searching on the field ? You will only want to do this if you expect surnames to be some 1,000 chars long ;-) Otherwise, you just take CHAR / VARCHAR, and put an index on the surname column, to speed up lookups and order by statements. Names and other short text: CHAR (faster) or VARCHAR (saves disk space), Dates: DATE or DATETIME (if you need time values, too) or TIMESTAMP (if you want this for the obvious reason the name is telling), Images: You _can_ store images in the database (BLOB column type), but in most cases people just use _references_ to the images, like the file name (as CHAR/VARCHAR). The images themselves are not stored in the database. You can find more info on column types here: http://www.mysql.com/doc/en/Column_types.html Any advice on what field types I should use for names \ dates and Images would be appreciated - oh and table type. Table type: MyISAM is the fastest table type for quite alot of situations. If you expect a high update / select ratio for your records (I assume you don't), InnoDB tables will be superior. If you need advanced stuff like transactions, foreign keys etc. (I assume you don't, at least not at the beginning), you will _have_ to use InnoDB, as MyISAM doesn't support this. I suggest you start with MyISAM, and if you find out it doesn't (optimally) fit your needs, you can still switch to InnoDB. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: John Berman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 04, 2003 4:38 PM Subject: Field \ Table Types - Beginner Hi Well I searched the web for answers first but there is so much its hard to know were to start I'm creating a Genealogical Database and want to start right and ensure my basic table and fields are correct (I see that I can a large variety of table types) My data is made of Names, Dates and occasionally images. From the web side the vast majority of queries will be made on the surname so I'm thinking that I should create a full text index on the surname field ? as that will enable fast searching on the field ? Any advice on what field types I should use for names \ dates and Images would be appreciated - oh and table type. Regards John - 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
Multi Table Types
I have the latest stable mysql installed with innodb support.. is it possible to have all myisam tables but one that i need to have innodb table type for row level locking and transactions Randy - 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: Multi Table Types
At 23:43 -0400 8/18/02, Randy Johnson wrote: I have the latest stable mysql installed with innodb support.. is it possible to have all myisam tables but one that i need to have innodb table type for row level locking and transactions Yes. - 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: Table types in replication
On Thu, Jul 18, 2002 at 04:40:39PM +0100, Pete French wrote: Maybe BDB tables are not for me after all... Yeah, just go with InnoDB. :-) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 50 days, processed 1,076,939,411 queries (247/sec. avg) - 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: Re: Table types in replication
Yeah, just go with InnoDB. :-) any good ? I took a look at the documentation, but it all seemed somewhat heavyweight for my liking... I have an application that runs very nicely at the moment - we have one minor problem which is that we have one insert into two tables which may not hapen properly if a process is killed (e.g. websever sutdown) at the wrong moment. it happens once in a blue boom, but I wanted to fix it by rolling it up in a BEGIN / COMMIT just for those two sql transactions. But I;ve been working on it for 4 days now and it's not happening unfortunately! If INNODB is actually table (unlike the problems I have had with BDB) then I might try that, but currently I am thinking of just sticking with myISAM. cheers, -pcf. - 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
Table types in replication
If I have amast/salve pair where the slave is replicating from the master then do the table types have to be the same ? Specifically can I have a myisam table on the master and replicate to a bdb table on the slave ? The reasoning behind this is to try and find a *fast* was to convert a MYISAM table to a BDB table having the database down for the minimum amount of time. Idea so far is this: on current database machine do a ''mysqldump' and then enable logging. Load onto new database machine with table types set of BDB. Make new db machine replicate from old until it has caught up with the new data which was inserted into the master whilst the load was happening. When both are in sync then take them down and point allupdating clients to the new database machine. Any comments ? Converting the tables in situ is painfully slow - a couple of days I suspect. -pcf. - 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: Table types in replication
Hello! Pete French wrote: If I have amast/salve pair where the slave is replicating from the master then do the table types have to be the same ? Specifically can I have a myisam table on the master and replicate to a bdb table on the slave ? The reasoning behind this is to try and find a *fast* was to convert a MYISAM table to a BDB table having the database down for the minimum amount of time. Idea so far is this: How about this: CREATE TABLE new_table (all like your current one, besides indexes) TYPE=BDB; INSERT INTO new_table SELECT * FROM old_table; CREATE INDEX ... ON new_table;... ALTER TABLE new_table ADD PRIMARY KEY (...);... RENAME old_table TO old_table_bak; RENAME new_table TO old_table; That should cause a downtime of less than a second... last but not least you need insert all rows that have been created between the INSERT and the last RENAME. on current database machine do a ''mysqldump' and then enable logging. Load onto new database machine with table types set of BDB. Make new db machine replicate from old until it has caught up with the new data which was inserted into the master whilst the load was happening. When both are in sync then take them down and point allupdating clients to the new database machine. Any comments ? Converting the tables in situ is painfully slow - a couple of days I suspect. Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Table types in replication
CREATE TABLE new_table (all like your current one, besides indexes) TYPE=BDB; INSERT INTO new_table SELECT * FROM old_table; CREATE INDEX ... ON new_table;... ALTER TABLE new_table ADD PRIMARY KEY (...);... RENAME old_table TO old_table_bak; RENAME new_table TO old_table; That should cause a downtime of less than a second... Interesting suggestion... I have about a million rows in that table, but I'll try it... last but not least you need insert all rows that have been created between the INSERT and the last RENAME. Well, if its less than a second I can afford to have the database down for that long - its the several days that is problematical. *quick test* About 3 minutes to do thecopy -not bad at all! I've been doing some experimentswith BDB tables though and am having real problems- of the locking up the mysql server type! I have a small benchmark I use to check the speed of my main table, which locks the tbale, does two updates n a single rowof that table and unlocks it again. I run many of these in parallel (up to 100) to get a ffeel for the load. Running these tests on the BDB version of the table I have found that after aout 30 connections I get Can't lock file (errno: 12) at which point the server locks up. It will not shutdown properly, and just hangs - eventually needing to be killed by hand. Maybe BDB tables are not for me after all... -pcf. - 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
table types
Hi all, What are the major differences between BDB and MyISAM separate the fact that BDB is slower and suports transactions, and MyISAM faster and does not support transactions ? (On most recent version of MySQL 3.23) What are the major avantages/drawbacks of InnoDB ? Thanks - 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
Converting table types
I don't believe my web host has either InnoDB or BDB installed on their system so I ran some tests here: mysql alter table Development_EventType TYPE=INNODB; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 That's the result I get, but when I do a table dump, the type is still showing MyISAM. 1. Is my syntax correct on this? 2. Is there a way to list table types installed in a system? I do have php installed , there, if that helps. Carl - 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: Converting table types
Excellent, thanks much, that solved the problem: mysql SHOW VARIABLES LIKE 'have\_%'; +---+---+ | Variable_name | Value | +---+---+ | have_bdb | NO| | have_gemini | NO| | have_innodb | NO| | have_isam | YES | | have_raid | NO| | have_ssl | NO| +---+---+ 6 rows in set (0.00 sec) mysql Looks to pretty clear like it ain't there. Carl On Sun, 7 Apr 2002, Paul DuBois wrote: At 20:23 -0400 4/7/02, Carl Schmidt wrote: I don't believe my web host has either InnoDB or BDB installed on their system so I ran some tests here: mysql alter table Development_EventType TYPE=INNODB; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 That's the result I get, but when I do a table dump, the type is still showing MyISAM. 1. Is my syntax correct on this? Yes, but MySQL won't complain if you alter a table to a type that isn't available. 2. Is there a way to list table types installed in a system? I do have php installed , there, if that helps. SHOW VARIABLES LIKE 'have\_%' will show many of them. MyISAM doesn't show up in the list; it's always available, from MySQL 3.23 on. Carl - 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
Follow up question to table types
One thing did occur to me though. I was looking at the syntax for actually creating a database on the mysql server. I wanted to make sure that those table types that are installed with mysql do not have to be specified as _available_ to tables in a particular database. In other words, when a database is created from the command line, are there any options that say something like make InnoDB types available for this database or something like that? Kind of a shot in the dark, but I wanted to cover all the bases. Carl - 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
joins across table types?
Hello, I have finally decided to start using innodb tables in production enviroments and I noticed that I was able to do a join between a myisam table and a innodb table. Will the ability to do joins across table types always be supported in future versions of mysql? Also is there a standard set of features that all mysql must be able to support that mysql.com developers use internaly? Thanks. -- Vincent Stoessel [EMAIL PROTECTED] Linux and Java Application Developer (301) 362-1750 AIM, MSN: xaymaca2020 , Yahoo Messenger: vks_jamaica - 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
joins across table types?
Hello, I have finally decided to start using innodb tables in production enviroments and I noticed that I was able to do a join between a myisam table and a innodb table. Will the ability to do joins across table types always be supported in future versions of mysql? Also is there a standard set of features that all mysql must be able to support that mysql.com developers use internaly? Thanks. -- Vincent Stoessel [EMAIL PROTECTED] Linux and Java Application Developer (301) 362-1750 AIM, MSN: xaymaca2020 , Yahoo Messenger: vks_jamaica - 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
Table types
We're currently using MyISAM tables for everything. Are there circumstances in which the InnoDB table type would be better even if we're not going to use commit/rollback, or are transactions the only advantage of InnoDB? Would InnoDB's row-level locking improve speed over MyISAM for tables that have lots of updates and inserts, or does the transaction overhead cancel that out? -- Keith C. Ivey [EMAIL PROTECTED] Washington, DC - 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: Table types
On Wed, Jan 30, 2002 at 10:35:21AM -0500, Keith C. Ivey wrote: We're currently using MyISAM tables for everything. Are there circumstances in which the InnoDB table type would be better even if we're not going to use commit/rollback, or are transactions the only advantage of InnoDB? Concurrency is the the other main benefit. Would InnoDB's row-level locking improve speed over MyISAM for tables that have lots of updates and inserts, or does the transaction overhead cancel that out? It does not cancel out the overhead (in my experience). Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 28 days, processed 624,393,907 queries (257/sec. avg) - 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
Forcing Table Types
This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+-+ | Table | Create Table | +---+-+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - 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: Forcing Table Types
Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+--- --+ | Table | Create Table | +---+--- --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - 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: Forcing Table Types
I am using the RPM's, but it didn't give an error so I'm assuming it is properly configured. The MySQL version is: [ken@ken ken]$ mysql --version mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686) Sorry -- I should have included that. I'm assuming there's something wrong with my syntax as I can't create Berkley tables either. Frankly I don't care what table type I use, I just want transactions -- and if I can get them, foreign keys w/ cascade deletes, etc... On Friday 28 December 2001 01:25 pm, Weaver, Walt wrote: Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+-- - --+ | Table | Create Table +---+-- - --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - 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: Forcing Table Types
Hi! On Dec 28, Ken Kinder wrote: This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Try SHOW VARIABLES LIKE 'have_%'; It'll resolve your question. (you'll see have_innodb=NO, have_bdb=NO) Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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: Forcing Table Types
Run the following query: SHOW VARIABLES LIKE 'have_%'; You should see something like this: +---+---+ | Variable_name | Value | +---+---+ | have_bdb | NO| | have_gemini | NO| | have_innodb | YES | | have_isam | YES | | have_raid | NO| | have_openssl | NO| +---+---+ 6 rows in set (0.00 sec) It will tell you if BDB and/or InnoDB is active. It might say disabled, if so you will need to enable in my.cnf -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 12:26 PM To: Weaver, Walt; [EMAIL PROTECTED] Subject: Re: Forcing Table Types I am using the RPM's, but it didn't give an error so I'm assuming it is properly configured. The MySQL version is: [ken@ken ken]$ mysql --version mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686) Sorry -- I should have included that. I'm assuming there's something wrong with my syntax as I can't create Berkley tables either. Frankly I don't care what table type I use, I just want transactions -- and if I can get them, foreign keys w/ cascade deletes, etc... On Friday 28 December 2001 01:25 pm, Weaver, Walt wrote: Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+--- --- - --+ | Table | Create Table +---+--- --- - --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - 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: Forcing Table Types
You can see if you're allowed to create Berkeley and InnoDB by using the show variables command. Just I guess, but the rpm's probably aren't configured for Berkeley and InnoDB tables. You're probably going to have to download the tarball and configure it yourself. If you'd like I can send you the configure script I used to set up MySQL for Berkeley and InnoDB. --Walt -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:26 PM To: Weaver, Walt; [EMAIL PROTECTED] Subject: Re: Forcing Table Types I am using the RPM's, but it didn't give an error so I'm assuming it is properly configured. The MySQL version is: [ken@ken ken]$ mysql --version mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686) Sorry -- I should have included that. I'm assuming there's something wrong with my syntax as I can't create Berkley tables either. Frankly I don't care what table type I use, I just want transactions -- and if I can get them, foreign keys w/ cascade deletes, etc... On Friday 28 December 2001 01:25 pm, Weaver, Walt wrote: Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+-- - --+ | Table | Create Table +---+-- - --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - 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
Question on Table types and EJB persistance
Hi all, I know this might be slightly off topics. I was trying to make JBoss/MM-MySQL_JDBC/MySQL to work together. However, I found the following problem. With MyISAM type, the container-managed persists ok; but, with INNODB type, JBoss doesn't able to persist the data back to the Innodb table. I wonder if anybody have experience similar scenario and would kindly share some thoughts on this. Best regards, Michael - 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: table types
Pete Kuczynski wrote: Hi, I understand mysql supported InnoDB and BDB table types for the purposes of transaction logging, which I need to use. Which is recommended by you folks for NT4 boxes. Test both if you need to know :) I believe that InnoDB is better now but this depends of context of usage. Test it! -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Hong Kong, China ___/ www.mysql.com - 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
table types
Hi, I understand mysql supported InnoDB and BDB table types for the purposes of transaction logging, which I need to use. Which is recommended by you folks for NT4 boxes. Pete ___ Pete Kuczynski Sr. Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - 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
