Bug 1409726

Summary: Database creation fails with SQL error
Product: [Community] Bugzilla Reporter: Yuxiang Zhu <yuxzhu>
Component: DatabaseAssignee: Jeff Fearn 🐞 <jfearn>
Status: CLOSED NEXTRELEASE QA Contact: tools-bugs <tools-bugs>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 5.0CC: huiwang, qgong, yijli
Target Milestone: ---   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: 5.0.3.rh22 Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2017-01-20 03:19:57 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Yuxiang Zhu 2017-01-03 07:13:17 UTC 
Description of problem:
Running /usr/share/rh-bugzilla/bin/rh-database-update.pl against an empty DB will get a SQL syntax error.

Version-Release number of selected component (if applicable):
5.0.3-rh20


Additional info:

Now filling the 'status_workflow' table with valid bug status transitions for Red Hat...
Adding new column 'last_used' to the 'namedquery_group_map' table...
DBD::Pg::db do failed: ERROR:  column "now" does not exist
<pre>
 at /var/www/html/bugzilla//Bugzilla/DB.pm line 667.
        Bugzilla::DB::bz_add_column('Bugzilla::DB::Pg=HASH(0x7d9d5b0)', 'namedquery_group_map', 'last_used', 'HASH(0x8054038)') called at /var/www/html/bugzilla/extensions/SchemaChange/Extension.pm line 224
        Bugzilla::Extension::SchemaChange::install_update_db('Bugzilla::Extension::SchemaChange=HASH(0x7c56b68)', undef) called at /var/www/html/bugzilla//Bugzilla/Hook.pm line 20
        Bugzilla::Hook::process('install_update_db') called at /var/www/html/bugzilla//Bugzilla/Install/DB.pm line 739
        Bugzilla::Install::DB::update_table_definitions('HASH(0x40f7ed8)') called at /usr/share/rh-bugzilla/bin/rh-database-update.pl line 59

</pre> at /var/www/html/bugzilla//Bugzilla/DB.pm line 667.

Since PostgreSQL doesn't allow omit the parentheses when using with DEFAULT, we have to add the parentheses explicitly:

From 9e71d7959b2e08f2fd69927b5cf72fc75d39a559 Mon Sep 17 00:00:00 2001
From: Yuxiang Zhu <vfreex+git>
Date: Tue, 3 Jan 2017 13:57:21 +0800
Subject: [PATCH] Fix a syntax error when performing DB schema migration.

Parentheses cannot be omitted if using with DEFAULT
(https://www.postgresql.org/docs/9.1/static/functions-datetime.html).
---
 extensions/SchemaChange/Extension.pm | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/extensions/SchemaChange/Extension.pm b/extensions/SchemaChange/Extension.pm
index 4d3a667..32fe1a3 100644
--- a/extensions/SchemaChange/Extension.pm
+++ b/extensions/SchemaChange/Extension.pm
@@ -222,7 +222,7 @@ sub install_update_db {
     # This will monitor when a saved search was lased used, so it can be
     # removed after an expiry period.
     $dbh->bz_add_column('namedquery_group_map', 'last_used',
-        { TYPE => 'DATETIME', 'DEFAULT' => 'now', NOTNULL => 1});
+        { TYPE => 'DATETIME', 'DEFAULT' => 'now()', NOTNULL => 1});
     # REDHAT EXTENSION END 523845
 
     # REDHAT EXTENSION START 876015
-- 
2.9.3
Comment 1 Rony Gong 🔥 2017-01-19 02:20:18 UTC 
Tested on QA environment(5.0.3-rh22)
Result: Pass