Re: Fulltext 3 letter words
I think you are using .ini files present in the source directory . Can you copy the .ini file to your datadir ( eg : my-innodb-heavy-4G.ini conf file for 4GB RAM ) and pt out the same at startup ? Not sure whether iam in the right path .. Correct me if iam wrong --Praj On Mon, 24 Apr 2006 13:59:27 +1000 Taco Fleur [EMAIL PROTECTED] wrote: Hi Prasad, I have no my.cfn, I have a my.ini which is located at in the same directory as the my-innodb-heavy-4G.ini C:\Program Files\MySQL\MySQL Server 5.0 Kind regards, Taco Fleur (Mobile 0421 851 786) Commerce Engine Pty Ltd - Australia's leading online Payment Gateway . Local Call 1300 859 179 Postal Address: PO Box 15118, City East Brisbane, Queensland, 4002, Australia Head office: 31 Valencia Court, Eatons Hill, Queensland, 4037, Australia Telephone: +61 (0) 7 3857 3881 Fax: +61 (0) 7 3414 6464 Internet: http://www.commerceengine.com.au * Accepting payments online is easy * Developer API, XML, POST * Low fees * Full integration by a one-stop-shop The information contained in this email may be confidential. You should only disclose, re-transmit, copy, distribute, act in reliance on or commercialise the information if you are authorised to do so. Any views expressed in this email communication are those of the individual sender, except where the sender specifically states them to be the views of Commerce Engine Pty Ltd. Any advice contained in this e-mail has been prepared without taking into account your objectives, financial situation or needs. Before acting on any advice in this e-mail, Commerce Engine recommends that you consider whether it is appropriate for your circumstances. Commerce Engine does not represent, warrant or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, virus or interference. Commerce Engine Pty Ltd ACN 118 850 552 -Original Message- From: Prasad [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 1:51 PM To: [EMAIL PROTECTED] Subject: Re: Fulltext 3 letter words Hi Taco Fleur., Where is your current my.cnf file present? have it in c:\my.cnf or you have to mention where your file present in the startup with --defaults-file=path/my.cnf.Note even without cnf file also mysql can be started. -Prasad. Sify. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: 'Prasad' [EMAIL PROTECTED] Sent: Monday, April 24, 2006 9:10 AM Subject: RE: Fulltext 3 letter words Sorry I am on OS Windows 2003 Kind regards, Taco Fleur (Mobile 0421 851 786) Commerce Engine Pty Ltd - Australia's leading online Payment Gateway . Local Call 1300 859 179 Postal Address: PO Box 15118, City East Brisbane, Queensland, 4002, Australia Head office: 31 Valencia Court, Eatons Hill, Queensland, 4037, Australia Telephone: +61 (0) 7 3857 3881 Fax: +61 (0) 7 3414 6464 Internet: http://www.commerceengine.com.au * Accepting payments online is easy * Developer API, XML, POST * Low fees * Full integration by a one-stop-shop The information contained in this email may be confidential. You should only disclose, re-transmit, copy, distribute, act in reliance on or commercialise the information if you are authorised to do so. Any views expressed in this email communication are those of the individual sender, except where the sender specifically states them to be the views of Commerce Engine Pty Ltd. Any advice contained in this e-mail has been prepared without taking into account your objectives, financial situation or needs. Before acting on any advice in this e-mail, Commerce Engine recommends that you consider whether it is appropriate for your circumstances. Commerce Engine does not represent, warrant or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, virus or interference. Commerce Engine Pty Ltd ACN 118 850 552 -Original Message- From: Prasad [mailto:[EMAIL PROTECTED] Sent: Monday, 24 April 2006 1:32 PM To: [EMAIL PROTECTED] Subject: Re: Fulltext 3 letter words Hi., Are you using unix flavor? If so do you have a file my.cnf under /etc. If not so copy the file from /mysqlhomepath/support-file/my-huge.cnf to /etc/my.cnf Modify the changes in /etc/my.cnf under [mysqld] Innodb tables dosent support fulltext index type. You can gothrough the below link where you can find full-text search functions. http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html -Prasad. Sify. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: 'John Hicks' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, April 24, 2006 8:22 AM Subject: RE: Fulltext 3 letter words In my-innodb-heavy-4G.ini I
Re: MySQL 5 - Slow Login from remote
Check where u resolve . that could help you I guess this DNS problem ,correct me if iam wrong . --Praj On Thu, 20 Apr 2006 18:43:39 +0200 Kai Schmidt [EMAIL PROTECTED] wrote: Hi @all i have a Problem with Login into a MySQL 5 Server. When i try to login from a remote Machine into the Server, it needs up to 30 seconds. When i login from the same machine all is at it should be. After login the Server is responding normal to querys. My Environment: SuSE 10.0 MySQL 5.0.20-max (i tried it also with 5.0.20-standard and 5.0.18-standard) I tried to login from an MS Windows XP Machine with ODBC 3.51.20 and with MySQL Administrator and Query Browser. I tried it also with SuSE 10 with MySQL Administrator and Query Browser. Everytime it is the same. Login Process is very poor. When i use the Query Browser, for each Query there is a new Login to the Server. So he needs for each Query about 30 Seconds and for the Query (a small one) 1 Second. Thats ugly. Somebody a Tip for me, how to figure out the Problem? Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password for Root
Hi Restart mysqld with the --skip-grant-tables option More info : http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html --Praj On Mon, 10 Apr 2006 12:57:32 +0600 Kosala Atapattu [EMAIL PROTECTED] wrote: Hi people, I have a small problem. I forgot the password for user root in my personal MySQL instance. I'm a Linux user and running Debian Sarge on my computer. I have few other DBs which I created and which I have access to (still I remember the passwords) but are not having access to MySQL database. Is there any way to recover from this situation. If I reinitialize the DB (somehow) how can I port my existing information back in to the initialized DB (without exporting and importing). Any Debian friends who can help me. Cheers, Kosala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB how to.
Hi Can you through us more lights on error ? where does this occur ? -bash-2.05b$ perror 10 30 OS error code 10: No child processes OS error code 30: Read-only file system -bash-2.05b$ -Praj On Wed, 5 Apr 2006 08:48:08 +0700 Truong Tan Son [EMAIL PROTECTED] wrote: [S] Dhandapani wrote: mysql show global variables like '%innodb%'; +-++ | Variable_name | Value | +-++ | have_innodb | YES OK, all thing is let default setting. mysql *|SET AUTOCOMMIT=0;|* Query OK, 0 rows affected (0.00 sec) mysql *|INSERT INTO CUSTOMER VALUES (15, 'John');|* Query OK, 1 row affected (0.00 sec) ERROR 1030 (HY000): Got error -1 from storage engine What is error ? I follow Document: http://dev.mysql.com/doc/refman/5.0/en/innodb-transactions-with-different-apis.html The error is same. Please teach me. Best regards, -- mysql *|ROLLBACK;|* Query OK, 0 rows affected (0.00 sec) mysql *|SELECT * FROM CUSTOMER;|* +--++ | A| B | +--++ || | +--++ the inset stattement has been rollbacked. Reference http://dev.mysql.com/doc/refman/5.0/en/innodb-transactions-with-different-apis.html Regards, Dhandapani Dba Sify Limited. Truong Tan Son wrote: Dear Sir, I install MySQL 5.0.18 on RH EL4, but do not know how to use InnoDB for ROLLBACK. ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-innodb make make install **/etc/my.cnf: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 with setting above, I could not use InnoDB. How to know InnoDB is installed, and start it ? Thanks you and best regards, -- ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] www.sify.com - your homepage on the internet for news, sports, finance, astrology, movies, entertainment, food, languages etc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB how to.
Gotit Thnx ;) You have set innodb_force_recovery to 3 which means , do not run transaction rollbacks after recovery. Just try removing the innodb_force_recovery from my.cnf file or change the value More info : http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html --Praj On Wed, 5 Apr 2006 14:02:06 +0700 Truong Tan Son [EMAIL PROTECTED] wrote: Prasanna Raj [EMAIL PROTECTED] wrote: Can you through us more lights on error ? where does this occur ? **ERROR: InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. Please teach me raw. **/etc/my.cnf: # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/mysql/var) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] init_connect='SET AUTOCOMMIT=0' innodb_force_recovery = 3 port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr
Re: Restoring a binary log ( mysqlbinlog )
Hi All Correct me if iam wrong You might have run the create table syntax from hrdb database .So it gives you the create table also when you do mysqlbinlog . CREATE TABLE intra.table1 AS SELECT * FROM NO DATABASE NAMEtable2 , which means you are under hrdb database. Else you can use below syntax which wont get loaded under hrdb CREATE TABLE intra.table1 AS SELECT * FROM hrdb.table2 ( OR ) use intra ; CREATE TABLE table1 AS SELECT * FROM hrdb.table2 --Praj On Wed, 05 Apr 2006 08:44:52 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi , Yesterday our main database was dropped by mystake. This has never happened before. Furtunately we had a daily backup performed with mysqldump at about 06:00 am that day. As the problem ocurred at about 12:00am we had to restore the transactions from the binary log ( we have several binary log files for each day ) For this pourpose we used mysqlbinlog in order to prepare a source file with the transactions to be restored, but as we needed to restore transactions for only one database, we used the option -d win mysqlbinlog like this mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql after that we used the resulting file like this mysql -e source src.sql all the process took about 1 hour to complete and our data was perfectly restored. But examinig the source file src.sql we noted that there were some transactions pertaining other databases than hrdb which we think shouldn't be there because of the -d option used with mysqlbinlog. These transaction were table creation querys like CREATE TABLE intra.table1 AS SELECT * FROM table2 In this query table1 is located in database intra and table2 belongs to database hrdb since the creation of table1 occurs in database intra we thought this query shouldn't be in the source Am I right ? The problem with this is that the time for the whole restore operation would have been considerabily lower if all of these useless transactions were excluded ( this write operations were created as a result from executing reports not data modification ) thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring a binary log ( mysqlbinlog )
It will be written in binary file but when you do mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql It wont be loaded in src.sql --Praj On Wed, 05 Apr 2006 09:53:34 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi, please see comments below.. thank you Mauricio On Wed, 2006-04-05 at 09:02, Prasanna Raj wrote: Hi All Correct me if iam wrong You might have run the create table syntax from hrdb database .So it gives you the create table also when you do mysqlbinlog . CREATE TABLE intra.table1 AS SELECT * FROM NO DATABASE NAMEtable2 , which means you are under hrdb database. Thast exactly the situation. Else you can use below syntax which wont get loaded under hrdb CREATE TABLE intra.table1 AS SELECT * FROM hrdb.table2 ( OR ) use intra ; CREATE TABLE table1 AS SELECT * FROM hrdb.table2 Would this prevent this kind of querys from appearing in the source ? --Praj On Wed, 05 Apr 2006 08:44:52 -0300 Mauricio Pellegrini [EMAIL PROTECTED] wrote: Hi , Yesterday our main database was dropped by mystake. This has never happened before. Furtunately we had a daily backup performed with mysqldump at about 06:00 am that day. As the problem ocurred at about 12:00am we had to restore the transactions from the binary log ( we have several binary log files for each day ) For this pourpose we used mysqlbinlog in order to prepare a source file with the transactions to be restored, but as we needed to restore transactions for only one database, we used the option -d win mysqlbinlog like this mysqlbinlog -d hrdb --start-date-time=2006-04-04 06:00:00 src.sql after that we used the resulting file like this mysql -e source src.sql all the process took about 1 hour to complete and our data was perfectly restored. But examinig the source file src.sql we noted that there were some transactions pertaining other databases than hrdb which we think shouldn't be there because of the -d option used with mysqlbinlog. These transaction were table creation querys like CREATE TABLE intra.table1 AS SELECT * FROM table2 In this query table1 is located in database intra and table2 belongs to database hrdb since the creation of table1 occurs in database intra we thought this query shouldn't be in the source Am I right ? The problem with this is that the time for the whole restore operation would have been considerabily lower if all of these useless transactions were excluded ( this write operations were created as a result from executing reports not data modification ) thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: better way of doing 1800 sequential updates?
Try creating INDEX on id column I think that will do the trick ;) --Praj On Mon, 3 Apr 2006 19:07:34 -0600 Ariel Sánchez Mora [EMAIL PROTECTED] wrote: This table holds latest data from an app: mysql select * from ultimas_respuestas_snmp limit 10; +++---+-+---++--+ | id | info_oficina | columna_donde_guardar | info_interfaz | valorSNMP | nombre_dns | hora | +++---+-+---++--+ | 0 | Sucursal Canas | USO_CPU_1min || error | canas | 18:49:53 | | 1 | Sucursal Canas | RAM_LIBRE || error | canas | 18:49:54 | | 2 | Sucursal Canas | ESTADO_ADMIN_1| TDM 195-2883 ICE| 1 | canas | 18:49:55 | | 3 | Sucursal Canas | ESTADO_ADMIN_2| RDSI 669-9010 ICE | error | canas | 18:49:56 | | 4 | Sucursal Canas | ESTADO_ADMIN_3| RDSI_doble 669-9010 ICE | error | canas | 18:49:57 | | 5 | Sucursal Canas | ESTADO_PROTOCOLO_1| TDM 195-2883 ICE| error | canas | 18:49:58 | | 6 | Sucursal Canas | ESTADO_PROTOCOLO_2| RDSI 669-9010 ICE | error | canas | 18:49:59 | | 7 | Sucursal Canas | ESTADO_PROTOCOLO_3| RDSI_doble 669-9010 ICE | 5 | canas | 18:50:00 | | 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE| error | canas | 18:50:01 | | 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE | error | canas | 18:50:02 | +++---+-+---++--+ 10 rows in set (0.00 sec) without the limit 10 1780 rows in set (0.03 sec) the create table: ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` ( `id` int(4) NOT NULL default '0', `info_oficina` varchar(35) default NULL, `columna_donde_guardar` varchar(30) default NULL, `info_interfaz` varchar(30) default NULL, `valorSNMP` varchar(12) default NULL, `nombre_dns` varchar(20) default NULL, `hora` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I cannot use indexes because my dbexpress driver doesn't support it (long story short, I'll change it in the next version). Now, I have to make a lot of sequential updates, like UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:21 WHERE id =0; UPDATE ultimas_respuestas_SNMP SET valorSNMP =10, hora =18:47:22 WHERE id =1; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =2; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =3; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =4; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =5; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =6; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =7; UPDATE ultimas_respuestas_SNMP SET valorSNMP =7000, hora =18:47:24 WHERE id =8; . . . UPDATE ultimas_respuestas_SNMP SET valorSNMP =0, hora =18:48:38 WHERE id =1778; UPDATE ultimas_respuestas_SNMP SET valorSNMP =, hora =18:48:38 WHERE id =1779; This makes my server CPU load top 100% for about 1:20 s. First question: is update the best command for this? I've seen replace that might work too; has anyone played around with something like this before? Second: is there a better way of formulating the update command, for this sequence?? Perhaps one that takes advantage of the sequential inserts? TIA, all comments welcome. I am a newbie by the way, trying to optimize my first MySQL related program. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete Duplicates
Subquries will help you . --Praj On Thu, 30 Mar 2006 21:11:56 -0500 Rich [EMAIL PROTECTED] wrote: Hi there. Any quick way of killing duplicate records? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance problems.
Is tat query is the problem ? Then turn on your slow queies and try optimizing those slow queries ? Post your queries and table description for further help :) --Praj On Wed, 29 Mar 2006 12:33:20 -0500 Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259 mysql/ 3055 mysql/snort 2184 mysql/snort_archive 1546 mysql/snort_archive/data.MYD 1546 mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74mysql/snort_archive/event.MYD 74mysql/snort/event.MYD 42mysql/snort_archive/data.MYI 42mysql/snort/data.MYI 40mysql/snort_archive/icmphdr.MYI 40mysql/snort/icmphdr.MYI 35mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:44 16:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348
Re: ~BCP for mysql~
Try Using Select into outfile More Info : http://dev.mysql.com/doc/refman/5.0/en/select.html --Praj On Wed, 22 Mar 2006 12:59:44 +0530 Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, Can we any use bcp to perform bulk copy for MYSQL as in SYBASE ? If not, is there any other tool which can do the same operation. I need to perform a bcp out from Mysql and then bcp in to Sybase. Please help me out regarding the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
I guess u had 2.5 million records splited into 1600 tables if iam not wrong :) Why you split the tables to 1600 is there any specific count ? Why you moved to merge table reason behind ? Since 2.5 million records to 1600 is a huge count on nos of tables :( instead of one ;) --Praj On Tue, 14 Mar 2006 07:14:52 -0400 Paul Halliday [EMAIL PROTECTED] wrote: Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update using 'set' keyword
Hi Iam not sure about the answer correct me if iam wrong :( Dont use single quotes in count_of_logons .. Try : $sql = UPDATE members SET count_of_logons = count_of_logons + 1 WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; --Praj On Mon, 13 Mar 2006 17:18:58 -0500 fbsd_user [EMAIL PROTECTED] wrote: Trying to bump the count_of_logons by 1 using this update. Phpmyadmin shows the count staying at zero. I think that this SET count_of_logons = 'count_of_logons + 1' is not coded correctly, but I get no errors so can not tell. Anybody have any ideas? The table def has count_of_logons INT, $sql = UPDATE members SET count_of_logons = 'count_of_logons + 1' WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL mysql-max-4.0.20 - Error 2008 - MySQL client out of Memory
Hi Tom, Use -q option on mysqldump ;) -q Won't buffer query, It will dump directly to stdout. --Praj On Tue, 14 Mar 2006 11:56:42 + Tom Brown [EMAIL PROTECTED] wrote: Hi Trying to dump a complete db where 1 of the tables contains about 88 million rows - When the dump runs on the command line the following error is thrown mysqldump: Out of memory (Needed 3543176 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Is it possible to give more memory to the client when running on the command line? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Saving
I guess you are Not selecting the database .. any error msg ? eg : mysql_select_db( '' ) or die( 'Could not select database' ); --Praj On Wed, 8 Mar 2006 07:45:20 -0300 Kleiton L R Soares [EMAIL PROTECTED] wrote: Please, i'm a crazy ! i dont can write in a table, please anybody can help me try find the error Thanks ?php $ipt_nome = $HTTP_POST_VARS[nome]; $ipt_telefone = $HTTP_POST_VARS[telefones]; $ipt_nascimento = $HTTP_POST_VARS[nascimento]; $ipt_estado = $HTTP_POST_VARS[estado]; $ipt_cidade = $HTTP_POST_VARS[cidade]; $ipt_bairro = $HTTP_POST_VARS[bairro]; $ipt_cep = $HTTP_POST_VARS[codcep]; $ipt_endereco = $HTTP_POST_VARS[endereco]; $ipt_email = $HTTP_POST_VARS[email]; $ipt_site = $HTTP_POST_VARS[site]; $ipt_estuda = $HTTP_POST_VARS[estuda]; $ipt_curso = $HTTP_POST_VARS[curso]; $ipt_periodo = $HTTP_POST_VARS[periodo]; $ipt_faculdade = $HTTP_POST_VARS[faculdade]; $ipt_trabalha = $HTTP_POST_VARS[trabalha]; $ipt_ultima = $HTTP_POST_VARS[ultimaempresa]; $ipt_ultima_desde = $HTTP_POST_VARS[ultimadesde]; $ipt_ultima_ate = $HTTP_POST_VARS[ultimaate]; $ipt_ultima_cargo = $HTTP_POST_VARS[ultimacargo]; $ipt_ultima_descricao = $HTTP_POST_VARS[ultimadescricao]; $ipt_penultima = $HTTP_POST_VARS[penempresa]; $ipt_penultima_desde = $HTTP_POST_VARS[pendesde]; $ipt_penultima_ate = $HTTP_POST_VARS[penate]; $ipt_penultima_cargo = $HTTP_POST_VARS[pencargo]; $ipt_penultima_cargo = $HTTP_POST_VARS[pencargo]; $ipt_penultima_descricao = $HTTP_POST_VARS[pendescricao]; $ipt_ante_penultima = $HTTP_POST_VARS[apempresa]; $ipt_ante_penultima_desde = $HTTP_POST_VARS[apdesde]; $ipt_ante_penultima_ate = $HTTP_POST_VARS[adate]; $ipt_ante_penultima_cargo = $HTTP_POST_VARS[apcargo]; $ipt_ante_penultima_descricao = $HTTP_POST_VARS[apdescricao]; $ipt_horario = $HTTP_POST_VARS[horario]; $ipt_interesse = $HTTP_POST_VARS[areainteresse]; $ipt_ingles = $HTTP_POST_VARS[ingles]; $ipt_ult_obs = $HTTP_POST_VARS[ultobs]; //Inicio da Linha de kleiton $conexao = mysql_connect(localhost , admcurriculos , pcs2006) or print mysql_error(); mysql_query(insert into curtos (nome, telcel, nascimento, estado, cida de, bairro, codcep, endereco, email, site, estuda, curso, periodo, faculdade, tr abalha, ultimaempresa, ultimadesde, ultimaate, ultimacargo, ultimadescricao, pen empresa, pendesde, penate, pencargo, pendescricao, apempresa, apdesde, apate, ap cargo, apdescricao, horario, ingles, areainteresse, ultob) values ('$ipt_nome',' $ipt_telefone','$ipt_nascimento','$ipt_estado','$ipt_cidade','$ipt_bairro','$ipt _cep','$ipt_endereco','$ipt_email','$ipt_site','$ipt_estuda','$ipt_curso','$ipt_ periodo','$ipt_faculdade','$ipt_trabalha','$ipt_ultima','$ipt_ultima_desde','$ip t_ultima_ate','$ipt_ultima_cargo','$ipt_ultima_descricao','$ipt_penultima','$ipt _penultima_desde','$ipt_penultima_ate','$ipt_penultima_cargo','$ipt_penultima_de scricao','$ipt_ante_penultima','$ipt_ante_penultima_desde','$ipt_ante_penultima_ ate','$ipt_ante_penultima_cargo','$ipt_ante_penultima_descricao','$ipt_horario', '$ipt_ingles','$ipt_interesse','$ipt_ult_obs'), $conexao) or print mysql_error( ); mysql_close($conexao) or print mysql_error(); mysql_close($conexao) or print mysql_error(); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to know the base tables?
Hi Simple way is show table status; there is a column Type : value MRG_MyISAM is the base table. --Praj On Tue, 7 Mar 2006 14:02:44 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, How do you know what the sources are for a MERGE table? SHOW TABLE STATUS doesn't show it, as far as I can see? Is it really only available in SHOW CREATE TABLE? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to know the base tables?
Oh yeah got it u meant UNION clause . Still i have no idea about that i usally see it in show create tables statements . Sorry :( --Praj On Tue, 7 Mar 2006 15:10:51 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi, Simple way is show table status; there is a column Type : value MRG_MyISAM is the base table. No, that's not what I meant -- I meant, the merge table uses a UNION clause to get signal where it get it's data from. Where can I get the sources for the merge table? That is, what is used in the UNION clause when creating it. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com --Praj On Tue, 7 Mar 2006 14:02:44 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, How do you know what the sources are for a MERGE table? SHOW TABLE STATUS doesn't show it, as far as I can see? Is it really only available in SHOW CREATE TABLE? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication
Yes ..u can use older version as master and 5.0 version as slave http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html http://linux.com.hk/penguin/developer/mysql/manual_Replication.html#Replication_Compatibility Ciao Praj On Fri, 3 Mar 2006 12:32:24 +0200 Octavian Rasnita [EMAIL PROTECTED] wrote: Hi, I have 2 servers. On one of them I have MySQL 4.1 (the main server) and on the second I have MySQL 5.0. I want to use the second server to replicate the first server. Is it possible or the servers should have the same version? Or I will need to install one more MySQL 4.1 on the second server and use it for replicating the first server? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]