MySQL Forums - MySQL Administrator

Logging spikes (no replies)

Robert Accettura — Thu, 19 Nov 2009 14:31:39 +0000

I currently monitor `SHOW PROCESSLIST` and graph number of processes returned every minute to give me a metric of how busy my mysql instance is.

Generally it's pretty quiet (1-3). Two times now I've seen a spike in excess of a hundred, grinding this small server to a halt.

Looking at my httpd logs I don't see unusual traffic, looking at my mail logs, I don't see anything that would account for it on that end either. It's not an internal process since it happened at different times, and no processes in the cron use mysql.

What logging can I setup to better identify what's causing these spikes? I'm trying to find the origin so I can stop it at the source. This server was never really intended to take that kind of load.

MySQL Workbench 5.2.8 (BETA 1): Upgrade from MySQL Administrator, MySQL Query Browser (no replies)

Edwin DeSouza — Wed, 18 Nov 2009 19:44:58 +0000

WB 5.2.8 includes:
- Model (upgrade from DBDesigner)
- Query (upgrade from MySQL Query Browser)
- Admin (upgrade from MySQL Administrator)
- SSH-Tunnel (new)

Quick-Start Tutorial:
http://wb.mysql.com/?p=406

Download WB 5.2:
http://dev.mysql.com/downloads/workbench/

Mysql GUI Tools in Solaris 10 not running (no replies)

Roger Appleby — Mon, 16 Nov 2009 20:16:10 +0000

I have downloaded and installed the 5.0 gui (x64) tools for linux (tarball)- they are supposed to work in Solaris - but none of the tools run. The export in the shell file seems to generate the following error. (same in all the tools).

./mysql-administrator: MA_DIR=/opt/mysql-gui-tools-5.0: is not an identifier

I have installed them in the /opt directory (recommended) and the path is actually correct. I navigate to the directory /opt/mysql-gui-tools-5.0 with the terminal and run ./mysql-administrator. (I am running the Java Desktop 3).

I am somewhat new to Solaris - but it seems the shell file may not be compatible.

Has anyone had any luck with this - my only other option is to do a remote connection and use a windows machine (ugh) to manage the DB.

Is it possible to create a shema on a remote server? (no replies)

AEM TISup — Mon, 16 Nov 2009 14:52:40 +0000

I've logged on with MySQL Administrator to a remote server with 'root' but get the following error when trying to create a new schema:

MySQL Error Number 1044
Access denied for user 'root'@'myhostname' to database 'newdb'

do I need to be on the server to create a schema?

Thanks!

Key Efficiency meaning in MySQL Administrator (1 reply)

Dan Ostertag — Mon, 16 Nov 2009 20:20:56 +0000

I'm trying to improve our db performance and am looking at the health parameters in the MySQL Administrator.

The Key Efficiency horizontal bar shows the Key Buffer Usage at 34,816 while the Key Buffer size is 26,214,400. This means that none of the bar is shaded, appearing to me that none of the Key buffer is being used. The Hitratio is 0%. How do I get this key cache to be used better? We have alot of keys, alot of 1-time queries on a small number of fairly large tables are made, does this mean none are every cached?

How do I get the key cache to work?

When I type "show status like '%key%';" I get 52205 key_read_requests but only key_reads. I assume this creates the 0% hitratio.

Thanks for your help.
Dan

Error Creating table in MySql Administrator (2 replies)

Brandon Larkin — Thu, 05 Nov 2009 20:49:13 +0000

I am getting an error when I try to create a table in MySql Administrator. The error and the generated SQL command are below, if anyone could point me in the right direction, that would be great.

CREATE TABLE `434_larkin04`.`guestbook` (
`id` INT AUTO_INCREMENT,
`fullname` VARCHAR,
`email` VARCHAR,
`website` VARCHAR,
`words` TEXT,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8
COMMENT = 'Guestbook DB for Assignment 7-1';

Error executing SQL commands to create table.
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 '
`email` VARCHAR,
`website` VARCHAR,
`words` TEXT,
PRIMARY KEY (`id`)
)
' at line 3 (error 1064)

Another Hard Luck Access Story (no replies)

David Harrison — Wed, 04 Nov 2009 23:43:12 +0000

Another access hard-luck story.

I've installed MySQl 5.1 on my local machine (PC Windows) with no issues. I've used the DOS command interface to crate database, tables, perform queries, etc. I discoverd MyQSL Client and down loaded v 5.1.11. Installed and pointed it at my local databse. Works great. Love the interface and it relieves a lot of the tedium of working with the database.

I've developed an app and now want to deploy to my host. My host has a shared environment and I was given an id that was supposed to be an admin id. The host requires that I create a database using their website before I can access mySQL. No problem I created an empty database. For remote access I need to provide an IP address of my client. No Problem.

I then configure a connection for my host, username & password, server address, etc. I launch a connection and voila, I'm in! Launch the Query browser, great. Except I don't see my database in the list of schemas. I can only see information_schema. Hmmm. Maybee it's there and I can't see it. OK, try show databases. No go only information_schema. OK what privileges do I have? show grants. 'GRANT USAGE ON *.* TO 'username'@'' IDENTIFIED BY PASSWORD 'some password''

Well, maybe I need to expressly grant privileges on the new database to the "admin" account. Back to the website, over to phpMyAdmin (man what an ugly, clumsy place) grant all on db.* to 'me' identified by 'password'. Now I'm ready.

