Error while updating: SQLSTATE[42S01]: Base table or view already exists

Support & Bugs
1

Hello!

I have been recently switching hosting providers. I have transferred my database and installed Piwik as fresh installation on the new server. In the configuration screen I have provided the same custom prefix for SQL tables that was in my previous installation.

Immediately after installation, I am redirected to Piwik update screen where it asks me to update my MySQL database. I cannot perform it however because I get the following error message:


/home/<username>/<domainname>/piwik/core/Updates/1.8.3-b1.php:
Error trying to execute the query 'CREATE TABLE `<prefix>_report` (
					`idreport` INT(11) NOT NULL AUTO_INCREMENT,
					`idsite` INTEGER(11) NOT NULL,
					`login` VARCHAR(100) NOT NULL,
					`description` VARCHAR(255) NOT NULL,
					`period` VARCHAR(10) NOT NULL,
					`type` VARCHAR(10) NOT NULL,
					`format` VARCHAR(10) NOT NULL,
					`reports` TEXT NOT NULL,
					`parameters` TEXT NULL,
					`ts_created` TIMESTAMP NULL,
					`ts_last_sent` TIMESTAMP NULL,
					`deleted` tinyint(4) NOT NULL default 0,
					PRIMARY KEY (`idreport`)
				) DEFAULT CHARSET=utf8'.
The error was: SQLSTATE[42S01]: Base table or view already exists: 1050 Table '<prefix>_report' already exists

Do you have any suggestions? :slight_smile:

2

Sorry, I think you hit a rare bug (installing a new version of piwik in an older database).

Can you try, deleting the mysql table piwik_report and try again ?

3

Worked like a charm! Thank you :slight_smile:

4

hi i ran across this exact same error message the only thing is in my sql admin I cannot see a table called.

piwik_report

I also got this error message via the shell script.

Error trying to execute the query ‘CREATE TABLE piwik_report ( idreport
INT(11) NOT NULL AUTO_INCREMENT, idsite INTEGER(11) NOT NULL, login
VARCHAR(100) NOT NULL, description VARCHAR(255) NOT NULL, period VARCHAR(10)
NOT NULL, type VARCHAR(10) NOT NULL, format VARCHAR(10) NOT NULL, reports
TEXT NOT NULL, parameters TEXT NULL, ts_created TIMESTAMP NULL,
ts_last_sent TIMESTAMP NULL, deleted tinyint(4) NOT NULL default 0, PRIMARY
KEY (idreport) ) DEFAULT CHARSET=utf8’. The error was: SQLSTATE[42S01]: Base
table or view already exists: 1050 Table ‘piwik_report’ already exists

thanks les

5

I forgot to mention I have no special plug ins installed in piwik

6

Can you try to execute the SQL query

UPDATE piwik_option SET option_value = ‘1.8.4’ WHERE option_value=‘version_core’

7

Hi tried to do it in a shell(as you typed). Command not found was result.

Was that how you wanted me to execute script? in a shell or via sql admin?

thanks

8

I just saw this in case it helps.

Piwik database will be upgraded from version 1.8.1 to the new version 1.9.

9

Figured mine out I had to delete all my sql data to allow the upgrade to proceed to 1.9.

Thanks for your help Matt

10

I have the same problem as the topic starter. I tried removing that table but it still generates the same exact error. Suggestions?

11

Hi if you cant do the SQL delete record then all I found was to delete all the data.I hope you dont have alot of data…

12

I can do the delete command for the table in SQL but it adds it back and then the error appears. I have a LOT of data :frowning:

13

Hi,
I changed the version number form 1.8.2 to 1.8.4 in my piwik database and the up[grade performed seamlessly. After a manual upgrade.

Hope that helps

Pete

14

I encountered this problem after manually upgrading from 1.7.1 to 1.9.2. I’m running the following on my server:

Debian Squeeze
MySQL 5.5.25
PHP 5.4.9

I successfully resolved the issue (per the instructions printed in the browser under the error message) by changing the version_core value (in the piwik_option table) from 1.7.2-rc7 to 1.8.3-b1. I used MySQL Workbench to change the value. The command it executed was:


[b]UPDATE `piwik_data`.`piwik_option` SET `option_value`='1.8.3-b1' WHERE `option_name`='version_core';[/b]

(Where piwik_data is the name of my Piwik database.)

I refreshed the update screen in my browser and it completed successfully.

15

We done something like that upgrading, so i guess that could help to resolve the issue.