Bugzilla – Attachment 12804 Details for
Bug 8915
Referential integrity constraints and InnoDB vs MyISAM storage engine
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Script fixing missing constraints
0001-Bug-8915-A-script-fixing-missing-MySQL-contraints.patch (text/plain), 5.24 KB, created by
Frédéric Demians
on 2012-10-14 15:07:40 UTC
(
hide
)
Description:
Script fixing missing constraints
Filename:
MIME Type:
Creator:
Frédéric Demians
Created:
2012-10-14 15:07:40 UTC
Size:
5.24 KB
patch
obsolete
>From 577fc69695671c34ed00833772c67c9b05ac1197 Mon Sep 17 00:00:00 2001 >From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Demians?= <f.demians@tamil.fr> >Date: Sat, 13 Oct 2012 16:45:56 +0200 >Subject: [PATCH] Bug 8915 A script fixing missing MySQL contraints > >Help: fix_mysql_contraints.pl -h >--- > misc/maintenance/fix_mysql_constraints.pl | 169 ++++++++++++++++++++++++++++++ > 1 file changed, 169 insertions(+) > create mode 100755 misc/maintenance/fix_mysql_constraints.pl > >diff --git a/misc/maintenance/fix_mysql_constraints.pl b/misc/maintenance/fix_mysql_constraints.pl >new file mode 100755 >index 0000000..be29b80 >--- /dev/null >+++ b/misc/maintenance/fix_mysql_constraints.pl >@@ -0,0 +1,169 @@ >+#!/usr/bin/perl >+# >+# Copyright (C) 2012 Tamil s.a.r.l. >+# >+# This file is part of Koha. >+# >+# Koha is free software; you can redistribute it and/or modify it under the >+# terms of the GNU General Public License as published by the Free Software >+# Foundation; either version 2 of the License, or (at your option) any later >+# version. >+# >+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY >+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR >+# A PARTICULAR PURPOSE. See the GNU General Public License for more details. >+# >+# You should have received a copy of the GNU General Public License along >+# with Koha; if not, write to the Free Software Foundation, Inc., >+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. >+ >+use Modern::Perl; >+BEGIN { >+ # find Koha's Perl modules >+ # test carefully before changing this >+ use FindBin; >+ eval { require "$FindBin::Bin/../kohalib.pl" }; >+} >+ >+use Getopt::Long; >+use Pod::Usage; >+use YAML; >+use Try::Tiny; >+use C4::Context; >+ >+ >+my ($doit, $alterengine, $help); >+my $result = GetOptions( >+ 'doit' => \$doit, >+ 'alterengine' => \$alterengine, >+ 'help|h' => \$help, >+); >+ >+ >+sub usage { >+ pod2usage( -verbose => 2 ); >+ exit; >+} >+ >+ >+sub fix_mysql_constraints { >+ my ($doit) = @_; >+ >+ # Get all current DB constraints >+ my $dbh = C4::Context->dbh; >+ $dbh->{RaiseError} = 1; >+ $dbh->{ShowErrorStatement} = 1; >+ my $database = C4::Context->config('database'); >+ my %db_constraint = map { $_->[0] => undef } @{$dbh->selectall_arrayref( >+ "SELECT CONSTRAINT_NAME >+ FROM information_schema.table_constraints >+ WHERE constraint_schema = '$database' >+ AND CONSTRAINT_TYPE != 'PRIMARY KEY' ")}; >+ >+ my $base_dir = C4::Context->config('intranetdir'); >+ open my $fh, "<", "$base_dir/installer/data/mysql/kohastructure.sql"; >+ unless ($fh) { >+ say "Unable to open kohastructure.sql file"; >+ exit; >+ } >+ >+ my $table_name; >+ my $engine_altered; >+ # FIXME: This hide problem. But if you run this script, it means that you >+ # have already identified issues with your Koha DB integrity, and will fix >+ # any necessary tables requiring records deleting. >+ $dbh->do("SET FOREIGN_KEY_CHECKS=0"); >+ my $line = <$fh>; >+ while ( $line ) { >+ if ( $line =~ /CREATE TABLE (.*?) / ) { >+ $table_name = $1; >+ $table_name =~ s/\`//g; >+ $engine_altered = 0; >+ $line = <$fh>; >+ next; >+ } >+ unless ( $line =~ /CONSTRAINT /i ) { >+ $line = <$fh>; >+ next; >+ } >+ my $constraint = $line; >+ CONTRAINT_LOOP: >+ while ( $constraint !~ /,/ ) { >+ $line = <$fh>; >+ last CONTRAINT_LOOP if $line =~ /ENGINE/i; >+ $line =~ s/^ */ /; >+ $constraint .= $line; >+ } >+ $constraint =~ s/^ *//; >+ $constraint =~ s/\n//g; >+ $constraint =~ s/ *$//; >+ $constraint =~ s/,$//; >+ my ($name) = $constraint =~ /CONSTRAINT (.*?) /; >+ $name =~ s/\`//g; >+ unless ( exists($db_constraint{$name}) ) { >+ if ( $alterengine && !$engine_altered ) { >+ my $sql = "ALTER TABLE $table_name ENGINE = 'InnoDB'"; >+ say $sql; >+ if ( $doit ) { >+ try { >+ $dbh->do($sql) if $doit; >+ $engine_altered = 1; >+ } catch { >+ say "Error: $_;"; >+ }; >+ } >+ } >+ my $sql = "ALTER TABLE $table_name ADD $constraint"; >+ say $sql; >+ if ( $doit ) { >+ try { >+ $dbh->do($sql) if $doit; >+ } catch { >+ say "Error: $_"; >+ } >+ } >+ } >+ $line = <$fh> if $line =~ /CONSTRAINT/i; >+ } >+} >+ >+ >+usage() if $help; >+ >+fix_mysql_constraints($doit); >+ >+=head1 NAME >+ >+fix_mysql_constraints.pl >+ >+=head1 SYNOPSIS >+ >+ fix_mysql_constraints.pl --help >+ fix_mysql_constraints.pl >+ fix_mysql_constraints.pl --doit >+ >+=head1 DESCRIPTION >+ >+See bug #8915 >+ >+Alter tables to add missing constraints. Prior to altering tables, it may be >+necessary to alter tables storage engine from MyISAM to InnoDB. >+ >+=over 8 >+ >+=item B<--help> >+ >+Prints this help >+ >+=item B<--doit> >+ >+Alter tables effectively, otherwise just display the ALTER TABLE directives. >+ >+=item B<--alterengine >+ >+Prior to add missing constraints, alter table engine to InnoDB. >+ >+=back >+ >+=cut >+ >-- >1.7.12.3 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 8915
:
12799
|
12804
|
13166
|
13342
|
13355