From 675f296c501184bd13644e1e6a430f8f3bd7b1f6 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 29 Aug 2018 17:05:50 -0300 Subject: [PATCH] Bug 21281: Surround creator_templates.rows with backquotes 'rows' is a reserved word since MariaDB 10.2.4 and MySQL https://mariadb.com/kb/en/library/mariadb-1024-release-notes/ https://dev.mysql.com/doc/refman/8.0/en/keywords.html Test plan: Do not apply this patch and make sure you recreate the reported issue Apply this patch and confirm that it is now fixed. QA will take care for the changes in installer and test files --- C4/Creators/Lib.pm | 4 ++-- installer/data/mysql/patroncards_upgrade.pl | 2 +- t/db_dependent/Creators/Lib.t | 4 ++-- 3 files changed, 5 insertions(+), 5 deletions(-) diff --git a/C4/Creators/Lib.pm b/C4/Creators/Lib.pm index 3c53498d1a..46ae21a328 100644 --- a/C4/Creators/Lib.pm +++ b/C4/Creators/Lib.pm @@ -67,7 +67,7 @@ C4::Creators::Lib sub _SELECT { my @params = @_; - my $query = "SELECT $params[0] FROM $params[1]"; + my $query = "SELECT `$params[0]` FROM $params[1]"; $params[2] ? $query .= " WHERE $params[2];" : $query .= ';'; my $sth = C4::Context->dbh->prepare($query); # $sth->{'TraceLevel'} = 3; @@ -145,7 +145,7 @@ my $output_formats = [ sub _build_query { my ( $params, $table ) = @_; my @fields = exists $params->{fields} ? @{ $params->{fields} } : (); - my $query = "SELECT " . ( @fields ? join(', ', @fields ) : '*' ) . " FROM $table"; + my $query = "SELECT " . ( @fields ? join(', ', map {"`$_`"} @fields ) : '*' ) . " FROM $table"; my @where_args; if ( exists $params->{filters} ) { $query .= ' WHERE 1 '; diff --git a/installer/data/mysql/patroncards_upgrade.pl b/installer/data/mysql/patroncards_upgrade.pl index 5b128cde0a..b47049bec6 100755 --- a/installer/data/mysql/patroncards_upgrade.pl +++ b/installer/data/mysql/patroncards_upgrade.pl @@ -136,7 +136,7 @@ $sth->do("INSERT INTO `creator_batches_tmp` (label_id, batch_id, item_number, ti $sth->do("INSERT INTO `creator_layouts_tmp` (layout_id, barcode_type, printing_type, layout_name, guidebox, callnum_split, text_justify, format_string) SELECT layout_id, barcode_type, printing_type, layout_name, guidebox, callnum_split, text_justify, format_string FROM labels_layouts;") or die "DB ERROR: " . $sth->errstr . "\n"; -$sth->do("INSERT INTO `creator_templates_tmp` (template_id, template_code, template_desc, page_width, page_height, label_width, label_height, top_margin, left_margin, cols, rows, col_gap, row_gap, units) SELECT template_id, template_code, template_desc, page_width, page_height, label_width, label_height, top_margin, left_margin, cols, rows, col_gap, row_gap, units FROM labels_templates;") or die "DB ERROR: " . $sth->errstr . "\n"; +$sth->do("INSERT INTO `creator_templates_tmp` (template_id, template_code, template_desc, page_width, page_height, label_width, label_height, top_margin, left_margin, cols, `rows`, col_gap, row_gap, units) SELECT template_id, template_code, template_desc, page_width, page_height, label_width, label_height, top_margin, left_margin, cols, `rows`, col_gap, row_gap, units FROM labels_templates;") or die "DB ERROR: " . $sth->errstr . "\n"; # Drop old tables.... diff --git a/t/db_dependent/Creators/Lib.t b/t/db_dependent/Creators/Lib.t index c820fb73ef..7a981dee48 100644 --- a/t/db_dependent/Creators/Lib.t +++ b/t/db_dependent/Creators/Lib.t @@ -87,7 +87,7 @@ my $query = ' (profile_id , template_code, template_desc, page_width, page_height , label_width , label_height , top_text_margin, left_text_margin, top_margin , left_margin , cols, - rows , col_gap , row_gap , units, + `rows` , col_gap , row_gap , units, creator) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; my $insert_sth = $dbh->prepare($query); @@ -369,7 +369,7 @@ $templates = get_all_templates( { filters => { rows => 7} } ); $query = ' SELECT count(*) FROM creator_templates - WHERE rows = 7 + WHERE `rows` = 7 '; $count = $dbh->selectrow_array($query); is( $count, 1, 'There is 1 template matching' ); -- 2.11.0