MYSQL :: Running Mysql Commands In A Batch File

Running MySQL Using A Dos Batch File
I am used to use MySQL on Unix, now I have to do some development for MySQL running on Windows machine. The following script works well in Unix but I am unable to run it on Windows. "MY_FLAG" is the one which is causing issue. Due to certain restriction in my framework, I can use the normal batch by supplying the .sql file while opening the mysql connection ( infact i need to run multiple source files) Code:

Running A Sql Batch Script From The Linux Command
s this possible?

I want to write a command script which i want to put into a cron job which will download a db backup from cpanel. then i want to unzip it, and run the sql file, so keeping the database on the mirror machine up to date.

So what i have is:

#get the database
wget -v --http-user user --http-pass pass https://www.site.com/path/to/cpanel/backup
#move to backups folder
date=`date -I`
mv -fv ./mysqldump.gz /home/user/dbbackup/$date.gz
now what i also want to do is:

mysql -uuser -ppassword
u database
set foreign_key_checks = 0;
. mysqldumpfile.sql
set foreign_key_checks =1;
exit
is it possible to do that last bit from the command-line? as far as i know, a shell script can't run commands to mysql?

Batch File
I am new to running batch files. Is there a way to loop through a results set in a batch file? I want to be able to create tables using table names that are stored in a table. I had hoped that I could query the table to retrieve all tablenames, then loop through this result set, creating a table with each tablename returned. (Table definitions will be the same for all tables created. Only the tablenames are different.)

Cant Execute Batch File
Hi new to this but pulled out nearly all my hair with this.
I got the test database open and I want to automate creating the menagerie table ( as in the tutorial ) so I download the textfile save it to c:/ and execute the command source c:/cr_pets_tbl.txt all I get is unable to open file error 22.
I have tried creating my own text file ( windows, notepad ) just containing the following
use test;
create table pets(name varchar(10),species varchar(8),born date);

simple as that then type in the consoe source c:/pets.txt;
unable to open file error 22 or sometimes error 2
tried enclosing filename in " and ' tried source= filename
tried . instead of source but nothing works.
using manual insert command or load data local infile works but not this, HELP.
Malcolm K.

Batch File For Backup
Can I make complete Backup of a database (same as from MySQL Administrator) using batch file?

Batch FIle To Create Database
I am trying to create a batch file to create a database and populate it with create table statements. I can't figure out why it will not create the database and load the tables. I have the following in my batch file

/mysql/bin/mysql.exe -vvv -u root -pcs3911 < C:mySql est.txt

and the following is my test.txt file

CREATE DATABASE 'lovettsite';

