MySQL Forums - Knowledge Base

complex update statement (no replies)

Chris Reimann — Mon, 16 Nov 2009 19:31:30 +0000

I want to update this table based on a rather complex logic in terms of what UPDATE can handle - as far as I know...

The table ("example") design:

id         MEDIUMINT UNSIGNED
user_id    MEDIUMINT UNSIGNED
isarchived TINYINT   UNSIGNED
more_data_cols...

1) i set them ALL to isarchived = 1

2) per DISTINCT user_id, i want ONLY 1 row to be isarchived = 0.
how do I go about doing that?

Thanks so much!

Mysql suddendly very slow (1 reply)

Peter Fedeux — Fri, 13 Nov 2009 09:43:08 +0000

Site just died yesterday and started taking forever to complete the simplest queries, mostly slow when doing mysql update, removed loads of indexes from 1 table which uses a few for a stats page

And in error log i get this
"091113 04:45:56 mysqld ended

091113 04:45:57 mysqld started
091113 4:45:57 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
091113 4:45:57 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
/usr/libexec/mysqld: File '/var/log/mysql/slow_queries.log' not found (Errcode: 2)
091113 4:45:57 [ERROR] Could not use /var/log/mysql/slow_queries.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
091113 4:45:57 InnoDB: Started; log sequence number 2 952290533
091113 4:45:57 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
091113 4:51:07 [Note] /usr/libexec/mysqld: Normal shutdown

091113 4:51:07 InnoDB: Starting shutdown...
091113 4:52:09 InnoDB: Shutdown completed; log sequence number 2 1294714176
091113 4:52:09 [Note] /usr/libexec/mysqld: Shutdown complete

091113 04:52:09 mysqld ended

091113 04:56:03 mysqld started
091113 4:56:03 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
091113 4:56:03 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
/usr/libexec/mysqld: File '/var/log/mysql/slow_queries.log' not found (Errcode: 2)
091113 4:56:03 [ERROR] Could not use /var/log/mysql/slow_queries.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
091113 4:56:03 InnoDB: Started; log sequence number 2 1294714176
091113 4:56:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
091113 9:18:27 [Note] /usr/libexec/mysqld: Normal shutdown

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 732 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 731 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 729 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 728 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 727 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 725 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 723 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 722 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 721 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 720 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 718 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 717 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 714 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 712 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 711 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 708 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 705 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 696 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 693 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 692 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 691 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 689 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 688 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 687 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 686 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 680 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 679 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 674 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 670 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 666 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 564 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 431 user: 'root'

091113 9:18:29 [Warning] /usr/libexec/mysqld: Forcing close of thread 120 user: 'root'

091113 9:18:32 InnoDB: Starting shutdown...
091113 9:18:33 InnoDB: Shutdown completed; log sequence number 2 1295163982
091113 9:18:33 [Note] /usr/libexec/mysqld: Shutdown complete

091113 09:18:33 mysqld ended

091113 09:18:34 mysqld started
091113 9:18:34 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
091113 9:18:34 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
/usr/libexec/mysqld: File '/var/log/mysql/slow_queries.log' not found (Errcode: 2)
091113 9:18:34 [ERROR] Could not use /var/log/mysql/slow_queries.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
091113 9:18:34 InnoDB: Started; log sequence number 2 1295163982
091113 9:18:34 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
091113 9:26:35 [Note] /usr/libexec/mysqld: Normal shutdown

091113 9:26:36 [ERROR] /usr/libexec/mysqld: Sort aborted
091113 9:26:37 InnoDB: Starting shutdown...
091113 9:26:39 InnoDB: Shutdown completed; log sequence number 2 1305712595
091113 9:26:39 [Note] /usr/libexec/mysqld: Shutdown complete

091113 09:26:39 mysqld ended

091113 09:26:39 mysqld started
091113 9:26:39 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
091113 9:26:39 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
/usr/libexec/mysqld: File '/var/log/mysql/slow_queries.log' not found (Errcode: 2)
091113 9:26:39 [ERROR] Could not use /var/log/mysql/slow_queries.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
091113 9:26:40 InnoDB: Started; log sequence number 2 1305712595
091113 9:26:40 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
"

If i restart mysql it loads normally for about 2 minutes then goes very slow again, nothing has been changed, no new pages or code has been added

Thanks

load data local infile (1 reply)

