From 9f306898886efe1c2a29f2b622885ec1dfa4fe9a 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 Signed-off-by: Chris Cormack Signed-off-by: Marcel de Rooy Signed-off-by: Nick Clemens (cherry picked from commit 7de47202f2876892524c8b68de962c0776f32881) Signed-off-by: Martin Renvoize --- 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.39.5