Requires MySQL user with CREATE VIEW permission

Good catch :-)

I added this to the README.txt.

Thanks

Anyone else having this problem can possibly add these tables manually via phpMySQL.
Cpanel>PHPMySQLAdmin > select DB then use the SQL window to add each table.

Version 6.x Ubercart Views Table Structure.

-- --------------------------------------------------------

--
-- Stand-in structure for view `uc_order_products_pair_vw`
--
CREATE TABLE IF NOT EXISTS `uc_order_products_pair_vw` (
`nid` int(10) unsigned
,`pair_nid` int(10) unsigned
,`pair_sum_qty` decimal(28,0)
,`order_count` bigint(21)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `uc_order_products_qty_vw`
--
CREATE TABLE IF NOT EXISTS `uc_order_products_qty_vw` (
`nid` int(10) unsigned
,`order_count` bigint(21)
,`avg_qty` decimal(10,4)
,`sum_qty` decimal(28,0)
,`max_qty` smallint(5) unsigned
,`min_qty` smallint(5) unsigned
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `uc_order_products_user_vw`
--
CREATE TABLE IF NOT EXISTS `uc_order_products_user_vw` (
`nid` int(10) unsigned
,`uid` int(10) unsigned
,`order_count` bigint(21)
,`avg_qty` decimal(10,4)
,`sum_qty` decimal(28,0)
,`max_qty` smallint(5) unsigned
,`min_qty` smallint(5) unsigned
);
-- --------------------------------------------------------

thanks for the db scripts... this should be put in the readme for the module.

Chris

It is true that creating these tables will remove some errors from your admin console, but it will not make the views work. So rather than going through the trouble of creating redundant tables, I think that you should disable the views that are based on DB views all together, and then leave it at that.

Preferred option though, is to get the needed rights to the data base, or find some one else who has it to do it for you. The SQL for creating the DB views can be found in the .install file, but I will list them here for easy access:

   CREATE VIEW uc_order_products_qty_vw (nid,order_count,avg_qty,sum_qty,max_qty,min_qty)  
          AS SELECT `op`.`nid` AS `nid`, 
            COUNT(`op`.`nid`) AS `order_count`,
            AVG(`op`.`qty`) AS `avg_qty`, 
            SUM(`op`.`qty`) AS `sum_qty`,
            MAX(`op`.`qty`) AS `max_qty`, 
            MIN(`op`.`qty`) AS `min_qty` 
          FROM uc_order_products `op` 
            GROUP BY `op`.`nid` 
            ORDER BY `op`.`nid`;

   CREATE VIEW uc_order_products_user_vw (nid,uid,order_count,avg_qty,sum_qty,max_qty,min_qty)  
         AS SELECT `op`.`nid` AS `nid`, 
           `o`.`uid` AS `uid`, 
           COUNT(`o`.`order_id`) AS `order_count`, 
           AVG(`op`.`qty`) AS `avg_qty`, 
           SUM(`op`.`qty`) AS `sum_qty`,
           MAX(`op`.`qty`) AS `max_qty`, 
           MIN(`op`.`qty`) AS `min_qty` 
         FROM uc_order_products `op`, 
           uc_orders `o` 
         WHERE `op`.`order_id` =`o`.`order_id` 
           GROUP BY `o`.`uid`,`op`.`nid` 
           ORDER BY `o`.`uid`,`op`.`nid`;

   CREATE VIEW  uc_order_products_pair_vw (nid,pair_nid,pair_sum_qty,order_count)  
         AS SELECT op1.`nid` AS `nid`, 
           op2.`nid` AS `pair_nid`, 
           SUM(op2.`qty`) AS `pair_sum_qty`, 
           COUNT(op2.`nid`) AS `order_count` 
         FROM uc_order_products op1, 
           uc_order_products op2 
         WHERE op1.`order_id` = op2.`order_id` AND  op1.`nid` <> op2.`nid` 
           GROUP BY `nid`, `pair_nid`  
           ORDER BY `nid`, `order_count` DESC, `pair_sum_qty` DESC;

Would be good if you also include info in the docs as to how to fix the problem rather than just state what the problem is e.g.

If you have granted all privileges to the db user, but you are still getting a command denied error, please explicitly grant CREATE VIEW to your db user.

You can grant create privileges via phpMyAdmin as root MySQL user. Execute the following query:

GRANT CREATE VIEW ON yourdbname.* TO 'dbusername'@'localhost'

(Replace your database and user / host names as appropriate.)

Run another query - FLUSH PRIVILEGES - after the above command to ensure new privileges are activated.

Good idea - I have added this to the README.txt - thank you for the input.

Thanks for the module :)

why is it so different? can we have any different approach other than having views?

I logged in to mysql as root and ran this command:

mysql> GRANT CREATE VIEW ON {dbname}.* TO '{dbusername}'@'localhost';

I got this:
Query OK, 0 rows affected (0.40 sec)

I still have problems saying that no permissions to create views when I tried to enable the module. I manually created views using the above code while I am there using the root user, still the module doesn't seem to work. Any suggestions?

What kind of error messages do you get?

Just chiming in:

I'm with siteground using cPanel, and while I didn't know about the "GRANT CREATE VIEW" option at the time, manually going into phpMyAdmin and using the de-drupalized code from the module's install file (ie. minus the curly brackets) allowed me to get it running. Very odd. I had "all privileges" too...

Anyhow, just sayin.

Subscribing.

It seems that this is a common problem for all shared hosting/cPanel users, not just this module:
http://www.hostingrails.com/MySQL-CREATE-VIEW-command

subscribing

Would very much like to see this dependency go away, as it potentially causes problems with module updates. Having a minor module put a kink in what should be a routine update is a sufficient problem that I can't imagine putting this on a client site until that dependency goes away.

+1

I've set up both 'admin' and 'root' to have create view permission. I flushed permissions, cleared cache and I still don't see any new views with the names new_products, popular_products, etc.

I'm trying to figure out what the problem is. Here are a couple of things I notice that might be the cause:

In the permissions table I see another row showing database-specific 'All privileges' and it won't allow me to add 'create view' to that one. I'm hosting (development) at webenabled.com and the username for this one is my application name and I don't know if this is the problem.

Also, I notice that the rows it added for admin and root show grant as 'no'. I don't know what that means either.

Any ideas?