SQL-error when moving database

  • Posts: 182
  • Thank you received: 15
11 years 2 weeks ago #90626

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`;

Rather than how I expect it to look.

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


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

Please Log in or Create an account to join the conversation.

  • Posts: 81182
  • Thank you received: 12995
  • MODERATOR
11 years 2 weeks ago #90711

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.

Please Log in or Create an account to join the conversation.

  • Posts: 182
  • Thank you received: 15
11 years 2 weeks ago #90718

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

Please Log in or Create an account to join the conversation.

  • Posts: 81182
  • Thank you received: 12995
  • MODERATOR
11 years 2 weeks ago #90720

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

Please Log in or Create an account to join the conversation.

  • Posts: 182
  • Thank you received: 15
11 years 2 weeks ago #90721

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.


Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 81182
  • Thank you received: 12995
  • MODERATOR
11 years 1 week ago #90984

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:

CREATE OR REPLACE 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`;

and not
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`;

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.

Please Log in or Create an account to join the conversation.

  • Posts: 182
  • Thank you received: 15
11 years 1 week ago #90989

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

Please Log in or Create an account to join the conversation.

  • Posts: 81182
  • Thank you received: 12995
  • MODERATOR
11 years 1 week ago #91143

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.

Please Log in or Create an account to join the conversation.

  • Posts: 72
  • Thank you received: 3
10 years 11 months ago #97671

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.

Please Log in or Create an account to join the conversation.

  • Posts: 81182
  • Thank you received: 12995
  • MODERATOR
10 years 11 months ago #97693

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

Please Log in or Create an account to join the conversation.

Time to create page: 0.091 seconds
Powered by Kunena Forum