[Skip to main content](#vessel-content) # SQL-error when moving database ## - **[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")** - [![jansuhr's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar6377.jpg)](/forum/user/6377-jansuhr "View jansuhr's Profile") Topic Author - [ Offline](/forum/user/6377-jansuhr) Less More - Posts: 182 - Thank you received: 15 - - - [](http://www.colorconsult.se) - 13 years 8 months ago [\#90626](#90626) by [jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile") *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#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: 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")* was created by *[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")* 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: Code: 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. Code: 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 --- Jan Suhr [ www.colorconsult.se](http://www.colorconsult.se) [ www.jansuhr.se](http://www.jansuhr.se) Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86323 - Thank you received: 14283 - - 13 years 8 months ago [\#90711](#90711) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#90711 "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. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")** - [![jansuhr's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar6377.jpg)](/forum/user/6377-jansuhr "View jansuhr's Profile") Topic Author - [ Offline](/forum/user/6377-jansuhr) Less More - Posts: 182 - Thank you received: 15 - - - [](http://www.colorconsult.se) - 13 years 8 months ago [\#90718](#90718) by [jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile") Replied by *[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#90718 "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")* 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 --- Jan Suhr [ www.colorconsult.se](http://www.colorconsult.se) [ www.jansuhr.se](http://www.jansuhr.se) Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86323 - Thank you received: 14283 - - 13 years 8 months ago [\#90720](#90720) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#90720 "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")* You should see with your hosting company/server admin if they can authorize your mysql user to create views. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")** - [![jansuhr's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar6377.jpg)](/forum/user/6377-jansuhr "View jansuhr's Profile") Topic Author - [ Offline](/forum/user/6377-jansuhr) Less More - Posts: 182 - Thank you received: 15 - - - [](http://www.colorconsult.se) - 13 years 8 months ago [\#90721](#90721) by [jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile") Replied by *[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#90721 "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")* 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. [ ![Screen_Shot_2013-02-18_at_19_00_31.png](/forum/attachment/8639) ](/forum/attachment/8639 "...t_19_00_31.png") --- Jan Suhr [ www.colorconsult.se](http://www.colorconsult.se) [ www.jansuhr.se](http://www.jansuhr.se) - [ ![Screen_Shot_2013-02-18_at_19_00_31.png](/forum/attachment/8639) ](/forum/attachment/8639 "...t_19_00_31.png") [ ](/forum/attachment/8639?download=1 "...t_19_00_31.png") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86323 - Thank you received: 14283 - - 13 years 8 months ago [\#90984](#90984) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#90984 "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")* 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: Code: 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 Code: 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")** - [![jansuhr's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar6377.jpg)](/forum/user/6377-jansuhr "View jansuhr's Profile") Topic Author - [ Offline](/forum/user/6377-jansuhr) Less More - Posts: 182 - Thank you received: 15 - - - [](http://www.colorconsult.se) - 13 years 8 months ago [\#90989](#90989) by [jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile") Replied by *[jansuhr](/forum/user/6377-jansuhr "View jansuhr's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#90989 "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")* 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 --- Jan Suhr [ www.colorconsult.se](http://www.colorconsult.se) [ www.jansuhr.se](http://www.jansuhr.se) Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86323 - Thank you received: 14283 - - 13 years 8 months ago [\#91143](#91143) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#91143 "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")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[Huub](/forum/user/5333-huub "View Huub's Profile")** - [![Huub's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/5333-huub "View Huub's Profile") - [ Offline](/forum/user/5333-huub) Less More - Posts: 72 - Thank you received: 3 - - 13 years 7 months ago [\#97671](#97671) by [Huub](/forum/user/5333-huub "View Huub's Profile") Replied by *[Huub](/forum/user/5333-huub "View Huub's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#97671 "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")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86323 - Thank you received: 14283 - - 13 years 7 months ago [\#97693](#97693) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL-error when moving database](/forum/2-general-talk-about-hikashop/78486-sql-error-when-moving-database#97693 "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")* Yes you can remove it. It is actually not a table but a view. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzc4NDg2LXNxbC1lcnJvci13aGVuLW1vdmluZy1kYXRhYmFzZT90bXBsPW1hcmtkb3du) or [Create an account](/register) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.125 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)