bing li — Fri, 20 Nov 2009 12:26:43 +0000

Hallo everyone.
I want to add the pet.txt into my MySql table. But it failed.Could someone explain why? Thank you.

pet.txt
snoppy thomas spec2 m 2008-01-02 NULL

load data local infile'C:/Users/bing/Documents/book/mysql_5_1/pet.txt'

All the columns screez into one column, which I dont want.

import images into blob (1 reply)

Peter M. — Fri, 20 Nov 2009 12:21:13 +0000

Hello everybody,

first of all I do not want to discuss the pros and cons of the two approaches to store images (Either on filesystem or in the database)

Until now i store my images on the filesystem and only have the path to the image stored in the Database.

For some reasons i decided to give the other approach a try and directly store images in the database in a BLOB.

However, i already have images stored on my filesystem which should not get lost.

So, i'm looking for a method to convert the storing of images from approach A to approach B. e.g. filling my new BLOB column automaticly with the images, which are stored on the filesystem.

It would be realy great if this would be possible with an SQL-Script. Any ideas?

Thanks in advance
Peter

Help me Please. How is Converted to MySQL Date Format : d/m/y -> 06/11/2009 (1 reply)

Yadigar Onur FIÇICI — Tue, 10 Nov 2009 13:21:17 +0000

Help me Please. How is Converted to MySQL Date Format : d/m/y -> 06/11/2009

ABD Date Format : 2009-11-06 <- Convert -> Turkey Date Format : 06/11/2009

I'm sorry, I do not know good English.:)

Help is now thank you.

Mixing Japanese and Latin characters (no replies)

Jim ANDERSON — Tue, 03 Nov 2009 09:06:14 +0000

Hi.

One of my clients asked me to include a fourth language (in addition to EN, FR and NL) in his website: Japanese.
The website is developed with php and uses data from mySql tables.

I have no problems doing this in html, but when it comes to using data from mySQL tables all I get is gibberish.

Could you please let me know:
1. How I should create my database so that some fields can contain Japanese text
2. How I should create my php so that Japanese and latin characters display properly.

Thanks too all of you who can give me some concrete help on this issue.

jim Anderson

Mysql.com Estimated Worth $1.1 Million USD (no replies)

pritam sh — Thu, 29 Oct 2009 19:58:10 +0000

Mysql.com Estimated Worth $1.1 Million USD

Description The software's official homepage with news, downloads and documentation.
Daily Pageview 1,038,716
Daily Ads Revenue $1,529.77
Traffic Rank 1059(Alexa World Rank)
Page Rank (Goolge Page Rank)

check it out here http://thenetinfo.com/stats/mysql.com

error # 134 (1 reply)

khalid touati — Tue, 03 Nov 2009 00:19:23 +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!!?

Problem with creating a statement based on variables (no replies)

Hans Karlsson — Sat, 24 Oct 2009 14:42:26 +0000

I want to insert a string (YYYY-MM) in a table. The field is of type CHAR. The value in the INSERT-string is based on variables.

CREATE PROCEDURE `Test`.`TestPart2`(dteFromdat DATE)
.....
#Create the YearMonth variable
IF MONTH(dteFromDat)<10 THEN
SET txtYearMonth=CONCAT(YEAR(dteFromDat), '-0', MONTH(dteFromDat));
ELSE
SET txtYearMonth=CONCAT(YEAR(dteFromDat), '-', MONTH(dteFromDat));
END IF;

#Create the statement
SET @QUERY = CONCAT('INSERT INTO tblKorrlogg SET KorrDate=CURDATE(), YearMonth=', txtYearMonth);
PREPARE stmt FROM @QUERY;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
.......

The statement seem now to looks like this "INSERT INTO tblKorrlogg SET KorrDate=CURDATE(), YearMonth=2009-05"

My problem is how ho add the apostrophes round 2009-05 so it looks like '2009-05' and MySQL will recognize the value as a string.

/Hans Karlsson

What would be the best query (2 replies)

Peter Fedeux — Wed, 28 Oct 2009 12:07:13 +0000

To check whether the box_id "2" has ANY records, that have ANY records with the same box_val as box_id "1"

Using only one query, rather than looping through all records where box_id = 2 then checking for match

box_id | box_val
________________________
1 | 25
1 | 74
1 | 54
2 | 25
2 | 35

Thanks!

question about join query (1 reply)

