MYSQL :: Batch Mode

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;

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 ;

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?

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

How To Find The Mode?
I have a table with 80,000 records in it, each with a field named 'referral_id'.

I need a way to find which number is the mode in the field 'referral_id', as well as the 2nd mode (2nd most recurring #), 3rd mode.....nth mode.

I know this is going to be a pretty inefficient query (but I have no other options at this point).

NO_BACKSLASH_ESCAPES Sql Mode
we have a table which contains file paths. Some of the clients are on Windows machines so the paths are like for example

C:Documents and SettingsFredlahlah

if I do the following query

select count(*) from table_name where path like 'C:Documents%'
result is zero rows

change query to
select count(*) from table_name where path like 'C:Documents%'
result is 37 rows (correct answer)

As far as I have understood if I set the SQL mode to be NO_BACKSLASH_ESCAPES I should get the correct answer in both cases above.

Please dont suggest I double backslash - I cant in our real example.

I DONT

any clues anyone?

STRICT_TRANS_TABLES SQL Mode
I have been using MySQL in a windows environment now for a couple of years. I have used 4.x and now have been using 5.0.

I often had problems with v5 and eventualy tracked it down to the SQL mode STRICT_TRANS_TABLES which by default is enabled when using the MySQL MSI installer.

I have read the documentation at MySQL website on SQL modes but I am still trying to understand what this SQL mode is/does exactly.

Can someone explain this SQL mode to me? What is the purpose of this mode and how does it differ from default SQL mode used in 4.x?

Strict Mode
I changed to strict mode (STRICT_ALL_TABLES), and it's okay.
Only problem is that I would like to allow to insert characters into
the integer columns.

Example, before I changed to the strict mode, I was allowed to insert characters into the INT field, and mysql convert them to the 0.

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.

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.

Excuting MySQL In DOS Mode
I am in DOS mode. ( I don't know how to do in DOS mode.)

It shows like the below.

mysql> select * from mysql
->

If I press enter key, it produces "-> ," I like to stop generating "->,
and execute the code "select * from mysql"

how can I exeute the code "select * from mysql" and show the result?

Mysql Access In Dos Mode
(1) I am using XP.
(2) I installed mysql, apache, and PHP a few days ago.
(3) there is phpMyAdmin folder in "htdocs" folder of my Apache.

I can't connect to mysql in dos mode.

I entered "mysql" in (C:/mysql/bin/).

Then, It says "ERROR1045: access denied for user 'ODBC@127.0.0.1'<using password: NO>"

(Q1)what does that mean "ODBC" in your guess?

(Q2)what is "127.0.01"?
It seems like "IP". but my IP was different number as I checked.

So, I enter "mysql -uroot -p".
then, It ask "password".

Because I created a new user and the password with the phpAdmin tool,
I entered the password.

It says "ERROR1045: Access denied for user 'root@127.0.0.1'<using password: YES>"

(Q)Do you see any problems in my approach to learning mysql and phpAdmin?

(Q) why does it deny my access to it?

(Q) what should I do for the access?

Unix Socket Vs TCP Mode
Our mysql database always run smoothness, but there have a strange question always happy.
We retrieve content using unix socket mode is always Correct, using TCP mode sometime will meet no return Error. What's wrong with TCP mode?

For example, We need to retrieve content from table Tab_Memo, the condition with â€˜largeâ€™ is over 20KB, with â€˜smallâ€™ is about â€˜5KBâ€™.

Using unix socket mode:
----------------------------------
mysql -u root -p password
1ã€select * from Tab_Memo where name = â€˜largeâ€™; // The result is Correct.
2ã€select * from Tab_Memo where name = â€˜smallâ€™; //The result is Correct.

Using unix socket mode
----------------------------------
1ã€select * from Tab_Memo where name = â€˜largeâ€™; // NO result return.
2ã€select * from Tab_Memo where name = â€˜smallâ€™; //The result is Correct.

Starting In Binary Mode
I have installed mysql 5.1 onto one of our Redhat Enterprise 3 test boxes. Theres nothing particularly important on the box as far as I am aware.

Anyhow I stopped the server yesterday, with the idea of starting it in Binary Mode. I am looking into how we can do the backups for the system. Since we are due to inherit some mysql databases to look after. Code:

Output In Sql Mode Of GNU Emacs
I am using WinXP, GNU Emacs 23, MySQL 5.
When I start a mysql process with `sql-mysql' in emacs, no output is
displayed in the buffer until I quit or kill the process.

After I add the following line into the initialization file of emacs,

(setq sql-mysql-options '("-C" "-t" "-f" "-n"))

the output comes out, but the status line still doesn't show up. (e.g.
1
row in set (0.02 sec))

What's worse, I try to log the session with `T logfile', but no
logfile is generated.

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 ?

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?

Strict Mode And Defaults - Best Practice?
Assuming I have MySQL 5 in strict mode, what is the best practice for dealing with varchar fields (for instance) where no specific default value need be set? Is it best to simply make such a field nullable?

Lock In Share Mode / For Update
In v. 5.0.x you cannot lock rows if the query doesn't use PK or UK. (BUG #12205)
Does this also apply to v. 5.1 ? I have to know, because I want to migrate from 5.0.4 to 5.1.11.

InnoDB Lock In Share Mode
We've just setup a new webspace account with Demon Internet in the UK and
it has PHP/mySQL services

I'm just testing the InnoDB an am having a problem with the following
statement

SELECT * FROM invtest LOCK IN SHARE MODE

Which gives the following error:-

Error

SQL-query :

SELECT *
FROM invtest
LOCK IN SHARE MODE LIMIT 0 , 30

MySQL said:

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 'LIMIT 0,
30' at line 1

I can't seem to find anything about this limit clause/parameter in any of
the help files. Demon say 'Dunno mate we just provide the service !!!'

Useful Relevance With FullText Boolean Mode
Show full header Is there any way to make relevance when using boolean mode more useful? If not, are there plans in the Fulltext development "todo" for making it useful?

I'm thinking of just counting the keyword occurences in records with a PHP script somehow and using that to order the records somehow. Or doing a non boolean fulltext search w/relevance first and then melding that in somehow with a boolean search.

See my results below, my "peeps" are asking me for relevance/ranking - what do I do?

FULLTEXT Search IN BOOLEAN MODE
SELECT id, student_first_name, student_last_name, major, minor ,
MATCH(major, minor) AGAINST ('"mechanical engineering"')
AS score
FROM students
WHERE MATCH(major, minor) AGAINST ('"mechanical engineering"' IN
BOOLEAN MODE)
GROUP BY id, student_first_name, student_last_name, major, minor, score
ORDER BY score DESC

This query in MySQL 4.1.12 using MyISAM tables with FULLTEXT index that
maps to student.major, student.minor, produces results that do this:

1) Matches major/minor "Mechanical Engineering"
2) Matches major/minor "Mechanical"
3) Matches major/minor "Mechanic"
4) Matches major/minor "Engineering"
5) Matches major/minor "Engineer"

