Create Command Denied / New sources / Global phpMyAdmin user

[bfcoop]

Whenever I try to create a new table in my club's private database, I get the error message "#1142 - CREATE command denied to user." I'm guessing I need to set some sort of security switch for the account or execute some set of PHP or SQL commands in order to gain the privilege to create a table, but I can't figure out what. For the time being, I'd be satisfied with making a copy (both structure and data) of the one existing table (the one with the same name as the database), giving the copy a different name, but the COPY operation runs into the same error. BTW, I have successfully imported data from a csv file into the private database, so evidently I at least have the privilege to do whatever CREATE is involved in that sort of operation. Any suggestions would be appreciated.

[support]

Importing from CSV only requires INSERT privileges, not CREATE. Why do you want/need to create a new table? Usually when people run into this error, it's because they are making a mistake while importing (see below topic), but it doesn't sound like that is the case with you. It sounds like you actually want to create a new table.

http://foodclub.org/phpbb/viewtopic.php?f=1012&t=1872

[bfcoop]

Besides UNFI and Frontier, my club buys from about a dozen "local" vendors. Members can build their UNFI and Frontier orders at the UNFI and Frontier websites, and then batchers consolidate and finalize the orders for those sources. But for the local vendors, members have to email their orders to a batcher, who then enters them into a 20-year-old Access database on her PC, consolidates the orders, and places them to the vendors. I'm trying to replace that database with Foodclub, and I hope eventually to link to UNFI and Frontier so members will be able to do one-stop-shopping. The problem is that, unlike UNFI and Frontier, either the local vendors don't have product databases that could be interfaced with Foodclub, or else we offer to our members (using our own product codes) only a limited subset of what is in those vendors' databases. That's the reason we've maintained the vendor database in Access all those years. As mentioned, I've been able to import this database (in csv form) into Foodclub, but since there's only one product table, all the vendors are mixed together. I'm currently using the Manufacturer column to distinguish the products offered by each vendor, but that doesn't help much when it's time to consolidate orders by vendor. I need to be able to create a product table for each vendor -- similar to the product tables Foodclub can import from UNFI, Frontier, and other sources -- but unlike those tables, we need to maintain the vendor product tables ourselves. Further complicating matters, some of the local vendors sell us not only their own products, but also the products of other manufacturers, thereby serving as distributors in somewhat the same way as UNFI and Frontier, but on a much smaller scale. Bottom line: We need to be able to distinguish between Vendor and Manufacturer, and I think the only way to do that is by creating and maintaining a separate product table for each vendor.

[support]

Thank you for that full explanation of your needs.

Yes, Foodclub will allow you to do one-stop shopping on all your local vendors, plus UNFI and Frontier. Several other clubs are now switching from UNFI's site to Foodclub because they don't like the new UNFI site, and plus they don't allow splits for their Western region customers.

Frontier's site does have slightly better product info and browse capability than Foodclub (for now), but it also doesn't do splits, though not a huge problem since many Frontier items don't require splitting. But also Foodclub will notify you of Frontier sales each month. There are probably other benefits of Foodclub over using the Frontier site, not to mention one-stop shopping.

Anyway, about your local vendors: you are correct, you will want separate private databases for each. The way to accomplish this is to create separate sources, and enable a private database for each one. See this topic on adding new sources:

http://foodclub.org/phpbb/viewtopic.php?f=774&t=1900

Once you have new sources for them, I can give you a single global phpMyAdmin user which has access to all of them. So go and create the new sources (easiest is foodclub.org/admin using "Link to account id" and then Account linking), and let me know what phpMyAdmin user you want to have global access to all the databases.

Also, I am going to move this topic to the "Product database" subforum since it is about product databases.

[bfcoop]

I've now created the new source accounts and linked them to my club's account, brighton. bfcoop is the admin user I would like to give global access to all the databases. Do you need to know the names of the new source accounts, or can you already determine that from the fact that they are now linked to brighton? BTW, after I created each source account, I followed the link in the "successful" message to try to log in to the account with the user name and password I had specified during the account creation process, but in each case I received an "invalid user name or password" error message. Is that because there is some sort of lag time between account creation and accessibility; or do I have to take further steps in order to make those accounts accessible; or do I just have to wait till you give bfcoop global access to all the databases?

[support]

Alright, phpmyadmin global user has been enabled. This means you can enable a private database on each of your sources, and then grant access to phpmyadmin and they will show up under your single phpmyadmin login: brighton.

No, I don't need to know the IDs of the new sources, as I can see them all linked.

As for not being able to log in, I think you are referring to logging in to Foodclub, not phpmyadmin. You won't be able to login to Foodclub on a new source that is not yet linked, if using the quick-account creation (foodclub.org/admin). But once it's linked, all users can access it after logging in.

Also, I noticed you have several sources plus what looks like a main account "Brighton Food Coop." Let me know if you want that one converted to a main home page. See this support topic on what that does:

http://foodclub.org/phpbb/viewtopic.php?f=774&t=2316

[bfcoop]

I had initially planned to use the main account's database to lump together several smaller vendors, but after seeing the advantages of the "main home page" approach, I decided to make "Brighton Food Coop" the main home page and break up those vendors into separate sources; so now I've created 4 additional source accounts and linked them to the main account. Will bfcoop's global access apply to those accounts automatically, or will you have to do that again for those accounts? (in which case, sorry about the extra work) Go ahead and convert "Brighton Food Coop" to the main home page. Meanwhile, I'll be in process of breaking up the product data and importing it to the various source databases. Thanks!

[support]

Your global phpmyadmin user stays global for all future sources, as long as you use the quick-account creation. (foodclub.org/admin) Using the new account wizard with vendor "Other" will auto-create a new phpmyadmin user, bypassing the global one you have set, so just avoid that. (though it's not hard for me to fix)

Alright, I made your brighton a "main" account. There were some items ordered (on Order and Splits), which now you won't be able to remove. I will delete those when you say it's okay (probably test orders, but don't want to delete until you say). You can see them from these users' Shopping carts: hauk, jimb, kalou, winch.

[bfcoop]

Yes, those orders are just test data and can be deleted at your earliest convenience. Hope that's easy for you to do. Otherwise, I should have deleted them myself. Thanks for all this terrific support!

[support]

Okay, test orders deleted. Also, I will move this topic to a subforum shortly - in case you can't find it, look there.