MySQL Error on Install

youthpoint

I have been installing ZenPhoto for the first time - looks great

However after the db gets created and it sends me to the reset admin password page I get a MySQL error that stops everything:

Zenphoto Error
MySQL Query ( SELECT folder, sort_order, `show`, `dynamic`, `search_params` FROM `zp_albums` ORDER BY id ) Failed. Error: Unknown column 'dynamic' in 'field list'

I am not sure what the db structure is supposed to be and if the install stopped short of a full db dump or not

I have the following tables:
zp_administrators
zp_admintoalbum
zp_albums
zp_comments
zp_images
zp_options

and the zp_albums contains:

id
parentid
folder
title
desc
date
place
show
closecomments
commentson
thumb
mtime
sort_type
subalbum_sort_type
sort_order
image_sortdirection
album_sortdirection
hitcounter int(11)
password
password_hint
tags

Any help would be appreciated... thanks

PS -- I have tried:
-a full reinstall
-dumped the db several times and had setup redo it
-redid the config file
-a complete search of this forum for answers ;-)

sbillard

There are many fields missing from your albums table. I would suggest you download the release again, clean out the zenphoto files and upload the new ones, drop the tables from the database, and run setup again.

youthpoint

OK - I have done all that and seem to be getting the same error!

I also get an error on the page that creates the db

`Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/asmith/public_html/zenphoto/zp-core/setup.php on line 51`

here is lines 46-54 of setup.php
`if (file_exists("zp-config.php")) {

require("zp-config.php");

if($connection = @mysql_connect($_zp_conf_vars['mysql_host'], $_zp_conf_vars['mysql_user'], $_zp_conf_vars['mysql_pass'])){

if (@mysql_select_db($_zp_conf_vars['mysql_database'])) {

$result = @mysql_query("SELECT `id` FROM " . $_zp_conf_vars['mysql_prefix'].'options' . " LIMIT 1", $connection);

if (mysql_num_rows($result) > 0) $upgrade = true;

require_once("admin-functions.php");

}

}`

Is there a SQL dump file I can use rather than the setup?

sbillard

What is happening is that the update part of setup is not happening. This may be because of some user rights on the database not allowing table updates.

If you wish to not have the above error, you can download the nightly build. That version of setup.php has the following code:
`if (file_exists("zp-config.php")) {

require("zp-config.php");

if($connection = @mysql_connect($_zp_conf_vars['mysql_host'], $_zp_conf_vars['mysql_user'], $_zp_conf_vars['mysql_pass'])){

if (@mysql_select_db($_zp_conf_vars['mysql_database'])) {

$result = @mysql_query("SELECT `id` FROM " . $_zp_conf_vars['mysql_prefix'].'options' . " LIMIT 1", $connection);

if ($result) {

if (mysql_num_rows($result) > 0) $upgrade = true;

}

require_once("admin-functions.php");

}

}

}`

But I do not think this has any effect on your problem. This code is just testing to see if it is doing an upgrade or a new install. With the error it thinks it is doing a new install.

oliv76

Hello,
Well I have the same error while running the install with a user which have select, delete, insert update privileges on the database.
I previously ran the install with root mysql account and I had no problem (except Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/asmith/public_html/zenphoto/zp-core/setup.php on line 51 ) which was because I write the mysql data in the zp-config.php.example before doing the install.

I have another question off topic : is there any translation in french of zenphoto?

Oliv

sbillard

Work is in progress to make zenphoto translatable. It is not there yet. I believe there have been volunteers for French. If not and you are interested, let us know.

oliv76

ok thanks,

For the pevious problem, I grant all admin rights to the zenphoto user for the zenphoto db and it's work. Inset, select, update, delete rights are not sufficient, even if previously had create zenphoto db
@+Oliv

sbillard

The zenphoto admin user rights are different from the MySQL database rights. Which are we talking about here?

oliv76

Before install this code to create zenphoto user who will run the install process gives the previous error:
grant insert,delete,select on zenphoto.* to zenphoto_user@localhost
set password for zenphoto_user@localhost ...

instead this code work :
grant all on zenphoto.* to zenphoto_user@localhost identified by ..
flush privilieges;

@+Oliv

oliv76

sorry I forget the "update" in the first insert of the previous post, but no mention of the create (maybe that the cause)

@+Oliv

sbillard

Zenphoto does need create rights.

Unknown

I hit the exact same error described in the post that started this thread. What misled me is that before first running setup I added the mysql user and password specified in the zp-config.php file to mysql and created an empty mysql database using the name from zp-config.php, but forgot to grant mysql privileges to that database to that user. Setup then gave me the error:

"MySQL access rights [unknown]

"Your MySQL user must have Create, Drop, Select, Insert, Update, and Delete rights.

"The SHOW GRANTS query failed."

Being a big believer in least privilege, I then granted the zenphoto mysql user the specified privileges to the zenphoto database: Create, Drop, Select, Insert, Update, and Delete. Rerunning setup after this is when I got the same error message that started this thread. As it turns out, the zenphoto mysql user also needs Alter privileges to the zenphoto database. Perhaps the confusion is that what sbillard calls the "update" part of setup when he writes "What is happening is that the update part of setup is not happening" actually uses mysql "alter table" statements, which requires the mysql user to have Alter privileges which are separate from Update privileges: mysql Update privileges only all a user to change the actual data stored in a table, not to alter the definition of the table or its columns. I'd suggest adding "Alter" to the list of privileges specified as required in line 455 of setup.php (zenphoto-1.1.6):

`gettext("Your MySQL user must have Create, Drop, Select, Insert, Update, Delete, and Alter rights.") . $report);`

sbillard

Consider it done.