473,395 Members | 2,437 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

My notes: Hopefully this will help someone

Hopefully this will help someone...

Helpful Things to Know about MySQL
Mike Chirico (mc******@users.sourceforge.net)
Last Updated: Fri Apr 16 11:47:34 EDT 2004

The latest version of this document can be found at:
http://prdownloads.sourceforge.net/s...l.txt?download

Format is better on the above link. I'm looking for suggestions and
examples...


TIP 1:

Find out who is doing what, and kill the process if needed. This example
kills
Id 657

mysql> show processlist;
show processlist;

+-----+------+-----------+---------+---------+-------+-------+--------------
----+
| Id | User | Host | db | Command | Time | State | Info
|

+-----+------+-----------+---------+---------+-------+-------+--------------
----+
| 657 | prog | localhost | weather | Sleep | 28619 | | NULL
|
| 782 | prog | localhost | weather | Sleep | 853 | | NULL
|
| 785 | prog | localhost | NULL | Query | 0 | NULL | show
processlist |

+-----+------+-----------+---------+---------+-------+-------+--------------
----+
3 rows in set (0.00 sec)

mysql>kill 657

Or, from the command line, to kill process 782

[root@third-fl-71 mysql]# mysqladmin processlist

+-----+------+-----------+---------+---------+------+-------+---------------
---+
| Id | User | Host | db | Command | Time | State | Info
|

+-----+------+-----------+---------+---------+------+-------+---------------
---+
| 782 | prog | localhost | weather | Sleep | 2676 | |
|
| 785 | prog | localhost | | Sleep | 1823 | |
|
| 793 | root | localhost | | Query | 0 | | show
processlist |

+-----+------+-----------+---------+---------+------+-------+---------------
---+
[root@third-fl-71 mysql]#

[root@third-fl-71 mysql]# mysqladmin kill 782

Note, the following can also be helpful

mysql>show status;
or
mysql>show status\G

TIP 2:

Clean up binary log files. For a default install they may be in
/usr/local/var/
with names ending in -bin.000001,-bin.000002,..
mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)

TIP 3:

Can the order of the columns in a create statement make a difference?
YES

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the "automatically generated" time.
So
if the record is updated, or inserted, this time gets changed. If the
order is changed, "timeEnter" is before "timeUpdate", then, "timeEnter"
would get updated. First timestamp column updates automatically.

Not, in the table above timeEnter will only get updated if passed a null
value.

insert into t (a,b,timeEnter) values (1,2,NULL);

TIP 4:

Connect, create table and select with Perl (Linux). First the DBI module
is needed, which
can be installed from the system prompt as follows:
# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql

The following is an example program:
#! /usr/bin/perl -w
# Copyright (GPL) Mike Chirico mc******@users.sourceforge.net
#
# Program does the following:
# o connects to mysql
# o creates perlTest if it doesn't exist
# o inserts records
# o selects and displays records
#
# This program assumes DBI
#
# perl -MCPAN -e shell
# cpan> install DBI
# cpan> install DBD::mysql
#
#
#
#
#

use strict;
use DBI;

# You will need to change the following:
# o database
# o user
# o password
my $database="yourdatabase";
my $user="user1";
my $passwd="hidden";
my $count = 0;
my $tblcreate= "
CREATE TABLE IF NOT EXISTS perlTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)

) ";

my $insert= "
insert into perlTest (a,b,c)
values (1,2,3),(4,5,6),(7,8,9)";

my $select="
select a,b,c from perlTest ";


my $dsn = "DBI:mysql:host=localhost;database=${database} ";
my $dbh = DBI->connect ($dsn, $user, $passwd)
or die "Cannot connect to server\n";

my $s = $dbh->prepare($tblcreate);
$s->execute();
$s = $dbh->prepare($insert);
$s->execute();

$s = $dbh->prepare($select);
$s->execute();
while(my @val = $s->fetchrow_array())
{
print " $val[0] $val[1] $val[2]\n";
++$count;
}
$s->finish();

$dbh->disconnect ( );

exit (0);
TIP 5:

Remove duplicate entires. Assume the following table and data.

CREATE TABLE IF NOT EXISTS dupTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)

);

insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);

mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+------+------+------+------+---------------------+
4 rows in set (0.00 sec)

mysql>

Note, the first two rows contains duplicates in columns a and b. It
contains
other duplicates; but, leave the other duplicates alone.

mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+------+------+------+------+---------------------+
3 rows in set (0.00 sec)

TIP 6:

Show status information on a table. Note, if the database was started
with --safe-show-database or --skip-show-database some of these
commands
may not work. Note the "\G" option my provide a nicer format.

Show the create statement:
mysql> show create table dupTest\G
show create table dupTest\G
*************************** 1. row ***************************
Table: dupTest
Create Table: CREATE TABLE `dupTest` (
`pkey` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
`b` int(11) default NULL,
`c` int(11) default NULL,
`timeEnter` timestamp NOT NULL,
PRIMARY KEY (`pkey`),
UNIQUE KEY `a` (`a`,`b`)
) TYPE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
More examples can be found at:
http://prdownloads.sourceforge.net/s...l.txt?download
Regards,

Mike Chirico
Jul 20 '05 #1
0 960

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
by: bearophileHUGS | last post by:
Here are some questions and suggestions of mine that I've collected in the last weeks on the language (please note that some (most?) of them are probably wrong/useless/silly, but I've seen that...
0
by: Mike Chirico | last post by:
Hopefully this will help someone... Helpful Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Last Updated: Fri Apr 16 11:47:34 EDT 2004 The latest version of this...
1
by: Nicole | last post by:
Hello! I hope there is someone out there who can shed some light on this for me. I have a module that is supposed to look at an access table, pull out each bid record, link to another table to...
2
by: Harry Putnam | last post by:
This may be right off the wall, and I haven't done any googling for lack of a good idea what I'm looking for. I have some text books on CD that are in html. I want to keep notes in these books...
0
by: | last post by:
Lacz Marketing Group Cash Out Now! Right now, thousands of people across North America are stuck with investments that they don't want. They would rather have the cash now! Whether it's a...
6
by: | last post by:
Lacz Marketing Group Toll Free Tel: (877) 672-9048 Email: laczmarketing@earthlink.net Web Address : www.cash4cashflows.com/elacz Cash Out Now!
25
by: Peter Michaux | last post by:
Hi, There have been a few suggestions for changing the format of the FAQ site to make it easier to maintain. VK suggested and XML procedure. Matt Kruse suggested a wiki. I think something...
1
by: Joe | last post by:
HI Has anyone been able to work with lotus notes automation classes??? Can you post sample code of how to use these classes. I have setup in VB but I am not able to port to C# This is what I...
4
by: Bob Alston | last post by:
I am trying to access data in a Lotus Notes database, from Access. The Notes database is release R5. I installed NotesSQL 8.x and have it working. However it chokes on one of the tables and...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.