MYSQL :: Running A Sql Batch Script From The Linux Command

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 Mysql Commands In A Batch File
I am trying to automate a mysql command to upload data everymorning on a windows server. I have created a .bat file and entered the commands to login to mysql but when it gets to the mysql prompt it fails to complete the rest of the commands.

cd
c:
cdmysqlin
mysql
TRUNCATE TABLE blah
LOAD DATA INFILE 'blah.txt' INTO blah
QUIT
exit

Is this possible?

Running 4.1 And 5.0 On Linux
Question: How can I run 4.1 and 5.0 simultaneously on linux - Suse 9.3
in particular? My goal is to do no harm to my current 4.1 setup. Can
the 4.1 and 5.0 rpm's play nice together? Can I use the rcmysql
commands for running multiple versions?

MySQL Running In Linux With Apache2
I have Fedora Linux running on my box. I have installed Apache2, PHP, MySQL. I used YUM to install everything.

yum install httpd

yum install php

yum install mysql-server mysql

yum instal php-mysql

What I want to know is that how can I start playing around with all this cool programs. I want to learn as much about all these awesome programs. I would like to install some kind of control center for MySQL. I heard MyPHPadmin is good. But how do I install it?
Can I do it through yum? Where can I find a good newbie guide?

thanks in advance :-)

Fedora Project
Information is Power!
I am doing this for the love of MySQL.

Running Queries To Mysql In Linux
How i use mysql in linux without the client and with it?

Mysql Running Slow On Mandrake Linux 10.0 For AMD 64
I'm having a problem with a new machine running Mysql version 4.0.18
on the AMD64 version of Mandrake 10.0. The new machine has got 64bit
AMD processor and 2GB of RAM.

Nearly all queries(updates are especially slow) are running 20/30 or
more times slower than on an inferior(32 bit processor, 1.5GB RAM)
machine runnning Mysql 3.23.56 on mandrake 9.0.

As far as I can tell mysql is configured correctly for the power of
the machine. The my.cnf file looks like this: Code:

How Do I Create A Database On Linux Machine Running Mysql?
I have an sql script file that is supposed to create
a set of database tables for me. Supposedly I type
the following on my linux box and its all supposed
to work...

mysql (ENTER)
. mysql_dump.sql.txt

When I type myslq <ENTER> I get a command prompt
that looks like this...

mysql>

But I don't know how to change directories from that
command prompt to the directory where mysql_dump.sql.txt
is located. Can someone tell me how to change dirs at
the mysql> prompt?

Running A Php Command A Certain Time
Is there any way that I can my php page run itself at say 12am every morning? This is so I can save the mornings beginning information in my mysql database?

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?

Running An SQL Command On All Tables Of A Database
Using MySQL 5.0 and only SQL, what's a way to run a SQL command on all tables of a database?

The command would be something like: delete userID from tblUser where userID = 'abcd1234';

Running MySQL's 'source' Command
I am having failures processing the following command and I wonder
if you can tell me what I must do in order to have success. When I try
to run source mysql_dump.sql.txt ==> it is a problem for me.

1) I put the file in /home/mlh/public_html/credifree/sql_script/.
2) I made that directory my current directory
3) I typed mysql and pressed ENTER
4) I then typed source mysql_dump.sql.txt and pressed ENTER

A bunch of error messages saying
ERROR 1046: No Database Selected
filled up the screen.

Well, of course there was no database selected. I do not yet have a
database. I thought the whole point of running
source mysql_dump.sql.txt
was to CREATE a set of database files on my linux box. Is that
incorrect? What must I do to avoid this error 1046?

And now, the last question...
Can you tell me where the database files will be placed when running
source mysql_dump.sql.txt as I described above from the same current
directory as described above? I do wish to KNOW in advance where the
files will be created.

Running A Modify SQL Command, When A INSERT Is Done On A Table
I have an encrypted PHP script that runs on MySQL back-end.

I want to modify the record entered by the script to the DB table, after every INSERT record..Since script is encrypted I can't change anything from PHP end..

How can I do this?

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?

Execute Mysql Command On Mysql 4.1 On Linux
I setup Mysql 4.1 on a linux box. But when I type 'mysq', I get this following error:
# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

even if I specify user name and password, like this mysql --user=root --password=
but that still does not work.

I was able to connect to that server using MSQL administrator with 'root' and no password. But when I telnet to that box and type 'mysql' and the xterm, I get this 'access denied' message.

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?

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?

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;

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.

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.........................................................................

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.

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?

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 ;

Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source.

Each productID has a number of partID's associated with it and those partID's are stored in my database.

I would like to create a list of products sorted by the number of parts each has.

If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server.

Option A)

