Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

convert phrase

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting convert phrase
# 1  
Old 04-24-2008
convert phrase
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?
Last edited by fredao; 04-24-2008 at 01:48 PM..
# 2  
Old 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.
Last edited by aigles; 04-24-2008 at 02:27 PM.. Reason: add output redirection to file final
# 3  
Old 04-24-2008
could you give a little explanations? thanks.
# 4  
Old 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.
# 5  
Old 04-24-2008
very clear. thanks a lot.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Phrase XML with Huge Data

HI Guys, I have Big XML file with Below Format :- Input :- <pokl>MKL=1,FN=1,GBNo=B10C</pokl> <d>192</d> <d>315</d> <d>35</d> <d>0,7,8</d> <pokl>MKL=1,dFN=1,GBNo=B11C</pokl> <d>162</d> <d>315</d> <d>35</d> <d>0,5,6</d> <pokl>MKL=1,dFN=1,GBNo=B12C</pokl> <d>188</d> (4 Replies)
Discussion started by: pareshkp
4 Replies

2. Shell Programming and Scripting

How to find a phrase and pull all lines that follow until the phrase occurs again?

I want to burst a report by using the page number value in the report header. Each section starts with *PAGE NO:* 1 Each section might have several pages, but the next section always starts back at 1. So I want to find the "*PAGE NO:* 1" value and pull all lines that follow until "*PAGE NO:* 1"... (4 Replies)
Discussion started by: Scottie1954
4 Replies

3. Shell Programming and Scripting

regarding about the (/) in the phrase

Hello, I am trying to print lines from a text file using this command gawk '/Filename:/' 11.rtf >> 22.rtf and it work ok. but if the phrase has included forward (/) something like that gawk '/File/name:/' 11.rtf >> 22.rtf it give error . so is there any manipulation when it... (1 Reply)
Discussion started by: davidkhan
1 Replies

4. Shell Programming and Scripting

Getting 15 characters after search phrase

I have data that looks like this: 2002 140 40800.0060 GPS 20 C1 25477810.2305 2002 140 41100.0060 GPS 20 C1 25298056.0453 I need to get data after certain pattern.. for example if i search for C1 it should return 25477810.2305 25298056.0453 To achieve this, it should: ... (3 Replies)
Discussion started by: bfr
3 Replies

5. Shell Programming and Scripting

find PHRASE and PATH

I've got a script which finds *.txt files in directories and subdirectories after providing the path by the user and then searches in the files for phrase given by the user How to write script in such way that the paths to the found *.txt files and the phrase given by the user were both... (2 Replies)
Discussion started by: patrykxes
2 Replies

6. Shell Programming and Scripting

match a phrase

Hi, I have a these sentences. $sent1="Transactivation of wound-responsive genes containing the core sequence of the auxin-responsive element by a wound-induced protein kinase-activated transcription factor in tobacco plants."; $sent2="I branching formation in erythroid differentiation is... (4 Replies)
Discussion started by: vanitham
4 Replies

7. Shell Programming and Scripting

Dont Know How to Phrase the Title

Ok i have an expect script that logs into an appliance kinda like a Cisco router, runs a command, and I need to get some calculation out of the output of that command. The reason I have to use an expect script is the data I am trying to harvest does not have an SNMP variable assigned to it. I... (1 Reply)
Discussion started by: barney34
1 Replies

8. Shell Programming and Scripting

Remove duplicate phrase

I have a value VAL=XXXX_20061001_00 XXXX_20061001_03 XXXX_20061001_00 XXXX_20061002_00 XXXX_20061002_03 XXXX_20061003_00 The VAL has duplicates like XXXX_20061001_00 repeated twice. How to make the VAL with unique values. Thanks in advance. (6 Replies)
Discussion started by: kesu2k
6 Replies
Login or Register to Ask a Question