convert phrase

Shell Programming and Scripting
1

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?

2

Try the following awk program :

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

Input file:

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:

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.

3

could you give a little explanations? thanks.

4 
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.

5

very clear. thanks a lot.