From 9cf21418fadc9dfb513a6fdd78713bbaba0abc68 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 Content-Type: text/plain; charset=utf-8 '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 --- 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 3c53498..46ae21a 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 5b128cd..b47049b 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 c820fb7..7a981de 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.1.4