mySQL Console
MySQL Console (mysql.exe) is a command-line sql interface.
It allows you to enter any sql command - Connect, Create, Select, Insert, and the like -
or to run a script containing those commands.
This program
Running mysql.exe via Windows Explorer
| Comments
| mysql.exe Commands
| Debugging Scripts
- Registry Mods
Running mysql.exe via Windows Explorer
The simplest way to run mysql.exe (IMHO) is to
- Right click an *.sql file
- Select mySQL Console from the menu
To make this work, I had to modify the registry
This is a possible *.reg file - on some systems, the .sql key may already exist.
[HKEY_CLASSES_ROOT\.sql]
@="sqlfile"
[HKEY_CLASSES_ROOT\sqlfile\shell\mysql\command]
@="cmd.exe /k \"\"C:\\wamp\\bin\\mysql\\mysql5.5.24\\bin\\mysql.exe\" --verbose < \"%l\" \""
|
Notice that
- Double quotes (") and backslashes (\) have to be escaped
\" and \\
- The entire string is in regular double quotes
If you do it manually, enter this
cmd.exe /k ""C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe" --verbose < "%l" "
|
- cmd.exe /k
- This causes the command window to stay open after the script completes execution
so that you can see the errors (if any - I usually get a few at first).
Double quotes are required around everything after the /k.
- "[path]\mysql.exe"
- The double quotes are required around the fully qualified program name
if it contains a space, path is usually system dependent
- --verbose
- This tells the program to display more data - without this, debug is more difficult
- < "%l"
- (That is a lower case L) This tells the program to load the file you right clicked
and to use the long path name
Comments
There are 2 methods to enter comments
- Single line comment - Double dashes (--) plus a space
-- any comment, can start after sql statements
-- must have a space
--This will cause a problem
|
- Block Comment - /* .. */
/* any comment, can use several lines */
|
mysql.exe Commands
In mysql.exe you can enter normal sql commands, terminated by a semicolon.
Several other "special" commands are supported
?
help;
Show the available "special" commands
| help set;
help show;
Show help for the specified command -
set and show in these examples
| show variables;
show variables like 've%';
Show all the current option settings
like allows wild cards % (any) and _ (one)
| delimiter ^;
delimiter ;^
Change the command terminator to ^ and back to ;
These commands are needed to define triggers and stored procedures
| source filename;
\. filename;
Read commands from the specified file
| show databases;
| List all the available databases (directories).
| use database;
connect database;
Select a directory that will be used if one is not applied.
connect works, but is not in the help.
| show tables;
show tables in database_name;
List all the user tables
| show create table TableName;
| Show the table's structure - table names are not case sensitive
| | | | | | | | | |
If you enter a command without a terminator (semicolon), the prompt changes from
mysql> to -> (continuation).
This allows you to type commands on several lines -
to return to normal mode, enter the current terminator and press enter.
There are no commands to perform the following function.
set echo on;
| With Firebird and isql.exe,
I use this to debug script errors when the script is run via a batch file.
With mysql.exe, add -v to the command line. There is no way to
control this feature from within the console.
| |
Debugging Scripts
Using mysql in batch mode, it is a challenge to debug scripts - this should help.
When running a script, it is likely that an error will be displayed ...
but the command that caused the error will not be displayed.
There are 2 ways to display where the error is
- Use -v (or --verbose) on the command line
- Use select statements to write comments to the screen
select 'Just a comment' ;
|
produces
| Batch mode with just one v
| When typed in interactive mode
and batch mode with -vvv
|
--------------
select 'Just a comment'
--------------
Just a comment
Just a comment
|
|
+----------------+
| Just a comment |
+----------------+
| Just a comment |
+----------------+
1 row in set (0.00 sec)
|
|
Registry Mods
These are several registry modifications that will simplify debug.
Only the commands are discussed below - a
reg file
is provided to modify your registry.
Please view and verify the content before merging the data ...
fixing any paths as appropriate.
(For instance, mysql.exe may be in a different directory on your system.)
Note - if your system already has commands associated with *.sql files, this *.reg file will ignore
and replace them. Please check first.
| Only show errors
| cmd.exe /k " "C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe" < "%l" "
Show All
Allows you to see which
lines caused the errors
| cmd.exe /k " "C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe" --verbose < "%l" "
output -> debug.txt
Write to file
| cmd.exe /k " "C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe" --verbose < "%l" > debug.txt "
debug -> notepad
Write to file and then display it
|
cmd.exe /k " "C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe" --verbose < "%l"
> debug.txt && notepad.exe debug.txt "
| | | |
In the first example, the code simply executes the sql file and displays the errors (if any).
This simplifies checking for errors.
The others display the sql text and associated errors - this simplifies debug.
For simple problems, I prefer to display the errors in the command window ...
but for more complex errors, or large files where I want to search for the errors,
then notepad is more convenient.
The last command (actually entered on a single line, wrapped for readability)
produces a file that contains the console output and then opens that file in notepad.
Notice that cmd supports multiple commands separated by
| & | Execute the next command as soon as possible
|
|---|
| && | Wait for previous command to finish without errors, then continue
|
|---|
Because a single ampersand does not wait for the previous command to complete
a double ampersand is required before running notepad,
otherwise notepad opens with only part of the data.
A few mysql.exe command line options
| < "filename"
| Input file - may be fully qualified
|
| > "filename"
| Output file - may be fully qualified
|
-v
-vvv
--verbose
| Causes completed commands to be displayed so that the errors are
easier to locate
Seeing the source would be better, but is not available
|
-u username
--user=user_name
| sysdba - overrides the default in the config file
|
| -p password
| whatever you want, if it is omitted, then you will be prompted
-- except when executing external scripts
|
There is an interesting problem - if a user name is not provided,
the default from the config file is used.
Even though there is no associated password, batch files seem to work fine.
They are able to drop and create databases without authorization.
When I open mysql.exe directly, I am prompted for a password.
However, there is never a prompt with a script/sql file.
You might assume that the first line of the file is treated as the password ..
but that text does not work in interactive mode.
Reference -
4.5.1 - The MySQL Command-Line Tool
Registry Mods for *.sql files
This is a more complete registry definition.
It includes notepad so that
accidentally double clicking an sql file will not actually run the script.
(That would be very bad :)
| <![CDATA[
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\.sql]
@="sqlfile"
[HKEY_CLASSES_ROOT\sqlfile]
"Readme"="Created via script 07-09-12"
@="sql queries"
[HKEY_CLASSES_ROOT\sqlfile\shell]
@="notepad"
[HKEY_CLASSES_ROOT\sqlfile\shell\mysql]
@="mySQL console"
[HKEY_CLASSES_ROOT\sqlfile\shell\mysql\command]
@="cmd.exe /k \"\"C:\\wamp\\bin\\mysql\\mysql5.5.24\\bin\\mysql.exe\" --verbose < \"%l\" \""
[HKEY_CLASSES_ROOT\sqlfile\shell\mysql_vvv]
@="mySQL console -vvv"
[HKEY_CLASSES_ROOT\sqlfile\shell\mysql_vvv\command]
@="cmd.exe /k \"\"C:\\wamp\\bin\\mysql\\mysql5.5.24\\bin\\mysql.exe\" -vvv < \"%l\" \""
[HKEY_CLASSES_ROOT\sqlfile\shell\notepad]
@="Notepad"
[HKEY_CLASSES_ROOT\sqlfile\shell\notepad\command]
@="notepad.exe %1"]]>
|
Using IE, you could just copy the above text to the clipboard and use that to make
your own *.reg file.
However, that won't work with Firefox (yet another reason I truly dislike that product.)
Therefore, I have provided the following
reg file
you can download - be sure to modify it for your system before using it.
Author:
Robert Clemenzi