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:

Rather than how I expect it to look.



What could be the cause? It is phpMyAdmin that exports the whole database but it is always the same error in the hikashop_click_view, hikashop_lead_view and hikashop_sale_view tables.


Jan

These are actually not tables but views.

It means that your new database doesn't allow the creation of views. You can either remove these queries or add the authorization for your mysql user to create views. HikaShop will work fine without them.

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

You should see with your hosting company/server admin if they can authorize your mysql user to create views.

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.

Maybe you're not using the root account in the configuration of your local website ?

I must say that I didn't use much Akeeba backup so I don't know much about it, but the query that we use to create the view is:
and not
so maybe that's an issue you would like to brought up with Akeeba backup if the problem doesn't come from the user access.

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

Hi,

You can simply delete the views via phpmyadmin before doing your backup. As I already said, these views are not necessary to HikaShop functionning. If Hikashop need them (for affiliate calculations), it will attempt to automatically create them.

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.

Yes you can remove it. It is actually not a table but a view.