A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

–A–

Access, convert from - see convert from MS Access

activity, which processes are running

from the MySQL command line – show full processlist

from the bash command line – mysqladmin processlist

add field – ALTER TABLE table_name ADD field_name;

to a certain location: ALTER TABLE table_name ADD field_name AFTER another_field;

ALTER TABLE Syntax

ALTER TABLE Syntax

affected rows, remove limit of only 1000 – start mysql with mysql –- select-limit 1000000

append unique different records:

insert into cityInfo
      (zipCode, state, city, citySoundex, county, sequence, postalStatus, latitude, longitude)
    select
      distinct c.zipCode, c.state, c.city, c.citySoundex, c.county, c.sequence, c.postalStatus, c.latitude, c.longitude
    from     cityInfoSupplement as c left join cityInfo as n
    on     (n.zipCode = c.zipCode
    and     n.state     = c.state
    and     n.city     = c.city)
   where     n.zipCode     is null

autonumber - CREATE TABLE Location(Location int(11) not null auto_increment);

–B–

blob field, display in MySQL Workbench – for some fields, all you get is a “blob” icon

  1. Go to Edit → Preferences
  2. Choose SQL Editor
  3. Under SQL Execution, check Treat BINARY/VARBINARY as nonbinary character string
  4. Restart MySQL Workbench (you will not be prompted or informed of this requirement).

block size – defaults seems to be 16K

MySQL 8 InnoDB 32KB and 64KB page sizes benefits for HDD says

Starting in MySQL 5.6, the page size for an InnoDB instance can be either 4KB, 8KB, or 16KB, controlled by the innodb_page_size configuration option. As of MySQL 5.7.6, InnoDB also supports 32KB and 64KB page sizes. For 32KB and 64KB page sizes, ROW_FORMAT=COMPRESSED is not supported and the maximum record size is 16KB.

Understanding block sizes says

The database will allocate space in a table or index in some given block size. In the case of SQL Server this is 8K, and 8K is the default on many systems. On some systems such as Oracle, this is configurable, and on PostgreSQL it is a build-time option. On most systems space allocation to tables is normally done in larger chunks, with blocks allocated within those chunks.

How to find out block size of database? suggests querying 3 variables:

select @@key_cache_block_size;
select @@transaction_alloc_block_size;
select @@transaction_prealloc_size;

On a default installation, transaction_alloc_block_size seems to default to 8192; the other two are smaller

We want to match transaction_alloc_block_size to disk. If on Windows, check default C drive space

fsutil fsinfo ntfsinfo c:

And there, Bytes Per Cluster seems to default to 4096 (4 KB). So, if we have an opportunity to specify D drive bytes per cluser, probably best to specify 8K instead.

–C–

commands – usr/bin

command, run a MySQL command from the Linux prompt –

echo 'create index isShip on orderItems (isShip)' | mysql fr > logFile

column, add – ALTER TABLE table_name ADD field_name;

to a certain location: ALTER TABLE table_name ADD field_name AFTER another_field;

ALTER TABLE Syntax

column, change data type - ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

column, delete – ALTER TABLE table_name DROP field_name;

ALTER TABLE Syntax

column, rename – use a CHANGE old_col_name column_definition clause. To do so, specify the old and new column names and the type that the column currently has. For example, to rename an INTEGER column from a to b, you can do this:

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

ALTER TABLE Syntax

comment – ‘--’ or – ‘#’

contention, find –

convert from MS Access

Access2MySQL - $35, 30 free uses

ExportSQL

copy row –

1. use mysqldump to generate “INSERT” SQL

mysqldump fr orderItems -t --where="orderItemID=481109" > 481109.sql

2. from one table to another:

insert into destinationdb.destinationtable
where code = ‘123’;

copy table – see table, copy

copy database table to local db from remote db

ssh www 'mysqldump --add-drop-table --opt projects main | gzip' | zcat | mysql projects