Back to MySQL client. Same story. Nothing, nada, zip, zilch.

Calls to the host support go nowhere as I can use phpMyAdmin to create database, tables, run queries, etc. So as far as they are concerned my user privileges are fine. It must be the client.

Now for the questions. What am I missing? Is there some configuration on the client side I need to make? Am I using the wrong version of the client? THe hosted db is 5.0.45. The info on MySQL site for this client version states that it is for 5.1 but would work with 5.0.

localhost database (no replies)

D G — Wed, 04 Nov 2009 12:38:36 +0000

Hi,

Im not in tune with the database aspects yet so...

I have a database residing on our server.....I want to dump this database so that it can be accessed through localhost on my laptop.

Does anyone know how this is done?

Thanks in advance.

Urema.

Database corruption (1 reply)

omkar prabhu — Mon, 02 Nov 2009 21:43:28 +0000

We are using mysql 5.0 with master slave setup. When we describe any table it gives following error
Error 'Incorrect information in file: './livedb/tablename.frm
Master database looks ok, only slave is having problem,when we do show slave status, we see below information

Replicate_Wild_Ignore_Table:
Last_Errno: 1033
Last_Error: Error 'Incorrect information in file: './livedb/print.frm'' on query. Default database: 'livedb2'. Query: 'DELETE FROM livedb.print WHERE TIMESTAMPDIFF(dayvalue, date_created, '2009-11-02 01:58:06') >= 9999'
Skip_Counter: 0
Exec_Master_Log_Pos: 653241805
Relay_Log_Space: 3073839
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Can anyone let me know how to trouble shoot this issue ie. reason behind this issue and how to go about fixing it?

need help from expert: mysql keeps crashing!!! (1 reply)

khalid touati — Wed, 04 Nov 2009 14:39:41 +0000

