convert phrase

fredao · #1 (**permalink**) 04-24-2008

I am converting a mysql database from myIsam to innodb. After dumping all databases to a file, I am trying to make modification:

sed s/ENGINE=MyISAM/ENGINE=InnoDB/ dump_1 > dump_1_inno

however, when I import the modified dump file to mysql server, I got error:

ERROR 1214 (HY000) at line 4093: The used table type doesn't support FULLTEXT indexes

Therefore, I need to keep those tables which has FULLTEXT indexes to remain MyIsam, and only change the rest to innodb.

Following is a part of the dump_1:

================

DROP TABLE IF EXISTS `mod`;
CREATE TABLE `mod` (
`id` int(10) NOT NULL auto_increment,
`prgr` tinyint(1) NOT NULL default '0' COMMENT 'ger document',
`coo` tinyint(1) NOT NULL default '0' COMMENT 'chment',
`hinu` tinyint(1) NOT NULL default '0' COMMENT 'Himenu',
FULLTEXT KEY `prgr` (`pagetitle`,`description`,`content`)
PRIMARY KEY (`id`),
KEY `id` (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COMMENT='Contains the site document tree.';

.
.
.

DROP TABLE IF EXISTS `prd`;
CREATE TABLE `prd` (

`language` varchar(3) collate utf8_unicode_ci NOT NULL default '',
`description` text collate utf8_unicode_ci NOT NULL,
`title` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`prD`,`skuID`,`language`,`siteCode`),
KEY `skuDescID` (`skuDescID`),
FULLTEXT KEY `description` (`description`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=239780 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

==================

So, I need to do following:

1. sed s/ENGINE=MyISAM/ENGINE=InnoDB/ dump_1 > dump_1_inno
2. search the file called dump_1_inno for the phrase "FULLTEXT KEY";
3. once found the phrase, change the subsequent "ENGINE=InnoDB" back to "ENGINE=MyISAM"; (it is unsure how many lines between the phrase "FULLTEXT KEY" and the next "ENGINE=InnoDB", and there maybe another "FULLTEXT KEY" between them)
4. repeat this until reach the end of file, and save to a file called "final";

Can someone help me out that how I can do this?

aigles · #2 (**permalink**) 04-24-2008

Try the following awk program :

Code:

awk '
/CREATE TABLE/ { change = 1 }
/FULLTEXT KEY/ { change = 0 }
change && /ENGINE=MyISAM/ { sub(/ENGINE=MyISAM/, "ENGINE=InnoDB") }
1
' dump_1 > final

Input file:

Code:

DROP TABLE IF EXISTS `mod`;
CREATE TABLE `mod` (
`id` int(10) NOT NULL auto_increment,
`prgr` tinyint(1) NOT NULL default '0' COMMENT 'ger document',
`coo` tinyint(1) NOT NULL default '0' COMMENT 'chment',
`hinu` tinyint(1) NOT NULL default '0' COMMENT 'Himenu',
FULLTEXT KEY `prgr` (`pagetitle`,`description`,`content`)
PRIMARY KEY (`id`),
KEY `id` (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COMMENT='Contains the site document tree.';


DROP TABLE IF EXISTS `prd`;
CREATE TABLE `prd` (

`language` varchar(3) collate utf8_unicode_ci NOT NULL default '',
`description` text collate utf8_unicode_ci NOT NULL,
`title` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`prD`,`skuID`,`language`,`siteCode`),
KEY `skuDescID` (`skuDescID`),
KEY `description` (`description`),
KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=239780 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Output:

Code:

DROP TABLE IF EXISTS `mod`;
CREATE TABLE `mod` (
`id` int(10) NOT NULL auto_increment,
`prgr` tinyint(1) NOT NULL default '0' COMMENT 'ger document',
`coo` tinyint(1) NOT NULL default '0' COMMENT 'chment',
`hinu` tinyint(1) NOT NULL default '0' COMMENT 'Himenu',
FULLTEXT KEY `prgr` (`pagetitle`,`description`,`content`)
PRIMARY KEY (`id`),
KEY `id` (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COMMENT='Contains the site document tree.';


DROP TABLE IF EXISTS `prd`;
CREATE TABLE `prd` (

`language` varchar(3) collate utf8_unicode_ci NOT NULL default '',
`description` text collate utf8_unicode_ci NOT NULL,
`title` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`prD`,`skuID`,`language`,`siteCode`),
KEY `skuDescID` (`skuDescID`),
KEY `description` (`description`),
KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=239780 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Jean-Pierre.

fredao · #3 (**permalink**) 04-24-2008

could you give a little explanations? thanks.

aigles · #4 (**permalink**) 04-24-2008

Code:

awk '

/CREATE TABLE/ {  # Found create statement 
   change = 1     #   Set engine change flag to TRUE
}                 #

/FULLTEXT KEY/ {  # Found Fulltext key 
   change = 0     #   Engine must be keep, so set
}                 #     engine change flag to FALSE

change && /ENGINE=MyISAM/ {              # Line contain ENGINE that must be changed
                                         # (engine change flag is TRUE)
   sub(/ENGINE=MyISAM/, "ENGINE=InnoDB") #    Change engine value in record
}                                        #

1                 # Select all lines for printing

' dump_1 > final

Jean-Pierre.

fredao · #5 (**permalink**) 04-24-2008

very clear. thanks a lot.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
match a phrase	vanitham	Shell Programming and Scripting	4	01-16-2008 02:18 AM
Dont Know How to Phrase the Title	barney34	Shell Programming and Scripting	1	12-12-2006 04:36 PM
Remove duplicate phrase	kesu2k	Shell Programming and Scripting	6	10-17-2006 04:34 PM
here-doc convert 2 script convert to single script?	yongho	Shell Programming and Scripting	2	07-07-2005 04:14 PM
Searching for a specific phrase on Unix server	mmcaleer	UNIX for Dummies Questions & Answers	1	02-07-2005 04:18 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by