Problems Upgrading to 5.04

Support & Bugs
1

I have problems upgrading to 5.04

I found some solution in the forums and put the sql command directly in PhpMyAdmin and changed the Version

but now it appears:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘t2.idaction_url’ in ‘on clause’

and in some parts it says:

Keine Daten
1-10 von NaN

I deleted already manualy the tmp/templates_c files, but still no changes

What can I do?

Thanks in advance!

2

Anyone can help me?

It’s important, since it’s for my webstore and I’m checking it every day!

3

Looks like you have a wrong data table?

Did you break your update process?

If taken a look into my database:

CREATE TABLE `piwik_log_link_visit_action` (
  `idlink_va` int(11) NOT NULL auto_increment,
  `idvisit` int(10) unsigned NOT NULL default '0',
  `idaction_url` int(10) unsigned NOT NULL default '0',
  `idaction_url_ref` int(10) unsigned NOT NULL default '0',
  `idaction_name` int(10) unsigned default NULL,
  `time_spent_ref_action` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`idlink_va`),
  KEY `index_idvisit` (`idvisit`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Do you have the same structure in your table? If not you can try to add the column manually (but I guess it won’t work for older entries)

Regards,
Christian

4

Yes, the installation stopped , so I tried to finish it manually with help of forum entries!

My database don’t look the same.

How can I update it? What’s the command?

Thank you!

5

If you have phpMyAdmin (or any other web tool) you can manually add the missing columns.

Otherwise I need to know which columns are missing? Or you can search for an ALTER sql statement.

Regards,
Christian.

6

it looks like this:

CREATE TABLE IF NOT EXISTS piwik_log_link_visit_action (
idlink_va int(11) NOT NULL auto_increment,
idvisit int(10) unsigned NOT NULL,
idaction int(10) unsigned NOT NULL,
idaction_ref int(11) unsigned NOT NULL,
time_spent_ref_action int(10) unsigned NOT NULL,
PRIMARY KEY (idlink_va),
KEY index_idvisit (idvisit)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=287435 ;

would appreciate it, if you tell me the command style_emoticons/<#EMO_DIR#>/wink.gif

7

You shouldn’t bump the version number without knowing why the update failed – you obviously have a different root cause.

Do you recall what sql it originally failed on? You may have skipped numerous updates.

8

well, I wanted to update from 0.2.32 to the latest version

but the update stopped, then i started it again and it said:

update 4.0 to 5.4

it didn’t work again and the error still occured:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘t2.idaction_url’ in ‘on clause’

So I searched in the forum, used some sql commant in php command, changed the version number manually and tried again, but still not working

looks like i messed it up

but how can i fix it?
i am already a few days without stats for my store and i don’t like that style_emoticons/<#EMO_DIR#>/sad.gif

9

that’s also what i thought, but how can i fit it??

thanks for your help!

10

Look at the scripts in core/Updates and pick out the SQL.

The most common cause for failure is lacking the necessary privileges to drop/create/alter indexes.

11

sorry, i don’t understand what you mean style_emoticons/<#EMO_DIR#>/blink.gif

12

i tried to do a sql demand from the core/update files, but getting this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘ALTER TABLE '. Piwik::prefixTable('log_visit') .' ADD config_java TINY’ at line 1

the code , or better the ```sign is wrong or not?

‘ALTER TABLE '. Piwik::prefixTable('log_visit') .'
ADD config_java TINYINT(1) NOT NULL AFTER config_flash’ => ‘1060’,
‘ALTER TABLE '. Piwik::prefixTable('log_visit') .'
ADD config_quicktime TINYINT(1) NOT NULL AFTER config_director’ => ‘1060’,
‘ALTER TABLE '. Piwik::prefixTable('log_visit') .'
ADD config_gears TINYINT(1) NOT NULL AFTER config_windowsmedia,
ADD config_silverlight TINYINT(1) NOT NULL AFTER config_gears’ => false,

13

I’m typing this from my iPhone, so I’ll provide one example and a terse explanation.

This is PHP. You can’t pass this “as is” to MySQL. Hence, the error message.

'ALTER TABLE `'. Piwik::prefixTable('log_visit') .'`
ADD `config_java` TINYINT(1) NOT NULL AFTER `config_flash`' => '1060',

Assuming your table prefix is “piwik_”, the above would become the following SQL:

ALTER TABLE `piwik_log_visit` ADD `config_java` TINYINT(1) NOT NULL AFTER `config_flash`;

The ‘1060’ at the end is a MySQL error code representing an error condition that we can ignore. Any other error requires investigation.

If no error code appears (ie it instead says ‘false’), then the query is mandatory – failure is unacceptable.

If you run into a privilege issue, contact your hosting provider to either run the sql for you, or to grant you the privileges required.

14

hm thanks, i tried to change it

i tried to change that update code:

‘ALTER TABLE '. Piwik::prefixTable('log_conversion') .'
CHANGE idlink_va idlink_va INT(11) DEFAULT NULL’ => false,
‘ALTER TABLE '. Piwik::prefixTable('log_conversion') .'
CHANGE idaction idaction INT(11) DEFAULT NULL’ => false,

into this one, but it’s not working, what’s wrong???

ALTER TABLE piwik_log_conversion CHANGE idlink_va INT(11) DEFAULT NULL’;
ALTER TABLE piwik_log_conversion CHANGE idaction INT(11) DEFAULT NULL’

15

well, this line worked now

but how do i change this, just getting always errors style_emoticons/<#EMO_DIR#>/sad.gif

‘UPDATE '. Piwik::prefixTable('log_visit') .'
SET location_ip=location_ip+CAST(POW(2,32) AS UNSIGNED) WHERE location_ip < 0’ => false,
‘ALTER TABLE '. Piwik::prefixTable('log_visit') .'
CHANGE location_ip location_ip BIGINT UNSIGNED NOT NULL’ => false,
‘UPDATE '. Piwik::prefixTable('logger_api_call') .'
SET caller_ip=caller_ip+CAST(POW(2,32) AS UNSIGNED) WHERE caller_ip < 0’ => false,
‘ALTER TABLE '. Piwik::prefixTable('logger_api_call') .'
CHANGE caller_ip caller_ip BIGINT UNSIGNED’ => false,
// 0.4 [1140]
‘ALTER TABLE '. Piwik::prefixTable('log_visit') .'
CHANGE location_ip location_ip BIGINT UNSIGNED NOT NULL’ => false,
‘ALTER TABLE '. Piwik::prefixTable('logger_api_call') .'
CHANGE caller_ip caller_ip BIGINT UNSIGNED’ => false,

16

any help please!

right now i am even thinking more about installing the new piwik version and just importing the old structure
but don’t know how this works too style_emoticons/<#EMO_DIR#>/sad.gif

17

I havn’t checked if its correct, but this should work:

UPDATE `piwik_log_visit` SET location_ip=location_ip+CAST(POW(2,32) AS UNSIGNED) WHERE location_ip < 0;
ALTER TABLE `piwik_log_visit` CHANGE `location_ip` `location_ip` BIGINT UNSIGNED NOT NULL;
UPDATE `piwik_logger_api_call` SET caller_ip=caller_ip+CAST(POW(2,32) AS UNSIGNED) WHERE caller_ip < 0;
ALTER TABLE `piwik_logger_api_call` CHANGE `caller_ip` `caller_ip` BIGINT UNSIGNED;
ALTER TABLE `piwik_log_visit` CHANGE `location_ip` `location_ip` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `piwik_logger_api_call` CHANGE `caller_ip` `caller_ip` BIGINT UNSIGNED;

Regards,
Christian.

18

great, it worked! thank you!

so i could do some updates manually, but i’m not going further with the 5.0 one:

‘UPDATE ’ . Piwik::prefixTable(‘log_action’) . ’ SET hash = CRC32(name);’ => false,
‘CREATE INDEX index_type_hash ON ’ . Piwik::prefixTable(‘log_action’) . ’ (type, hash);’ => ‘1061’,
'DROP INDEX index_type_name ON ’ . Piwik::prefixTable(‘log_action’) . ‘;’ => ‘1091’,

how can i do that in phpmyadmin?

Also I didn’t know how to do that update from 0.4.4. manually, since its very different, no mysql commands!

19

ah Piwik seems to work again!

But just for case, do I need to do these missing updates from my last post?

THANK YOU GUYS!

20

It’s a performance optimization.