SELECT productID, Count(*) counter
FROM part
WHERE productID
IN(large list of productID's received, maybe 1 or >10000)
GROUP BY productID
ORDER BY counter DESC
LIMIT ?, 10

Option B)

Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query.

Option C)

???

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 Two SUM
SELECT SUM(nlamount WHERE nlaccount='3202' THEN 1 ELSE 0 END) AS receivables, SUM(nlamount WHERE nlaccount='3207' THEN 1 ELSE 0 END) AS receivablesvat
FROM nominalledger
WHERE nlcompanyid=$companyid
AND nldate >= $company_end_year
GROUP BY nlcompanyid

I want to run this script, I basically want to add up nlamount twice, when nlaccount=3202 and nlaccount =3207
i have got te syntax wrong though in the SUM command, I have tried googling it, but there are only basic tutorials in sql outside the SUM instead of inside the SUM

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.

Is There A Way To Calculate Running SUM
I have a table (T1) consisting of 2 columns, Date and Observerd as follows:

Date Observed
4/1/07 2
4/2/07 4
4/7/07 3
4/10/07 5
4/12/07 1
4/18/07 7
4/22/07 2

and we want to write a query to create a table or a view with the following data:

Date ThreeDaySum
4/1/07 9 <-- (2+4+3)
4/2/07 12 <-- (4+3+5)
4/7/07 9 <-- (3+5+1)
4/10/07 13 <-- (5+1+7)
4/12/07 10 <-- (1+7+2)

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 Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

#1064 - While Running A Doc Example
I plan to use the function ADDTIME(expr1, expr2) in an SQL request. I looked at the doc and tried the normal syntax, but I have always the following error even if I enter the example from the doc, why ?

#1064 - You have an error in your SQL syntax.

Even if I enter SELECT ADDTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");

I am using MySQL under Windows 2000 using EasyPHP 1.7
(Apache 1.3.27, PHP 4.3.3, MySQL 4.0.15, PhpMyAdmin 2.5.3. )

Slow Running Sql
i am not using mysql but an unknown database system on a unix box - i have no control over the database but have purchased an odbc driver that seems very 'clunky' after using mysql - this is an sql statement question rather than a mysql tech question. if i run this:

SELECT
MK_01_vehicleRecords.registrationnumber, MK_01_VehicleRecords.vehiclenumber
FROM MK_01_VehicleRecords
WHERE (MK_01_VehicleRecords.vehiclenumber = '36176')

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.

Running Balance With Where
I am using the following query:
SET @bal1=0,@bal2=0,@bal3=0,@bal4=0,@bal5=0,@bal6=0;
SELECT Id, Date, FCode, Descr, Debit, @bal5 := @bal5 + DebT AS RunDeb, Credit, @bal6 := @bal6 + CreT AS RunCre, @bal2 := @bal2 + net AS RunBal, Rec
FROM (
select
fin_records.id,
fin_records.id_production_unit as PUnit,
fin_records.id_bank_account as BAcc,
date_format(fin_records.fin_record_date,'%d-%m-%y') as Date,
fin_accounts.fin_account_code as FCode,
fin_records.fin_record_desc AS Descr,
fin_records.fin_record_debit AS Debit,
@bal3 := fin_records.fin_record_debit AS DebT,
fin_records.fin_record_credit AS Credit,
@bal4 := fin_records.fin_record_credit AS CreT,
@bal1 := fin_records.fin_record_debit - fin_records.fin_record_credit AS Net,
fin_records.fin_record_rec AS Rec
from fin_records inner join fin_accounts on fin_accounts.id=fin_records.id_fin_account where (fin_records.id_production_unit='$up') and (fin_records.id_bank_account='$ba') and (fin_record_date between makedate(year(curdate()),1) and makedate(year(curdate()),365)) order by fin_records.fin_record_date,fin_records.id
) AS tmp;

I should build another column to get RunBal2, that should include only records with Rec = 1.

Mysqld Is Not Running
cannot seem to run mysql commands, although queries seem to work well as I can make databases and view tables. Hope that makes sense.

I installed mySQL 5 on Windows XP and tried to stick with the default installation options and paths. I lauch mySQL from this: ""C:Program FilesMySQLMySQL Server 5.0inmysqld", enter a password (but not a username so I'm assuming that's root user), get a mysql> prompt. So far so good.

The following commands do not seem to work though:
mysqladmin version
mysqladmin variables

Typing these commands then clicking the return key brings up the -> prompt. I've checked the processes and "mysql.exe" and "mysqld-nt.exe" are running. From a c: prompt I tried to install mysqld and was told it is already running. What am I missing here? I'm sure it is something very simple...

Ultimately I need to bring in a table from a SQL dump from another system. I've tried using variations of: mysql -h host -u user -p dbname < dumpfile.sql

I'm combing through the docs, but still don't have a clue.

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.