Re: Como monitorar o InnoDB Change Buffer
Please, ignore last message, wrong list :) -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr Em 27 de maio de 2015 14:29, Wagner Bianchi wagnerbianch...@gmail.com escreveu: Pessoal, depois de solicitar para o time da Oracle a inclusão de informações de monitoramento do InnoDB Change Buffer, a adição de informação foi levada em consideração e compartilho com vocês: https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.6/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.7/en/innodb-insert-buffering.html Tome cuidado ao trabalhar esse subsistema, pois, o valor de innodb_change_buffering é um percentual do Buffer Pool que é destinado ao change buffer - principal objetivo aqui é diminuir a pressão por I/O em I/O-Bound workloads. Abraço!! -- *Wagner Bianchi, +55.31.8654.9510 %2B55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr
Como monitorar o InnoDB Change Buffer
Pessoal, depois de solicitar para o time da Oracle a inclusão de informações de monitoramento do InnoDB Change Buffer, a adição de informação foi levada em consideração e compartilho com vocês: https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.6/en/innodb-insert-buffering.html https://dev.mysql.com/doc/refman/5.7/en/innodb-insert-buffering.html Tome cuidado ao trabalhar esse subsistema, pois, o valor de innodb_change_buffering é um percentual do Buffer Pool que é destinado ao change buffer - principal objetivo aqui é diminuir a pressão por I/O em I/O-Bound workloads. Abraço!! -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE Director https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541, MySQL Certified Professional Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P. Email: m...@wagnerbianchi.com Skype: wbianchijr
Re: After long semaphore waits MySQL becomes unresponsive.
Just to confirm, is that MySQL 5.5.11? -- Wagner Bianchi Mobile: +55.31.8654.9510 Em 11/03/2015, às 19:56, Michael Walker - Rotech m...@rotech.ca escreveu: Same problem here... happens about once a month, since recent MySQL version update http://qnalist.com/questions/4209635/after-long-semaphore-waits-mysql-becomes-unresponsive
Re: mysql strangeness...
Hello Chris, Can pls you share the below command output... SHOW STATUS LIKE 'Threads%'; SELECT @@thread_cache_size, @@net_buffer_length, @@max_allowed_packet; What about the limits.conf config on operating system level? -- WB, MySQL Oracle ACE Em 07/12/2014, às 20:03, Chris Knipe sav...@savage.za.org escreveu: FYI - just an example... mysql SELECT VERSION(); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:203720459 Current database: NNTP +-+ | VERSION() | +-+ | 5.5.38-0ubuntu0.12.04.1-log | +-+ 1 row in set (33.94 sec) mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 5.5.38-0ubuntu0.12.04.1-log | +-+ 1 row in set (0.00 sec) This is from the mysql client running on the same host as the mysql server, connected to localhost via TCP. Current connections to the DB was at about 200 out of 500 -- Chris. -Original Message- From: Chris Knipe [mailto:sav...@savage.za.org] Sent: Sunday, December 07, 2014 11:34 PM To: 'mysql@lists.mysql.com' Subject: mysql strangeness... Hi, I have a not so busy MySQL server (+- 150 Selects/sec, 180 Deletes/Sec, 320 Updates/Sec, 90 Inserts/Sec and 200 Replace/Sec), max 512 concurrent connections. The server is running on a Dell R720 with 64GB Ram, Xeon E5-2620. Data is on a 4 x 3TB (RAID10) SATA3 array, and binlogs on a 4 x 600GB (RAID10) SSD array. MySQL 5.5.38 Up to a while ago (few days / week), everything was running absolutely fine. Lately however, I have noticed more and more times that I am hitting my max connection limits. Standard tools like mysql-tuner (which took more than an hour to run because of slow connections to MySQL), tuning-primer, innotop, etc. all shows that the configured variables are within normal parameters, and MySQL is also not under *any* IO load what so ever. I'd say 99% of all tables are InnoDB, and even the one single Memory table that I have, seems to be running slow queries :-( I'm also seeing a lot of connection reset by peer network related errors. What I am seeing, is that even when there are only like 300 or so connections established to the server, even the mysql (command utility) running on the localhost, takes MINUTES to establish a connection to the mysql server - however, once the connection IS established, queries execute absolutely fine and within seconds. It's just the initial establishment of the connection to mysql which is taking forever. The process list shows 99% of all the active connections in a READING FROM NET state (unauthenticated users). Skip-name-resolve and all those things are disabled, and tcpdumps also confirmed that mysql is in fact NOT doing DNS lookups. The server IMHO is under no significant load at all (dedicated mysql system), and there's only +- 5Mbps of traffic on the 1Gbps interface in the server. All in all, graphs indicate 750/800 QPS with about 30% reads, and 70% writes. Can anyone perhaps suggest some things to look into here? I'm pretty sure that the problem is with the initial establishment of the TCP connection to mysql, rather than it being a mysql issue related to queries and what not. Telneting to the mysql socket, I do get the initial greeting from the server virtually instantaneous like it should be. So the only thing that I can think of is that something is whacked in terms of authenticating users? Box is firewalled, only authorised hosts are permitted to connect, all users are authenticating by password and IP (%) -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql strangeness...
In some past experiences, firewall can add a small overhead in connection establishment. If you're using iptables, you can try disable it for a second, test the connection establishment to check if the overhead is being added by the firewall and enable that afterwards. Let us know how's it going. -- WB, MySQL Oracle ACE Em 07/12/2014, às 20:03, Chris Knipe sav...@savage.za.org escreveu: FYI - just an example... mysql SELECT VERSION(); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:203720459 Current database: NNTP +-+ | VERSION() | +-+ | 5.5.38-0ubuntu0.12.04.1-log | +-+ 1 row in set (33.94 sec) mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 5.5.38-0ubuntu0.12.04.1-log | +-+ 1 row in set (0.00 sec) This is from the mysql client running on the same host as the mysql server, connected to localhost via TCP. Current connections to the DB was at about 200 out of 500 -- Chris. -Original Message- From: Chris Knipe [mailto:sav...@savage.za.org] Sent: Sunday, December 07, 2014 11:34 PM To: 'mysql@lists.mysql.com' Subject: mysql strangeness... Hi, I have a not so busy MySQL server (+- 150 Selects/sec, 180 Deletes/Sec, 320 Updates/Sec, 90 Inserts/Sec and 200 Replace/Sec), max 512 concurrent connections. The server is running on a Dell R720 with 64GB Ram, Xeon E5-2620. Data is on a 4 x 3TB (RAID10) SATA3 array, and binlogs on a 4 x 600GB (RAID10) SSD array. MySQL 5.5.38 Up to a while ago (few days / week), everything was running absolutely fine. Lately however, I have noticed more and more times that I am hitting my max connection limits. Standard tools like mysql-tuner (which took more than an hour to run because of slow connections to MySQL), tuning-primer, innotop, etc. all shows that the configured variables are within normal parameters, and MySQL is also not under *any* IO load what so ever. I'd say 99% of all tables are InnoDB, and even the one single Memory table that I have, seems to be running slow queries :-( I'm also seeing a lot of connection reset by peer network related errors. What I am seeing, is that even when there are only like 300 or so connections established to the server, even the mysql (command utility) running on the localhost, takes MINUTES to establish a connection to the mysql server - however, once the connection IS established, queries execute absolutely fine and within seconds. It's just the initial establishment of the connection to mysql which is taking forever. The process list shows 99% of all the active connections in a READING FROM NET state (unauthenticated users). Skip-name-resolve and all those things are disabled, and tcpdumps also confirmed that mysql is in fact NOT doing DNS lookups. The server IMHO is under no significant load at all (dedicated mysql system), and there's only +- 5Mbps of traffic on the 1Gbps interface in the server. All in all, graphs indicate 750/800 QPS with about 30% reads, and 70% writes. Can anyone perhaps suggest some things to look into here? I'm pretty sure that the problem is with the initial establishment of the TCP connection to mysql, rather than it being a mysql issue related to queries and what not. Telneting to the mysql socket, I do get the initial greeting from the server virtually instantaneous like it should be. So the only thing that I can think of is that something is whacked in terms of authenticating users? Box is firewalled, only authorised hosts are permitted to connect, all users are authenticating by password and IP (%) -- Chris. -- 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: mysql strangeness...
Chris, thanks for you prompt reply. Try to raise up some configuration regarding /etc/security/limits.conf mysql soft nofile 10240 mysql hard nofile 40960 mysql soft nproc 10240 mysql hard nproc 40960 root soft nofile 10240 root hard nofile 40960 root soft nproc 10240 root hard nproc 40960 [root@server mysql-rpm]# ulimit -a mysql core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 192031 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 9 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 9 cpu time (seconds, -t) unlimited max user processes (-u) 9 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited I've been running some *not that busy* mysql instances with these values and max_connections=3000 and max_user_connections=0 to deal with simultaneous connections. It seems to be OS constraint as you mentioned and the limits configurations above will be a good start to check if the problem will happen again. Let us know how's it going, cheers!! -- *Wagner Bianchi, +55.31.8654.9510* Oracle ACE https://apex.oracle.com/pls/otn/f?p=19297:4:331870797705::NO:4:P4_ID:4541, MySQL Certified Professional Email: m...@wagnerbianchi.com Skype: wbianchijr 2014-12-08 11:56 GMT-02:00 Chris Knipe sav...@savage.za.org: On Mon, Dec 8, 2014 at 3:02 PM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Hello Chris, Can pls you share the below command output... SHOW STATUS LIKE 'Threads%'; SELECT @@thread_cache_size, @@net_buffer_length, @@max_allowed_packet; mysql SHOW STATUS LIKE 'Threads%'; +---+---+ | Variable_name | Value | +---+---+ | Threads_cached| 422 | | Threads_connected | 92| | Threads_created | 514 | | Threads_running | 1 | +---+---+ 4 rows in set (0.43 sec) mysql SELECT @@thread_cache_size, @@net_buffer_length, @@max_allowed_packet; +-+-+--+ | @@thread_cache_size | @@net_buffer_length | @@max_allowed_packet | +-+-+--+ |1024 | 16384 | 16777216 | +-+-+--+ 1 row in set (0.00 sec) mysql The server is very not busy at all right now however. The issues seems to start happening once we've reach the 250 to 300 concurrent connection mark. I'm tempted to just through another 64GB of memory to the box and up the max connections, but 1st price of course would be to identify and resolve the problem, rather than just throwing hardware at the problem :-) What about the limits.conf config on operating system level? They have been dealt with. mySQL has 4096 file descriptors available. Through all of this, not one single error is logged to the errorlog either. limits.conf: mysql soft nofile 4096 mysql hard nofile 4096 I'm personally actually leading more towards that this is a OS issue rather than a mysql issue, but I have no idea to where to even start looking to debug this :-( -- Chris.
Re: Indexes issue importing tablespaces
Did you check if an ANALYZE TABLE is enough in this case? -- Wagner Bianchi Mobile: +55.31.8654.9510 Em 10/10/2014, às 09:06, Ruben Cardenal my...@ruben.cn escreveu: Hi, I have this problem among several different instaces of 5.6.20. I take all the steps as stated in http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html and get no errors neither warnings, neither in the cli or the mysql log. (13:23:02) [borrame] alter table creditLine discard tablespace; Query OK, 0 rows affected (0.30 sec) (copy operation of the .cfg and .ibd files from the origin server) (13:23:19) [borrame] alter table creditLine import tablespace; Query OK, 0 rows affected (44.35 sec) 2014-10-10 13:26:42 1657 [Note] InnoDB: Importing tablespace for table 'letsbonus/creditLine' that was exported from host 'dualla' 2014-10-10 13:26:42 1657 [Note] InnoDB: Phase I - Update all pages 2014-10-10 13:27:04 1657 [Note] InnoDB: Sync to disk 2014-10-10 13:27:25 1657 [Note] InnoDB: Sync to disk - done! 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase III - Flush changes to disk 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase IV - Flush complete 2014-10-10 13:27:26 1657 [Note] InnoDB: borrame.creditLine autoinc value set to 87313435 After this, the indexes look empty: (13:27:26) [borrame] show index from creditLine; ++++--+--+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++++--+--+---+-+--++--++-+---+ | creditLine | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | endDate | 1 | endDate | A | 0 | NULL | NULL | YES | BTREE | | | | creditLine | 1 | status | 1 | status | A | 0 | NULL | NULL | | BTREE | | | ++++--+--+---+-+--++--++-+---+ 7 rows in set (0.00 sec) I have to optimize or null-alter the table to get them working: (13:27:34) [borrame] alter table creditLine engine = InnoDB; Query OK, 0 rows affected (12 min 57.41 sec) Records: 0 Duplicates: 0 Warnings: 0 (13:51:17) [borrame] show index from creditLine; ++++--+--+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++++--+--+---+-+--++--++-+---+ | creditLine | 0 | PRIMARY | 1 | id | A | 32237680 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 16118840 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology | A | 1792 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 8967 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 2 | NULL | NULL | | BTREE | | | | creditLine | 1 | endDate | 1 | endDate | A | 293069 | NULL | NULL | YES | BTREE | | | | creditLine | 1 | status | 1 | status | A | 4 | NULL | NULL | | BTREE | | | ++++--+--+---+-+--++--++-+---+ 7 rows in set (0.00 sec) Is this a know issue? Or something I'm missing? I've checked the doc and saw nothing related to this. Thanks, Rubén.
Re: Proxy / connected failover question
I like HAProxy as well as it simplifies many of the things you seem to be looking for. -- Wagner Bianchi Mobile: +55.31.8654.9510 Em 09/07/2014, às 07:48, Heck, Walter walterh...@olindata.com escreveu: Johan, I don't think there's any need for the heavyness (and ugliness ;) ) of MySQL Proxy. We're using haproxy for a similar setup (just with galera behind it, but that shouldn't really matter. Have a look at this blog post that explains most of it: http://www.olindata.com/blog/2014/04/managing-percona-xtradb-cluster-puppet#haproxy cheers, On Wed, Jul 9, 2014 at 12:11 PM, Johan De Meersman vegiv...@tuxera.be wrote: Hullo peoples, I'm not usually an advocate of MySQL Proxy and the like, but I'm stuck with one shitty application that utterly breaks whenever the database goes away unexpectedly. I can't change the application itself, so I find myself looking for options that allow the heathen contraption to not notice it's connection has switched. I am aware that connection state etc is likely to be lost anyway; I'll have to see wether or not that's going to be an issue during testing. I have two main questions: * am I remembering right that MySQL Proxy provides transparent failover ? * Are there other contenders in the same field, or alternate solutions ? Ideally I'm looking for a hyper-stable tool that can run on it's own VM, so the application doesn't notice when I switch backends. All the other applications play nice, in that they simply reconnect and go on with business, so it doesn't even *have* to take improbably loads. Thank you for any and all suggestions and information, Johan -- What's tiny and yellow and very, very dangerous? A canary with the root password. -- Best regards, Walter Heck CEO / Founder OlinData http://olindata.com/?src=wh_gapp - Open Source Training Consulting Check out our upcoming trainings http://olindata.com/training/upcoming
Re: Optimizing InnoDB tables
Hi Antonio, como esta? What's the mysql version you're running? Have you tried to ALTER TABLE x ENGINE=InnoDB? -- WB, MySQL Oracle ACE Em 24/06/2014, às 08:03, Antonio Fernández Pérez antoniofernan...@fabergroup.es escreveu: Hi list, I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables. SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND Data_free 0 After that, I have seen that there are 49 fragmented tables. With one table, I have executed optimize table table_name; and analyze table table_name;. The result is the same, the table continuos fragmented. Any ideas? I have followed the mysqltuner recomendations ... Thanks in advance. Regards, Antonio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: This just seems to slow
Multiple line insert is the better choice...it will be organized in transaction blocks of many lines and it will speed up data insertion. [bian...@mysql.com]# mysqldump -u root -p --all-databases -e file.dump -e: extended-inserts Best regards. -- Wagner Bianchi 2011/1/3 Daevid Vincent dae...@daevid.com Another option would be to mangle your insert statement with some other language like PHP, Python, Ruby, etc. so that the inserts are MULTI inserts instead. Something like: INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299519), VALUES (13071, 299520), VALUES (13071, 299521), ... That will radically speed up the inserts. Also delete your INDEX / KEYs and add them at the very end instead. -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Sunday, January 02, 2011 8:42 PM To: mysql@lists.mysql.com Subject: Re: This just seems to slow Jerry, Use Load Data Infile when loading a lot of data. Whoever is giving you the data should be able to dump it to a CSV file. Your imports will be much faster. Mike At 07:51 PM 1/2/2011, you wrote: I'm trying to load data into a simple table, and it is taking many hours (and still not done). I know hardware, etc., can have a big effect, but NOTHING should have this big an effect. = us-gii show create table t_dmu_history\G *** 1. row *** Table: t_dmu_history Create Table: CREATE TABLE `t_dmu_history` ( `t_dmu_history_id` int(11) NOT NULL AUTO_INCREMENT, `DM_History_DM_ID` int(11) DEFAULT NULL, `DM_History_Customer_ID` int(11) DEFAULT NULL, PRIMARY KEY (`t_dmu_history_id`), KEY `DM_History_DM_ID` (`DM_History_DM_ID`), KEY `DM_History_Customer_ID` (`DM_History_Customer_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1446317 DEFAULT CHARSET=utf8 = Here's a snip of what the input file looks like: = SET autocommit=1; # # Dumping data for table 'T_DMU_History' # INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299519); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299520); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299521); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299522); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299524); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299526); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299527); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299528); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299529); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299531); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299532); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299533); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299534); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299535); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298880); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298881); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298882); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298883); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298884); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298885); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298886); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298887); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298889); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298890); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298891); = There are about 87 records. I realize that using one INSERT per row is going to hurt, but I don't control the format of the incoming data. Besides, I'd have thought this would be pretty quick regardless of how clumsy the method was. Is that autocommit a problem? This is a bulk load into an empty table, so I'm not worried about ACID. Any suggestions? Regards, Jerry Schwartz Global Information
Re: MySQL raw files to .SQL
Did a tried MaatKit? MaatKit let you control over the number of threads dedicated to extract a MySQL backup. Best regards. -- Wagner Bianchi 2010/12/31 Lydia Rowe ly...@lydiarowe.com In order to get a database into A .SQL file, I usually import the raw files, .MYI and such, into a database and then run mysqldump. Is there a quicker, one-step solution? Thanks! Sent from my iPad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: my.cnf file
Are you show about the non-outage operation with this command? Best regards. -- Wagner Bianchi 2010/12/31 Sharl.Jimh.Tsin amoiz.sh...@gmail.com rpm -qpi mysql*.rpm | grep my.cnf Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/12/30 Lydia Rowe ly...@lydiarowe.com: find / -name my.cnf -- Lydia On Thu, 2010-12-30 at 11:09 -0200, Wagner Bianchi wrote: I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=amoiz.sh...@gmail.com
Re: my.cnf file
Please, forget my last note, I answered in a wrong thread! Sorry. Best regards. -- Wagner Bianchi 2010/12/31 Wagner Bianchi wagnerbianch...@gmail.com Are you show about the non-outage operation with this command? Best regards. -- Wagner Bianchi 2010/12/31 Sharl.Jimh.Tsin amoiz.sh...@gmail.com rpm -qpi mysql*.rpm | grep my.cnf Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/12/30 Lydia Rowe ly...@lydiarowe.com: find / -name my.cnf -- Lydia On Thu, 2010-12-30 at 11:09 -0200, Wagner Bianchi wrote: I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=amoiz.sh...@gmail.com
Re: /etc/init.d/mysql start WITHOUT integrity check?
Let me know with you whether I understood what do you want to do. Normally, after mysqld restart on OSs as Ubuntu/Debian, we can observe a script execution, which will check integrity of all databases tables and present a message of Corrupt What I did when I wanted to get rid this check of was comment the lines inside the file script with # character. Please, let us know if it is the operation do you want to avoid when restart mysqld. Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Daevid, I'm not quite sure I understand why you want to restart your master. Adding a slave shouldn't require any restarts/reloads. What have you changed in the my.cnf to solicit a restart? Andy From: ext Daevid Vincent [dae...@daevid.com] Sent: 29 December 2010 20:25 To: 'mysql' Subject: /etc/init.d/mysql start WITHOUT integrity check? Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity check? Can I pass in a command line parameter or set something in the my.cnf file? Our DB is a Billion rows (with a B) and that check can take HOURS. All we want to do is restart the server to put another slave online because sadly /etc/init.d/mysql reload does NOT re-load the config file (as one might hope), it is only to reload PRIVS (how useless is that since GRANT already does that). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: my.cnf file
I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: /etc/init.d/mysql start WITHOUT integrity check?
There are different and connected things being discussed here. 1-) Is there a way to read new configurations from my.cnf whithout restart server? = No. A simple mysqladmin reaload don't submit server to read configuration file again. 2-) Is there a way to avoid debian-script to check databases' integrity? = Yeah, there is! Open the debian script and comment all rows. Best regards. -- Wagner Bianchi 2010/12/30 Shawn Green shawn.l.gr...@oracle.com On 12/30/2010 5:00 PM, Daevid Vincent wrote: Comment WHAT lines? I looked through /etc/init.d/mysql and don't see anything related to check or chk. I eyeballed each line in the file and nothing stands out as the culprit causing an integrity check of the databases. develo...@mypse:/etc/init.d$ ps aux | grep mysql 46:root 10239 0.0 0.1 1680 520 ?SDec25 0:00 /bin/sh /usr/bin/mysqld_safe 49:mysql11165 0.0 5.4 129924 27864 ?Sl Dec25 0:58 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 50:root 11167 0.0 0.1 2920 692 ?SDec25 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld 81:1000 19149 0.0 0.1 3004 788 pts/0R+ 21:54 0:00 grep -n -i --color=auto mysql mailto:develo...@mypse:/etc/init.d$ develo...@mypse:/etc/init.d$ cat /proc/11165/cmdline /usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--user=mysql--pid-fil e=/var/run/mysqld/mysqld.pid--skip-external-locking--port=3306--socket=/var /run/mysqld/mysqld.sockd Does anyone know if a kill -SIGHUP 11165 will cause a reload of the configuration? It won't reload the configuration. http://dev.mysql.com/doc/refman/5.1/en/server-signal-response.html Are you sure you aren't seeing the results of a dirty shutdown and auto-recovery? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Back-up Plan for Large Database
Hello there, ZMANDA is a good tool to extract backups from MySQL databases. But, when subject is fault tolerant and keep databases on air 24x7, you need to think about replication to have a SLAVE server as a online copy of your environment, other SLAVE servers to permit you switch among servers in case of MASTER fails and things like that. In advance to have ZMANDA as you backup tool, do you have server replicating data on you environment? Best regards. -- Wagner Bianchi 2010/12/27 Adarsh Sharma adarsh.sha...@orkash.com Dear all, Back-up is the most important thing that need special attention. We have a production Mysql Server of near about 200 GB data and expect to grow @ 50 GB per month. Our application continuously writes data in Mysql tables. I followed some Links but want some more thoughts to choose best option. http://www.zmanda.com/mysql-backup-considerations.html I also read about RAID and some other features too. But I want to know what is the best back up plan for 24/7 running Large Production Mysql Cluster. Thanks Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Back-up Plan for Large Database
*You have no guarantee the data on the slave matches the master 100%.* * * Try it with *semi-synchronous* replication. Best regards. -- Wagner Bianchi 2010/12/27 Johnny Withers joh...@pixelated.net Might want to check out LVM snapshots: http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Using a slave to pull backups from is something I would not do. You have no guarantee the data on the slave matches the master 100%. On Mon, Dec 27, 2010 at 5:19 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, Back-up is the most important thing that need special attention. We have a production Mysql Server of near about 200 GB data and expect to grow @ 50 GB per month. Our application continuously writes data in Mysql tables. I followed some Links but want some more thoughts to choose best option. http://www.zmanda.com/mysql-backup-considerations.html I also read about RAID and some other features too. But I want to know what is the best back up plan for 24/7 running Large Production Mysql Cluster. Thanks Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Table cache not being updated
The response of table_cahe's new values is not imedite. MySQL cache engine will putting new objects in cache on demand intead of to replace them. Configure new table_cahe value at my.cnf or your configuration file, restart mysqld e going on monitoring. Let time pass and see what happen. Best regards. -- Wagner Bianchi 2010/12/22 杨涛涛 david.y...@actionsky.com How did you adjust this variable? Stay it in my.cnf or just set it? David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com 2010/11/24 Machiel Richards machi...@rdc.co.za Hi All Maybe someone can help me with this one. We have set the table_cache to 1024, however the open tables value stays 64 of 64. Everything I checked stated that the open tables is related to the table_cache variable. Can someone please assist on why the value isn't being updated? The MySQL version is 5.051a regards Machiel
Re: Discontinued AUTO_INCREMENT problem....
Too curious...could you share a SHOW CREATE TABLE from this table as requested before? Best regards. -- Wagner Bianchi 2010/12/21 杨涛涛 david.y...@actionsky.com Hi. You can show us your show create table statement as well. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/20 Xavier Correyeur x.correy...@free.fr Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Re: Trigger?
I think if you built a trigger to update value of foo's column after, this trigger will not be compiled cause it will execute two transactions on the same one. Try it... Best regards. -- Wagner Bianchi 2010/12/21 Jerry Schwartz je...@gii.co.jp Aha! That was the clue I needed. Thank you so much. So, to make sure I understand: A “BEFORE” trigger is executed **between** the time that the record is assembled and the time that the action occurs. That’s why the constraints on the field value were being applied before my trigger was triggered. Contrariwise, I assume that an “AFTER” trigger would be executed last, after everything has been done. Am I correct? By the way, SET NEW.foo = IFNULL(NEW.foo, 'ok') works just fine. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com *From:* Wagner Bianchi [mailto:wagnerbianch...@gmail.com] *Sent:* Monday, December 20, 2010 6:44 PM *To:* Jerry Schwartz *Cc:* mysql@lists.mysql.com *Subject:* Re: Trigger? Well, to produce this result, the first thing that we have to do is to *get rid of* the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql show create table testtrigger\G *** 1. row *** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql create trigger trg_test - before insert on testtrigger - for each row - begin - if(NEW.foo IS NULL || NEW.foo = '') then - set NEW.foo = 'Ok'; - end if; - end; - // Query OK, 0 rows affected (0.04 sec) mysql insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql select * from testtrigger; +-+--+ | id | foo | +-+--+ | 100 | Ok | +-+--+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz je...@gii.co.jp I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Trigger?
Well, to produce this result, the first thing that we have to do is to *get rid of* the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql show create table testtrigger\G *** 1. row *** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql create trigger trg_test - before insert on testtrigger - for each row - begin - if(NEW.foo IS NULL || NEW.foo = '') then - set NEW.foo = 'Ok'; - end if; - end; - // Query OK, 0 rows affected (0.04 sec) mysql insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql select * from testtrigger; +-+--+ | id | foo | +-+--+ | 100 | Ok | +-+--+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz je...@gii.co.jp I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: INSERT DELAYED and logging
Maybe, the table in use must be a table that is inside cache now - SHOW OPEN TABLES, controlled by table_cache, I mean. Well, if the amount of data trasactioned is too small as a simple INSERT, you don't have to be worried, I suggest. If you partition the table, we must a benchmark to know the performance relation of a INSERT and compress data into Archive Storage Engine or the insertion data into a partitioned table. Best regards. -- WB 2010/11/30 Johan De Meersman vegiv...@tuxera.be I would assume that it's slower because it gets put on the delay thread anyway, and thus executes only whenever that thread gets some attention. I'm not sure wether there are other influencing factors. I should also think that not in use in this context means not locked against inserts, so the MyISAM insert-while-selecting at the end of a continguous table may well apply. No guarantees, though - I'm not that hot on this depth. On Tue, Nov 30, 2010 at 8:46 AM, WLGades wlga...@gmail.com wrote: What I'm confused by though, is this line. Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. What's the definition of in use? Does a logging table do that given that it's pretty much append-only/write-only? Waynn On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman vegiv...@tuxera.be wrote: No, I think it's a good idea to do INSERT DELAYED here - it's only logging application, and it's generally more important to not slow down the application for that. It's only ever into a single table, so there's only going to be a single delay thread for it anyway. Archive tables are a good idea, agreed, but I suspect that inserts into that are going to be slower than into regular MyISAM because of the compression, so why not use that overhead to (slightly) speed up your end-user experience instead ? You can always partition the table based on the log date or whatever, if your table risks getting too big. On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Log Mysql slow query into table
Have a look on it: mysql show variables like '%slow%'; +-+--+ | Variable_name | Value| +-+--+ | log_slow_queries| OFF | | slow_launch_time| 2| | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/grey-slow.log | +-+--+ 4 rows in set (0.05 sec) mysql set global slow_query_log = 1; Query OK, 0 rows affected (0.00 sec) mysql show variables like '%slow%'; +-+--+ | Variable_name | Value| +-+--+ | log_slow_queries| ON | | slow_launch_time| 2| | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/grey-slow.log | +-+--+ 4 rows in set (0.06 sec) one advise is, be aware that guide its slow queries to a tabel will impact MySQL's performance. Best regards. -- WB Skype: wbianchijr (preferred way to contact me) 2010/11/30 Cool Cool cool_r...@yahoo.com Hi, I am trying to log slow queries into both file and table. I had set as SET GLOBAL log_output =`TABLE,FILE`; But it isnt getting logged into tables. Can I know if I need to create table or am I missing anything ? Thanks. Regards, Ram
Re: INSERT DELAYED and logging
Friends, I did a benchmark regarding to this subject. Please, I am considering your comments. = http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/ Best regards. -- WB 2010/11/30 Wagner Bianchi wagnerbianch...@gmail.com Maybe, the table in use must be a table that is inside cache now - SHOW OPEN TABLES, controlled by table_cache, I mean. Well, if the amount of data trasactioned is too small as a simple INSERT, you don't have to be worried, I suggest. If you partition the table, we must a benchmark to know the performance relation of a INSERT and compress data into Archive Storage Engine or the insertion data into a partitioned table. Best regards. -- WB 2010/11/30 Johan De Meersman vegiv...@tuxera.be I would assume that it's slower because it gets put on the delay thread anyway, and thus executes only whenever that thread gets some attention. I'm not sure wether there are other influencing factors. I should also think that not in use in this context means not locked against inserts, so the MyISAM insert-while-selecting at the end of a continguous table may well apply. No guarantees, though - I'm not that hot on this depth. On Tue, Nov 30, 2010 at 8:46 AM, WLGades wlga...@gmail.com wrote: What I'm confused by though, is this line. Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. What's the definition of in use? Does a logging table do that given that it's pretty much append-only/write-only? Waynn On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman vegiv...@tuxera.be wrote: No, I think it's a good idea to do INSERT DELAYED here - it's only logging application, and it's generally more important to not slow down the application for that. It's only ever into a single table, so there's only going to be a single delay thread for it anyway. Archive tables are a good idea, agreed, but I suspect that inserts into that are going to be slower than into regular MyISAM because of the compression, so why not use that overhead to (slightly) speed up your end-user experience instead ? You can always partition the table based on the log date or whatever, if your table risks getting too big. On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: INSERT DELAYED and logging
I'll provide it to, bear with me, pls... Best regards. -- WB 2010/11/30 Johan De Meersman vegiv...@tuxera.be Interesting, but I feel the difference is rather small - could you rerun with, say, 50.000 queries ? Also, different concurrency levels (1, 100) might be interesting to see. Yes, I'm to lazy to do it myself, what did you think :-p On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Friends, I did a benchmark regarding to this subject. Please, I am considering your comments. = http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/ Best regards. -- WB 2010/11/30 Wagner Bianchi wagnerbianch...@gmail.com Maybe, the table in use must be a table that is inside cache now - SHOW OPEN TABLES, controlled by table_cache, I mean. Well, if the amount of data trasactioned is too small as a simple INSERT, you don't have to be worried, I suggest. If you partition the table, we must a benchmark to know the performance relation of a INSERT and compress data into Archive Storage Engine or the insertion data into a partitioned table. Best regards. -- WB 2010/11/30 Johan De Meersman vegiv...@tuxera.be I would assume that it's slower because it gets put on the delay thread anyway, and thus executes only whenever that thread gets some attention. I'm not sure wether there are other influencing factors. I should also think that not in use in this context means not locked against inserts, so the MyISAM insert-while-selecting at the end of a continguous table may well apply. No guarantees, though - I'm not that hot on this depth. On Tue, Nov 30, 2010 at 8:46 AM, WLGades wlga...@gmail.com wrote: What I'm confused by though, is this line. Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. What's the definition of in use? Does a logging table do that given that it's pretty much append-only/write-only? Waynn On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman vegiv...@tuxera.bewrote: No, I think it's a good idea to do INSERT DELAYED here - it's only logging application, and it's generally more important to not slow down the application for that. It's only ever into a single table, so there's only going to be a single delay thread for it anyway. Archive tables are a good idea, agreed, but I suspect that inserts into that are going to be slower than into regular MyISAM because of the compression, so why not use that overhead to (slightly) speed up your end-user experience instead ? You can always partition the table based on the log date or whatever, if your table risks getting too big. On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel
Re: From Maurizio Ponti, Switzerland
Start mysqld with --skip-grant-tables option, give an update on the root
password, mentioning the new password the you want to put for your access
with root user and be happy.
Well, make some like this:
shell mysqld --skip-grant-tables
Open another tty, terminal or prompt:
shell mysql
mysql update mysql.user set password = PASSWORD('12345') where user ='root'
and host = 'localhost';
mysql \q
Stop MySQL...
# Linux or Unix
shell /etc/init.d/mysql restart
# MS Windows
C:\ net stop MySQL
C:\ net start MySQL
And the, create a new connection with MySQL Serber using user new password.
Best regards.
--
WB
2010/11/29 Maurizio Ponti maurizio.po...@gmail.com
Dear Sirs, I would like to post the list:
Topic: mysql server installation, password problems
Dear Sirs, I downloaded the last MySQL server version some weeks ago,
then I forgot the root password. I disinstalled everything and
reinstalled from new, but I'm always asked for the old password in order
to define a new one. It seems that an old file related to the password
is still there in my computer and I could not erase it by disinstalling
the server. Could you tell me which is the file and how could I delete
it? Or what should I do in order to solve the problem? Thank you very
much. Maurizio
Re: From Maurizio Ponti, Switzerland
Hi Michael,
I am not sure whether your UPDATE statement will affect all root users
password or only which one that will access from a localhost.
Best regards.
--
WB
2010/11/29 Michael Dykman mdyk...@gmail.com
as root, stop your mysql server in the normal way
ie :$ service mysqld stop
run mysql explicitly to skipp credentials
ie. (run it in the back ground)
$ /usr/libexec/mysqld --skip-grant-tables
once the server starts, you should be able to:
$ mysql -u root
assming you get in (no reason you shouldn't if you got this far), you
can use SQL statements to manipulate user data; you can't use GRANT or
SET PASSWORD so
use mysql;
update user set Password = PASSWORD('yourpassword') where User = 'root';
exit your session and kill your mysqld process. (get the pid via ps
-aux, then kill that)
restart mysql normally, and you should be good. (I had to do this
over the weekend on a system someone else setup but failed to record
the credentials).
- michael dykman
On Mon, Nov 29, 2010 at 12:28 PM, Maurizio Ponti
maurizio.po...@gmail.com wrote:
Dear Sirs, I would like to post the list:
Topic: mysql server installation, password problems
Dear Sirs, I downloaded the last MySQL server version some weeks ago,
then I forgot the root password. I disinstalled everything and
reinstalled from new, but I'm always asked for the old password in order
to define a new one. It seems that an old file related to the password
is still there in my computer and I could not erase it by disinstalling
the server. Could you tell me which is the file and how could I delete
it? Or what should I do in order to solve the problem? Thank you very
much. Maurizio
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: [PHP] mySQL query assistance...
This is the general list. If your problem is with MySQL and queries, let us know. Best regards. -- WB 2010/11/29 Daniel P. Brown daniel.br...@parasane.net On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: INSERT DELAYED and logging
Well, analyze if you need to create an excessive overhead into the MySQL Server because a simple INSERT. What you must have a look is it: - How much data this connection is delivering to MySQL's handlers? - A word DELAYED in this case is making MySQL surfer? Perhaps, you are sophisticating something that do not need it. Besides it, analyzing your log table, I imagine this table can be an Archive table instead of MyISAM. Log tables or history tables can be controlled by Archive Storage Engine to have more compressed data. Although, Archive Storage Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid of you INSERT DELAYED: - ALTER TABLE tbl_name ENGINE = ARCHIVE; Best regards. -- WB 2010/11/29 WLGades wlga...@gmail.com I'm adding a table to our site that logs all page loads. In the past, when I built this, I used MyISAM and INSERT DELAYED. I went back to look at the documentation to see if I should still do this, and saw this (taken from http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html): Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. Does that mean that I shouldn't use it if all I'm doing is INSERT (essentially an append-only table), with only very occasional SELECTs? In addition, the last time I took this approach for logging, it worked well until the table got to 65M+ rows, when it would crash every now and then. I know I can archive off the table on a per month/quarter basis as well. Waynn
Re: How do I get a list of all defined UDF's known to the system?
mysql DELIMITER // mysql CREATE PROCEDURE test.sp1() SELECT 'Hello!' AS Msg; - // Query OK, 0 rows affected (0,00 sec) mysql SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES; - // +--+ | ROUTINE_NAME | +--+ | sp1 | +--+ 1 row in set (0,00 sec) Wagner Bianchi 2010/2/8 Robert Gilland robert.gill...@basx.com.au Hi, Trying this SQL (SELECT * FROM INFORMATON_SCHEMA.ROUTINES) On MySQL 7.0 (C:\Program Files\MySQL\MySQL Server 7.0\bin) I get the following error: Table “information_schema.routines” does not exist. Kind Regards, Robert. *From:* Wagner Bianchi [mailto:wagnerbianch...@gmail.com] *Sent:* Monday, 8 February 2010 11:11 PM *To:* Robert Gilland *Subject:* Re: How do I get a list of all defined UDF's known to the system? SELECT * FROM INFORMATON_SCHEMA.ROUTINES; Aply filter over this query. -- Wagner Bianchi 2010/2/8 Sir Wally Lewis robert.gill...@basx.com.au Thanks Suresh Kuna sureshkumar...@gmail.com wrote in message news:23397e991002072211l95b2063i40876e0ada93e...@mail.gmail.com... Hi Robert, We can see the functions by using the below command Show function status ; -- Suresh Kuna MySQL DBA On Mon, Feb 8, 2010 at 10:37 AM, Sir Wally Lewis robert.gill...@basx.com.au wrote: How do I get a list of all defined UDF's known to the system? Kind Regards, Robert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- This message has been scanned for viruses and dangerous content by *MailScanner* http://www.mailscanner.info/, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by *MailScanner* http://www.mailscanner.info/, and is believed to be clean.
MySQL Proxy
Hi friends, Recently I accept an idea of testing a MySQL Proxy to concept an environment that will use two slave servers below it.Ok, the theory is good and I decide to try this, but, when I began to test step-by-step the MySQL Proxy manual I felt that somenthing was wrong. In first time, I started MySQL proxy with mysql-proxy --proxy-read-only-backend-addresses=localhost:3306 and connected with mysql server using mysql -u root -p -P 4042 only to test readOnly behavior...so, I had inserted some lines and updated too - I read on the manual that port 4042 is a readOnly port that filters UPDATE and INSERT and I didn't saw this behavior. Ok, I looking forward on the manual yet, I read about the mc.lua, Is that script exists? I don't know, cause I going on with tests, in this time, starting mysql-proxy with mysql-proxy --proxy-read-only-backend-addresses=localhost:3306 --proxy-lua-script=mc.lua and made all tests again with port 4042. Once my tests fail. Anybody here use this and MySQL-Proxy function? Anybody can give some explanation how does it works? Thanks in advanced. Wagner Bianchi
MySQL Proxy
Hi friends, Recently I accept an idea of testing a MySQL Proxy to concept an environment that will use two slave servers below it.Ok, the theory is good and I decide to try this, but, when I began to test step-by-step the MySQL Proxy manual I felt that somenthing was wrong. In first time, I started MySQL proxy with mysql-proxy --proxy-read-only-backend-addresses=localhost:3306 and connected with mysql server using mysql -u root -p -P 4042 only to test readOnly behavior...so, I had inserted some lines and updated too - I read on the manual that port 4042 is a readOnly port that filters UPDATE and INSERT and I didn't saw this behavior. Ok, I looking forward on the manual yet, I read about the mc.lua, Is that script exists? I don't know, cause I going on with tests, in this time, starting mysql-proxy with mysql-proxy --proxy-read-only-backend-addresses=localhost:3306 --proxy-lua-script=mc.lua and made all tests again with port 4042. Once my tests fail. Anybody here use this and MySQL-Proxy function? Anybody can give some explanation how does it works? Thanks in advanced. Wagner Bianchi
Re: Is table_open_cache a private cache of a session?
Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com Got your idea. Thank you very much. Now I know how table cache works :-) For the bug, yes, it's related to the value of FD_SETSIZE, which is limited to 1024 at my RedHat box. Maybe I should update it to a suitable value. 2010/2/2 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Is table_open_cache a private cache of a session?
Sorry, *open_files_limit... 2010/2/1 Wagner Bianchi wagnerbianch...@gmail.com Is FD_SETSIZE regards to an open_file_limit? WB 2010/2/1 Cui Shijun rancp...@gmail.com Got your idea. Thank you very much. Now I know how table cache works :-) For the bug, yes, it's related to the value of FD_SETSIZE, which is limited to 1024 at my RedHat box. Maybe I should update it to a suitable value. 2010/2/2 Johan De Meersman vegiv...@tuxera.be: On Mon, Feb 1, 2010 at 4:51 PM, Cui Shijun rancp...@gmail.com wrote: I'm also confused by the difference relationship between open table and open file descriptor by the table cache. open table is a MySQL concept. Open file descriptor is an OS concept. A single table (MyISAM) consists of three files: the .frm (description), the .MYD (data) and the .MYI (indices). Thus, a single open table can correspond to multiple open files. Additionally, temp tables, sortfiles and whatnot also consume file descriptors. As far as I understand, when a thread ask the global cache for a table: * if the table is opened before and currently not used by other thread, the request thread will get this table and *there is a cache entry that* is currently not used* - multiple entries can exist for the same table. * if no table in table cache is available( currently used by other thread, or not opened before ), the request thread will open this table The thread will get a new cache object that opens that table, yes. Once open a table, mysql *might?( I'm not sure )* open a file descriptor corresponding to the data file of the table. In that case, when the number of table opened simultaneously goes too big, mysql will use too much file descriptors and then hit the bug 48929. Your experience( I've had one occurrence where it grew to 26.000 open tables ) seems to show there must be something wrong with my understanding, Hmm... :-( I just skimmed over it, but the bug seem related specifically to InnoDB, and to a highly specific file descriptor number being equal to some form of hardcoded limit - maybe different OSes or linux distro's have different values for said limit, or maybe it only occurs under specific conditions. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Re: I would like to post on lists.mysql.com
Hi, if you did your subscription in any lists, you will automatically added to send and receive e-mails from professionals that are connected in it. See lists here: http://lists.mysql.com/ Wagner Bianchi 2010/1/30 Daniel Brown danbr...@php.net On Sat, Jan 30, 2010 at 01:49, Vikram A vikkiatb...@yahoo.in wrote: Dear Admin, I would like to share and get inputs from experts on MYSQL Db. I request you to grant access to me. You may not have noticed, but you're already posting to the list. All you have to do is subscribe and you have full access. -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ Looking for hosting or dedicated servers? Ask me how we can fit your budget! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: How to force Warning: #1048 Column cannot be null to Error
mysql SET sql_mode = TRADITIONAL; Query OK, 0 rows affected (0.02 sec) Then the insert query will fail and the row will not be added to the table...see you. Wagner Bianchi 2010/1/30 Claudio Nanni claudio.na...@gmail.com Take a look at SQL_MODE ;) Claudio On Jan 30, 2010 5:05 PM, Miao Jiang jiangfri...@gmail.com wrote: When I try insert NULL to VARCHAR NOT NULL column, It will shows a warning and convert NULL to '' then insert 。 I want to MySQL raise an exception when I try do that. How to do that? Thank you. Miao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: how to dump database or tables
*Make a backup of yours databases:* shell mysqldump -u user -p --all-databases -e path/file.dmp Implicit to this command quoted above, you will have the --opt option ( shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset ) and -e ( use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded ). Consider to use -e option when you have a big backup - *you will get more faster restore*, improving the time-recovery. *Make a backup of a table:* shell mysqldump -u user -p mysql user -e path/file.dmp In this last way, you will copy to a file only a table of mysql database - its simply like that. *Restore the backup:* To restore, use mysql client, like this... shell mysql -u user -p path/file.dmp Other considerations regards of the operations of restore a backup is to apply the correct O_DSYNC innodb_flush_method to InnoDB, disable foreign key checks and autocommit. These practicals will execute you backup faster then other way. When you use MyISAM, configure in my.cnf the bulk_insert_buffer_size with a value big enough and don't worry, start restore and be happy. *See it on manual:* http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet 2010/1/29 Anand kumar anand@gmail.com it should be windows.. . On Fri, Jan 29, 2010 at 2:29 PM, Suresh Kuna sureshkumar...@gmail.com wrote: Which OS your are using ? Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:25 PM, muralikrishna g muralikrishn...@gmail.comwrote: i am working on my pc with mysql-5.0.27-community-nt i have created several data bases and tables in that.. to take backup, we have to use dump., i dont know the correct syntax how to use dump to take backup to a specific location., after that how to resore. please help me regarding this. thanks in advance
Fwd: how to switch between users
On other SGBDs you can issue: sqlplus conn other_user; But, using MySQL you can't do it...start new connection. -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet 2010/1/29 Suresh Kuna sureshkumar...@gmail.com Hi Murali, We can switch to another user only by a new connection and not possible in mysql prompt. Thanks Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:24 PM, muralikrishna g muralikrishn...@gmail.comwrote: i am working on my pc with mysql-5.0.27-community-nt i have created users by using create user and i given some privileges, but i dont know how to switch between users on mysql command line, please help me regarding this.. thanks in advance -- Thanks Suresh Kuna MySQL DBA
Re: WAMP vs LAMP
*Hi JS,* I never see socket file on MS Windows...are you sure about it? But, the other question is *yes*, if you make a connection with the MySQL Server (mysqld) using -h localhost, you will connect with the server using a socket file (linux only), but, if you make using -h 127.0.0.1, TCP/IP will be use. *See this: * *--protocolhttp://dev.mysql.com/doc/refman/5.0/en/connecting.html#option_general_protocolValue * *Connection Protocol* *Allowable Operating Systems* TCP TCP/IP connection to local or remote server All SOCKET Unix socket file connection to local server *Unix only* PIPE Named-pipe connection to local or remote server Windows only MEMORY Shared-memory connection to local server Windows only *Source*: http://dev.mysql.com/doc/refman/5.0/en/connecting.html -- Wagner Bianchi 2010/1/29 Jerry Schwartz jschwa...@the-infoshop.com *From:* Wagner Bianchi [mailto:wagnerbianch...@gmail.com] *Sent:* Thursday, January 28, 2010 2:03 PM *To:* Jerry Schwartz *Subject:* Re: WAMP vs LAMP [JS] The file paths were all the same, actually, and the address for MySQL is just “localhost”. [WB]*Consider to use MySQL on Unix like environment because the socket file. This way you will get more performance then use TCP/IP on MS Windows *. *[JS] That’s an interesting suggestion. Windows has socket files, but I’ve never looked at them. In fact, I don’t even know if MySQL can us a socket file and TCP/IP at the same time. We’re going to have more ODBC traffic than web traffic, I expect.* Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com * * Best regards. -- Wagner Bianchi 2010/1/28 Jerry Schwartz jschwa...@the-infoshop.com From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Wednesday, January 27, 2010 12:18 PM To: Jerry Schwartz Cc: shawn.gr...@sun.com; Daevid Vincent; Dan Nelson; mysql@lists.mysql.com Subject: Re: Event feature already working in Server 5.1.37 On Wed, Jan 27, 2010 at 5:52 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: [JS] I second this. Instead of using a LAMP development environment, I went with WAMP -- even though our production environment was LAMP. Generally a bad idea - you keep running into annoying minor differences between the systems. File paths, for example :-) [JS] The file paths were all the same, actually, and the address for MySQL is just “localhost”. I’ve only run into one incompatibility, and that one bit me yesterday: On Windows, the PHP rand() function has a native range of 1 – 32767. I replaced that with a call to mt_rand(), and all’s right with the world. (Why are we using random numbers? It would take a psychiatric evaluation of my predecessor to determine that.) It was a lot easier than setting up LAMP in a virtual machine. I'll set up up in under an hour, if you want :-) [JS] I’m sure you could. I actually did, before deciding that it wasn’t worth it what with the port forwarding and all. When we shut down our LAMP site for cost reasons, I moved it to a WAMP environment that I bought off the Wait. You shut down machines for cost reasons, and then go buy new ones ? [JS] The one we shut down was externally hosted, and had customer-accessible information on it. When management decided to consolidate our customer-accessible sites in Japan, there was no reason to have our administrative stuff hosted externally. shelf for $800. For that money I got 8GB of RAM, four cores, and a RAID controller. Another $90 for a second drive, and I've got mirroring going. Granted, it's a low-traffic site used for internal administration; but I think this box could handle a lot more traffic than it does. It seems to be loafing all of the time. Oh, probably. Webserving isn't all that hard of a job, if the site is reasonably well-designed. If you're implying that the LAMP setup you had earlier didn't perform quite as well, though, I'll go out on a leg and say that it probably wasn't managed very well. [JS] It was fine. It's a home/SOHO/gamer system, so it probably isn't as physically robust as a server grade machine at twice the price; but if it dies, I can be up and running on a newer, bigger, cheaper machine in little more than the time it takes me to run to the nearest big-box store. True. Me and my server grade machine, however, will not have had that downtime, because I'll have been notified that a redundant component has failed, and will have replaced it while the machine was running. It's ultimately a matter of how much your uptime is worth to you, and keep in mind that on a saturday evening you may not even find a new machine until monday morning, and then you still have to start installing everything, not to mention find the latest backups of your data. Me, I'll go
Re: Event feature already working in Server 5.1.37
Hi friends, This isn't rarelly to see...its common on these days. Get this: http://dev.mysql.com/tech-resources/articles/mysql_on_windows.html WB 2010/1/27 Shawn Green shawn.gr...@sun.com Daevid Vincent wrote: -Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent ...snipped ... People really use Windows for a mySQL server? Weird. ... Yes, they do. Not only is MySQL as cheap as the free version of MS SQL but it doesn't suffer from the hard limits the free version of MS SQL imposes and it works across all of your servers, regardless of platform. MS products are limited to Windows boxes. You cannot assemble a new Linux box and get MS-anything to run on it natively. With C, C++, .NET, JAVA, and ODBC connection options available, it's very easy to make a connection to MySQL from practically any MS development language. Some connectors will even integrate themselves into Visual Studio. Windows, as foreign as it may seem, is actually a very viable MySQL development platform. I encourage you to try it out and let us know what you think. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Fwd: auto_increment without primary key in innodb?
Yeah, Paul... This is so clear...the auto_increment column may be indexed like: - KEY(); - UNIQUE(); - PRIMARY KEY() ...when you create or alter a table. -- Wagner Bianchi 2010/1/25 Paul DuBois paul.dub...@sun.com The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Problems with slave_skip_errors on replication
Hi friends, Last weekend I made an environment that use a MySQL Server version 4.1 that was defined to be the MASTER and other one version 5.1 defined as SLAVE. Because the application that was concept working over exception, often the SLAVE server got new error and replication stops. Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you may see specified after this massage, but, the replication continue stops, even after this configurations. mysql show variables like 'slave_skip_errors'; +---+---+ | Variable_name | Value | +---+---+ | slave_skip_errors | 1 | +---+---+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 172.28.8.70 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bmg58-bin.000265 Read_Master_Log_Pos: 251871 Relay_Log_File: pid-file-relay-bin.07 Relay_Log_Pos: 961348 Relay_Master_Log_File: bmg58-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x.', 'x.sp', null)' Skip_Counter: 0 Exec_Master_Log_Pos: 952913 Relay_Log_Space: 264590369 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x', 'x.sp', null)' 1 row in set (0.00 sec) Have other thing to do, or this problem is made by the mix of versions? Best regards! -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Re: Problems with slave_skip_errors on replication
Ok, Suresh. . .I started MySQL with slave_skip_errors = all and It solve the problem! Thanks. WB 2010/1/25 Suresh Kuna sureshkumar...@gmail.com Hi Wagner, You have to start the server with the option as below for skipping the error. --slave-skip-errorr= 1062 or all 1062 - will skip the your error as the error number is 1062 and all will skip all the errors. You have to mention specific error numbers to skip the same. -- Thanks Suresh Kuna MySQL DBA On Mon, Jan 25, 2010 at 6:06 PM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Hi friends, Last weekend I made an environment that use a MySQL Server version 4.1 that was defined to be the MASTER and other one version 5.1 defined as SLAVE. Because the application that was concept working over exception, often the SLAVE server got new error and replication stops. Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you may see specified after this massage, but, the replication continue stops, even after this configurations. mysql show variables like 'slave_skip_errors'; +---+---+ | Variable_name | Value | +---+---+ | slave_skip_errors | 1 | +---+---+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 172.28.8.70 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: X58-bin.000265 Read_Master_Log_Pos: 251871 Relay_Log_File: pid-file-relay-bin.07 Relay_Log_Pos: 961348 Relay_Master_Log_File: X58-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x.', 'x.sp', null)' Skip_Counter: 0 Exec_Master_Log_Pos: 952913 Relay_Log_Space: 264590369 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x', 'x.sp', null)' 1 row in set (0.00 sec) Have other thing to do, or this problem is made by the mix of versions? Best regards!
Res: Table Locking (Was: Best CPU config for a busy DB server)
Locking will ocours with MyISAM tables when INSERT, UPDATE, DELETE or REPLACE statemats arrive that tables, lock at the table level. In INNODB engine, a lock ocours at the row-level. BDB have lock at the page-level. Wagner Bianchi Diretor de Tecnologia - INFODBA CT [EMAIL PROTECTED] - (31) 3272 - 0226 / 9114 - 7695 - Mensagem original De: Rob Wultsch [EMAIL PROTECTED] Para: JW [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Enviadas: Domingo, 11 de Maio de 2008 0:04:17 Assunto: Re: Table Locking (Was: Best CPU config for a busy DB server) On Sat, May 10, 2008 at 4:24 PM, JW [EMAIL PROTECTED] wrote: Table locking will occur with MyISAM tables when any row(s) of the table is being updated (Update,Delete,Insert,Load Data etc). If you are only executing Select statements, then they can be executed in parallel and won't be blocked. Just curious: you say with MyISAM tables - do any of the other table types (InnoDB, Falcon, etc) behave differently? Thanks, JW When locks are necessary, InnoDB uses row-level locking. MySQL 5.0 Certification Study Guide, page 419 -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Res: MySQL purge logs
mysql reset master; Wagner Bianchi Diretor de Tecnologia - INFODBA CT [EMAIL PROTECTED] - (31) 3272 - 0226 / 9114 - 7695 - Mensagem original De: Kaushal Shriyan [EMAIL PROTECTED] Para: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Enviadas: Domingo, 11 de Maio de 2008 13:39:02 Assunto: MySQL purge logs Hi I am referring to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html whats the exact syntax to purge this MySQL Binary Logs -rw-rw 1 mysql 701 1.1G May 5 07:39 host1-bin.000681 -rw-rw 1 mysql 701 1.1G May 5 09:09 host1-bin.000682 -rw-rw 1 mysql 701 1.1G May 5 10:49 host1-bin.000683 -rw-rw 1 mysql 701 1.1G May 5 20:24 host1-bin.000684 -rw-rw 1 mysql 701 1.1G May 5 21:47 host1-bin.000685 -rw-rw 1 mysql 701 1.1G May 5 23:31 host1-bin.000686 -rw-rw 1 mysql 701 1.1G May 6 01:40 host1-bin.000687 -rw-rw 1 mysql 701 1.1G May 6 04:26 host1-bin.000688 -rw-rw 1 mysql 701 1.1G May 6 07:00 host1-bin.000689 -rw-rw 1 mysql 701 1.1G May 6 08:58 host1-bin.000690 -rw-rw 1 mysql 701 1.1G May 6 17:54 host1-bin.000691 -rw-rw 1 mysql 701 1.1G May 6 21:01 host1-bin.000692 -rw-rw 1 mysql 701 1.1G May 6 22:46 host1-bin.000693 -rw-rw 1 mysql 701 1.1G May 7 00:56 host1-bin.000694 -rw-rw 1 mysql 701 1.1G May 7 02:52 host1-bin.000695 -rw-rw 1 mysql 701 1.1G May 7 05:44 host1-bin.000696 -rw-rw 1 mysql 701 1.1G May 7 07:28 host1-bin.000697 -rw-rw 1 mysql 701 1.1G May 7 09:09 host1-bin.000698 -rw-rw 1 mysql 701 1.1G May 7 18:40 host1-bin.000699 -rw-rw 1 mysql 701 1.1G May 7 21:00 host1-bin.000700 -rw-rw 1 mysql 701 1.1G May 7 22:35 host1-bin.000701 -rw-rw 1 mysql 701 1.1G May 8 00:40 host1-bin.000702 -rw-rw 1 mysql 701 1.1G May 8 03:20 host1-bin.000703 -rw-rw 1 mysql 701 1.1G May 8 05:53 host1-bin.000704 -rw-rw 1 mysql 701 1.1G May 8 07:59 host1-bin.000705 -rw-rw 1 mysql 701 1.1G May 8 09:24 host1-bin.000706 -rw-rw 1 mysql 701 1.1G May 8 18:36 host1-bin.000707 -rw-rw 1 mysql 701 1.1G May 8 21:21 host1-bin.000708 -rw-rw 1 mysql 701 1.1G May 8 22:57 host1-bin.000709 -rw-rw 1 mysql 701 1.1G May 9 01:25 host1-bin.000710 -rw-rw 1 mysql 701 1.1G May 9 03:41 host1-bin.000711 -rw-rw 1 mysql 701 1.1G May 9 06:05 host1-bin.000712 -rw-rw 1 mysql 701 1.1G May 9 07:50 host1-bin.000713 -rw-rw 1 mysql 701 1.1G May 9 09:29 host1-bin.000714 -rw-rw 1 mysql 701 1.1G May 9 19:20 host1-bin.000715 -rw-rw 1 mysql 701 1.1G May 9 21:46 host1-bin.000716 Thanks and Regards Kaushal Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Res: German collation for UTF8 missing
Hi Friend, Try this: CHARACTER SET: latin1 COLLATION: latin1_german2_ci Cya! Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 - Mensagem original De: Yves Goergen [EMAIL PROTECTED] Para: Marten Lehmann [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Enviadas: Quarta-feira, 14 de Novembro de 2007 21:24:47 Assunto: Re: German collation for UTF8 missing On 14.11.2007 21:43 CE(S)T, Marten Lehmann wrote: I want to store my data with UTF8, thus I'm using the utf8 charset for my tables. But which collcation shall I use? I cannot find anything appropriate. If I recall that correctly, utf8_swedish_ci is the collation to use for european/western european languages. Those Swedish people think they can stand for whole Europe... ;) Not tested my reply, though. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
--shared-memory, ??
Hi friends, I'm read somethings about the MySQL works with the option --shared-memory on Windows and about this I have some doubts. Anybody here know explain what are the chages compered without the parameter? Thk`s... Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Res: german datetime format?
You can treat it with DATE_FORMAT() and TIME_FORMAT(), change the format of MySQL variable, don't have way. You have to make a explicity convertion. ;-) Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 - Mensagem original De: Baron Schwartz [EMAIL PROTECTED] Para: Ralf Hüsing [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Enviadas: Sexta-feira, 19 de Outubro de 2007 22:56:02 Assunto: Re: german datetime format? Ralf Hüsing wrote: Hi, can i change the datetime format on mysql in a german format? At the moment the dates are stored like 2007-10-19 19:06:17 but if i send a query (which comes from user input) the query looks like WHERE Datum = '19.10.2007' and i got not what i want. iam using mysql (5.0.45) on (german) windows 2000, clients are connected via ODBC-Driver (3.51.21.00) and the application is using ADODB (mdac-lastest version). Try converting the user input to the correct type with STR_TO_DATE(), which despite its name can return a DATETIME value. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
DOCUMENTATION ABOUT SHOW PROFILE
Hi friends, Somebody here in this list have or knows where i get or read about the PROFILE of MySQL (SHOW PROFILE) ? I see this article of Schummi, but is most superficial. (http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html) Can u help me? Thk's 4 all. Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
CONCEPTS ABOUT PRIVILEGES!
Hi friends, This is the first time i post in this list and send a hello 4 all. I'm from Brazil. So, I have a doubt about the moment when u creat a new user. In my high-school course, my teacher is no good with MySQL and he said this: When u create a new user, that don't have the global privileges! Maybe, I think is not correctly, cause that new user have a USAGE privileges ok, and have the possibles privileges revoked in this moment. In resuming, the user have all global privileges, but, revoked! What you think about that? Thk's. Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8654 - 9510 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Res: MySQL only listens on localhost
After check the my.cnf, how said by friend Patricio, give the GRANT ALL ON *.* TO root@'%'; for thet user do the remote connections. For some doubts, keep on list! Bye! Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - +55 (31) 3272 - 0226 / 8427 - 8803 - Mensagem original De: Patricio A. Bruna [EMAIL PROTECTED] Para: Franz Edler [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Enviadas: Sábado, 13 de Outubro de 2007 17:07:04 Assunto: Re: MySQL only listens on localhost Check for bind-address in your my.cnf file - Franz Edler [EMAIL PROTECTED] escribió: Hello, Can anyone please give me a hint what I can do that MySQL also listens to the physical address of the host. I see that MySQL listen only to 127.0.0.1:3306 and therefore every connection via the physical interface is reset. What can I do? Regards Franz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
