Re: MySQL 5.5 Slow performance to insert
Hi, Your INSEERquery status is Copying to tmp table, this means fetching rows which has to be inserted is slow. You should tune SELECT statement in your insert query. Adding indexes and/or simplifying query and/or .. so on. ``` ---TRANSACTION 31D6D74, ACTIVE 27107 sec mysql tables in use 8, locked 8 7470 lock struct(s), heap size 801208, 849088 row lock(s) MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080 10.180.17.252 root Copying to tmp table insert into CONFERENCIA_ENCALHE (data, preco_capa_informado, qtde, qtde_informada, chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id, movimento_estoque_id, movimento_estoque_cota_id, produto_edicao_id,juramentada) (select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde, cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0 from movimento_estoque_cota_memoria mec, movimento_estoque_memoria me, chamada_encalhe ce, chamada_encalhe_cota cec, controle_conferencia_encalhe cce, controle_conferencia_encalhe_cota ccec, produto_edicao pe where mec.tipo_movimento_id = ``` yoku0825, 2015-07-25 5:35 GMT+09:00 Camilo Vieira camilo.vie...@gmail.com: Hi, My MySQL server is performing very slow inserts. Does somebody could help me to understand what's happening? mysql show engine innodb status \G *** 1. row *** Type: InnoDB Name: Status: = 150724 17:40:28 INNODB MONITOR OUTPUT = Per second averages calculated from the last 1 seconds - BACKGROUND THREAD - srv_master_thread loops: 173967 1_second, 173966 sleeps, 17386 10_second, 191 background, 191 flush srv_master_thread log flush and writes: 174022 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 40941, signal count 55139 Mutex spin waits 101247, rounds 886301, OS waits 24221 RW-shared spins 29684, rounds 413636, OS waits 11014 RW-excl spins 18765, rounds 257448, OS waits 5190 Spin rounds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl LATEST FOREIGN KEY ERROR 150722 18:23:08 Transaction: TRANSACTION 31AC5E3, ACTIVE 8 sec inserting mysql tables in use 3, locked 3 826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries 9932 MySQL thread id 932, OS thread handle 0x7feaf0042700, query id 42396 10.180.17.252 root insert into movimento_estoque_cota ( ID,APROVADO_AUTOMATICAMENTE,DATA_APROVACAO,STATUS,DATA,DATA_CRIACAO,TIPO_MOVIMENTO_ID,USUARIO_ID, QTDE,PRODUTO_EDICAO_ID,COTA_ID,ESTOQUE_PROD_COTA_ID,ORIGEM,APROVADOR_ID,LANCAMENTO_ID,status_estoque_financeiro, PRECO_COM_DESCONTO,PRECO_VENDA,VALOR_DESCONTO,FORMA_COMERCIALIZACAO) (select ID,true,date(sysdate()),'APROVADO',DATA,date(sysdate()),TIPO_MOVIMENTO_ID,1, QTDE,PRODUTO_EDICAO_ID,COTA_ID, ESTOQUE_PROD_COTA_ID, 'CARGA_INICIAL',1,LANCAMENTO_ID, IF(STATUS_ESTOQUE_FINANCEIRO=1,'FINANCEIRO_PROCESSADO','FINANCEIRO_NAO_PROCESSADO'), PRECO_COM_DESCONTO,PRECO_ Foreign key constraint fails for table `teste_historico`.`movimento_estoque_cota`: , CONSTRAINT `FK459444C362506D6B` FOREIGN KEY (`ESTOQUE_PROD_COTA_ID`) REFERENCES `estoque_produto_cota` (`ID`) Trying to add in child table, in index `FK459444C362506D6B` tuple: DATA TUPLE: 2 fields; 0: len 8; hex 80054211; asc B ;; 1: len 8; hex 8009743f; asc t?;; But in parent table `teste_historico`.`estoque_produto_cota`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80053f0d; asc ? ;; 1: len 6; hex 031aba25; asc %;; 2: len 7; hex 85561a1a14; ascV ;; 3: len 9; hex 80; asc ;; 4: len 9; hex 80003c; asc ;; 5: len 8; hex 8044; ascD;; 6: len 8; hex 8001007d; asc};; TRANSACTIONS Trx id counter 31DDD14 Purge done for trx's n:o 31D8A49 undo n:o 0 History list length 425 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 18036, OS thread handle 0x7fea5e591700, query id 2854409 localhost root show engine innodb status ---TRANSACTION 31DDD13, not started MySQL thread id 16024, OS thread handle 0x7fea5e4cd700, query id 2854407 10.129.28.111 root ---TRANSACTION 31DC01D, not started MySQL thread id 15932, OS thread handle 0x7fea5e52f700, query id 2812134 10.129.28.111 root ---TRANSACTION 31D83B4, not started MySQL thread id 15931, OS thread handle 0x7fea5d714700, query id 2721807 10.129.28.111 root ---TRANSACTION 31D886C, not started MySQL thread id 15930, OS thread handle 0x7fea5d776700, query id 2730189 10.129.28.111 root ---TRANSACTION 31D8396, not started MySQL thread id 15924, OS thread handle 0x7feaf0073700, query id 2721613 10.129.28.111 root ---TRANSACTION
Re: ORDER BY not using index?
Hi, Your query have to access all rows in `myTable`, thus MySQL optimizer guesses reading sequentially is faster than working through an index. http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html The case of not using index, * Reading whole myTable.MYD sequentially * Sorting 443k rows The case of using index, * Reading all of idx_DateAccessed * Reading whole myTable.MYD *randomly* MySQL would like to avoid randomly scan, maybe. You can avoid filesort with FORCE INDEX, as you tell. Regards, 2015-07-18 16:32 GMT+09:00 Chris Knipe sav...@savage.za.org: Hi, Can someone perhaps assist with the below... I'm not sure at all why my index aren't being used for the ORDER BY. Currently some 443K records in the table, but this will grow to a good few million. I simply cannot, afford a filesort. mysql SELECT COUNT(*) FROM myTable; +--+ | COUNT(*) | +--+ | 443808 | +--+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT * FROM myTable ORDER BY DateAccessed; ++-+--+--+---+--+-+--+++ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--+++ | 1 | SIMPLE | myTable | ALL | NULL | NULL | NULL| NULL | 443808 | Using filesort | ++-+--+--+---+--+-+--+++ 1 row in set (0.00 sec) mysql EXPLAIN SELECT * FROM myTable FORCE INDEX (idx_DateAccessed) ORDER BY DateAccessed; ++-+--+---+---+--+-+--++---+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+---+--+-+--++---+ | 1 | SIMPLE | myTable | index | NULL | idx_DateAccessed | 4 | NULL | 443808 | | ++-+--+---+---+--+-+--++---+ 1 row in set (0.00 sec) mysql SHOW CREATE TABLE myTable; +--+-+ | Table| Create Table | +--+-+ | myTable | CREATE TABLE `myTable` ( `ArticleID` char(32) NOT NULL, `DateObtained` int(10) unsigned NOT NULL, `DateAccessed` int(10) unsigned NOT NULL, `TimesAccessed` int(10) unsigned NOT NULL, PRIMARY KEY (`ArticleID`), KEY `idx_DateAccessed` (`DateAccessed`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 | +--+-+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM myTable; +--++--+--+--+---+-+--++--++-+---+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--++--+--+--+---+-+--++--++-+---+ | myTable | 0 | PRIMARY |1 | ArticleID| A | 443808 | NULL | NULL | | BTREE | | | | myTable | 1 | idx_DateAccessed |1 | DateAccessed | A | 147936 | NULL | NULL | | BTREE | | | +--++--+--+--+---+-+--++--++-+---+ 2 rows in set (0.00 sec) -- Regards, Chris Knipe -- MySQL General Mailing List For list archives:
Re: Mysql Syslog client.
Hi Stephan, I've already tried syslog but it works fine in my environment. $ /usr/mysql/5.7.6/bin/mysql --syslog -h 192.168.198.214 -u yoku mysql show databases; Mar 13 11:27:57 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka', MYSQL_USER:'yoku', CONNECTION_ID:9, DB_SERVER:'192.168.198.214', DB:'--', QUERY:'show databases;' $ /usr/mysql/5.7.6/bin/mysql -h sys-db02 -P 3307 -u yoku --syslog mysql status Mar 13 11:30:01 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka', MYSQL_USER:'yoku', CONNECTION_ID:17818, DB_SERVER:'sys-db02', DB:'--', QUERY:'status' mysql show databases; Mar 13 11:30:03 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka', MYSQL_USER:'yoku', CONNECTION_ID:17818, DB_SERVER:'sys-db02', DB:'--', QUERY:'show databases;' $ /usr/mysql/5.7.6/bin/mysql -S /usr/mysql/5.7.6/data/mysql.sock -u root --syslog mysql select current_user(); Mar 13 11:30:24 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka', MYSQL_USER:'root', CONNECTION_ID:10, DB_SERVER:'--', DB:'--', QUERY:'select current_user();' mysql command line client's --syslog sends log to local(client machine)'s syslogd. yoku0825, 2015-03-13 3:59 GMT+09:00 Stephan Gomes Higuti higuti@gmail.com: Hello guys. Im using MySQL 5.7.6 for testing some new features, including syslog support for client. Its working great and Im getting the following lines when using localhost: Mar 12 12:36:34 server1 MysqlClient[17854]: SYSTEM_USER:'user1', MYSQL_USER:'userdb', CONNECTION_ID:5, DB_SERVER:'--', DB:'mysql', QUERY:'use mysql' Mar 12 12:36:36 server1 MysqlClient[17854]: SYSTEM_USER:'user1', MYSQL_USER:'userdb', CONNECTION_ID:5, DB_SERVER:'--', DB:'mysql', QUERY:'show tables;' Mar 12 12:36:55 server1 MysqlClient[17856]: SYSTEM_USER:'user1', MYSQL_USER:'userdb', CONNECTION_ID:6, DB_SERVER:'--', DB:'mysql', QUERY:'use mysql ' However, if I try to access MySQL in any other server, for example mysql -uuser -ppasswd -hserver2, it doesn't log in syslog. My client section config is: [client] #password = [your_password] port= 3306 socket = /var/lib/mysql/mysql.sock syslog Have any of you tried this? Thank you in advance. Stephan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: command is not allowed with this MySQL version
Hi, Christophe has already told, The used MySQL version is 5.5.40 from Debian Wheezy package. Maybe you missed *mysql command-line client's --local-infile option* $ bin/mysql mysql SELECT @@version; ++ | @@version | ++ | 5.5.40-log | ++ 1 row in set (0.00 sec) mysql SELECT @@local_infile; ++ | @@local_infile | ++ | 1 | ++ 1 row in set (0.00 sec) mysql LOAD DATA LOCAL INFILE '/files/tmp_import.csv' INTO TABLE users FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES (@dummy, nom, prenom, @dummy, @dummy, email, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy,@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy); ERROR 1148 (42000): The used command is not allowed with this MySQL version $ bin/mysql --local-infile mysql LOAD DATA LOCAL INFILE '/files/tmp_import.csv' INTO TABLE users FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES (@dummy, nom, prenom, @dummy, @dummy, email, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy,@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy); ERROR 1146 (42S02): Table 'd1.users' doesn't exist Would you try it? yoku0825, 2014-12-12 11:00 GMT+09:00 Johan De Meersman vegiv...@tuxera.be: - Original Message - From: Christophe t...@stuxnet.org Subject: command is not allowed with this MySQL version 'The used command is not allowed with this MySQL version' Out of sheer morbid curiosity, what version were they running? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say. If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com: It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
Maybe no, as you knew. It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. Not only binary logs, each tables in your dump is based the time when mysqldump began to dump *each* tables. It means, for example, table1 in your dump is based 2014-10-07 00:00:00, and next table2 is based 2014-10-07 00:00:01, and next table3 is .. I don't have a motivation for restoring its consistency.. Regards, 2014-10-07 15:44 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com: So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be of any useful? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 11:55 AM, yoku ts. yoku0...@gmail.com wrote: Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say. If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com : It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Order column in the second table
Hi, Would you try STRAIGHT_JOIN? mysql56 ALTER TABLE masik DROP KEY idx_test, ADD KEY idx_test(szam, id); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql56 EXPLAIN SELECT e.id FROM masik m STRAIGHT_JOIN egyik e ON e.id= m.id WHERE e.duma= 'aaa' ORDER BY m.szam ASC; ++-+---++---+--+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+--+-+-+--+-+ | 1 | SIMPLE | m | index | m_idx | idx_test | 8 | NULL|2 | Using index | | 1 | SIMPLE | e | eq_ref | PRIMARY,e_idx | PRIMARY | 4 | d2.m.id |1 | Using where | ++-+---++---+--+-+-+--+-+ 2 rows in set (0.00 sec) This may be faster than original situation if e.duma doesn't have a well cardinality or you can use LIMIT clause. This can eliminate a temporary table and file, but this needs whole index scan of m.idx_test. Regards, yoku0825 2014-06-12 20:42 GMT+09:00 Lay András and...@lay.hu: Hi! On Thu, Jun 12, 2014 at 1:36 PM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi Lay, If I don't mistake, you can't eliminate Using temporary and Using filesort because you are using an order by. Try the explain again removing order by and check the output. Thank you, I know, without order no problem: (root@localhost) [test] explain select e.id from egyik e,masik m where e.id=m.id and e.duma='aaa'; +--+-+---+--+---+---+-+---+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +--+-+---+--+---+---+-+---+--+--+ |1 | SIMPLE | e | ref | PRIMARY,e_idx | e_idx | 767 | const |1 | Using where; Using index | |1 | SIMPLE | m | ref | m_idx | m_idx | 4 | test.e.id |1 | Using index | +--+-+---+--+---+---+-+---+--+--+ 2 rows in set (0.02 sec) When you use an order by, MySQL needs to use filesort and spends some time sorting the result set. Also, create a temporary table with the rows prevously. This is the reason. Not every situation. If the order column in the first table, no problem too: (root@localhost) [test] explain select e.id from egyik e,masik m where e.id=m.id and e.duma='aaa'order by e.duma asc; +--+-+---+--+---+---+-+---+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +--+-+---+--+---+---+-+---+--+--+ |1 | SIMPLE | e | ref | PRIMARY,e_idx | e_idx | 767 | const |1 | Using where; Using index | |1 | SIMPLE | m | ref | m_idx | m_idx | 4 | test.e.id |1 | Using index | +--+-+---+--+---+---+-+---+--+--+ 2 rows in set (0.00 sec) But i need to order with column in the second table. Bye! Lay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: blob data types
Hello, I can see MyISAM stores BLOB column as same space as other data type column, but InnoDB doesn't. (If you mean same .ibd file it's true) http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-overview.html ROW_FORMAT= Compact holds first 768 bytes of BLOB column, ROW_FORMAT= Dynamic or Compressed holds first 20bytes of column. http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-internals-storage-blobs.html 2014-05-26 18:56 GMT+09:00 Reindl Harald h.rei...@thelounge.net: Am 26.05.2014 11:40, schrieb geetanjali mehra: I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. inside the table, it's just a field type
Re: Why MySQL-server-5.5.37 install fail?
Hello, mysqladmin and mysql command-line client is included in MySQL-client package. mysql_secure_installation is included in MySQL-server but it's a perl script which calls mysql command-line client internally. Thus, you should install MySQL-client-*.rpm and try again. Regards, 2014-05-04 15:29 GMT+09:00 EdwardKing zhan...@neusoft.com: I'm newbie to mysql and I want to install mysql under Centos 5.8 using MySQL-server-5.5.37-1.linux2.6.i386.rpm, I use following command to intall mysql #[root@master software]# rpm -ivh MySQL-server-5.5.37-1.linux2.6.i386.rpm Preparing...### [100%] 1:MySQL-server ### [100%] PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h master password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. Please report any problems at http://bugs.mysql.com/ Then I use following command: [root@master bin]# pwd /usr/bin [root@master bin]# /usr/bin/mysqladmin -u root password '123456' bash: mysqladmin: command not found [root@master bin]# ls mysqladmin ls: mysqladmin: No such file or directory [root@master bin]# mysql_secure_installation Can't find a 'mysql' client in PATH or ./bin How to install mysql and start mysql? Why are the commands of mysqladmin and mysql_secure_installation fail? I am puzzled with it for many days, I still can't find a solution. Anyone could help me? Thanks. Thanks. --- Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s) is intended only for the use of the intended recipient and may be confidential and/or privileged of Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying is strictly prohibited, and may be unlawful.If you have received this communication in error,please immediately notify the sender by return e-mail, and delete the original message and all copies from your system. Thank you. ---
Re: All client commands to syslog?
Hello, --syslog is option of mysql command line client and it works in my environment. Do you put [mysql] section in your my.cnf? Regards, yoku 2013/3/28 Rafał Radecki radecki.ra...@gmail.com: Hi All. I have a production setup of four databases connected with replication. I would like to log every command that clients execute for auditing. I've read http://www.percona.com/doc/percona-server/5.5/diagnostics/mysql_syslog.html?id=percona-server:features:mysql_syslogredirect=1#client-variables but despite the fact that I use percona Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 it does not seem to work. I've added syslog/syslog ON to my my.cnf server configuration file but no info about executed commands in logs. I've also read http://www.mysqlperformanceblog.com/2008/07/08/logging-your-mysql-command-line-client-sessions/. What is the best way to log all client commands? Best regards, Rafal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Error in MMM setup from 5.1 to 5.6
Hello, It's because of binlog_checksum = crc32 setted default at 5.6.5. If you use 5.6's master and 5.5(or earlier)'s slave, you need to set binlog_checksum = none on 5.6. 2013/2/12 Adarsh Sharma eddy.ada...@gmail.com: Hi, I have a Multi Master Mysql setup between two mysql servers running 5.1.66- version. But i need to try Mysql 5.6 latest version. I configured a slave with Mysql 5.6.10 running but when i am trying start slaves in 5.1 Master it shows me below error : *Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.01' a'* * * Also while installing 5.6 it shows me below msg in logs : 2013-02-12 04:45:21 6882 [Note] InnoDB: CPU does not support crc32 instructions I did some R n D about the issue but not succeeded, Anyone has any context about this. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Hi
Because of that, those queries don't use index.
log-queries-not-using-indexes works even if query time less than
long-query-time.
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes
regards,
yoku
2012/9/1 Adarsh Sharma eddy.ada...@gmail.com:
Hi all,
I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow query log
by setting below parameters in my.cnf :
log-slow-queries=/usr/local/mysql/slow-query.log
long_query_time=100
log-queries-not-using-indexes
I am assuming from the inf. from the internet that long_query_time is in
seconds , but i see the slow query log , there are lots of statements (
queries ) :
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.052784 Lock_time: 0.43 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409734;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:14
# User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.053599 Lock_time: 0.79 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-click-enhancer-deferred';
# User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.054185 Lock_time: 0.86 Rows_sent: 1 Rows_examined:
141145
SET timestamp=1346409794;
select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME
date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
ENTITY_NAME='FETL-ImpressionRC-conversion';
# Time: 120831 10:43:22
# User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111]
# Query_time: 0.000163 Lock_time: 0.45 Rows_sent: 1 Rows_examined: 13
I don't understand the query time unit in slow query log because i expect
queries to be logged that takes 100 s. I tested with sleep command for
60s , it doesn't logged in slow query log and when i sleep for 120 s it
logged but i don't why the other queries are logging in slow log.
# Query_time: 120.000259 Lock_time: 0.00 Rows_sent: 1 Rows_examined: 0
SET timestamp=1346443103;
SELECT SLEEP(120);
And also my slow log is increasing and decided to purge thorogh below
command :
cat /dev/null /var/lib/mysql/slow-queries.log
Anyone any ideas about this.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: alternative to slow query
Hello, add index to expression1_id and expression2_id on expression_expression. it doesn't use index,following, WHERE ee2.expression1_id = $ID OR ee1.expression2_id = $ID regards, 2012/7/3 brian mysql-l...@logi.ca I have a table that joins on itself through a second table: table expression: id INT PRIMARY KEY, lang_id INT term VARCHAR(128) table expression_expression: id INT PRIMARY KEY expression1_id INT expression2_id INT In order to find associated records, I had originally used a UNION, which worked very well. However, the application is written in PHP and uses PDO. PDOStatement::getColumnMeta() doesn't return anything for the table name with a UNION and this is crucial to the application. So I've come up with the following substitute: SELECT e.id, e.lang_id, e.term FROM expression AS e LEFT JOIN expression_expression AS ee1 ON ee1.expression1_id = e.id LEFT JOIN expression_expression AS ee2 ON ee2.expression2_id = e.id WHERE ee2.expression1_id = $ID OR ee1.expression2_id = $ID This gives me the correct values but is rather (~2-4 sec) slow. Here's the EXPLAIN output: *** 1. row *** id: 1 select_type: SIMPLE table: e type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 95127 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: ee1 type: ref possible_keys: expression1_id_idx key: expression1_id_idx key_len: 8 ref: db_lexi.e.id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: ee2 type: ref possible_keys: expression2_id_idx key: expression2_id_idx key_len: 8 ref: db_lexi.e.id rows: 1 Extra: Using where 3 rows in set (0.00 sec) Can someone suggest a better approach? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