where you substitute the database for “projects” and the table for “main”. With the version of MySQL that we're using now --add-drop-table and --opt are the defaults, so you don't need to specify them on the command-line anymore. If you get:

Lost connection to MySQL server during query when dumping table `addresses` at row: 1175073

then get from reportQuery server:

ssh www 'mysqldump -h www14 --add-drop-table --opt fr addresses | gzip' | zcat | mysql fr

This makes it so your long-running query doesn't lock up the table, blocking scripts from running. The reason it gave you "lost connection" errors is that the query-killer we run on db and the fastQuery servers killed your query to unlock the other queries.

create database – mysqladmin -u root [-p] create prova (“prova” is the name of the database you want to create)

–D–

data – var/lib/mysql

data type of a field, change – see column, change data type

database disk location

select @@datadir;

databases, list – show databases

dbtools

download (US)

delete field – ALTER TABLE table_name DROP field_name;

ALTER TABLE Syntax

display each field on a separate line – replace the “;” at the end with a “\G” as in

SELECT projectID, feedbackID, current FROM feedback WHERE projectID=9341\G

download from mysql.com

dump table structure from command line

all tables - mysqldump --no-data fr> ~/path/filename.txt
just one table - mysqldump --opt fr channels --no-data > ~/path/filename.txt

–E–

ERROR 1104: The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok – do an “explain” first. In order to do that (and if the command is in a file you can edit using emacs), you might want to run “M-x Collapse-spaces”, after using M-^ to join all the lines together into one.

ERROR 1175 “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” - SET SQL_SAFE_UPDATES=0

–F–

field, change data type - see column, change data type

field, add – ALTER TABLE table_name ADD field_name;

to a certain location: ALTER TABLE table_name ADD field_name AFTER another_field ;

ALTER TABLE Syntax

field, delete – ALTER TABLE table_name DROP field_name;

ALTER TABLE Syntax

field, rename – see column, rename

file, read MySQL commands from

from bash shell:

create a text file 'text_file' that contains the commands you wish to execute. Then invoke mysql as shown here:

shell> mysql database < text_file

Or start your text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:

shell> mysql < text_file

from within MySQL

execute a SQL script file using the source command:

mysql> source filename;

or

\. <script Name>

file, redirect MySQL output to – see redirect MySQL output to a file

front ends

gmysql

MySQL Control Center (MySQLCC)

MySQLFront

MySQLGUI - work has discontinued

phpMyAdmin

xMySQLadmin – need libm.so.5 libraries to run

–G–

global and session variables, setting and finding out values of

There are two kinds of system variables: Thread-specific (or connection-specific) variables that are unique to the current connection and global variables that are used to configure global events. Global variables also are used to set up the initial values of the corresponding thread-specific variables for new connections.

When mysqld starts, all global variables are initialized from command line arguments and option files. You can change the value with the SET GLOBAL command. When a new thread is created, the thread-specific variables are initialized from the global variables and they will not change even if you issue a new SET GLOBAL command.

To set the value for a GLOBAL variable, you should use one of the following syntaxes: (Here we use sort_buffer_size as an example variable)

SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;

To set the value for a SESSION variable, you can use one of the following syntaxes:

SET SESSION sort_buffer_size=value;
SET @@session.sort_buffer_size=value;
SET sort_buffer_size=value;

If you don't specify GLOBAL/code> or SESSION then SESSION is used. LOCAL is a synonym for SESSION.

To retrieve the value for a GLOBAL variable you can use one of the following commands:

SELECT @@global.sort_buffer_size;
SHOW GLOBAL VARIABLES like 'sort_buffer_size';

To retrieve the value for a SESSION variable you can use one of the following commands:

SELECT @@session.sort_buffer_size;
SHOW SESSION VARIABLES like 'sort_buffer_size';

When you retrieve a variable value with the @@variable_name syntax and you don't specify GLOBAL or SESSION then MySQL will return the thread-specific (SESSION) value if it exists. If not, MySQL will return the global value.

