From dc39152e4b1b881f0115aa5cbe42fbf48a71e5c5 Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 28 Oct 2007 12:51:30 +0000 Subject: [PATCH] finish mysql locking workaround --- FS/FS/Schema.pm | 167 +++++++++++++++++++++++++++++++----------------- FS/FS/Setup.pm | 16 ++++- FS/FS/svc_acct.pm | 18 ++++-- FS/bin/freeside-upgrade | 22 ++++++- 4 files changed, 155 insertions(+), 68 deletions(-) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index d1be9f1f0..bd098def7 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -65,13 +65,22 @@ assuming it is up-to-date). See L. sub dbdef { $dbdef; } -=item dbdef_dist [ OPTION => VALUE ... ] +=item dbdef_dist [ DATASRC ] Returns the current canoical database definition as defined in this file. +Optionally, pass a DBI data source to enable syntax specific to that database. +Currently, this enables "TYPE=InnoDB" for MySQL databases. + =cut sub dbdef_dist { + my $datasrc = @_ ? shift : ''; + + my $local_options = ''; + if ( $datasrc =~ /^dbi:mysql/i ) { + $local_options = 'TYPE=InnoDB'; + } ### # create a dbdef object from the old data structure @@ -79,7 +88,6 @@ sub dbdef_dist { my $tables_hashref = tables_hashref(); - #turn it into objects my $dbdef = new DBIx::DBSchema map { @@ -125,10 +133,11 @@ sub dbdef_dist { @$index; DBIx::DBSchema::Table->new({ - 'name' => $tablename, - 'primary_key' => $tables_hashref->{$tablename}{'primary_key'}, - 'columns' => \@columns, - 'indices' => \@indices, + 'name' => $tablename, + 'primary_key' => $tables_hashref->{$tablename}{'primary_key'}, + 'columns' => \@columns, + 'indices' => \@indices, + 'local_options' => $local_options, }); } keys %$tables_hashref; @@ -184,66 +193,104 @@ sub dbdef_dist { keys %indices; my $h_tableobj = DBIx::DBSchema::Table->new( { - 'name' => "h_$table", - 'primary_key' => 'historynum', - 'indices' => \%h_indices, - 'columns' => [ - DBIx::DBSchema::Column->new( { - 'name' => 'historynum', - 'type' => 'serial', - 'null' => 'NOT NULL', - 'length' => '', - 'default' => '', - 'local' => '', - } ), - DBIx::DBSchema::Column->new( { - 'name' => 'history_date', - 'type' => 'int', - 'null' => 'NULL', - 'length' => '', - 'default' => '', - 'local' => '', - } ), - DBIx::DBSchema::Column->new( { - 'name' => 'history_user', - 'type' => 'varchar', - 'null' => 'NOT NULL', - 'length' => '80', - 'default' => '', - 'local' => '', - } ), - DBIx::DBSchema::Column->new( { - 'name' => 'history_action', - 'type' => 'varchar', - 'null' => 'NOT NULL', - 'length' => '80', - 'default' => '', - 'local' => '', - } ), - map { - my $column = $tableobj->column($_); + 'name' => "h_$table", + 'primary_key' => 'historynum', + 'indices' => \%h_indices, + 'local_options' => $local_options, + 'columns' => [ + DBIx::DBSchema::Column->new( { + 'name' => 'historynum', + 'type' => 'serial', + 'null' => 'NOT NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_date', + 'type' => 'int', + 'null' => 'NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_user', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_action', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + map { + my $column = $tableobj->column($_); - #clone so as to not disturb the original - $column = DBIx::DBSchema::Column->new( { - map { $_ => $column->$_() } - qw( name type null length default local ) - } ); + #clone so as to not disturb the original + $column = DBIx::DBSchema::Column->new( { + map { $_ => $column->$_() } + qw( name type null length default local ) + } ); - if ( $column->type =~ /^(\w*)SERIAL$/i ) { - $column->type('int'); - $column->null('NULL'); - } - #$column->default('') - # if $column->default =~ /^nextval\(/i; - #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i; - #$column->local($local); - $column; - } $tableobj->columns - ], + if ( $column->type =~ /^(\w*)SERIAL$/i ) { + $column->type('int'); + $column->null('NULL'); + } + #$column->default('') + # if $column->default =~ /^nextval\(/i; + #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i; + #$column->local($local); + $column; + } $tableobj->columns + ], } ); $dbdef->addtable($h_tableobj); } + if ( $datasrc =~ /^dbi:mysql/i ) { + + my $dup_lock_table = DBIx::DBSchema::Table->new( { + 'name' => 'duplicate_lock', + 'primary_key' => 'duplocknum', + 'local_options' => $local_options, + 'columns' => [ + DBIx::DBSchema::Column->new( { + 'name' => 'duplocknum', + 'type' => 'serial', + 'null' => 'NOT NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'lockname', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + ], + 'indices' => { 'duplicate_lock1' => + DBIx::DBSchema::Index->new({ + 'name' => 'duplicate_lock1', + 'unique' => 1, + 'columns' => [ 'lockname' ], + }) + }, + } ); + + $dbdef->addtable($dup_lock_table); + + } + $dbdef; } diff --git a/FS/FS/Setup.pm b/FS/FS/Setup.pm index 55984d4c7..6807ef76e 100644 --- a/FS/FS/Setup.pm +++ b/FS/FS/Setup.pm @@ -5,7 +5,7 @@ use vars qw( @ISA @EXPORT_OK ); use Exporter; #use Tie::DxHash; use Tie::IxHash; -use FS::UID qw( dbh ); +use FS::UID qw( dbh driver_name ); use FS::Record; use FS::svc_domain; @@ -45,6 +45,8 @@ sub create_initial_data { populate_locales(); + populate_duplock(); + #initial_data data populate_initial_data(%opt); @@ -125,6 +127,18 @@ sub _add_locale { die $error if $error; } +sub populate_duplock { + + return unless driver_name =~ /^mysql/i; + + my $sth = dbh->prepare( + "INSERT INTO duplicate_lock ( lockname ) VALUES ( 'svc_acct' )" + ) or die dbh->errstr; + + $sth->execute or die $sth->errstr; + +} + sub populate_initial_data { my %opt = @_; diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm index f5d3a228e..1cddbeeec 100644 --- a/FS/FS/svc_acct.pm +++ b/FS/FS/svc_acct.pm @@ -20,7 +20,7 @@ use Date::Format; use Crypt::PasswdMD5 1.2; use Data::Dumper; use Authen::Passphrase; -use FS::UID qw( datasrc ); +use FS::UID qw( datasrc driver_name ); use FS::Conf; use FS::Record qw( qsearch qsearchs fields dbh dbdef ); use FS::Msgcat qw(gettext); @@ -1198,11 +1198,19 @@ sub _check_duplicate { my $global_unique = $conf->config('global_unique-username') || 'none'; return '' if $global_unique eq 'disabled'; - #this is Pg-specific. what to do for mysql etc? - # ( mysql LOCK TABLES certainly isn't equivalent or useful here :/ ) warn "$me locking svc_acct table for duplicate search" if $DEBUG; - dbh->do("LOCK TABLE svc_acct IN SHARE ROW EXCLUSIVE MODE") - or die dbh->errstr; + if ( driver_name =~ /^Pg/i ) { + dbh->do("LOCK TABLE svc_acct IN SHARE ROW EXCLUSIVE MODE") + or die dbh->errstr; + } elsif ( driver_name =~ /^mysql/i ) { + dbh->do("SELECT * FROM duplicate_lock + WHERE lockname = 'svc_acct' + FOR UPDATE" + ) or die dbh->errstr; + } else { + die "unknown database ". driver_name. + "; don't know how to lock for duplicate search"; + } warn "$me acquired svc_acct table lock for duplicate search" if $DEBUG; my $part_svc = qsearchs('part_svc', { 'svcpart' => $self->svcpart } ); diff --git a/FS/bin/freeside-upgrade b/FS/bin/freeside-upgrade index e5410c2e9..02a615a68 100755 --- a/FS/bin/freeside-upgrade +++ b/FS/bin/freeside-upgrade @@ -56,7 +56,7 @@ if (dbdef->table('cust_main')->column('agent_custid')) { if ( $DRY_RUN ) { print - join(";\n", @bugfix, dbdef->sql_update_schema( dbdef_dist, $dbh ) ). ";\n"; + join(";\n", @bugfix, dbdef->sql_update_schema( dbdef_dist(datasrc), $dbh ) ). ";\n"; exit; } else { foreach my $statement ( @bugfix ) { @@ -64,7 +64,7 @@ if ( $DRY_RUN ) { or die "Error: ". $dbh->errstr. "\n executing: $statement"; } - dbdef->update_schema( dbdef_dist, $dbh ); + dbdef->update_schema( dbdef_dist(datasrc), $dbh ); } my $hashref = {}; @@ -73,6 +73,24 @@ $hashref->{debug} = 1 if $DEBUG; print join "\n", prune_applications($hashref); print "\n" if $DRY_RUN; +if ( $dbh->{Driver}->{Name} =~ /^mysql/i ) { + + my $sth = $dbh->prepare( + "SELECT COUNT(*) FROM duplicate_lock WHERE lockname = 'svc_acct'" + ) or die $dbh->errstr; + + $sth->execute or die $sth->errstr; + + unless ( $sth->fetchrow_arrayref->[0] ) { + + $sth = $dbh->prepare( + "INSERT INTO duplicate_lock ( lockname ) VALUES ( 'svc_acct' )" + ) or die $dbh->errstr; + + $sth->execute or die $sth->errstr; + + } +} $dbh->commit or die $dbh->errstr; -- 2.11.0