Re: Older tables caught between ISAM and MyISAM
Hello. Have you tried mysql_convert_table_format script? Spencer Yost [EMAIL PROTECTED] wrote: I just upgraded a server to MySQL 4.1.4. I was at 4.0.8 - everything worked perfect at 4.0.8 However, since the upgrade, a few dozen tables in a few older databases are apparently still in ISAM format. The tables now misbehave when trying to access them(marked in use, error out, trash the server, you name it). This is especially troubling because one of the tables is mysql.user so none of my customer's programs work )-; downgrading back to 4.0.8 for other reasons unfortunately so I have to slog through this. What is infuriating is isamchk reports that each table is in fine shape(with an accurate record count), but no other utilities, like myisamchk, mysqldump, will touch the table . Likewise, none of the SQL statements like REPAIR table (with or without use_frm) or ALTER table work. All of the utilities statements, without hardly an exception, complain that the MYI file is missing. It is missing and never existed for these tables as I have backups going back years and they are on none of them. My guess is these table files appear on the surface to these utilities to be MyISAM(header flag or something) file, but are really ISAM files. Any help if figuring out how to get the data out of the files and into newer tables would be APPRECIATED Spencer Yost Sundance Consulting 336.287.8017 -- 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]
Older tables caught between ISAM and MyISAM
I just upgraded a server to MySQL 4.1.4. I was at 4.0.8 - everything worked perfect at 4.0.8 However, since the upgrade, a few dozen tables in a few older databases are apparently still in ISAM format. The tables now misbehave when trying to access them(marked in use, error out, trash the server, you name it). This is especially troubling because one of the tables is mysql.user so none of my customer's programs work )-; downgrading back to 4.0.8 for other reasons unfortunately so I have to slog through this. What is infuriating is isamchk reports that each table is in fine shape(with an accurate record count), but no other utilities, like myisamchk, mysqldump, will touch the table . Likewise, none of the SQL statements like REPAIR table (with or without use_frm) or ALTER table work. All of the utilities statements, without hardly an exception, complain that the MYI file is missing. It is missing and never existed for these tables as I have backups going back years and they are on none of them. My guess is these table files appear on the surface to these utilities to be MyISAM(header flag or something) file, but are really ISAM files. Any help if figuring out how to get the data out of the files and into newer tables would be APPRECIATED Spencer Yost Sundance Consulting 336.287.8017 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
Hello, Does anyone out there have a working version of MySQL that supports the ISAM and MyISAM engines and who might could convert my old database for me? I'd really appreciate it. Please reply off the list. Thanks, Bagus -Original Message- From: Bagus [mailto:[EMAIL PROTECTED] Sent: Saturday, January 22, 2005 10:48 PM To: mysql@lists.mysql.com Subject: RE: isam to myisam SHOW ENGINES says no support for ISAM. So that explains it. Which other server would you suggest I try installing? Do you mean I should try installing an earlier version of mysql? Where should I find one? I'm worried about messing up my new installation by installing an old one. Is there an alternative? Thanks, Bagus (ugh) -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, January 22, 2005 7:46 PM To: Bagus; mysql@lists.mysql.com Subject: RE: isam to myisam At 19:41 -0600 1/22/05, Bagus wrote: According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. Hmm. Perhaps your server doesn't have support for ISAM compiled in. What does the output of SHOW ENGINES indicate about the ISAM storage engine? If it says NO, it won't be able to read ISAM tables at all. You'll need to find another server that does support ISAM. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
At 19:41 -0600 1/22/05, Bagus wrote: According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. Hmm. Perhaps your server doesn't have support for ISAM compiled in. What does the output of SHOW ENGINES indicate about the ISAM storage engine? If it says NO, it won't be able to read ISAM tables at all. You'll need to find another server that does support ISAM. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
SHOW ENGINES says no support for ISAM. So that explains it. Which other server would you suggest I try installing? Do you mean I should try installing an earlier version of mysql? Where should I find one? I'm worried about messing up my new installation by installing an old one. Is there an alternative? Thanks, Bagus (ugh) -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, January 22, 2005 7:46 PM To: Bagus; mysql@lists.mysql.com Subject: RE: isam to myisam At 19:41 -0600 1/22/05, Bagus wrote: According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. Hmm. Perhaps your server doesn't have support for ISAM compiled in. What does the output of SHOW ENGINES indicate about the ISAM storage engine? If it says NO, it won't be able to read ISAM tables at all. You'll need to find another server that does support ISAM. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
isam to myisam
Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: isam to myisam
At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
I did try --type='myisam' --type='isam' --type=myisam --type=isam --type='MYISAM' --type=MYISAM --type='ISAM' --type=ISAM All produced identical errors. Specifying the individual tables didn't change the error note. Any other ideas? Is this the right way to go about changing these ISD, ISM, and frm files to MYD, MYI and frm? Do I need to do this? Is there another way to get mysql to read the old ISD, ISM and frm files? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ISAM to MyISAM Convert pls
Hello. As of MySQL 4.1, ISAM support is included in the source but not enabled in binary distributions. [EMAIL PROTECTED] wrote: hello i hope im correct here i have a problem, after upgrade 4.1 from 4.0 one table is away (index) i have table.ISM,.frm and .ISD. when i try to convert it with ALTER TABLE table TYPE = MYISAM; he only says did not found table.MYI (where table is my name of the table) how i can convert it to MYI? before update i had MYI file with this table..the other tables are ok only this are now ISM,FRM and )ISD file whoich cannot be read my mysql now... thx richard -- 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]
ISAM to MyISAM Convert pls
hello i hope im correct here i have a problem, after upgrade 4.1 from 4.0 one table is away (index) i have table.ISM,.frm and .ISD. when i try to convert it with ALTER TABLE table TYPE = MYISAM; he only says did not found table.MYI (where table is my name of the table) how i can convert it to MYI? before update i had MYI file with this table..the other tables are ok only this are now ISM,FRM and )ISD file whoich cannot be read my mysql now... thx richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade from ISAM to MyISAM fails with read only error
Any ideas why the following upgrade from ISAM to MyISAM fails: mysql alter table users type = MYISAM; ERROR 1036: Table 'users' is read only This only occurs for about half the tables I tried to upgrade. Thanks! Paul - 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: Upgrade from ISAM to MyISAM fails with read only error
Hey everyone. I figured it out. Packed tables are read only. Thanks! Paul -Original Message- From: Paul Borghese [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 9:26 PM To: [EMAIL PROTECTED] Subject: Upgrade from ISAM to MyISAM fails with read only error Any ideas why the following upgrade from ISAM to MyISAM fails: mysql alter table users type = MYISAM; ERROR 1036: Table 'users' is read only This only occurs for about half the tables I tried to upgrade. Thanks! Paul - 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: question about converting isam to myisam tables (shortcut?)
Dave, Thanks for your response. The problem with that, while it is technically one insert statement, mySQL still builds the primary key record-by-record (keycache), rather than sorting the keys and creating the index in one shot, like myisamshk can do. INSERT... would take upwards of 24 hours to complete on my table of 18 million records, while myisamchk can do it in about 10 minutes. I know my method works, but I wanted to confirm that it was acceptable, and I wasn't missing anything serious. -Hank Instead of dropping down to OS cp commands, insert the data from table TBL_FLAT to TBL_INDX. INSERT INTO TBL_INDX SELECT * FROM TBL_FLAT; This will be valid in mysql. The insert will be fast since only one insert statement. Let the list know if this works for you. Or if your method is valid as well. David -Original Message- From: Henry Hank [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 26, 2002 3:37 PM To: [EMAIL PROTECTED] Subject: question about converting isam to myisam tables (shotcut!) Hello all, I'm in the process of building a new mysql db server box to migrate a database off an existing web server. The webserver is running mysql 3.22.29, and the new box is running 3.23. The two biggest tables are about 20 million records and the data and index files are each just over 1GB, and each have a primary key and three or four non-unique indexes. I've copied the data files from the old box to the new, but when I use the ALTER TABLE...TYPE=MYISAM, it takes forever since it is rebuilding the indexes by keycache. So this is what I'm doing instead, and I'm asking if there are any problems associated with this approach: - the existing ISAM Table is called TBL_ISAM - Create a new/empty myisam table with no indexes named TBL_FLAT - Create a new/empty myisam table with all indexes named TBL_INDX mysql insert into TBL_FLAT select * from TBL_ISAM; (runs very quickly - not updating any indexes) mysql flush tables; the in the OS shell (linux) do the following: $ cp TBL_INDX.frm TBL_FLAT.frm $ cp TNL_INDX.MYI TBL_FLAT.MYI then to rebuild all the indexes via sorting, use myisamchk: $ myisamchk -r -q TBL_FLAT I'm left with a fully populated and indexed TBL_FLAT table, which seems to work perfectly as the original converted ISAM table. An in addition, I did first try myisamchk -r -k0 to disable the indexes and do the insert into the TBL_INDX table, but mysql still insists on building the primary key via the keycache - and it takes way too long. My method allows me to populate the data file with no indexes, and then fool mysql into rebuilding all the indexes at once via sorting. Does anyone think there are any risks/problems with this approach? Thanks in advance! -Hank __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.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
question about converting isam to myisam tables (shotcut!)
Hello all, I'm in the process of building a new mysql db server box to migrate a database off an existing web server. The webserver is running mysql 3.22.29, and the new box is running 3.23. The two biggest tables are about 20 million records and the data and index files are each just over 1GB, and each have a primary key and three or four non-unique indexes. I've copied the data files from the old box to the new, but when I use the ALTER TABLE...TYPE=MYISAM, it takes forever since it is rebuilding the indexes by keycache. So this is what I'm doing instead, and I'm asking if there are any problems associated with this approach: - the existing ISAM Table is called TBL_ISAM - Create a new/empty myisam table with no indexes named TBL_FLAT - Create a new/empty myisam table with all indexes named TBL_INDX mysql insert into TBL_FLAT select * from TBL_ISAM; (runs very quickly - not updating any indexes) mysql flush tables; the in the OS shell (linux) do the following: $ cp TBL_INDX.frm TBL_FLAT.frm $ cp TNL_INDX.MYI TBL_FLAT.MYI then to rebuild all the indexes via sorting, use myisamchk: $ myisamchk -r -q TBL_FLAT I'm left with a fully populated and indexed TBL_FLAT table, which seems to work perfectly as the original converted ISAM table. An in addition, I did first try myisamchk -r -k0 to disable the indexes and do the insert into the TBL_INDX table, but mysql still insists on building the primary key via the keycache - and it takes way too long. My method allows me to populate the data file with no indexes, and then fool mysql into rebuilding all the indexes at once via sorting. Does anyone think there are any risks/problems with this approach? Thanks in advance! -Hank __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.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
RE: question about converting isam to myisam tables (shotcut!)
Hank Instead of dropping down to OS cp commands, insert the data from table TBL_FLAT to TBL_INDX. INSERT INTO TBL_INDX SELECT * FROM TBL_FLAT; This will be valid in mysql. The insert will be fast since only one insert statement. Let the list know if this works for you. Or if your method is valid as well. David -Original Message- From: Henry Hank [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 26, 2002 3:37 PM To: [EMAIL PROTECTED] Subject: question about converting isam to myisam tables (shotcut!) Hello all, I'm in the process of building a new mysql db server box to migrate a database off an existing web server. The webserver is running mysql 3.22.29, and the new box is running 3.23. The two biggest tables are about 20 million records and the data and index files are each just over 1GB, and each have a primary key and three or four non-unique indexes. I've copied the data files from the old box to the new, but when I use the ALTER TABLE...TYPE=MYISAM, it takes forever since it is rebuilding the indexes by keycache. So this is what I'm doing instead, and I'm asking if there are any problems associated with this approach: - the existing ISAM Table is called TBL_ISAM - Create a new/empty myisam table with no indexes named TBL_FLAT - Create a new/empty myisam table with all indexes named TBL_INDX mysql insert into TBL_FLAT select * from TBL_ISAM; (runs very quickly - not updating any indexes) mysql flush tables; the in the OS shell (linux) do the following: $ cp TBL_INDX.frm TBL_FLAT.frm $ cp TNL_INDX.MYI TBL_FLAT.MYI then to rebuild all the indexes via sorting, use myisamchk: $ myisamchk -r -q TBL_FLAT I'm left with a fully populated and indexed TBL_FLAT table, which seems to work perfectly as the original converted ISAM table. An in addition, I did first try myisamchk -r -k0 to disable the indexes and do the insert into the TBL_INDX table, but mysql still insists on building the primary key via the keycache - and it takes way too long. My method allows me to populate the data file with no indexes, and then fool mysql into rebuilding all the indexes at once via sorting. Does anyone think there are any risks/problems with this approach? Thanks in advance! -Hank __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.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 - 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
Conversion from ISAM to MYISAM table format
I'm in the process of converting all of my database tables from ISAM to MYISAM table format. I'll run the mysql_convert_table_format script which basically does an Alter table. If during this conversion someone accessed a web page which writes to the database (insert or update), what will happen? Will the affected tables become corrupt or will MySQL lock the table, perform the Alter, then unlock the table so the table can be inserted or updated? Thanks, Ozette -- ___ Email: [EMAIL PROTECTED] - 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: Conversion from ISAM to MYISAM table format
On Thu, Mar 07, 2002 at 12:08:29AM -0500, Mr. Ozette Brown wrote: I'm in the process of converting all of my database tables from ISAM to MYISAM table format. I'll run the mysql_convert_table_format script which basically does an Alter table. If during this conversion someone accessed a web page which writes to the database (insert or update), what will happen? Will the affected tables become corrupt or will MySQL lock the table, perform the Alter, then unlock the table so the table can be inserted or updated? MySQL will lock the tables for you. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 27 days, processed 977,994,364 queries (408/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: ISAM 2 MyISAM Conversion
On Mon, Dec 03, 2001 at 09:28:11AM -0800, Steve Osborne wrote: I have an existing database with ISAM tables that I would like to convert to MyISAM tables. Using phpMyAdmin, I can change the table type using a drop-down box, however I do not know the repercussions of the table type conversion. Shouldn't be any. :-) -- 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 7 days, processed 158,349,997 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
ISAM 2 MyISAM Conversion
I have an existing database with ISAM tables that I would like to convert to MyISAM tables. Using phpMyAdmin, I can change the table type using a drop-down box, however I do not know the repercussions of the table type conversion. Any advice, Steve Osborne Database Programmer Chinook Multimedia Inc. [EMAIL PROTECTED] - 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
ISAM v.s. MYISAM
Hi there I am working on a site where they are using ISAM files, (.ISM .IS1), I want to know if there is a way of accessing these from MYSQL? From what I have been able to tell they appear totally differently in comparison to some of my MYISAM table when I open them in Notepad. Can anyone shed some light? ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 - 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: ISAM v.s. MYISAM
I'm working a project with the same exact scenario right now. My ver. of mySQL is 3.23.xx and my client's is 3.22.xx. there table sturcturing is ISAM and mine is myISAM. The only I've been able to transfer succesfully is by dumping all the data and re-importing on the clients end. -Original Message- From: Warren van der Merwe [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 15, 2001 7:27 AM To: [EMAIL PROTECTED] Subject: ISAM v.s. MYISAM Hi there I am working on a site where they are using ISAM files, (.ISM .IS1), I want to know if there is a way of accessing these from MYSQL? From what I have been able to tell they appear totally differently in comparison to some of my MYISAM table when I open them in Notepad. Can anyone shed some light? ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 - 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: ISAM and MYISAM
Hi, I've read some people talking about ISAM and MYISAM table. I dont understand what are those tables and can anyone explain to me and the difference of each table. and also which one has better performance? Read The Fine Manual, chapter 8: MySQL table types / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: ISAM and MYISAM
I'm new too, but a good starting point would be: http://www.mysql.com/doc/T/a/Table_types.html regards, thalis ---+ You're definitely on their list. The correct question to ask is what list it is. ---+ On Sat, 10 Feb 2001, Teddy A Jasin wrote: Hi, I've read some people talking about ISAM and MYISAM table. I dont understand what are those tables and can anyone explain to me and the difference of each table. and also which one has better performance? - 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
ISAM and MYISAM
Hi, I've read some people talking about ISAM and MYISAM table. I dont understand what are those tables and can anyone explain to me and the difference of each table. and also which one has better performance? thanks Teddy - 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
ISAM to MYISAM
I've noticed that most of the really cool new features in MySQL use myisam tables. My tables are from an earlier version, and are only ISAM. 2 Questions: 1) How do you convert the tables? 2) Will my data be changed or corrupted by doing #1? Aaron - 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