Hi All,
after shuting down Mysqld improperly, it's crashing now every 3 hours (when under use by apps running mysql, in weekend no PB) and i have to restart it to continue using it.
i tried to repair all tables after shuting down properly the server (as it said in "Mysql keeps crashing" in the reference manual") using these two commands:
mysqladmin shutdown;
myisamchk --silent --force */*.MYI ;
i was watching the processes launched for a long time i noticed that a lot of process not related to any database are running using root as user.
i have the processlist and the debug info while it was not working (crashed), but unfortunately i don't see here a way to attach this (and i don't want to make my message more long).
so for the kind expert person who is willing to help me :) (i hope somebdy will!), i'll email it to him and thanks for you all!

error when selecting Options tab (no replies)

Eric Tiffany — Sat, 31 Oct 2009 15:42:24 +0000

After installing the MySQL 5.1.40 package on my Mac (OS 10.6.1), I find that when I open MySQL Administrator and click the Options icon I get this message in an Alert popup:

Could not open GUI description file.
Error parsing XML file (bad document).

If I click OK, I am then shown some advertisement for "MySQL Network Monitoring & Advisory Service" and some buttons to Save, Discard Changes, and Click for more info.

What's up with this?

Mysql Command Line Client ARRGH (7 replies)

James Brown — Fri, 30 Oct 2009 10:19:32 +0000

Hi, I am on a windows server using the Mysql Command Line Client. I login with my password and have the mysql> prompt. I then issue the following:

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

But nothing happens

The cursor just wraps to a new line with a prompt ->

Can anyone tell me why my data is not dumping. Thanks

error 134 cause mysql to run out connections (5 replies)

khalid touati — Thu, 29 Oct 2009 00:30:01 +0000

Hi Guys,
i have an error taht miss up everything using mysql in my server, including websites of course.
here is the thing: the error start with a table as it says here in the logs:

091023 12:45:40 [ERROR] Got error 134 when reading table './otrs/user_preferences'
091023 12:46:02 [ERROR] Got error 134 when reading table './otrs/user_preferences'
091023 12:46:11 [ERROR] Got error 134 when reading table './otrs/user_preferences'
091023 12:46:17 [ERROR] Got error 134 when reading table './otrs/user_preferences'
091023 12:46:23 [ERROR] Got error 134 when reading table './otrs/user_preferences'
091023 14:26:06 [Note] /usr/sbin/mysqld: Normal shutdown

091023 14:26:08 InnoDB: Starting shutdown...
091023 14:26:12 InnoDB: Shutdown completed; log sequence number 1 3966143693
091023 14:26:12 [Note] /usr/sbin/mysqld: Shutdown complete

091023 14:26:12 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=/var/run/mysqld/mysqld-bin' to avoid this problem.
091023 14:26:13 InnoDB: Started; log sequence number 1 3966143693
091023 14:26:13 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44-r2

this error make that when you connect to website it says "mysql cannot connect to database", so i restared the mysql and i get the warning below it stays normal for few hours like 6 hours and the cycle get launched again.
any idea that can get ma out of the cycle please!!?

help with Mysqldump (1 reply)

James Brown — Tue, 27 Oct 2009 21:10:28 +0000

I need to back up a large Vbulletin (forum) database (750mb) On a windows server, so I can FTP the backup to a new Linux server. The instructions Vbulletin give to back up and restore their databases is as follows.

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

Can I use Mysql administrator to carry out the dump with these options --opt and -Q

(if I knew what those options meant it might help :embarrassed:)

Thanks

Large table issue (no replies)

omkar prabhu — Sat, 24 Oct 2009 08:44:29 +0000

I have a table growing immensely every day , currently it has around 18777410 records.
Our site is available 24x7 and we are using mysql replication to distrubute load on the database server ( a master and single slave combination).
It is almost two years we running the application on mysql 5.0.45 everything seems to fine, but now we are facing problem with maintaining this huge table.
Following are key problem areas:
a) Running aggregate queries takes more than 5 minutes, which is not acceptable with respect to site performane
b) Adding index is not possible as it takes more than 3/4 hours which result in site downtime
c) command like optimize and analyze cant be run as it is online application and it will result in locking of this important table.

Can any one suggest possible approach to solve this problem, we are thinking of trying following approach
a) Have seperate table for having archive data,so query requiring complete data, will need to be fire two queries or have union on archive data and live data
b) Upgrade to mysql 5.1 and see having partitioning on this table helps
c) have mutliple database slave server to hit queries on most used use cases on this table.

Let me know if anyone faced similar problem and how to go about solving it

Backup via mySQLAdmin - character set problem? (no replies)

Larry Bradley — Fri, 23 Oct 2009 21:26:22 +0000

Using mySQL server 5.0 on a Windows system, using myIsam. I have a "users" table that has the data encrypted by my program. When I back up the "users" table alone, the INSERT statement data is correct. If I backup the entire database, and look at the "users" table data, the insert statement data is different (and incorrect). Using "normal" backup option, and the backup options are the same for the two cases.

If I do the same thing on my Ubuntu system, all is well.

Something weird is going on. Why should the entire database backup and a single table backup give different results? What should I be looking for?

Thanks

Larry

MySQL Workbench 5.2.6 (ALPHA): SSH-Tunnel, Administrator, Query Browser (1 reply)

Edwin DeSouza — Thu, 29 Oct 2009 21:30:27 +0000

WB 5.2.6 now includes:
- MySQL Query Browser (Feature Complete)
- SSH-Tunnel (Feature Complete)
- MySQL Administrator (Not Complete yet)

We need your help, testing & feedback!

Download WB 5.2:
http://dev.mysql.com/downloads/workbench/

MySQLWorkbench: SSH-Tunnel
http://wb.mysql.com/?p=370

MySQL Workbench: Administrator
http://wb.mysql.com/?p=374

MySQL Workbench 5.2: Features of MySQL Query Browser
http://dev.mysql.com/workbench/?p=229

MySQL Workbench 5.2: QuickTour of Query Browser
http://dev.mysql.com/workbench/?p=230

Which permission user need for dumping triggers? (2 replies)

Vitaly Karasik — Mon, 26 Oct 2009 13:24:36 +0000

I need to run mysqldump for backup my DB, inlcuding triggers. Of course, I want to do this using user with minimal permissions. Manual says that "SUPER" permission needs for creating/deleting triggers, but I need just read permissions.
Any ideas? I use 5.0.86 serrver.

Thanks,
Vitaly

MySQL GUI Tool - BACKUP using command line (8 replies)

Manhao Chen — Sun, 25 Oct 2009 07:50:28 +0000

hi all,

after losing all my internal forum data (likely not a lot), i figured its time to have a schedule backup

but first i want to make sure i can run a backup using command line and then i can just make it a .bat file and schedule it after

so anyone knows how to write the line so that it backs up my database say my db name is... happy_db and save to... C:\happy_db_backup\

my database is on another machine say... 192.168.10.40
username: root
password: somepassword

i'm after the same way as how the GUI tool backs up so i can just restore the file

i'm using windows xp/vista 32bit

thanks
Manhao

1130 Host is not allowed to connect to this MySQL server (2 replies)

P R — Tue, 20 Oct 2009 07:33:44 +0000

Hello,

I have two computers. MySQL-Server is running on the first computer. I can access the MySQL-Server with MySQL Administrator local without any problem. But I get the Error 1130 trying to access the MySQL-Server from the second computer. And I can ping the first computer from the second computer.

Many thanks
PR

Problems with remote connections (1 reply)

Interim Service — Mon, 19 Oct 2009 13:43:16 +0000

Hello,

We have a problem with connections from a remote machine to the db server. The configuration (arch) is currently:

Web(app - drupal based, php(current), apache(current) on FreeBSD) -> DB (MySQL on CentOS).

rel. my.cnf settings:

max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 256M
query_cache_type = 1
join_buffer_size = 512K
max_connections = 1000
wait_timeout = 120
connect_timeout = 10
join_buffer_size = 1M
table_cache = 1024
sort_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 8M
key_buffer = 256M
key_buffer_size = 64M
thread_concurrency = 4

# Added
wait_timeout = 5
connect_timeout = 12

........

We've played with the timeouts to no avail (longer/shorter).

When the server is under high load, mysql stops fielding requests; it's like it locks. To replicate the issue, if i do two full dumps of the database (700M) in a row while the web server is active under low load, it displays the same characteristics.

To fix, we have to 1) restart mysqld 2) restart apache. (doing just one or the other won't work).

When the two processes were on the same box, this wasn't an issue (when apache and MySQL were living on the same host). We used this same config when the two were living on the same host.

Low load for us is ~100 concurrent connections, High load in the order of 500. I replicated the high load lockup with 100 concurrent, with mysqldump (twice).

I've tried various versions of apache/php against various versions of MySQL. We've tried MySQL on both FreeBSD and Linux, same issues on both, across all versions.

I haven't seen anything in the log files. (just starts/stops). When warnings were enabled, we saw connect timeouts, but we have a strong reason to believe this was related to drupal not calling mysql_close() in one or more of its modules. This hasn't been conclusively tested, however.

Is there some network functionality aspect of MySQL that I'm missing? Am I supposed to be setting something in the config files that I'm unaware of? Is it a files open issue, or a tmp files issues? I'm lost.

Below, you'll find the results of "SHOW VARIABLES;" -- if you need anything else, let me know, I'm most desperate at this point.

Best,

Interim.

.........................

Variable_name Value
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 50
basedir /usr/
bdb_cache_size 8388600
bdb_home /var/lib/mysql/
bdb_log_buffer_size 524288
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir /tmp/
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
completion_type 0
concurrent_insert 1
connect_timeout 12
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
div_precision_increment 4
engine_condition_pushdown OFF
expire_logs_days 0
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
group_concat_max_len 1024
have_archive NO
have_bdb YES
have_blackhole_engine NO
have_compress YES
have_crypt YES
have_csv NO
have_dynamic_loading YES
have_example_engine NO
have_federated_engine NO
have_geometry YES
have_innodb YES
have_isam NO
have_merge_engine YES
have_ndbcluster NO
have_openssl DISABLED
have_ssl DISABLED
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
hostname **** (edited).
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
interactive_timeout 28800
join_buffer_size 1044480
key_buffer_size 67108864
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/share/mysql/english/
large_files_support ON
large_page_size 0
large_pages OFF
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries ON
log_warnings 1
long_query_time 4
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16776192
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 1000
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 4294967295
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords ON
open_files_limit 5000
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/run/mysqld/mysqld.pid
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 8388608
query_cache_min_res_unit 4096
query_cache_size 268435456
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 4190208
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 0
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir /tmp/
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 8388600
sql_big_selects ON
sql_mode
sql_notes ON
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine MyISAM
sync_binlog 0
sync_frm ON
system_time_zone EDT
table_cache 1024
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 64
thread_stack 131072
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.45-log
version_bdb Sleepycat Software: Berkeley DB 4.1.24: (July 4, 2007)
version_comment Source distribution
version_compile_machine i686
version_compile_os redhat-linux-gnu
wait_timeout 5

Very high CPU utilisation with 40-50 concurrent connection on Innodb table (no replies)

Pankaj joshi — Wed, 14 Oct 2009 14:15:32 +0000

I am getting very high cpu utilisation around 90% when 40-50 concurrent connections are executing .

The version of mysql is 5.1.32 community log

RAM 8GB

processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2800.100
cache size : 1024 KB
physical id : 0
siblings : 1
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu tsc msr pae mce cx8 apic mtrr mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 7004.24
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2800.100
cache size : 1024 KB
physical id : 1
siblings : 1
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu tsc msr pae mce cx8 apic mtrr mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 7004.24
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2800.100
cache size : 1024 KB
physical id : 2
siblings : 1
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu tsc msr pae mce cx8 apic mtrr mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 7004.24
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2800.100
cache size : 1024 KB
physical id : 3
siblings : 1
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu tsc msr pae mce cx8 apic mtrr mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall lm constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 7004.24
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

show processlist

Id db Command Time State Info
1 \N Binlog Dump 13894 Has sent all binlog to slave; waiting for binlog to be updated \N
106164 DRS Sleep 3956 \N
3588858 \N Query 0 \N show full processlist
4297685 heywire Query 0 Sending data select id, name, description, main_image, back_image, start_date, end_date, user_id, username, location, category, email, url, pretty_url, tinkurl, creation_date, last_post from event ORDER by id LIMIT 10200,300
4297688 heywire Query 0 Sending data select id, name, description, main_image, back_image, start_date, end_date, user_id, username, location, category, email, url, pretty_url, tinkurl, creation_date, last_post from event where featured='true' ORDER by id LIMIT 3000,300
4297721 heywire Query 0 freeing items select id, username, password, main_image, back_image, creation_date from person where username='_vanya'
4297760 heywire Sleep 0 \N
4297762 heywire Query 0 Sending data select id, name, description, main_image, back_image, start_date, end_date, user_id, username, location, category, email, url, pretty_url, tinkurl, creation_date, last_post from event where featured='true' ORDER by id LIMIT 6900,300
4297763 \N Connect \N Reading from net \N
4297770 heywire Query 0 Sending data select id, name, description, main_image, back_image, start_date, end_date, user_id, username, location, category, email, url, pretty_url, tinkurl, creation_date, last_post from event ORDER by id LIMIT 10500,300
4297772 \N Sleep 0 \N
4297774 \N Connect \N Reading from net \N
4297777 heywire Sleep 0 \N
4297779 heywire Query 0 Sending data select id, name, description, main_image, back_image, start_date, end_date, user_id, username, location, category, email, url, pretty_url, tinkurl, creation_date, last_post from event where featured='true' ORDER by id LIMIT 9900,300
4297775 \N Sleep 0 \N
4297781 \N Sleep 0 \N
4297783 \N Connect \N Reading from net \N
4297784 heywire Sleep 0 \N
4297790 \N Connect \N Reading from net \N
4297789 \N Connect \N Reading from net \N
4297788 heywire Sleep 0 \N
4297787 heywire Sleep 0 \N
4297786 \N Connect \N Reading from net \N
4297785 \N Connect \N Reading from net \N
4297782 \N Connect \N Reading from net \N

the table event has only 10000 records

the table strucutre for event table is

CREATE TABLE `event` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`start_date` timestamp NULL DEFAULT NULL,
`end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`main_image` varchar(2000) DEFAULT NULL,
`back_image` varchar(2000) DEFAULT NULL,
`description` varchar(2000) DEFAULT NULL,
`pretty_url` varchar(2000) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`url` varchar(2000) DEFAULT NULL,
`username` varchar(2000) DEFAULT NULL,
`location` varchar(255) DEFAULT NULL,
`creation_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`category` varchar(255) DEFAULT NULL,
`featured` varchar(10) DEFAULT NULL,
`last_post_count` int(11) DEFAULT NULL,
`address` varchar(500) DEFAULT NULL,
`address2` varchar(500) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`zip` varchar(100) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
`locked` varchar(10) DEFAULT NULL,
`tinkurl` varchar(200) DEFAULT NULL,
`last_post` varchar(255) DEFAULT NULL,
`ad_image` varchar(2000) DEFAULT NULL,
`ad_url` varchar(2000) DEFAULT NULL,
`moderated` varchar(10) DEFAULT NULL,
`flag` int(11) DEFAULT NULL,
`projected_page` varchar(10) DEFAULT NULL,
`projected_main_image` varchar(2000) DEFAULT NULL,
`projected_back_image` varchar(2000) DEFAULT NULL,
`projected_page_twitter` varchar(255) DEFAULT NULL,
`code_plugin` varchar(2000) DEFAULT NULL,
`last_pub_date` bigint(20) unsigned DEFAULT NULL,
`comments` text,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `pretty_url` (`pretty_url`(767)),
KEY `idx_event_description` (`category`)
) ENGINE=InnoDB

the query which is running very frequently is
select id, name, description, main_image, back_image, start_date, end_date, user_id, username, location, category, email, url, pretty_url, tinkurl, creation_date, last_post from event ORDER by id LIMIT 10500,300

Please let me know if anything else would be required .

Thanks

add myd file (6 replies)

Amit Singh — Tue, 20 Oct 2009 08:04:38 +0000

i am not able add myd file to toad

No install (3 replies)

Jorge Hernandez — Fri, 09 Oct 2009 19:23:43 +0000

Are there any administration tools for MySQL that do not require installation or being executed on a web server?

I would like to be able of administering a MySQL Server from my workstation but I don't have rights to install anything on it, so if you guys know something that can help me I'd appreciated it.

Thanks in advance,

backup of mysql database. (1 reply)

Barba Papa — Thu, 08 Oct 2009 21:22:07 +0000

Hi,

While taking backup of mysql database, will simply copying the database folder do ? My database folder is /var/lib/mysql. Will simply copying contents of this database help or do I need to run mysqldump command and then only take backup ??

What is the difference that I may encounter ?

MySQL Workbench 5.2.4 (ALPHA): SSH-Tunnel, Administrator, Query Browser. (no replies)

Edwin DeSouza — Thu, 08 Oct 2009 00:22:55 +0000

WB 5.2.4 now includes:
- MySQL Query Browser (Feature Complete)
- SSH-Tunnel (Feature Complete)
- MySQL Administrator (Not Complete yet)

We need your help, testing & feedback!

Download WB 5.2.4:
http://dev.mysql.com/downloads/workbench/

MySQLWorkbench: SSH-Tunnel
http://wb.mysql.com/?p=370

MySQL Workbench: Administrator
http://wb.mysql.com/?p=374

MySQL Workbench 5.2: Features of MySQL Query Browser
http://dev.mysql.com/workbench/?p=229

MySQL Workbench 5.2: QuickTour of Query Browser
http://dev.mysql.com/workbench/?p=230

Insert/Update queries getting locked (4 replies)

Ganesh Bansal — Thu, 08 Oct 2009 15:09:24 +0000

Hi All

I am facing locking problems from past few days. Simple UPDATE query which is updating a few columns in my table on basis of primary key column gets locked. Same is with my INSERT queries. Kindly note that no SELECT query got locked till now.

Below is o/p of following commands:
SHOW ENGINE INNODB STATUS \G
SHOW CREATE TABLE PersonNews \G
SHOW TABLE STATUS LIKE 'PersonNews' \G
SHOW CREATE TABLE Cities \G
SHOW TABLE STATUS LIKE 'Cities' \G
SHOW VARIABLES LIKE '%buffer%';

Any suggestion/help will be highly appreciated. Let me know if I need to send more details about my problem.

Regards

Ganesh
=================================================================================
mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Status:
=====================================
091007 6:42:10 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12104, signal count 12083
Mutex spin waits 0, rounds 64284, OS waits 417
RW-shared spins 24086, OS waits 11407; RW-excl spins 261, OS waits 103
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091007 6:29:35 Transaction:
TRANSACTION 0 51198677, ACTIVE 0 sec, process no 7125, OS thread id 1160321360 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 6922, query id 157895 localhost 127.0.0.1 root updating
DELETE FROM `FundPlan`.`OperationDayWorkingCapitalPlans` WHERE Id = 90
Foreign key constraint fails for table `fundplan/transactions`:
,
CONSTRAINT `fk_1235981` FOREIGN KEY (`ODWCPId`) REFERENCES `operationdayworkingcapitalplans` (`Id`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 28 fields;
0: len 8; hex 800000000000005a; asc Z;; 1: len 6; hex 0000030d3ad5; asc : ;; 2: len 7; hex 00000173db2fbc; asc s / ;; 3: len 13; hex 446166666f64696c4f74686572; asc DaffodilOther;; 4: SQL NULL; 5: len 8; hex 80000000000001b3; asc ;; 6: len 8; hex 80001245cd099280; asc E ;; 7: len 8; hex 0000000000000000; asc ;; 8: SQL NULL; 9: SQL NULL; 10: len 8; hex 8000000000000012; asc ;; 11: len 8; hex 0000000000000000; asc ;; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: SQL NULL; 16: len 8; hex 0000000000000000; asc ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 8; hex 8000000000000005; asc ;;

But in child table `fundplan/transactions`, in index `fk_1235981`, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 800000000000005a; asc Z;; 1: len 8; hex 800000000000001c; asc ;;

------------
TRANSACTIONS
------------
Trx id counter 0 51201977
Purge done for trx's n:o < 0 51201927 undo n:o < 0 0
History list length 14
Total number of lock structs in row lock hash table 35
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1164847440
MySQL thread id 8415, query id 189783 localhost root
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1164581200
MySQL thread id 8096, query id 210882 59.144.174.74 gaurav.garg
---TRANSACTION 0 51201968, not started, process no 7125, OS thread id 1161918800
MySQL thread id 8112, query id 210867 210.7.76.130 gaurav.garg
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1158457680
MySQL thread id 1, query id 210882 210.7.76.130 gaurav.garg
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1160587600
MySQL thread id 18, query id 210983 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 0, not started, process no 7125, OS thread id 1159256400
MySQL thread id 5, query id 210882 124.30.27.18 nitin.goel
---TRANSACTION 0 51201729, ACTIVE 65 sec, process no 7125, OS thread id 1164314960
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1216
MySQL thread id 9418, query id 208088 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`Cities` AS V4Core_Cities SET `StateId` = null WHERE CityId = 19394
---TRANSACTION 0 51200228, ACTIVE 350 sec, process no 7125, OS thread id 1163250000
MySQL thread id 8379, query id 210662 localhost 127.0.0.1 root
---TRANSACTION 0 51198878, ACTIVE 676 sec, process no 7125, OS thread id 1164048720
mysql tables in use 1, locked 1
MySQL thread id 7114, query id 161516 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`PersonNews` AS V4Core_PersonNews SET `Image` = null, `groupId` = 'testsc' WHERE PersonNewId = 22783
---TRANSACTION 0 51198356, ACTIVE 815 sec, process no 7125, OS thread id 1161652560
mysql tables in use 1, locked 1
MySQL thread id 6612, query id 151839 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`PersonNews` AS V4Core_PersonNews SET `Image` = null, `groupId` = 'testsc' WHERE PersonNewId = 22783
---TRANSACTION 0 51193823, ACTIVE 2078 sec, process no 7125, OS thread id 1160055120 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 232, query id 63926 124.30.27.18 nitin.goel update
INSERT INTO `CITIES`( `CityId`, `CityName`, `CityCode`, `StateId`, `DistrictId`, `TargetDataPorted`, `SourceCityId`, `DClientLocationId`, `IsApproved`, `FromPinCode`, `ToPinCode`, `IsKeyCity` ) VALUES ( 22827 , 'Test City' , 'test' , 19 , 616 , null , 2028166963 , null , null , null , null , null )
------- TRX HAS BEEN WAITING 2078 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 245778 n bits 144 index `PRIMARY` of table `v4core/states` trx id 0 51193823 lock mode S locks rec but not gap waiting
Record lock, heap no 20
------------------
---TRANSACTION 0 51193621, ACTIVE 2158 sec, process no 7125, OS thread id 1160853840
mysql tables in use 1, locked 1
42 lock struct(s), heap size 6752, undo log entries 452
MySQL thread id 2048, query id 63966 localhost 127.0.0.1 root Table lock
UPDATE `V4Core`.`PersonNews` AS V4Core_PersonNews SET `Name` = 'Jack Zeo', `groupId` = 'Test_1', `groupId1` = 'Test' WHERE PersonNewId = 21095
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
173991 OS file reads, 8274 OS file writes, 3259 OS fsyncs
32.06 reads/s, 19034 avg bytes/read, 7.37 writes/s, 2.69 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
3327 inserts, 3327 merged recs, 1333 merges
Hash table size 17393, used cells 11534, node heap has 29 buffer(s)
442.16 hash searches/s, 374.85 non-hash searches/s
---
LOG
---
Log sequence number 13 4217492506
Log flushed up to 13 4217492506
Last checkpoint at 13 4217492506
0 pending log writes, 0 pending chkp writes
2524 log i/o's done, 2.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 50069952; in additional pool allocated 1048576
Buffer pool size 512
Free buffers 0
Database pages 483
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 273868, created 48, written 5785
37.25 reads/s, 0.06 creates/s, 5.31 writes/s
Buffer pool hit rate 985 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 7125, id 1157658960, state: sleeping
Number of rows inserted 3679, updated 1297, deleted 88, read 25031128
1.87 inserts/s, 0.12 updates/s, 0.00 deletes/s, 608.02 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

mysql>

==================================================================================
mysql> SHOW CREATE TABLE PersonNews \G
*************************** 1. row ***************************
Table: PersonNews
Create Table: CREATE TABLE `personnews` (
`groupId` varchar(45) default NULL,
`PersonNewId` bigint(20) NOT NULL auto_increment,
`LName` varchar(254) default NULL,
`MName` varchar(254) default NULL,
`FName` varchar(254) default NULL,
`FatherName` varchar(254) default NULL,
`MotherName` varchar(254) default NULL,
`DateOfBirth` datetime default NULL,
`EmailId` varchar(254) default NULL,
`IdentityMark` varchar(254) default NULL,
`Name` varchar(254) default NULL,
`ShortName` varchar(70) default NULL,
`PANNumber` varchar(70) default NULL,
`MaritalStatusId` bigint(20) default NULL,
`BloodGroupId` bigint(20) default NULL,
`LanguageId` bigint(20) default NULL,
`ReligionId` bigint(20) default NULL,
`CasteId` bigint(20) default NULL,
`CityOfBirth` bigint(20) default NULL,
`CountryOfBirth` int(11) default NULL,
`GenderId` bigint(20) default NULL,
`NationaityId` bigint(20) default NULL,
`TargetDataPorted` tinyint(1) default NULL,
`SourcePersonId` bigint(20) default NULL,
`AddressLine1` varchar(500) default NULL,
`AddressLine2` varchar(500) default NULL,
`PhoneNo` varchar(255) default NULL,
`WeddingDate` datetime default NULL,
`CorrespondanceAddressLine1` varchar(255) default NULL,
`CorrespondanceAddressLine2` varchar(255) default NULL,
`CorrespondanceAddressContactNo` varchar(255) default NULL,
`CorrespondanceAddressPinCode` varchar(255) default NULL,
`PermanentAddressLine1` varchar(255) default NULL,
`PermanentAddressLine2` varchar(255) default NULL,
`PermanentAddressContactNo` varchar(255) default NULL,
`PermanentAddressPinCode` varchar(255) default NULL,
`CorrespondanceAddressCity` bigint(20) default NULL,
`PermanentAddressCity` bigint(20) default NULL,
`Salutation` bigint(20) default NULL,
`Fax` varchar(255) default NULL,
`ContactNo` varchar(255) default NULL,
`MobileNo` varchar(255) default NULL,
`Image` varchar(1024) default NULL,
`groupId1` varchar(45) default NULL,
`SameAsCorrespondenceAddressSameAsCorrespondenceAddress` tinyint(1) default NULL,
`CorrespondanceFAXNo` varchar(50) default NULL,
`PermanentFAXNo` varchar(50) default NULL,
`AreaOfInterest` varchar(100) default NULL,
`StateOfBirth` int(11) default NULL,
PRIMARY KEY (`PersonNewId`),
KEY `fk_35505` (`LanguageId`),
KEY `fk_35506` (`ReligionId`),
KEY `fk_35507` (`CasteId`),
KEY `fk_35503` (`MaritalStatusId`),
KEY `fk_35504` (`BloodGroupId`),
KEY `fk_36852` (`CountryOfBirth`),
KEY `fk_36851` (`CityOfBirth`),
KEY `fk_37066` (`NationaityId`),
KEY `fk_37065` (`GenderId`),
KEY `fk_1213456` (`CorrespondanceAddressCity`),
KEY `fk_1213457` (`PermanentAddressCity`),
KEY `fk_1213563` (`Salutation`),
KEY `fk_1225000` (`StateOfBirth`),
CONSTRAINT `fk_1213456` FOREIGN KEY (`CorrespondanceAddressCity`) REFERENCES `cities` (`CityId`),
CONSTRAINT `fk_1213457` FOREIGN KEY (`PermanentAddressCity`) REFERENCES `cities` (`CityId`),
CONSTRAINT `fk_1213563` FOREIGN KEY (`Salutation`) REFERENCES `salutations` (`Id`),
CONSTRAINT `fk_1225000` FOREIGN KEY (`StateOfBirth`) REFERENCES `states` (`StateId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_35503` FOREIGN KEY (`MaritalStatusId`) REFERENCES `maritalstatuses` (`MaritalStatusId`),
CONSTRAINT `fk_35504` FOREIGN KEY (`BloodGroupId`) REFERENCES `bloodgroups` (`BloodGroupId`),
CONSTRAINT `fk_35505` FOREIGN KEY (`LanguageId`) REFERENCES `languages` (`LanguageId`),
CONSTRAINT `fk_35506` FOREIGN KEY (`ReligionId`) REFERENCES `religions` (`ReligionId`),
CONSTRAINT `fk_35507` FOREIGN KEY (`CasteId`) REFERENCES `castes` (`CasteId`),
CONSTRAINT `fk_36851` FOREIGN KEY (`CityOfBirth`) REFERENCES `cities` (`CityId`),
CONSTRAINT `fk_36852` FOREIGN KEY (`CountryOfBirth`) REFERENCES `countries` (`CountryId`),
CONSTRAINT `fk_37065` FOREIGN KEY (`GenderId`) REFERENCES `genders` (`Id`),
CONSTRAINT `fk_37066` FOREIGN KEY (`NationaityId`) REFERENCES `nationalities` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=24559 DEFAULT CHARSET=latin1
1 row in set (0.89 sec)

mysql> SHOW TABLE STATUS LIKE 'PersonNews' \G
*************************** 1. row ***************************
Name: personnews
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 11266
Avg_row_length: 234
Data_length: 2637824
Max_data_length: 0
Index_length: 3375104
Data_free: 0
Auto_increment: 24559
Create_time: 2009-04-14 03:32:49
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 893952 kB; (`CorrespondanceAddressCity`) REFER `v4core/cities`(`Cit
1 row in set (0.44 sec)

mysql> SHOW CREATE TABLE Cities \G
*************************** 1. row ***************************
Table: Cities
Create Table: CREATE TABLE `cities` (
`CityId` bigint(20) NOT NULL auto_increment,
`CityName` varchar(150) NOT NULL,
`CityCode` varchar(150) default NULL,
`StateId` int(11) NOT NULL,
`DistrictId` bigint(20) default NULL,
`TargetDataPorted` tinyint(1) default NULL,
`SourceCityId` bigint(20) default NULL,
`DClientLocationId` bigint(20) default NULL,
`IsApproved` tinyint(1) default NULL,
`FromPinCode` varchar(255) default NULL,
`ToPinCode` varchar(255) default NULL,
`IsKeyCity` tinyint(1) default NULL,
PRIMARY KEY (`CityId`),
KEY `fk_34630` (`StateId`),
KEY `fk_34631` (`DistrictId`),
CONSTRAINT `fk_34630` FOREIGN KEY (`StateId`) REFERENCES `states` (`StateId`),
CONSTRAINT `fk_34631` FOREIGN KEY (`DistrictId`) REFERENCES `districts` (`DistrictId`)
) ENGINE=InnoDB AUTO_INCREMENT=23773 DEFAULT CHARSET=latin1
1 row in set (0.44 sec)

mysql> SHOW TABLE STATUS LIKE 'Cities' \G
*************************** 1. row ***************************
Name: cities
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 16550
Avg_row_length: 96
Data_length: 1589248
Max_data_length: 0
Index_length: 2048000
Data_free: 0
Auto_increment: 23773
Create_time: 2009-05-09 02:52:09
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 893952 kB; (`StateId`) REFER `v4core/states`(`StateId`); (`District
1 row in set (0.45 sec)

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| bdb_log_buffer_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+---------+
13 rows in set (0.42 sec)

mysql>

Experiencing periodic slow session creation (no replies)

Keith Pierno — Tue, 06 Oct 2009 15:29:16 +0000

Can anyone explain the process session create from the initial client connect through creation of user's session. Currently, I am seeing intermittent slow downs where a new connections hang for about 5 to 20 seconds. If you have an existing connection you are not affected but all new connections hang. There is no information to be found in the logs related to the issue. This is true of both local users and remote connections. We are well below our session limit and have skip-resolve-name set. Server is RHEL5 64bit with MySQL Server version: 5.0.45. The server has ample resources.

[root@xxxxx log]# cat /proc/meminfo | grep MemTotal
MemTotal: 34979304 kB

[root@xxxxx log]# cat /proc/cpuinfo | grep processor
processor : 0
processor : 1
processor : 2
processor : 3
processor : 4
processor : 5
processor : 6
processor : 7

[root@xxxxx log]# uptime
11:29:15 up 351 days, 1:39, 2 users, load average: 1.84, 2.04, 2.15

[root@xxxxx log]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections=1600
wait_timeout=5
thread_cache_size=40

log-bin=mysql-bin
server-id=10
#binlog-ignore-db=mysql

skip-slave-start
skip-name-resolve

# adding this so that we can create a slave of this guy
log-slave-updates
replicate-same-server-id = 0

expire_logs_days = 7

max_allowed_packet = 16M

transaction-isolation =READ-UNCOMMITTED

query_cache_size= 1024M
long_query_time = 1
log_slow_queries
innodb_buffer_pool_size = 20000M
innodb_additional_mem_pool_size = 200M
innodb_file_per_table
innodb_log_file_size = 2000M
innodb_log_buffer_size = 8M

innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 2
key_buffer = 256M
table_cache = 250
sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 512M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]
log=/var/log/mysqld.log
log-error=/var/log/mysqld.err
pid-file=/var/run/mysqld/mysqld.pid

MySQL Administrator and old WinMySQLadmin.exe are the same? In Mysql 5.1 free version is it included? (no replies)

Leonidas Savvides — Sun, 04 Oct 2009 19:55:15 +0000

MySQL Administrator and old WinMySQLadmin.exe are the same? In Mysql 5.1 free version is it included?

Stored Procedure Kills MYSQL ADMIN (no replies)

Evan Panagiotopoulos — Sun, 27 Sep 2009 12:56:56 +0000

I am trying to learn stored procedures and after searching the web I created the following:

CREATE DEFINER=`root`@`localhost` PROCEDURE `all_counties`()
BEGIN
SELECT
*
FROM
`counties`;
END

If I try to edit it or delete it using the Catalogs->Stored Procedures option, the application ends.
I just downloaded this application from mysql.com and I am using a Mac and I am using version 5.0.67.

Thanks,

Evan