My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
for
  Advanced search   Search tips   Subscriptions
Issue 386: missing mysql_nextval_id() from gerrit 2.1.1 and 2.1.1.1
1 person starred this issue and may be notified of changes. Back to list
Status:  WontFix
Owner:  ----
Closed:  Jan 2010


Sign in to add a comment
 
Reported by arusse...@gmail.com, Jan 14, 2010
Affected Version:
Environment:
Gentoo Linux x86
sun-jre-bin-1.6.0.17.ebuild

What steps will reproduce the problem?
1. java -jar gerrit.war —cat sql/mysql_nextval.sql

What is the expected output? What do you see instead?
1. We would get the sql query

Please provide any additional information below.
Instead, the output is:

error: no such file sql/mysql_nextval.sql
Jan 14, 2010
#1 sop@google.com
The SQL scripts are no longer available for --cat.  During installation of a
new site and database, Gerrit should have automatically created the functions
under MySQL.
Status: WontFix
Jan 14, 2010
#2 arusse...@gmail.com
Hi,

I'm not sure why this bug was closed.

Two things:

1. The documentation for gerrit 2.1 still says to do that
2. The function is not there, and does not work.

I had to manually increment the ID for each project I added.

Please note, this is not an upgrade, this is a fresh installation. On another server,
which was an upgrade from 2.0.24.2, the SQL query did work.

Thanks,
Anthony
Jan 14, 2010
#3 arusse...@gmail.com
For further reference, I am manually adding existing projects using this, taken from
the install guide:

INSERT INTO projects
(project_id
 ,use_contributor_agreements
 ,submit_type
 ,name)
VALUES
(nextval('project_id')
,'N'
,'M'
,'new/project');

Replacing nextval('project_id') with nextval_project_id() as stated in the guide.
This does work on another server which was an upgrade from 2.0.24.2, but not on this
server, which was a straight install using the latest 2.1.1.1.

Thanks,
Anthony
Jan 14, 2010
#4 sop@google.com
I think something is wrong with your MySQL server.

I just did a fresh installation of Gerrit 2.1.1.1 against a MySQL database,
and the function exists:

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 616
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database reviewdb
    -> ;
Query OK, 30 rows affected (0.09 sec)

mysql> CREATE DATABASE reviewdb;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER DATABASE reviewdb charset=latin1;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL ON reviewdb.* TO 'gerrit2'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> use reviewdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select nextval_project_id();
+----------------------+
| nextval_project_id() |
+----------------------+
|                    1 | 
+----------------------+
1 row in set (0.02 sec)


Jan 14, 2010
#5 arusse...@gmail.com
Hi,

I'll try on another server, but I have already reinstalled this from scratch twice now.

wiarlx001 ~ # mysql -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 124
Server version: 5.0.84-log Gentoo Linux mysql-5.0.84-r1

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use reviewdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select nextval_project_id();
ERROR 1305 (42000): FUNCTION reviewdb.nextval_project_id does not exist
mysql> 

For reference:

# mysql --version
mysql  Ver 14.12 Distrib 5.0.84, for pc-linux-gnu (i686) using readline 6.0

Just in case it is relevant.

Should I open an additional bug report for the documentation update?

Should I open a different bug report if I still see nextval_project_id() missing
after another installation?

Thanks,
Anthony
Jan 14, 2010
#6 sop@google.com
The documentation update is already done.

Since this is Gentoo, I have to ask, did you somehow manage to build MySQL
without its stored function support?

The script file in question is the following, but Gerrit really should have
fed it into MySQL during init, and aborted if MySQL rejected it:

delimiter //

CREATE FUNCTION nextval_project_id ()
  RETURNS BIGINT
  LANGUAGE SQL
  NOT DETERMINISTIC
  MODIFIES SQL DATA
BEGIN
  INSERT INTO project_id (s) VALUES (NULL);
  DELETE FROM project_id WHERE s = LAST_INSERT_ID();
  RETURN LAST_INSERT_ID();
END;
//

CREATE FUNCTION nextval_account_id ()
  RETURNS BIGINT
  LANGUAGE SQL
  NOT DETERMINISTIC
  MODIFIES SQL DATA
