From 52e4a8635eb48662da6bc48fa83dfe4758598fa2 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 (cherry picked from commit 9f306898886efe1c2a29f2b622885ec1dfa4fe9a) Signed-off-by: Fridolin Somers --- 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 749c6a761e..46b8349e75 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