# zsh and mysql



## MacImage (Jul 24, 2002)

im trying to execute a mysql script from php, using php's shell command. i build the script just fine and can verify that it executes from the terminal, but when i call it from php, apache's log file shows:

zsh: command not found: mysql 

the format of the command is:

mysql -u "username" --password="password" "databasename" < "script location" 

why would zsh not find the mysql command when i can run it from the OSX terminal application without a problem?

thanks for the help


----------



## newzworld (Jul 24, 2002)

locate where the mysql command itself was installed then add the folder where its located to the PATH environment variable by

export path=(<NAME OF FOLDER> $path)

and include that in your .zshenv file.  im not sure, but that should work, i dont use zsh that much cause tcsh is much better


----------



## MacImage (Jul 24, 2002)

where would .zshenv be located?


----------



## newzworld (Jul 24, 2002)

your home folder

UPDATE:  Did that work?


----------



## MacImage (Jul 24, 2002)

my home folder doesnt have .zshenv, so i created the file and added:

export path=(/usr/local/mysql/bin) 

still didnt work....

if i run zsh from the OSX shell, i can run mysql with no problem. weird?


----------



## newzworld (Jul 24, 2002)

export path=(/usr/local/mysql/bin $path) 

before you add it, try typing that in the commandline and hitting enter, if it doesnt work then tell me


----------



## MacImage (Jul 24, 2002)

> _Originally posted by newzworld _
> *export path=(/usr/local/mysql/bin $path)
> 
> before you add it, try typing that in the commandline and hitting enter, if it doesnt work then tell me *



[iaofficemanager:~] mjr% export path=(/usr/local/mysql/bin $path)
Badly placed ()'s.

EDIT: 

i also tried to use the absolute path in the command (/usr/local/mysql/bin/mysql -u blah blah blah) and still nothing.

EDIT AGAIN:

i checked the error log after i ran it with an absolute path and it doesnt complain about mysql not being found, its just not running the script. does the database have to be writable by the web server user even though i am explicity declaring a mysql username?

the script is valid too, i can paste it to a terminal session and it runs the mysql perfectg


----------



## BSDimwit (Jul 24, 2002)

just a text file with SQL commands in it?  Basically, with the command line argument you are using, it would try to open your scriptfile and feed it into mysql and if its not some sort of sql, it wouldn't work.

I guess it would help if you tell us what this script is supposed to do.


----------



## MacImage (Jul 24, 2002)

the script just dumps and recreates indexes. its only going to be ran once in a while, but it still needs to be done. i use php to build the queries, as many as 50 (that usually take a very long time to run) then dumps them to a text file. i then use php to call the script from the command line.

basically the code looks something like this


```
$scriptname = "index_rebuild.sql";

$text .= "ALTER TABLE names DROP INDEX lastname;\n";
$text .= "ALTER TABLE names ADD INDEX (lastname);\n";
//theres more, like 50, a couple different tables, and its done in a loop, too involved to actually show, but its all valid mysql

$output = fopen($scriptname,"w");
fwrite($text,$output);

$cmd = "mysql -u macimage --password=mmmhmm macimage < $scriptname";
$result = `$cmd`;

echo $cmd;
echo $result;
```

theres more to the code, but thats the gist of it. i can copy $cmd to a term window and it executes just fine, but it wont run from within php. that code wasnt copied exactly, so it might not be correct, syntax wise.

i know i shouldnt have to rebuild indexes, but i had a database of around 2 million records that was quick when i first implemented it, then slowly got worse until query times were around 60 seconds. i dumped all the indexes and recreated them and it was fine. this is for an end-user application so they can run this script when that happens to them without needing to touch the command line themselves. it all has to be web-based.


----------



## BSDimwit (Jul 24, 2002)

1. Make sure that php knows the path to your scriptfile and mysql executable.
2. Make sure that your database has proper priveleges granted to the www user.

Like so... do this with ./mysql

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
        ON macimage.*
        TO www@localhost
        IDENTIFIED BY 'mmmhmm';
        exit;

This will grant the webserver permissions to alter your tables in all the ways listed above.  You can add to or take away certain rights in the top line.

Then change your command line to show the user is now www instead of your username(or the one your were using).


If its simply not executing the script, I would bet that your problem is #2

Hope this helps


----------



## MacImage (Jul 24, 2002)

if the webserver user is "www", but the mysql user that i use is "macimage" would this still work?:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON macimage.* 
TO www@localhost 
IDENTIFIED BY 'mmmhmm';

im wondering if the user "www" needs write privelages to the database files even though im using the mysql user "macimage".


----------



## BSDimwit (Jul 25, 2002)

that you do need to do this... While I am by no means a mysql guru, I have gotten several web/php based things running by doing so.  Give it a try, if it doesn't work you can simply revoke the permissions you just granted using the revoke command  command.


----------