sherri guthrie — Mon, 19 Oct 2009 21:33:02 +0000

i'm trying to compile data from 2 different tables for a report. this code:

$result4 = mysql_query("select reportequipment.id, equipment.name,reportequipment.qty from reportequipment LEFT JOIN equipment
ON reportequipment.id = equipment.id where webid='$myid'")or die(mysql_error());

$i = 0;
while ( $row = mysql_fetch_array($result4) )
$i++;

returns the row reportequipment.id, but equipment.name is blank.

my results are:

id# name qty
1 (blank) 3

i need :

id# name qty
1 vest 3

thanks for taking the time to assist.

Table Walk using parent/child foreign keys (1 reply)

Jack Baker — Wed, 21 Oct 2009 17:42:31 +0000

Hi

We are using version 5.0.81 of MySQL database and need to do a tale walk using parent/child keys.

We have a table: inventory_categories

INVENTORY_CATEGORY_ID int(15)
PARENT_INVENTORY_CATEGORY_ID int(15)
INVENTORY_CATEGORY_NAME varchar(30)
INVENTORY_CATEGORY_DESCRIPTION varchar(80)
INVENTORY_CATEGORY_TYPE_ID int(15)
INVENTORY_CATEGORY_ORDER int(10)
GRAPHICS_FILE_NAME varchar(80)
DELETED_INDICATOR char(1)
CREATED_BY int(15)
CREATION_DATE datetime
LAST_UPDATED_BY int(15)
LAST_UPDATE_DATE datetime

Where each category can have one or more sub-catgories and each sub-catgory can have one or more sub-categories, etc.

We need a quick way to traverse the tables to find all sub-categories (inventory_category_id) for a given inventory_category_id.

We are using JSP with embedded SQL. We obviously would like to accomplish as much of this within the query itself.

Thanks in advance for your help!

Jack

MySQL For large number of files (1 reply)

List Andy — Thu, 15 Oct 2009 22:17:44 +0000

Hi all,

I need to efficiently handle a large number of files (upto 1TB in total). The data in question is a large collection of patent documents. Each patent document has sections like name of the person, topic, claim, background etc. I want to be able to store all this information within the database (each section in a separate column, for example). The data will be inserted only one time and I do not need to modify it once it is done. I will only need to be able to issue specific queries across different sections of the documents. For example: give me all documents filed by "person X" that are on "topic Y".

Would MySQL be appropriate for this?

Thanks in advance.

Connectivity issue (3 replies)

praveen chaduvula — Fri, 09 Oct 2009 05:42:42 +0000

Hi,
I am getting an error message while I am trying to connect to one mysql server hosted on a unix box.

"Lost connection to MYSQL Server during query."

Can anyone help me with this?

Regards,
Praveen.

Constraint help (1 reply)

T.J. Eakle — Mon, 05 Oct 2009 23:16:23 +0000

Hey there,

I have a user_id and primary_ind column. I would like as many of the same user id's to be able to be inserted into the table, but only one of them would be allowed to have a 'Y' as the primary ind. Can someone help me with that constraint script?

Thanks,

T.J.

insert problem with not null columns (1 reply)

Mazen Elsayed — Tue, 06 Oct 2009 08:58:52 +0000

Dear Friends;

I am new to mysql, and I created a table named "test", with 3 columns (id"PK", firstName, lastName), all of them not null.

the problem comes when I execute the following sql statement;

INSERT INTO test (id,firstName) VALUES (4,'steven');

the record will be accepted, and there is no value in the column "lastName", even I put that as not null. How can the mySQL can accept this query, what is the value that inserted into "lastName" columns ??

regards
Mazen Elsayed

Similar to search involving multiple tables (no replies)

Brook Julias II — Tue, 29 Sep 2009 14:43:49 +0000

Im trying to create a search function that will return all rows that are similar to the search constraints, but I would like the search constraint to be matched will multiple columns. (ie. If the user submitted the search "Acid C-Note" it would query the brand, cigarName, etc. columns of the cigar table returning any rows that in some way match the seach.)

Threads_running always 1 (no replies)

Anh Nguyen — Fri, 25 Sep 2009 16:05:32 +0000

Hi,

I applied a high load to mysql, both 'show status' and ps always showed 1 thread running. I am wondering if the issue has been discussed somewhere and a corresponding bug has been filed, and/or also if I have misconfigured mysql in any ways.
'show innodb status' showed transactions "not started" with different thread ids.

Suggestions/pointers are appreciated.

Here are my testing environment, and the results of the aforementioned commands:

[root@perf14 pt]# uname -a
Linux xxx.com 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

[root@perf14 pt]# /usr/libexec/mysqld --version --help
/usr/libexec/mysqld Ver 5.0.84-log for redhat-linux-gnu on x86_64 (Source distribution)

[root@perf14 pt]# rpm -qa | grep mysql
mysql-5.0.84-2.el5s2
mysql-libs-5.0.84-2.el5s2
php-mysql-5.2.9-2.el5s2
mysql-jdbc-5.0.8-1jpp.1.el5s2
mysql-libs-5.0.84-2.el5s2
mysql-connector-odbc-3.51.27r695-1.el5s2
mysqlclient14-4.1.22-1.el5s2
mysql-5.0.84-2.el5s2
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-server-5.0.84-2.el5s2

[root@perf14 calMode_200i_2min_withstatus.1]# cat my.cnf
[mysqld]
datadir=/home/mysql/data
user=mysql
old_passwords=1
socket=/var/lib/mysql/mysql.sock
innodb_data_home_dir=/home/mysql/innodb_data
innodb_data_file_path = ibdata1:2000M:autoextend
innodb_log_group_home_dir=/home/mysql/logs
innodb_additional_mem_pool_size=32M
#innodb_flush_log_at_trx_commit=120000
innodb_flush_log_at_trx_commit=0
max_connections=256
table_cache=4096
thread_cache=16
thread_cache_size=16
innodb_file_per_table = true

log-slow-queries=/tmp/mysql-slow.log
log-long-format
long_query_time = 6

# Set the Innodb buffer pool size to n GB
# = this value is in bytes (80% of total memory)
innodb-buffer-pool-size = 16G
# Turn off the check-sum of pages read from disk
innodb-checksums = false
# Let any number of threads commit concurrently
#innodb-commit-concurrency = 0
innodb-commit-concurrency = 8
# Turn off the double write function
innodb-doublewrite = false
# Use direct io
innodb-flush-method = O_DIRECT
#innodb-flush-method = O_DSYNC - TBT
# Use a large buffer for the transaction log (you may want to go over 8M, and go even larger)
innodb-log-buffer-size = 16M
#innodb-log-buffer-size = 1G
#innodb-log-buffer-size = 64M
# Set the log file size to one half the buffer pool above
innodb-log-file-size = 1G
#innodb_log_buffer_size = 8M -TBT alap
#innodb_log_file_size = 128M - TBT alap

#key_buffer = 1000M

# Turn off XA support as its not needed in this test
innodb-support-xa = false
# Set the number of concurrent threads to unlimited
innodb-thread-concurrency = 8
# Turn on large page memory support
large-pages = true
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

|
=====================================
090925 9:12:14 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3273, signal count 2226
Mutex spin waits 0, rounds 152068, OS waits 1940
RW-shared spins 2419, OS waits 1003; RW-excl spins 495, OS waits 166
------------
TRANSACTIONS
------------
Trx id counter 0 28368021
Purge done for trx's n:o < 0 28141830 undo n:o < 0 0
History list length 7
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 28367857, not started, process no 10262, OS thread id 118960364
8
MySQL thread id 55, query id 2862347 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367855, not started, process no 10262, OS thread id 118933740
MySQL thread id 54, query id 2862405 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367821, not started, process no 10262, OS thread id 118800620
8
MySQL thread id 49, query id 2862425 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367819, not started, process no 10262, OS thread id 118507756
8
MySQL thread id 38, query id 2862179 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28366479, not started, process no 10262, OS thread id 118534380
8
MySQL thread id 39, query id 2846595 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367838, not started, process no 10262, OS thread id 118827244
8

There are more "not started" threads, but I cut them out

--------
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
200 OS file reads, 4278 OS file writes, 2917 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.89 writes/s, 2.26 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 35401603, used cells 1948522, node heap has 2957 buffer(s)
3758.02 hash searches/s, 4329.20 non-hash searches/s
---
LOG
---
Log sequence number 7 1985380255
Log flushed up to 7 1985344379
Last checkpoint at 7 1983716162
0 pending log writes, 0 pending chkp writes
1824 log i/o's done, 2.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1886459797; in additional pool allocated 12493312
Buffer pool size 1048576
Free buffers 994542
Database pages 51077
Modified db pages 299
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 204, created 50983, written 53735
0.00 reads/s, 3.06 creates/s, 11.23 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10262, id 1177622848, state: sleeping
Number of rows inserted 8906466, updated 1, deleted 0, read 1364493
341.14 inserts/s, 0.00 updates/s, 0.00 deletes/s, 4139.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
1 row in set, 1 warning (0.10 sec)

mysql>
mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
--More--(45%)
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 246 |
| Bytes_sent | 35887 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
--More--(50%)
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
--More--(60%)
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 3 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 2 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
--More--(65%)
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 56 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 3 |
--More--(69%)
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 506 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 636 |
| Innodb_buffer_pool_pages_data | 51246 |
| Innodb_buffer_pool_pages_dirty | 341 |
| Innodb_buffer_pool_pages_flushed | 54657 |
| Innodb_buffer_pool_pages_free | 994352 |
| Innodb_buffer_pool_pages_misc | 2978 |
| Innodb_buffer_pool_pages_total | 1048576 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 84251496 |
| Innodb_buffer_pool_reads | 164 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 33545721 |
| Innodb_data_fsyncs | 3089 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 5525504 |
| Innodb_data_reads | 200 |
| Innodb_data_writes | 4496 |
| Innodb_data_written | 2001267712 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 2165426 |
| Innodb_log_writes | 1562 |
| Innodb_os_log_fsyncs | 1879 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1105554432 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 51152 |
| Innodb_pages_read | 204 |
| Innodb_pages_written | 54657 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 8934138 |
| Innodb_rows_read | 1699845 |
| Innodb_rows_updated | 1 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6694 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
--More--(84%)
| Max_used_connections | 51 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 15 |
| Open_streams | 0 |
| Open_tables | 106 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 3500545 |
| Questions | 10 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 3781054 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 51 |
| Threads_created | 51 |
| Threads_running | 1 |
| Uptime | 1690 |

mySQL DATE_FORMAT question (no replies)

Gheorghe Stefan — Fri, 25 Sep 2009 14:51:11 +0000

Hello,
I'm trying to implement DATE_FORMAT function in C for a personal project and I'm stuck on the %v %u %x %V %U %X parameters. Does somebody know what exactly is the difference between U and V (u and v respectively) ? What is the difference between 00..53 and 01..53? I've
made some interesting tests in mysql with january 1st for each year from 1990 to 2009 and found some strange values like:
1/1/2005 -> u=0 v=53 x=2004 U=0 V=52 X=2004
1/1/1995 -> u=0 v=52 x=1994 U=1 V= 1 X=1995.
Can somebody please explain these values to me?

Thank you very much!

Ads post jobs earn 30000 per month (no replies)

allabakasha ab — Thu, 24 Sep 2009 15:06:39 +0000

Dear Friends, Are you interested to make Rs.1000 to Rs 2000 A Day? This is not a get rich quick scheme. This is a legal opportunity to make good money when you do it part time. This opportunity is a proven way to make Rs.1000 to Rs.2000 A Day . There are already many people around the world grabbed this opportunity and making tons of money every month. If you are interested to know more about this opportunity, visit
www.postadsearnmoney.com call 9731363571 magicmoneyearn@gmail.com Posted Id- 00254

Not allow to create func usig admin rights (1 reply)

suhas mahajan — Tue, 06 Oct 2009 10:24:42 +0000

Hi All,

I am trying to create function on informational_schema using admin rights

But it is throwing me an ERROR 1044: Access denied for ser: 'username@.localhost' to database 'Informational_schema'

Thanks in advance

Convert Long value into Date (4 replies)

airease 16 — Fri, 09 Oct 2009 04:57:19 +0000

How to convert long value of date into date format using sql functions in mysql.

Convert Date Format (1 reply)

airease 16 — Tue, 06 Oct 2009 08:41:24 +0000

My sql version is 5. i want to convert date in format 'Tue sep 01 00:00:00 GMT 2009' into '2009-09-01 00:00:00'. I have tried Date_Format and str_to-date functions but if string contains timezone for eg. 'GMT' it doesn't convert it and gives null value. How can i convert date 'Tue sep 01 00:00:00 GMT 2009' into format '2009-09-01 00:00:00'

Urgent -Search column name by column number (1 reply)

suhas mahajan — Tue, 06 Oct 2009 10:36:55 +0000

Hi All,
How do we get exact column name by giving its column number?

Scenario,
We need to find column name by its position number in table.

E.g. I have emp table having fields (eno, ename, addr, email)

Now I will put the condition in where clause ---

Where column_number=2;
then it should take the ename. and returns the value.

How can I achieve it.

Thank in advance

Replication btw 2 tables in same database. (no replies)

Pratik Singh — Tue, 22 Sep 2009 08:26:20 +0000

Hello Guys ,

I have one odd kind of requirement. I need to copy some kind of data from table1 to table2 , I was using trigger earlier but that kills the DB.

is that possible if I can use replication btw 2 tables in same database. like what event insert/update/delete happened on table1 , that should replicate on table2 .. if that is possible then no need to use big trigger finally to copy the data from one table to other table...

in other tear I would say table1 is subset of table2 .. so what ever happen in table1 , should replicate on table2

would be great ,if there is other solution is possible to copy the data from table1 to table2 at the end process ( except trigger)

Please do let me know if my requirement is not enough clear ...

Thanks in advance !

Update database MYSQL with VB6 (no replies)

GERARD LAPORTE — Fri, 18 Sep 2009 09:58:18 +0000

Hi..I'm trying to replace FireBird by Mysql database. My VB6 programs don't want to update the database . I'm using Mysql V5.1 . I didn't have any problem to load data in the tables with a VB6 program but when trying to update a record I have the message execution error "2147467259(80004005) La mise a jour basee sur une requete a echoué car la ligne a mettre a jour est introuvable"(French version). But the table fields contain the good data . Here is a part of the code :

Set BONMONSQL = New Recordset
result_req = " select * from BONMONTEUR"
BONMONSQL.Open result_req, SQLServer, , adLockOptimistic

If Not BONMONSQL.EOF Then
BONMONSQL.MoveFirst
End If
Do While Not BONMONSQL.EOF

BONMONSQL!DateTransf = Date
BONMONSQL.Update
Loop
Please help

Table Locking (1 reply)

Vikas Gupta` — Tue, 06 Oct 2009 10:51:37 +0000

Hi all,

My question is in time period of Begin(start transaction) and Commit table is locked or not?

slave_compressed_protocol value in 4.1.11 Debian Etch (no replies)

Gabe Tucker — Tue, 15 Sep 2009 15:17:50 +0000

Hi,

I would like to know the value of slave_compressed_protocol in V4.1.11 on Debian Etch.

Show Variables does NOT give this variable.

I turned it on:
SET GLOBAL SLAVE_COMPRESSED_PROTOCOL=1;

There were no errors or warnings. I see nothing in the system log, on neither the Slave nor Master.

I would like to be able to verify its setting. How can I see that the Slave Compression is on?

Thanks
- Gabe

Import multiple CSV files in one Mysql table. (2 replies)

suhas mahajan — Mon, 14 Sep 2009 04:57:25 +0000

Hello all,
Scenario:

1. I have requirement where multiple CSV files should imported autoomatically in one table by appending it (myasql database).
E.g files a.csv has 10 record import in table A with 10 records then b.csv file has 20 records should get imported in table A i.e 10+20 records record in second file should append in table A.

I have written a batch script which import one csv file in mysql table but how can I achieve it for bulk or multiple files.

2. How can I import multiple files in multiple tables.
E.g - File a.csv, b.csv, c.csv (stored in one n/w folder) should get imported in Table a, b, c by using batch script.

Awaiting for your response.

Thanks in advance.

Inserting french characters in mySQL DB table (no replies)

Stephane Halle — Mon, 31 Aug 2009 15:31:01 +0000

Hello,

I feel like this has been discussed a whole bunch on different forums but still, I haven't been able to find out what's wrong with my set up...
I am having a major issue with inserting french characters in my mySQL DB. The french charaters display perfectly fine on the client-side page. If I edit a field directly in the phpMySQL control panel, the french characters also displays correctly.
But if I insert or update fields from a form on a webpage (using PHP), it converst the special french characters into something not readable. For example, à becomes Ã Â.

I feel as if I've tried it all... any ideas?? here are the variables set on my server for phpMySQL:
*********************************************
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_general_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
********************************************

Any ideas or suggestions would be much appreciated!

Thanks,
Steph