SQL-error when moving database

This is an error I always get when moving the Joomla database and if it has Hikashop in it.

It has something to do with the hikashop_click_view table.

In the exported SQL-file it looks like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `colorconsult`.`iqej4_hikashop_click_view` AS select `a`.`user_id` AS `user_id`,sum(`b`.`click_partner_price`) AS `click_price` from (`colorconsult`.`iqej4_hikashop_user` `a` left join `colorconsult`.`iqej4_hikashop_click` `b` on(((`a`.`user_id` = `b`.`click_partner_id`) and ((case when (`a`.`user_currency_id` = 0) then 150 else `a`.`user_currency_id` end) = `b`.`click_partner_currency_id`)))) where ((`a`.`user_partner_activated` = 1) and (`b`.`click_partner_paid` = 0)) group by `b`.`click_partner_id`;

CREATE TABLE `colorconsult`.`iqej4_hikashop_click_view` (
`user_id` INT( 10 ) UNSIGNED NOT NULL ,
`click_price` DECIMAL( 34, 5 ) NULL DEFAULT NULL
) ENGINE = MYISAM ;

Well, there is no problem creating those views when installing Hikashop, it is just when I make an export of the database and then I get the error when importing.
Same thing happens if I use Akeba Backup to move the site, when Akeba is restoring it stops with the error saying that it can't create the table xx_hikashop_click_view.


It is a hassle to fix that later.


Jan

If I look in my Localhost phpMyAdmin the root user has full access and everything granted and Create View is checked.

Wouldn't that mean that I would be able to do this on my Localhost? Because I can't.

Hi Nicolas,

The code that is faulty is from an export in phpMyAdmin, not Akeeba. But the same thing happens there too.

I just wanted to move over all my sites on my localhost to a new computer. The localhost is an XAMPP installation on Mac.

And I am running as root on my Localhost.

One thing I see is that on a plain Hikashop installation I have on one localhost site, just Joomla and Hikashop, those View are not there. I mean they are not as tables visible in the phpMyadmin for that site. It might be correct?


Jan

I ran into the same problem today. Did a backup from the root website and wanted to use it as the setup for a new site in a subfolder.
All went fine except for the hikashop_click_view.

Am i correct in understanding that this table can be removed without further consequences?

What i did now was create the table with this ....

CREATE TABLE IF NOT EXISTS `xxxx_hikashop_click_view` (`order_id` int(10) unsigned NOT NULL DEFAULT '0',`order_number` VARCHAR( 255 ) NOT NULL DEFAULT '') ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

that seemed to work so far.