The reason for requiring GLOBAL for setting GLOBAL only variables but not for retrieving them is to ensure that we don't later run into problems if we later would introduce a thread-specific variable with the same name or remove a thread-specific variable. In this case, you could accidentally change the state for the server as a whole, rather than just for your own connection.

–H–

–I–

index, create – create index index_name on table_name (column_name1, column_name1);

index, delete – drop index index_name on table_name;

index, show – SHOW INDEX FROM tbl_name

insert into cityInfo
������ (zipCode, state, city, citySoundex, county, sequence,
������� postalStatus, latitude, longitude)
�� select
���������� distinct c.zipCode, c.state, c.city, c.citySoundex, c.county, c.sequence,
���������� c.postalStatus, c.latitude, c.longitude
�� from��� cityInfoSupplement as c left join cityInfo as n
�� on����� (n.zipCode� = c.zipCode
�� and���� n.state���� = c.state
�� and���� n.city�� ���= c.city)
�� where�� n.zipCode�� is null

–J–

–K–

kill query – mysqladmin processlist to get the ID followed by mysqladmin kill ID

–L–

list databases – see databases, list

locked queries, find – mysqladmin processlist

–M–

matched records leave alone; unmatched records, append

insert into cityInfo ������� (zipCode, state, city, citySoundex, county, sequence,
�������� postalStatus, latitude, longitude)
��� select
����������� distinct c.zipCode, c.state, c.city, c.citySoundex, c.county, c.sequence,
����������� c.postalStatus, c.latitude, c.longitude
��� from��� cityInfoSupplement as c left join cityInfo as n
��� on����� (n.zipCode� = c.zipCode
��� and���� n.state���� = c.state
��� and���� n.city����� = c.city)
��� where�� n.zipCode�� is null

MyFrontEnd

discussion-board

Download

MySQL, start using – simply type in “mysql”.� If this doesn�t work and you have recently changed to superuser using “su”, use “su -” instead.

To start the server, see start server

–N–

–O–

ODBC drivers

ODBC

Settings – use the User, Password from the mysql.user table. Make sure the “Host” column for that record is “192.168.2.%”.

(use mysql; select * from user;)

If missing:

