MYSQL :: Using Batch Files

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.

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

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:

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 ;

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

???

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?

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)

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?

Log Files?
im trying to get general query logging and / or binary logging to work but i can`t seem to find the files or tell if logging is actually on.

I start my service like this
/etc/init.d/mysqld start
with -l test, or --log=test, or --log-bin=test1
and i don`t seem to be able to find these files.

if i look in var/lib/mysql i see mysql.sock, ibdata1, ib_logfile1, ib_logfile0, and my database folder and in it is the db.opt and .frm files.

Am i missing something, is there something else i am supposed to do?
is this the right place to find the log files?

i also looked in var/log and found the mysqld.log but nothing else pertaining to mysql.

3.23.54 Log Files
I'm using mysql 3.23.54 over Red Hat Linux 9. I'm having problems with
the error logs.

I've run mysqld start --l, mysqld start --log, mysqld start
--log-error, and mysqld start --log=MyErrors.log and not once managed
to find a log file.

I'm running mysql and then issuing:- SELECT * FROM myFile WHERE 1;
which gives ERROR 1046: No Database Selected. That should surely be
logged in a file somewhere.

I'm using:- find / -amin -5 to select anything altered in the last
five minutes, after many fruitless hours trying to find the correctly
named files (from <http://dev.mysql.com/doc/mysql/en/query-log.html>).
I can't see anything containing that error.

Ultimately, I'm trying to work out whether a J2EE app has connected
with mySQL. I want to see whether the CORBA error I'm getting is
because mySQL rejected the request, or whether it's from CORBA itself.
If mySQL rejected it, there'd be an error message. I just can't see
any error message store at all.

MP3 Files
Can you store MP3 files in a MySQL database?

Files In C: ?
I just installed the newest MySQL. I noticed there were files put in my C:. t1l4 and
t1l4.1

Is this normal? I used past versions and never had them installed. When I disable Mysql it deletes the files.

.frm .myi And .myd Files
I have an access databse which i migrated using the MySQL Migration Toolkit. It migrated successfully, however only .frm files were created. Should any .MYI or .MYD files have been created?

As the database is for a univeristy group project, we are going to need to transfer MySQL databases between the students in the group, but were not too sure how to do this correctly, I just tried sending the .frm files to a friend of mine and he got a number of "table does not exist errors."

Were creating an application in VB.NET initially it was using access, but there will be too many records for access to handle so we've chosen MySQL, we will need to also frequently create backups of the database, is this possible?

3.23.58 Log Files
I'm running MySQL 3.23.58 which came with Red Hat Enterprise AS 3.0 and am
having a few problems with starting it up if I modify the /etc/my.cnf file
to try and log the SQL running etc.
The essentials are:

Stopping and starting the database via: /etc/rc.d/init.d/mysqld [stop |
start]

Trying to write binary logs (and the others such as logging queries and slow
queries via entries in /etc/my.cnf).

Am getting errors in /var/log/mysqld* complaining about the files not being
present, therefore cannot use the files I specified in /etc/my.cnf.

Get Rid Of Bin Log Files
I have a stand alone MySQL Server (4.1) for my web-aps running on Debian 3.1.
I found several bin-log files with a size of 1,1 GB.
Furthermore they are two years old.

I would like to delete these files to clean up my disc.
Can I just delet them, or do I get any problems?

As I understand it, these file are for replication services.

No CNF Files
I downloaded and installed the non-installer Windows version of MySQL
tonight, but I can't find the .CNF files that I have to copy to the
systemroot.Can anyone tell me where to find these files, or maybe someone can post the
files here?

.frm Files
I'm having a problem:

I reformatted my HDD and the only backup files I can find for my MYSQL databases are the .frm files from my data folder. If I try to copy these into my new installation of MYSQL, then try to query them, MYSQL tells me the table doesn't exist.

Does anyone know if it's possible for me to retrieve my table structure?

FRM And MYD Files
I've typically backed up my dbs by copying MYD/FRM/MYI files.
I found this on another website:
<< Copies to the backup directory the minimum number of table files needed
to restore the table, after flushing any buffered changes to disk.
Currently works only for `MyISAM' tables.  For `MyISAM' tables, copies
`.frm' (definition)  and `.MYD' (data) files. The index file can be
rebuilt from those two files. >>
So my question is... HOW?  How can I regenerate all MYI files from MYD and FRM files

Get My MYD And MYI Files
I just only have one little question on my topic.
I have started up MySQL and am still learning how to type in functional queries , still a little rusty but managable.
However when I created a table , I could only see my FRM file. I could not see my MYD and MYI files. Why is this so ? I would like to have help in creating these files so that I could bring them to work.

MYD Files
I have these files and I want to restore the data to mysql
Can someone tell me how to do this in linux?
/translog.MYD
/ronuke/translog.MYI
/ronuke/translog.frm

The reason behind using these files is because mysqldump has given me a 1 GB file which I am not able to run...

Log Files
One of my table is turncated and i dn't have any data in that...
I need to know who has done this or i need to know the where the log is kept in mysql and
the resaon for that...And my database engine is InnoDB and I have soem log files if i open that i get the data in encrypted form..
I use the sqlyog

.sql Files
I'm fairly new to this..
I get this on virtually every sql file I try:

Quote: ERROR 1064 at line 14: 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 'DEFAULT CHARSET=utf8' at line 23
I get the same error (with a different "problem line" in quotes) even if I change what seems like it'd be the problem line...

3.23.58 Log Files Etc.
I'm running MySQL 3.23.58 which came with Red Hat Enterprise AS 3.0 and am
having a few problems with starting it up if I modify the /etc/my.cnf file
to try and log the SQL running etc. (apologies but I'm doing this from
memory having left my notes elsewhere). The essentials are:

Stopping and starting the database via: /etc/rc.d/init.d/mysqld [stop |
start]
Trying to write binary logs (and the others such as logging queries and slow
queries via entries in /etc/my.cnf).
Am getting errors in /var/log/mysqld* complaining about the files not being
present, therefore cannot use the files I specified in /etc/my.cnf.

I suspect this is something to do with permission, but any help is most
gratefuly received (apologies in advance for the lack of exact detail in
this posting).

Frm Files
i instelled mysql server and EMS mysql designer. i created a database but it seems to be kept in one folder and contain of different frm files.
how do i convert them into one sql file?

Put Files For Use
I am not sure where I can put my csv files to be accessed by the server the databases are stored on? We have a dedicated server with a mysql database on it that holds databases for several websites. So ideally I would like to put my csv files in /var/home/www/public_html/site_name/database_files
I am not sure what I need to set up permission wise for this though. Where can I stick them to get them working without any config changes?

MYD MYI And Frm Files
I currently have these files from a database. I don't have a way to do an actual dump from the original database.
How can I create a database with these files that I can in turn dump?
I will have to do this locally on a windows machine.

View SQL Files
I am about to by the content of a site, but it is in sql files. I need to view this content and extract to make a new site from it in html format.

How do I view SQL files and extract it content? Is there any program that does it?

Can I View .frm, .MYD And .MYI Files?
At my current host, the server crashed and all data seemed to be lost. Lucky for us they had had a backup of all data which they put back on the server.

But now I get errors with some tables in phpMyAdmin where they show up as "in use" and when I view them I get the following error:

MySQL
#1033 - Incorrect information in file: *.frm
or

MySQL
#1016 - Can't open file: '*.InnoDB'. (errno: 1)

Because of the issues, they send me the *.frm, *.MYD and *.MYI files.
But am I able to view these files to see the structure or data?
And can I verify if structure or data is corrupt?

Physical Files
I am going through backup and restoring operations and trying to get more familiar with them. In my mysqldata directory I created a couple databases and some tables in each. I only see files with filename.frm in the database directories that I created. However in other directories I have file extensions of filename.MYI and filename.MYD? I recently changed mysql default storeage engine to use innodb and am wondering if that has something to do with it?

What Are These Ib* Binary Files?
I notice in the mysql/data directory the following binary files:
ib_arch_log_0000000000
ib_logfile0
ib_logfile1
ibdata1

Some are rather large. I can't seem to find any explanation in the
documentation. What are they? What needs to be done with them?

MySQL & Files
Does anyone know how I can store files (mostly pictures) into a mySQL
table? I have tried the load_file function but it seems to not work.
i just get NULL as the value.

Any examples would be most appreciated. I am using RH9 and the latest
version of mySQL.

I know I should be storing the links on disk and the save a pointer in
the table but I want to put the files actually in the database.