CREATE TABLE `applications` (
`username` varchar(255) NOT NULL default '',
`firstname` varchar(255) NOT NULL default '',
`middlename` varchar(255) NOT NULL default '',
`lastname` varchar(255) NOT NULL default '',
`current_address_line_1` varchar(255) NOT NULL default '',
`current_address_line_2` varchar(255) NOT NULL default '',
`current_city_town` varchar(255) NOT NULL default '',
`current_state_province` varchar(255) NOT NULL default '',
`current_zip_code` varchar(16) NOT NULL default '',
`p.........................................................................

Using Batch-/cmd-file For Dumping Data
i want to build in data in my database that are written in single table dumps like [tablename].sql.

Each time it is nececery to use several files. And it is also nececary to do this using the command line because of the files are bigger than 1,5 gb
But i didnt find a way to transfere the needed commands into the mysql command line.

How To Execute A Batch Sql File Under Windows?
I have installed MySQL 5 in c:Program FilesMysql. I have a sql file named "books.sql" which is put in the subfolder in,it contains many statements that like "INSERT ... value ...".

my question is how to execute it? I try it, but failed.

One more question is how can I return from "->" to "sql>" promopt?

Ever Thought About Running Mysql Without My.cnf File ?
I installed from source mysql on Fedora3 and started mysql demon without my.cnf by chance and it was running . I m wondering did it happen with somebody else too?

Running Mysql Code Within A Php File?
I have been given a some mysql code that I have to run within a website - I am familiar with mysql in terms of insert info and querying a database but I at a loss as to how to make this work.

the mysql code works in mysqladmin, but how do I call it into my php code?

BTW the mysql code creates a new record in the database .......

Also, I need to tweak the code so it runs in a loop for different variables - in php I would run a FOREACH query, how do I do it in mysql?

Echo SQL Commands From A Source File
Running mySQL under Windows XP. Trying to debug a long input file of SQL statements (building tables and loading data from tab delimited text files) I want to be able to see the SQL commands that are executing in the output printed to the SQL command window so I know which error messages apply to which command. I have show warnings statements after each SQL statement at the moment so I see the errors/warnings, just not the command that generated the errors/warnings.

Is there an ECHO command or a flag when calling the script to get commands echoed as they are executed?

Urgent - Load Sql Commands From Txt File
mysql -u root -p -D mydatabase < 'c:/sqlcommands.txt'

All I ever get is the 'filename or volume' invalid, or something like that. It's not an issue with the path to the file, because I use c:/filename for other kinds of imports and it goes in fine. I urgently need to load some sql commands from this file else I have a lot of work to do.

Mysqld-nt Crashes When Executing Batch File In 5.03 Beta
I'm quite new to mysql but I had installed the 5.0.1 and 5.0.0a alpha under windows XP, and designed a little database for managing cards. To make some backup of this database I dumped it in a batch file. This file was working perfectly with mysql 5.0.1 and 5.0.0a.

When I try and execute my batch file, having previously created the database concerned, it crashes the mysqld-nt. On top of that the first table created in the database is listed but when a DROP DATABASE is issued it says that this table does not exist and then the command fails.

The database uses UTF8 and SET FOREIGN_KEY_CHECKS=0; is set in the begining of the file.

So I have two questions :

1- Can I force the database to be dropped or is it definitely corrupted?

2- Any clue on why the batch file crashes the server? (I'd be glad to send it is is a mere 250 ko when zipped)

Running Existing .sql File In Mysql Command Prompt?
I've seen "examples" of this in my book on MySQL, but it just shows the file on the prompt. It says that I can run an existing sql file - for creating a table - right from the prompt.

However, it doesn't really explain how to do this in detail. I've tried :

-h host - u user -D database -p < myfile.sql

but it gives an error. How do I point it to the right file on my system?

Running An Existing SQL File
I hope this question is not too 'newbie' to answer.. I'm reading a book (PHP and MySQL web development by Luke Welling and Laura Thomson), and am getting an error on an example script.

I'm trying to create tables using an existing SQL file through MySQL. According to the book, it should be done like this:

> mysql -h hostname -u username -D database -p < file.sql
(I've changed the hostname, username, etc.)

However, I get the same error over and over again: ERROR 1064 (42000): ... at line 1. The book didn't even mention where to put the file, I've tried all sorts of things, but can't get it to work..

Running Script File
I want to write mysql code like create table ... and insert into ...
in text file and run the codes in mysql.
I have installed mysql in my machine windows xp. and the help says the mysql> source (filename) will work to run the code file. but it does no work. says could not open the file.
I am not sure where should i put my code file and the extension of the file required.

Running Sql File From Shell Script
When i run the command "mysql -h host - u user - p password -D database <file.sql" (the file.sql contains an Update Scrpit) from command prompt

/usr/test> mysql -h host - u user - p password -D database <file.sql

i am able to run the file and i am getting expected results.

When i copy the same string in a shell script (suppose in update.sh) and try running it from the command prompt

/usr/test> . update.sh

i get an error as "No file or directory found"

i tried giving in the full path of file.sql and tried running hte shell script but it gave me the same results "No file or directory found".

Running External .sql Text File
I have a database on a sever that is running some version of MySQL 4. I wan't to put the same database on my local computer.

I ran a MySQL dump from the server and tried to run the resulting text file from the UNIX command line on my computer (Running MySQL 5 on Mac OS 10.4). I always get error 1064:

CODEERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
CREATE TABLE 'art_type' (
'art_type_id' tinyint(11) unsigned NOT NULL auto' at line 1

Mysql Batch Question
I'm writing this here because it's related to mysql. I'm using Mysql with PHP and because Mysql 4 doesn't support stored procedures, I would like to run several scripts from one database connection. In running a multi-line sql.txt file externally I can do this by delimiting the statements with a semi-colon so I tried to do the same:

$strsql = "";

$strsql = "delete from tbl1 where tbl1ID = '1';";
$strsql = $strsql . "delete from tbl1 where tbl1ID = '2';";
$strsql = $strsql . "delete from tbl1 where tbl1ID = '3';";

$result = @mysql_query($strsql, $db);

I'm not getting an error; the sql scripts simply won't run.

MySql Commands For Mac OS X
-I a new user and im working my way thru this program ive done some simple databases but i dont know how export my databases if i want to send it to someone else.

-Ive also been working with the test database in MySQL cause it wont let me create a new database. Do i have to be loged in to the admin account to create new databases?

So far ive managed to do some tables i wish to know how far can i take this and how do i integrate diferent tables os they cant take information from each other.

MySQL Commands
I apologize for the very basic question, but I am just learning to use mySQL. I installed it using xampp for Windows and am trying to run commands from a DOS Command Prompt (in the mysql subdirectory). Unfortunately it doesn't recognize any commands, and typing "help" just shows me DOS commands (and not mySQL commands). I can see the service is started in xampp...is there something else I need to do to make it recognize I'm trying to use mySQL commands? I apologize again for the basic question, but after 45 minutes of hopeless web searching.

Few Mysql Commands
I always use myphpadmin for mysql > Now if I use command line, and I am on mysql prompt ,
1. if I want to go to a certain datatbase what command do I need to type?
2. Now once I am in that database and want to se all the tables how do I do that.
3. How can I get into a table and see all the records.

PHP Commands Unexecuted, But MySQL Successfully Installed!
I have a problem I found nowhere else on the net in some resources:

My infrastructure:
OS: Win XP pro, SP2
Webserver: Apache 2
PHP: PHP 5
mySQL: mySQL 4.1
(php and apache work!)

And this is what happens:

I try to connect to a mySQL db via PHP, which is done by the regular mysql_connect statement. But when I access the .php - file via my browser by typing "localhost", the code is executed UNTIL the php commands, not further. The code is really cut off when I view the source in the browser. What am I doing wrong!

Batch Mode Feedback Of "mysql" Command Line Tool
while writing a bash shell script to backup some databases and I am having slight problems with the mysql command line tool's feedback.

Here goes command line:

#!/usr/bin/env bash
mysql -ubackup -e "UPDATE `mydb`.`sysvar` SET `val`='backup' WHERE `var`='updateState' AND `val`='0';"

The backspaces are nescessary for the shell, otherwise it'd interpret whatever is inclosed in --> ` <--- as command.

The problem is, I need to know wether a line has changed or not. The command always returns 0 (that is a success in Unix/Linux) if it was successfully executed, no matter wether or not a line had been changed, and produces no output at all.

Could anyone tell me how I get that info?

Batch Mode
I understand that to use batch mode it is necessary to put the commands you want to run into a file before telling MySQL to read its input from the file, but how do I create the file and what do I create it in? Also, I'm not really sure how you would then tell MySQL to read it.

Using Batch Files
I have a small problem with using batch files (platform: Windows XP). The problem I have is that I do not want to place and call all my db batch files from within BIN folder, but rather from, say c:a1a2a3. Calling a batch file in Windows like this is not an issue:

mysql>source c:/a1/a2/a3/test.sql;

Then, you can call test.sql from test2.sql, for example, by the same line of code:

source c:/a1/a2/a3/test.sql;

Is there any way to keep the base direcory, i.e. c:a1a2a3 in some sort of environment or whatever variable and then use it in the parameter passed to the source command.

I have experimented with user-defined variables to no avail:

SET @BASEDIR = 'c:/a1/a2/a3/test.sql';
SOURCE @BASEDIR;

Batch Mode
I want to execute the queries wriiten in a .sql file in a single attempt using the batch process of mysql.Since I don't have a good grip over the database systems, I am confused how should I go for it..

Using Batch Mode
can someone show me the way of using batch mode?
what is the syntax of it?
what should i do for the first step?
can someone tell me about that?

Getting Error In Batch Update
I am using Mysql 4.1.13 with Tomcat5 and Connector mysql-connector-java-3.1.10. When I try to update the table in batch mode sometimes( When server is idle for a long time) get following error. Code:

Consecutive Ids In A Batch Insert?
I am attempting to batch insert 100 rows into a table, and I need to know the 100 id's of all the inserted rows.

I know that I can find the id of the last inserted row despite my app
being multi-threaded, since the select last inserted command is
connection-specific.

Given the id of the last inserted row, can I back out all the ids of
the inserted rows? Are the inserts consecutive in an auto-increment
table?

If not, I will be looking to lock the table during inserts, but that
seems very sub-optimal.

Running mysql 5.0.24

Consecutive Ids In A Batch Insert
I am new to mysql and would appreciate any advice that you have to
offer.
I am attempting to batch insert 100 rows into a table, and I need to
know the 100 id's of all the inserted rows.

I know that I can find the id of the last inserted row despite my app
being multi-threaded, since the select last inserted command is
connection-specific.

Given the id of the last inserted row, can I back out all the ids of
the inserted rows? Are the inserts consecutive in an auto-increment
table?

If not, I will be looking to lock the table during inserts, but that
seems very sub-optimal.

Batch Mode Problem
I am having a problem with 2 sql statements in 1 batch file. Only the last
statement executes. If I separate the statements into individual files they
work fine.

Are you only allowed 1 statement per file?

Here is the contents of the batch file:
insert into monlog (monlog_locno, monlog_custno, monlog_time,
monlog_inputdate, monlog_errno) select loc_no, loc_custno,
(hour(now())*60)+minute(now()), now(), 200 from loc where
loc_next+29<(hour(now())*60)+minute(now())+30 and loc_errno>=200 and
loc_errno<=249;
update loc set loc_errno='250' where
loc_next+29<(hour(now())*60)+minute(now())+30;

Help Please Running Mysql On Os X
I am very new to mysql. I am using the sitepoint book 'Build your own Database Driven Website using PHP and MYSQL. I have installed mysql mysql-5.0.41-osx10.3-powerpc. Under preferences/mysql it indicates that the mysql server is running. When I try to access mysql through the terminal window I get a message - command not found. Or no permissions. I can go to the bin directory and list the contents - they seem to be there. I can't get past page 21/22 Post-installation setup tasks. I am also new to using the terminal window. I suspect I have missed something really basic - any suggestions on how I can get up and running would be much appreciated!

Need Some Help In Getting MySQL Set Up And Running.
I wasn't sure if someone would be able and willing to help me, but I am new to installing MySQL on a Windows 2003 server (actually .. any server), but I am installing it on a Windows 2003 server right now. I also installed a new version of PHP on the same server. Everything looks fine, but when I go to phpinfo.php I don't see anything saying MySQL. (I saw somewhere that if MySQL is installed properly, it should be displayed on the phpinfo.php page.)

I am currently trying to set up and run Cerberus Help Desk, but it isn't finding the MySQL database that I installed.

So, I would really appreciate it if someone could not just help me via email or postings in here, but actually by using Instant Messenger (AOL, Yahoo! or MSN) to walk me through the set up, testing, etc. so I can get things up and running properly.

I was hoping that someone with more experience than I might be willing to help me in this manner.

Could Not Get MySQL Running
I went to MS-DOS to start the MySQL application by typing mysqld into the bin directory of where I installed MySQL but once I did typed the command and pressed enter this is all I got :

A moment passed before I am shown the path of bin with no indication of MySQL server started. And also I have installed this as a window service.

Running MySQL 5.0
I've just downloaded and installed 5.0 on my Pc/ win 98se.
When I run the program MySQL Command Line Client, it appears to run for a second then disappears. I see the program flash open for a second and then it is gone.Complete newb needs some help.

MySQL 4.1 Running
I've been running mysql 4.1 for sometime and using phpMyAdmin (v2.6.2) to administer the databases.

Using php scripts I can also update/insert etc the databases (maintained on my laptop Mac OS X Tiger).

Suddenly, I can't get phpMyAdmin to load in the browser. I've also tried other MySQL GUI tools like CocoaMySQL and Navicat....they won't run either. I downloaded and installed phpMyAdmin 2.6.3-pl1 as well but no improvement.

Why would mysql run but the admin apps not work?

Running MySQL From A CD-ROM?
I have used PHP and MySQL for a few years now. Just today I can across an application that had a MySQL database running locally off a CD ROM. I did not know this was posible. When the application runs it starts up the MySQL database without the user even knowing they are running it. It is pretty amazing.

So I want to do the same thing. Is there any documentation on how to run MySQL locally. I would like to know how to build the database and create tables under these curcumstances.

Running MySQL
I have installed mysql-3.23.54-win and it all seems to have gone OK .... now i want to run it and setup a database. I dont have an icon on the desktop and there is nothing in Start - Programs ..... start it from DOS prompt via C:> C:mysqlinmysql.exe and i get error 2003 cant connect to MySQL server on localhost <10061>

OS Commands
How can i see the free space of the hard disk from mysql;
How can i run commands in os from my sql; In sql i use the comamnd xp_cmdshell whicll will run the commands in the os form inside sql.

Commands
I just installed MySQL onto my XP machine and don't know any syantax. The download location is:
C:Documents and SettingsTimMy DocumentsMySQL

What would a sample command be in the SQL command line? What would one be in the Windows Command prompt?

How Do I Upload A File To MySQL Database And View That File
I do not know how to upload a file to MySQL database and view the posting file on the webpage. I followed the code on-line that show me how to upload, but when I click "browse" to open my file in local drive and click "upload". Nothing happen ! How can I view it on the web page to make sure it was saved in data base.

Prepared Statements (for Batch Inserts)
I am looking into using prepared statements as a possible performance optimisation in my application. I am currently using a batch insert statement:

insert into mytable values (d11, d12, d13), (d21, d22, d23),...,(dn1, dn2, dn3)

The database insert/select logic is multi-threaded. The number of rows that are to be inserted in the batch is variable. I am using Connector/NET to access MySQL from the application.

MySqlCommand as far as I can tell is not thread safe which means creating a new command instance for each statement the application needs to execute for each thread.

How does this affect prepared statements ? Are they automatically cached under the hood so if a new MySqlCommand is created using a previosuly prepared statement, is this handled so as to prevent "preparing" the statement again ? Do I need to cache each MySqlCommand instance for each unique prepared statement ?

What performance gains if any will I get if I use prepared statements instead of non-prepared statements for batch inserts as shown above?

How would I define the prepared statement parameters for a batch insert with a variable number of rows ?

See Feedback Message On Batch Mode
I wrote a perl program to update/insert mysql
database. It worked fine but I could not see the feedback message to
show how many rows updated or inserted. I can see that if I issue SQL
commands interactively.

BTW, what's the easiest way to check return code from SQL commands in
perl. I use korn shell script to invoke perl script that does SQL
stuff. I need to pass the return code back to shell script.

See Feedback Message On Batch Mode
I am new on mysql. I wrote a perl program to update/insert mysql
database. It worked fine but I could not see the feedback message to
show how many rows updated or inserted. I can see that if I issue SQL
commands interactively.

BTW, what's the easiest way to check return code from SQL commands in
perl. I use korn shell script to invoke perl script that does SQL
stuff. I need to pass the return code back to shell script.

Tabs Missing In Batch Mode
in a shell script of mine I'm executing a mysql SELECT in batch mode. I
want to use the return value for other sql statements.
The problem I'm facing is that the tabs, which seperate the columns
from each other, are missing in the output.
What am I doing wrong?

#!/bin/sh
QUERY="select id, i.handle from idmap i, person p where
i.handle=p.handle and email="$1""
mysql mydatabase -pmypassword -N -B -e "$QUERY" | while read line ; do
personid=$(echo $line | cut -f1) ; handle=$(echo $line | cut -f2 ) ;
echo $handle ; done ;

MySQL Is Running, But I Can't Connect
I really need some help with this. MySQL will not start on boot despite
everything I've done to make sure that it is set to do so.

When I start it as root from a terminal with "/usr/bin/mysqld_safe
--skip-grant-tables &" I show several mysqld procs. and one mysqld_safe,
but I can't connect:

[root@adamsmdk madams]# ps -aux| grep mysql
Warning: bad syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html
root 3650 0.0 0.1 2276 664 ? S 11:09 0:00
/usr/lib/courier/authlib/authdaemond.mysql start
root 3654 0.0 0.1 2316 792 ? S 11:09 0:00
/usr/lib/courier/authlib/authdaemond.mysql start
root 3655 0.0 0.1 2276 664 ? S 11:09 0:00...............

Gdb Bus Error When Running App With Mysql 5.0
When linking with mysql 5.0 library, I can't no longer use gdb to debug
my app anymore. It cores immediately with a bus error. I've also
noticed that the size of the executable is more than doubled compared
with one linked with mysql 4.0