INSERT into user ( Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv )values ("192.168.2.%", "userid", "passwd", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y");

flush privileges;

only 1000 rows affected, remove limit – start mysql with mysql –-select-limit 1000000

order – order by

output to a file, redirect MySQL – see redirect MySQL output to a file

–P–

processes, which are running

from the MySQL command line – show full processlist

from the bash command line – mysqladmin processlist

–Q–

queries, which are running

from the MySQL command line – show full processlist

from the bash command line – mysqladmin processlist

query, kill – mysqladmin processlist to get the ID followed by mysqladmin kill ID

–R–

recover database table to local db from remote db

ssh www 'mysqldump --add-drop-table --opt projects main | gzip' | zcat | mysql projects

where you substitute the database for “projects” and the table for “main”. With the version of MySQL that we're using now --add-drop-table and --opt are the defaults, so you don't need to specify them on the command-line anymore. If you get:

Lost connection to MySQL server during query when dumping table `addresses` at row: 1175073

then get from reportQuery server:

ssh www 'mysqldump -h www14 --add-drop-table --opt fr addresses | gzip' | zcat | mysql fr

This makes it so your long-running query doesn't lock up the table, blocking scripts from running. The reason it gave you "lost connection" errors is that the query-killer we run on db and the fastQuery servers killed your query to unlock the other queries.

redirect MySQL output to a file – 2 ways

from the linux command prompt

mysql -e "describe abc" > temp.txt

from the MySQL command prompt

tee temp.txt;
Describe abs;
\t

rename table – ALTER TABLE tbl_name RENAME as new_tbl_name

replace table – see table, replace

replace text in a field –

SET SQL_SAFE_UPDATES=0;

UPDATE savedQueries SET searchSel = Replace(searchSel,'deliverable','isDeliverable');

rows affected, remove limit of only 1000 – start mysql with mysql -select-limit 1000000

run a MySQL command from the Linux prompt –

echo 'create index isShip on orderItems (isShip)' | mysql fr > logFile

run script –

from bash shell:

create a text file 'text_file' that contains the commands you wish to execute. Then invoke mysql as shown here:

shell> mysql database < text_file

Or start your text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:

shell> mysql < text_file

from within MySQL

execute a SQL script file using the source command:

mysql> source filename;

or

\. <script Name>

–S–

safe update mode� - “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” (ERROR 1175) - SET SQL_SAFE_UPDATES=0

schema - dump table structure – from command line, mysqldump --no-data fr> ~/path/filename.txt

script, run – see run script

show variables – “show variables”

shut down – mysqladmin -u root shutdown

sort – order by

SQL_MAX_JOIN_SIZE, determine – see global and session variables, setting and finding out values of

SELECT @@session.SQL_MAX_JOIN_SIZE; – often defaults to a million

Fix by SET SQL_BIG_SELECTS=1

start server – usr/bin/safe_mysqld &

start session – mysql -u user -p

start using MySQL – see MySQL, start using

switch tables – see table, replace

–T–

table, copy –

To copy structure and all rows: CREATE TABLE new_tbl_name SELECT * FROM tbl_name

To copy structure only: CREATE TABLE new_tbl_name SELECT * FROM tbl_name where 1 = 0

Note: this does NOT copy keys or indices!!� To get that info, show create table tbl_name;

table, copy database table to local db from remote db

ssh www 'mysqldump --add-drop-table --opt projects main | gzip' | zcat | mysql projects

where you substitute the database for “projects” and the table for “main”.� With the version of MySQL that we're using now --add-drop-table and --opt are the defaults, so you don't need to specify them on the command-line anymore.� If you get:

Lost connection to MySQL server during query when dumping table `addresses` at row: 1175073

then get from reportQuery server:

ssh www 'mysqldump -h www14 --add-drop-table --opt fr addresses | gzip' | zcat | mysql fr

This makes it so your long-running query doesn't lock up the table, blocking scripts from running.� The reason it gave you "lost connection" errors is that the query-killer we run on db and the fastQuery servers killed your query to unlock the other queries.

table, copy just 1 row – use mysqldump to generate “INSERT” SQL

mysqldump fr orderItems -t --where="orderItemID=481109" > 481109.sql

table, rename– ALTER TABLE tbl_name RENAME as new_tbl_name

table, replace - rename table cityInfo to cityInfoOld, cityInfo2 to cityInfo

table, show all fields in

from within MySQL – describe tablename;

from Linux command line to a file - mysqldump --opt fr channels --no-data > ~/path/filename.txt

tables, show all tables in a database – show tables;

top 5 records

select * from DemoTable limit 0,10;

–U–

unmatched records, append

insert into cityInfo
������� (zipCode, state, city, citySoundex, county, sequence,
�������� postalStatus, latitude, longitude)
��� select
����������� distinct c.zipCode, c.state, c.city, c.citySoundex, c.county, c.sequence,
����������� c.postalStatus, c.latitude, c.longitude
��� from ���cityInfoSupplement as c left join cityInfo as n
��� on����� (n.zipCode� = c.zipCode
��� and���� n.state���� = c.state
��� and���� n.city����� = c.city)
��� where�� n.zipCode�� is null

update example - update authorizedUsers set status = 'Using' where userName = '[email protected]';

user, add

INSERT into user ( Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv )values ("192.168.2.%", "userid", "passwd", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y");

flush privileges;

Instead of “192.168.2.%”, you need “localhost” if you want to get in using puTTy

–V–

verify that MySQL is running – mysqladmin version

version, determine - SELECT version() AS version;

view results better – see display each field on a separate line

–W–

–X–

–Y–

“You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” (ERROR 1175) - SET SQL_SAFE_UPDATES=0

–Z–

–No's–

1000 rows affected, remove limit – start mysql with mysql –-select-limit 1000000

1175 ERROR “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” - SET SQL_SAFE_UPDATES=0