I was under the impression, according to this MySQL book I have "MySQL
(Third Edition", Paul DuBois, Developer's Library), in Chapter 2, page
197 that if you group your search keyword in double quotes, you will
get only the results within double quotes IN BOOLEAN MODE.

Apparently this is not happening.

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?

MySQL: Fulltext Mode And Searching More Than One Field
some quick background: I'm using MySQL 4.1.18. This is my first experience with MySQL (or any database system for that matter). I do have programming experience.

Ok, now my table:

restaurant_db
|-------------------------------------------|
| Name[varchar(200)] | City[varchar(200)] |
|-------------------------------------------|
| Jade Palace | Laguna Niguel |
| Jade Palace | Aliso Viejo |
|-------------------------------------------|
Basically I'm storing restaurants inside my database. Each restaurant has two fields: name and city. In the example above, there are two jade palaces (restaurants): one in Laguna Niguel and one in Aliso Viejo (cities).

Now I have fulltextmode working perfectly. Both 'Name' & 'City' are fulltext indexed. But here's my problem. Consider the following query:

SELECT name, city, MATCH (Name,City) AGAINST
('jade, laguna') AS score
FROM restaurant_db WHERE MATCH (Name,City) AGAINST
('jade, laguna')
The above code is stripped straight from the MySQL dev docs and modified for my purposes.

Results:

Jade Palace Aliso Viejo 0.3876339495182
Jade Palace Laguna Niguel 0.3876339495182
From the above, one can conclude that it's simply searching one field at a time and once there's a match, it doesn't bother looking for matches in other fields. In the above case, once it found a match for 'jade' (name field), it didn't bother looking into matching 'laguna' (city field).

My desire is to have the above search results reversed so that the Laguna Niguel Jade Palace comes before the Aliso Viejo one by instructing MySQL to search all fields rather than terminate upon the first match. Furthermore (this is probably obvious), I would like MySQL to weigh results with the most matches higher than those with less matches.

Not Seeing MySQL Prompt In Emacs Sql-mode On Windows...
I've been using emacs sql-mode for some time now (with MySQL) and love it.
I've used it on both Windows and FreeBSD (sadly I do most of my work on
Windows though) and have noticed that the output the prompt and output on
the Windows machine isn't displayed, but is displayed on the FreeBSD
machine......

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)

Unable To Get BOOLEAN Mode Working With Wildcard Matching
I have successfully been able to perform a SELECT query where if someone enters a partial word from the beginning of the word, it will match it correctly from the DB. Such as.

Appl = Apple
Telep = Telephone

SELECT * FROM knowledge WHERE MATCH (subject,question,answer1) AGAINST ('+Appl* +Telep*' IN BOOLEAN MODE) ORDER BY catageory,pkey LIMIT 0,50

This search finds all matches which contain Appl and Telep using the * in the search string.

However I am unable to create a SELECT query which is able to find words which are either the end of a word or something in between. Such as.

ppl or ephon

If I don't use BOOLEAN mode and do a simple search which is something like this
%ppl% it works fine.

Full Text Search "in Boolean Mode"
I've just finished developing a database app for a customer. Part of this requires full text searching. In order to get a full list of results I need to seach "IN BOOLEAN MODE".

I've just discovered that the shared server on which the app is to be hosted is still running MySQL v3 which doesn't support the "IN BOOLEAN MODE" search modifier. Assuming I can't pursuade the ISP to upgrade MySQL (not very hopeful as it is a shared server)

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