BEGIN
  INSERT INTO account_id (s) VALUES (NULL);
  DELETE FROM account_id WHERE s = LAST_INSERT_ID();
  RETURN LAST_INSERT_ID();
END;
//

Jan 14, 2010
#7 arusse...@gmail.com
Here's the output of a new installation.

~ $ java -jar gerrit.war init -d test

*** Gerrit Code Review 2.1.1.1
*** 


*** Git Repositories
*** 

Location of Git repositories   [git]: 
Import existing repositories   [Y/n]? 

*** SQL Database
*** 

Database server type           [MYSQL/?]: 
Server hostname                [localhost]: 
Server port                    [(MYSQL default)]: 
Database name                  [reviewdb]: 
Database username              [gerrit2]: 
Change gerrit2's password      [y/N]? 

*** User Authentication
*** 

Authentication method          [OPENID/?]: 

*** Email Delivery
*** 

SMTP server hostname           [smtp.gmail.com]: 
SMTP server port               [587]: 
SMTP encryption                [TLS/?]: 
SMTP username                  [arussello@gmail.com]: 
arussello@gmail.com's password : 
              confirm password : 

*** Container Process
*** 

Run as                         [gerrit2]: 
Java runtime                   [/opt/sun-jre-bin-1.6.0.17/]: 
Upgrade /home/gerrit2/test/bin/gerrit.war [Y/n]? 
Copying gerrit.war to /home/gerrit2/test/bin/gerrit.war

*** SSH Daemon
*** 

Listen on address              [192.168.70.72]: 
Listen on port                 [29418]: 

*** HTTP Daemon
*** 

Behind reverse proxy           [y/N]? 
Use SSL (https://             [y/N]? 
Listen on address              [192.168.70.72]: 
Listen on port                 [8080]: 
Canonical URL                  [http://192.168.70.72:8080/ 

Scanning /home/gerrit2/test/git
Initialized /home/gerrit2/test
gerrit2@testlx001 ~ $ exit
logout

testlx001 ~ # mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 129
Server version: 5.0.84-log Gentoo Linux mysql-5.0.84-r1

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use reviewdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select nextval_project_id();
ERROR 1305 (42000): FUNCTION reviewdb.nextval_project_id does not exist
mysql> 

Thanks,
Anthony
Jan 14, 2010
#8 arusse...@gmail.com
Hi,

MySQL does have it's stored function support built in.

How would I manually add this function? When I try, I get this message:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL
DATA in its declaration and binary logging is enabled (you *might* want to use the
less safe log_bin_trust_function_creators variable)

Thanks,
Anthony
Jan 14, 2010
#9 arusse...@gmail.com
Hi,

Nevermind, I did this:

wiarlx001 ~ # mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 136
Server version: 5.0.84-log Gentoo Linux mysql-5.0.84-r1

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

mysql> use reviewdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delimiter //
mysql> 
mysql> CREATE FUNCTION nextval_project_id ()
    ->   RETURNS BIGINT
    ->   LANGUAGE SQL
    ->   NOT DETERMINISTIC
    ->   MODIFIES SQL DATA
    -> BEGIN
    ->   INSERT INTO project_id (s) VALUES (NULL);
    ->   DELETE FROM project_id WHERE s = LAST_INSERT_ID();
    ->   RETURN LAST_INSERT_ID();
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE FUNCTION nextval_account_id ()
    ->   RETURNS BIGINT
    ->   LANGUAGE SQL
    ->   NOT DETERMINISTIC
    ->   MODIFIES SQL DATA
    -> BEGIN
    ->   INSERT INTO account_id (s) VALUES (NULL);
    ->   DELETE FROM account_id WHERE s = LAST_INSERT_ID();
    ->   RETURN LAST_INSERT_ID();
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> quit

I did have to manually run nextval_project_id() a few times to get it to increment
and cover the projects I manually added.

Thanks,
Anthony
Jan 14, 2010
#10 sop@google.com
Sad.  It seems MySQL doesn't give an error message when creating the
function over JDBC.  Otherwise init would have aborted with an error
and a stack trace telling us about ERROR 1418 (HY000). 
Sign in to add a comment

Powered by Google Project Hosting