MySQL on remote ISP won't let me upload 1

[GKChesterton]

I am trying to load a small MySQL database to my remote ISP FuturePoint. They provide PHPMyAdmin. I can't figure out how to gain permissions. For most commands, I receive
#1044 - Access denied for user 'paparts'@'localhost'
to database 'papartscenter\_com\_-\_test'

I can create and drop a table from the PHPMyAdmin SQL tab. I can change my password (and I have, in case the ISP was looking for some kind of security intiative from me). I can run show grants, which returns:
[tt]
GRANT USAGE ON *.* TO 'paparts'@'localhost'
IDENTIFIED BY PASSWORD '403d8ac62e7e4b80'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, INDEX, ALTER ON
`papartscenter\_com\_-\_test`.*
TO 'paparts'@'localhost' [/tt]

To me this looks like I should be able to do plenty of stuff.

I only know of one User given to me as the user admin. I've changed the password back and forth to see if it was wanting some security initiative there. My Tech Support request has gotten no reply.

I see references on web pages to setting PHPMyAdmin permissions, but poking about the interface doesn't show any spot for that. There is another interface from FuturePoint for creating the database (can't be done from PHPMyAdmin) but that seems to be the only DB function there. When I find instructions for PHPMyAdmin and MySQL on the Net, they're generally written for the host administrator -- not the remote user admin. Or so I conclude. References to config.inc.php seem obviously not to apply.

How do I conquer this challenge and gain the personal elation I desire? Have I got a workable ISP who isn't forthcoming with support, or something worse?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[towerbase]

How are you trying to "load a small MySQL database"?

Are you trying to import data (e.g. CSV data) into an existing table using PHPMyAdmin or are you trying to use "LOAD DATA INFILE .." from an application that you've written?

The help that FuturePoint provides on their website describes how to import Excel spreadsheet data into a MySQL table. Have you followed those guidelines?

Andrew
Hampshire, UK

 

[GKChesterton]

Thanks Andrew for reply. Two days ago I thought that since an import for .sql file was supported, that meant I could bring in the whole schema with the data. Gave up on that. Found a variation of the Import tab that is referenced from table info and was able to import a CSV.

I didn't succeed with LOAD DATA INFILE. I tried using it with and without LOCAL (placing the source file in the ISP's directory or keeping it on my C drive), but got errors "SQL not correct," "permission not available," or "file not found."

In summary, the task is reduced from Not Possible to Pretty Tedious, and I suppose my one-swoop import was a fantasy.

If you know of a web site that focuses on these issues, "How to make your ISP work for you" or something like that with a focus on database, I would love to know where it is. The books I've seen assume you are running your own host server.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Google on "PHPMyAdmin User Documentation" yielded some better info:
1. Net Forge article with tips from the remote-user perspective.
2. Uploading a .SQL file

My original format for the DB is Access. That probably needs to remain the active-data format, as the interface is useful to the project client working offline. The online version will be read-only, and I will occasional update it using .csv exports. Importing via .sql format would require kicking the DB over to MySQL just to prepare the upload file, which doesn't seem to make sense.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[towerbase]

I use LOAD DATA INFILE .. a lot because it is very quick compared to other ways such as using lots of INSERT statements which is essentially what a .sql file is.

I regularly import about 1.5 million (fairly simple) records and it takes about 10 minutes on a broadband connection using LOAD DATA INFILE.

Place the source file on your c: drive and remember to use a double slash in the path where appropriate. I tend to use tabs as field separators to avoid double quote marks around strings. I also use new line instead of new line, carriage return again to save on bandwidth.

So a typical statement would look like

LOAD DATA LOCAL INFILE 'c:\\upload\\data.csv'
INTO TABLE tablename
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'

Andrew
Hampshire, UK

 

[GKChesterton]

Thanks so much for the tips. I imported a second table, more large and complex; and getting the .csv delimiters etc. all correct took many attempts, but finally it went in. I can apply both your advice on .csv format and use of the LOAD statement. I'm encouraged!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[towerbase]

I am glad to be of help.

I remember the frustration I had before I got LOAD DATA INFILE to work!

Andrew
Hampshire, UK

 

[GKChesterton]

The data is uploaded at FuturePoint (at least in sample form), but I can't push my PHP connection past the "Access Denied" error, and the tech support is not responsive. Their documentation seems scant too. I either need more hand-holding for my ignorance, or for the host service to correct their settings or permissions.

I'm inquiring with a local ISP so I can get this project moved over and moving ahead.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[towerbase]

If you know that the table data has arrived at FuturePoint (presumably by checking with PHPMYADMIN) your inability to access it through PHP probably indicates that you should start a thread in the PHP forum as I suspect it is no longer strictly a MySQL problem. It might be a problem with FuturePoint or with your PHP script.

Andrew
Hampshire, UK

 

[GKChesterton]

I am running MySQL 5 and PHP 5 with Apache server on my own workstation. I also bought PHP Designer 2007 Pro just to round out the arsenal.

I migrated the MS Access database to MySQL and have been running PHP queries from localhost just fine for the last two weeks. This gives me a strong assurance the PHP is okay.

There is the issue of mysqli_connect versus mysql_connect, so I've tried both in my upload versions. I think the issue is narrowed to something on the ISP side: either with user permissions (oops, we forgot to turn you on!) or some php.ini setting that doesn't agree with my home setup.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

It occurred to me to run phpinfo() on the host. I wish I understood more of it but nothing looked strange.

Also on the Gathering Clues Blog: using this script:

echo "PH[s][/s]P is working ...<br><br>";

$dbhost = "lo[s][/s]calhost";
$dbuser = "paparts";
$dbpass = "myPWD";
$dbname = "paparts_be";

$link = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

if (!$link) {
    Echo "Connect failed: %s\n", mysqli_connect_error();    exit();
}

Echo "Host information: %s\n", mysqli_get_host_info($link);

mysqli_close($link);

Yields these results:
[tt]
from Home Server
Pwd Correct Nice result
Pwd Wrong Errors, then "Connect failed" etc.
from ISP
Pwd Correct "PHP is working", that's all
Pwd Wrong "PHP is working", that's all
[/tt]

So it seems the script stops running and errors are not reported.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Okay, I got it. Basic problem was I needed to switch to mysqli command set.

But debugging with the new and slightly different parameters held me up. I put off making my fancy home setup and editor work for me, which wasn't smart. I only needed to alter my php.ini file for mysqli (oh yeah, and download the appropriate .dll). Things straightened out pretty good once I did that.

Anyway Andrew earned his star and I WILL make LOAD DATA INFILE work for me, you wait and see.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Correction on above, I needed to switch from mysqli to mysql.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​