Query from two databases
In each database I have a table with one column I want to compare and then count. So db1.a.odip and db2.aa.newip are the databases, table and coulumns. The data in each column is Ip's, so my result would be a list and count for each by subnet. So the result would be like: db1.a.odip count --- 10.10.10.30 192.168.5. 10 db2.aa.newip count --- 10.10.10. 20 192.168.5.40 I can parse two select statement to a perl script, but was wondering if this can be done in a select statement. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Db query help
In each database I have a table with one column I want to compare and then count. So db1.a.odip and db2.aa.newip are the databases, table and coulumns. The data in each column is Ip's, so my result would be a list and count for each by subnet. So the result would be like: db1.a.odip count --- 10.10.10.30 192.168.5. 10 db2.aa.newip count --- 10.10.10. 20 192.168.5.40 I can parse two select statement to a perl script, but was wondering if this can be done in a select statement. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help
I have two tables: DB 1: Table A: Userid: Dept: DB 2: Table B: Userid: Dept: Location: How would I query from DB 1 Table A for the Dept if I want to use that value for DB 2’s Dept? Both DB’s and tables have the same Userid. Does not work: Use 2; Select A.Dept from A where B.Userid = ‘sam’; I have to do the query using db 2. I hope this is not to dump of a question. Thanks. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to import data to diff tables
I have a table with 9 columns, one is an auto_increment for primary key. Data set: 2004-09-21 10:35:50,2004-09-21 10:45:48,tcp,111.111.111.111,80,222.222.222.222,1555,4700 Currently I just dump everyting into one table and query it that way. I was hoping to learn how to place the data into different tables. This will help the data files and index files from getting to large. Im just not sure where to start??? Currently I just use load data infile command from a shell script. I was wanting to have the datetime's stored in a different table, but have the same primary key as the data from the table with the Ip's and such. And help is great Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data in file with pipe
hello, I was tring to understand the steps to read from pipe and load using LOAD DATA INFILE? I dont understand the x's or the cat of tcp, can someone shed some light on this for me?? mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /dev/tcp/10.1.1.12/4711 /mysql/db/x/x mysql -e LOAD DATA INFILE 'x' INTO TABLE x x I have many text files a day that get loaded into the db at night and was looking at making something more real time, as these text files are created every 20 minutes. Im using a perl script to run insert statements on these text files but it takes a long time. The text files have around 140 lines every twenty minutes so you can imagine the tables get large quick. I thought I could wrap this LOAD DATA option in my perl script which could speed things up... Any help is great Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select help
Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help reading test-select
Hi, I was running the test-select under the /usr/local/mysql/sql-bench dir and was wondering if someone could help break it down for me ( Or at least the Testing big selects on the table section ) Thanks : [EMAIL PROTECTED] sql-bench]# perl test-select --password='d' Testing server 'MySQL 4.0.20 standard log' at 2004-05-27 8:29:45 Testing the speed of selecting on keys that consist of many parts The test-table has 1 rows and the test is done with 500 ranges. Creating table Inserting 1 rows Time to insert (1): 17 wallclock secs ( 0.28 usr 0.13 sys + 0.00 cusr 0.00 csys = 0.41 CPU) Test if the database has a query cache Time for select_cache (1): 3 wallclock secs ( 1.84 usr 0.20 sys + 0.00 cusr 0.00 csys = 2.04 CPU) Time for select_cache2 (1): 64 wallclock secs ( 2.29 usr 0.32 sys + 0.00 cusr 0.00 csys = 2.61 CPU) Testing big selects on the table Time for select_big (70:17207): 1 wallclock secs ( 0.22 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.26 CPU) Time for select_range (410:1057904): 19 wallclock secs (13.03 usr 1.86 sys + 0.00 cusr 0.00 csys = 14.89 CPU) Time for min_max_on_key (7): 17 wallclock secs (13.88 usr 1.18 sys + 0.00 cusr 0.00 csys = 15.06 CPU) Time for count_on_key (5): 12 wallclock secs ( 9.78 usr 0.94 sys + 0.00 cusr 0.00 csys = 10.72 CPU) Time for count_group_on_key_parts (1000:10): 2 wallclock secs ( 1.40 usr 0.12 sys + 0.00 cusr 0.00 csys = 1.52 CPU) Testing count(distinct) on the table Time for count_distinct_key_prefix (1000:1000): 0 wallclock secs ( 0.18 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.21 CPU) Time for count_distinct (1000:1000): 0 wallclock secs ( 0.23 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.24 CPU) Time for count_distinct_2 (1000:1000): 1 wallclock secs ( 0.24 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.26 CPU) Time for count_distinct_group_on_key (1000:6000): 0 wallclock secs ( 0.27 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.29 CPU) Time for count_distinct_group_on_key_parts (1000:10): 3 wallclock secs ( 1.34 usr 0.22 sys + 0.00 cusr 0.00 csys = 1.56 CPU) Time for count_distinct_group (1000:10): 2 wallclock secs ( 1.44 usr 0.11 sys + 0.00 cusr 0.00 csys = 1.55 CPU) Time for count_distinct_big (100:100): 15 wallclock secs (11.96 usr 1.52 sys + 0.00 cusr 0.00 csys = 13.48 CPU) Total time: 156 wallclock secs (58.39 usr 6.73 sys + 0.00 cusr 0.00 csys = 65.12 CPU) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql remote to apache
Is there a good tutorial on installing Mysql on one machine and having Apache on another? I'm thinking on the server with Apache I guess you would just compile Apache with the mysql/php and in your php scripts just point to the hostname of the mysql server? Making sure you can talk to port 3306. Has anyone seen performance issues with large databases and the results going over tcp, instead of the local installs which i use sockets? Some of my query results return 150,000 records. Im just running out of ram on the machine with both Apache/mysql and I cant upgrade to anymore memory for my dell server, at 4gb ram. rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One Mysql For loads and one for query
I have a Mysql db that is loaded with about 500,000 records a night using LOAD DATA INFILE. This goes on for a month then a new table is created and so on. Then data is then just queryed nothing else. To take some stress off of this server I was wondering if there is a way to have One Mysql server for loads and one to do querys?? I was thinking of turning on tcp/3306 and have a Load server use LOAD DATA LOCAL to load the data over tcp. So the current server would bascailly become my query server. Can you use LOAD DATA that way??? If not please direct me to the mysql tool that will do this, or a smarter way? thanks,rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One Mysql For loads and one for query
I thought I could use mysqlimport, but I notice that the text files need to be the same name as the table. My text files are named with a time stamp every hour so I have 24 files that get loaded. And the table name is nothing like the text files names. Is there a way to have mysqlimport import to a table that does not match the text file it is reading from?? From manual: mysqlimport strips any extension from the filename and uses the result to determine which table to import the file's contents into. Im sure someone can help me?? Rob -Original Message- From: rmck [EMAIL PROTECTED] Sent: Apr 30, 2004 8:54 AM To: [EMAIL PROTECTED] Subject: One Mysql For loads and one for query I have a Mysql db that is loaded with about 500,000 records a night using LOAD DATA INFILE. This goes on for a month then a new table is created and so on. Then data is then just queryed nothing else. To take some stress off of this server I was wondering if there is a way to have One Mysql server for loads and one to do querys?? I was thinking of turning on tcp/3306 and have a Load server use LOAD DATA LOCAL to load the data over tcp. So the current server would bascailly become my query server. Can you use LOAD DATA that way??? If not please direct me to the mysql tool that will do this, or a smarter way? thanks,rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from two tables
Hi I have two tables in the same Db : table 1: helpdesk | CREATE TABLE `helpdesk` ( `wcalledname` varchar(50) default NULL, `wcalledphone` varchar(50) default NULL, `wcalledemail` varchar(50) default NULL, `typeof` varchar(50) default NULL, `status` varchar(25) default NULL, `reasoncall` text, `reasoncalls` varchar(128) default NULL, `whorespond` varchar(128) default NULL, `datecallin` varchar(50) default NULL, `passedon` varchar(15) default NULL, `datetimetores` varchar(128) default NULL, `wresponded` varchar(128) default NULL, `resol` text, `dateclosed` varchar(25) default NULL, `hdcreatedate` datetime default NULL, `hdupdate` datetime default NULL, `hdid` int(10) NOT NULL auto_increment, PRIMARY KEY (`hdid`) ) TYPE=MyISAM table 2: archived | CREATE TABLE `archived` ( `wcalledname` varchar(50) default NULL, `wcalledphone` varchar(50) default NULL, `wcalledemail` varchar(50) default NULL, `typeof` varchar(50) default NULL, `status` varchar(25) default NULL, `reasoncall` text, `reasoncalls` varchar(128) default NULL, `whorespond` varchar(128) default NULL, `datecallin` varchar(50) default NULL, `passedon` varchar(15) default NULL, `datetimetores` varchar(128) default NULL, `wresponded` varchar(128) default NULL, `resol` text, `dateclosed` varchar(25) default NULL, `hdcreatedate` datetime default NULL, `hdupdate` datetime default NULL, `hdid` int(10) NOT NULL default '0', PRIMARY KEY (`hdid`) ) TYPE=MyISAM Once a month I archive records from the helpdesk table to the archived one if the record is closed, but sometimes a record is still open so some records for the last month are still in helpdesk while all the closed are in archived. My issue is I want to know how to query both tables for a list of all records that are created for a certain month weather closed or not. This works fine for one table: select * from `helpdesk` where month(hdcreatedate) = 3 order by `hdcreatedate` limit 0 , 30; I was trying to use this but it lists the records more than once: Help: select * from helpdesk,archived where month(helpdesk.hdcreatedate) = 3 and month(archived.hdcreatedate) = 3 LIMIT 0, 30; But it seems to list records over and over again. Please help if this type of query I'm trying to do can work. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from two tables #2
Hi I have two tables in the same Db : table 1: helpdesk | CREATE TABLE `helpdesk` ( `wcalledname` varchar(50) default NULL, `wcalledphone` varchar(50) default NULL, `wcalledemail` varchar(50) default NULL, `typeof` varchar(50) default NULL, `status` varchar(25) default NULL, `reasoncall` text, `reasoncalls` varchar(128) default NULL, `whorespond` varchar(128) default NULL, `datecallin` varchar(50) default NULL, `passedon` varchar(15) default NULL, `datetimetores` varchar(128) default NULL, `wresponded` varchar(128) default NULL, `resol` text, `dateclosed` varchar(25) default NULL, `hdcreatedate` datetime default NULL, `hdupdate` datetime default NULL, `hdid` int(10) NOT NULL auto_increment, PRIMARY KEY (`hdid`) ) TYPE=MyISAM table 2: archived | CREATE TABLE `archived` ( `wcalledname` varchar(50) default NULL, `wcalledphone` varchar(50) default NULL, `wcalledemail` varchar(50) default NULL, `typeof` varchar(50) default NULL, `status` varchar(25) default NULL, `reasoncall` text, `reasoncalls` varchar(128) default NULL, `whorespond` varchar(128) default NULL, `datecallin` varchar(50) default NULL, `passedon` varchar(15) default NULL, `datetimetores` varchar(128) default NULL, `wresponded` varchar(128) default NULL, `resol` text, `dateclosed` varchar(25) default NULL, `hdcreatedate` datetime default NULL, `hdupdate` datetime default NULL, `hdid` int(10) NOT NULL default '0', PRIMARY KEY (`hdid`) ) TYPE=MyISAM Once a month I archive records from the helpdesk table to the archived one if the record is closed, **Then I delete the closed records from the helpdesk table** but sometimes a record is still open so some records for the last month are still in helpdesk while all the closed are in archived. My issue is I want to know how to query both tables for a list of all records that are created for a certain month weather closed or not. This works fine for one table: select * from `helpdesk` where month(hdcreatedate) = 3 order by `hdcreatedate` limit 0 , 30; I was trying to use this but it lists the records more than once: Help: select * from helpdesk,archived where month(helpdesk.hdcreatedate) = 3 and month(archived.hdcreatedate) = 3 LIMIT 0, 30; But it seems to list records over and over again. Please help if this type of query I'm trying to do can work. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql openssl Question
Hello, I have a redhat system that is running mysql with openssl for secure connections. There was just a rpm update from redhat for openssl. I applied that and mysql/openssl connections seem fine. But my question is do I need to recomplie mysql to use the latest openssl This was a custom mysql server install. Syntax: shell ./configure (default=)--prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static (Added=)--with-vio --with-openssl --without-innodb --localstatedir=/local/db --with-mysqld-username=mysql mysql shows that ssl is in use: mysql \s blah blah SSL:Cipher in use is DHE-RSA-AES256-SHA blah blah mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date column question
I have a varchar field start on my db that stores dates. The dates are loaded into mysql from a txt file which are like this 2004-02-10 23:35:12 in the txt file. I'm thinking this is not a correct choice of datatype for the column which is called start. What is the best or recommended datatype to use for my date column start? Thanks upfront Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question
I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question part 2
I understand that I need to update the db's cardinality for this table I need speed Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one because with 56179085 records this could take a while... Thanks for the replies Rob -Forwarded Message- From: rmck [EMAIL PROTECTED] Sent: Feb 4, 2004 7:33 AM To: [EMAIL PROTECTED] Subject: index question I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table design question
I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? Should I use int for ports, which will have an index. Not sure how to store ip_address. This table has the possibility of having 800 millon records. Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repair Table Hung?
Help. I have a REPAIR table command that has been running since 1/15... I dont know if its hung or what? Should I kill it ? Top shows that it seems to be running? mysql show processlist; ++--+---+-+-++--+---+ | Id | User | Host | db | Command | Time | State| Info | ++--+---+-+-++--+---+ | 10 | root | localhost | ip_logs | Query | 391630 | Repair with keycache | REPAIR TABLE Jan04 QUICK ++--+---+-+-++--+--- # ls -alh Jan04.* -rw-rw1 mysqlmysql8.6K Dec 29 08:19 /curipdb/ip_logs/Jan04.frm -rw-rw1 mysqlmysql 23G Jan 15 16:25 /curipdb/ip_logs/Jan04.MYD -rw-rw1 mysqlmysql 22G Jan 20 06:30 /curipdb/ip_logs/Jan04.MYI # top sorted by CPU: 06:32:07 up 5 days, 14:31, 4 users, load average: 1.46, 1.49, 1.39 102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.2% user 0.1% system0.0% nice 0.0% iowait 99.2% idle CPU1 states: 12.0% user 0.4% system0.0% nice 0.0% iowait 87.0% idle Mem: 3874188k av, 3863468k used, 10720k free, 0k shrd, 33544k buff 2618824k actv, 563052k in_d, 89848k in_c Swap: 4289328k av, 527664k used, 3761664k free 3197660k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 5285 mysql 5 -10 501M 497M 452 S 12.5 13.1 1580m 0 mysqld 17002 root 16 0 1108 1108 820 S 0.3 0.0 0:13 0 top 728 root 16 0 2364 0 S 0.1 0.0 1:37 0 sshd Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2nd Request Repair Table Hung?
Help. I have a REPAIR table command that has been running since 1/15... I dont know if its hung or what? Should I kill it ? Top shows that it seems to be running? mysql show processlist; ++--+---+-+-++--+---+ | Id | User | Host | db | Command | Time | State| Info | ++--+---+-+-++--+---+ | 10 | root | localhost | ip_logs | Query | 391630 | Repair with keycache | REPAIR TABLE Jan04 QUICK ++--+---+-+-++--+--- # ls -alh Jan04.* -rw-rw1 mysqlmysql8.6K Dec 29 08:19 /curipdb/ip_logs/Jan04.frm -rw-rw1 mysqlmysql 23G Jan 15 16:25 /curipdb/ip_logs/Jan04.MYD -rw-rw1 mysqlmysql 22G Jan 20 06:30 /curipdb/ip_logs/Jan04.MYI # top sorted by CPU: 06:32:07 up 5 days, 14:31, 4 users, load average: 1.46, 1.49, 1.39 102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.2% user 0.1% system0.0% nice 0.0% iowait 99.2% idle CPU1 states: 12.0% user 0.4% system0.0% nice 0.0% iowait 87.0% idle Mem: 3874188k av, 3863468k used, 10720k free, 0k shrd, 33544k buff 2618824k actv, 563052k in_d, 89848k in_c Swap: 4289328k av, 527664k used, 3761664k free 3197660k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 5285 mysql 5 -10 501M 497M 452 S 12.5 13.1 1580m 0 mysqld 17002 root 16 0 1108 1108 820 S 0.3 0.0 0:13 0 top 728 root 16 0 2364 0 S 0.1 0.0 1:37 0 sshd Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHECK TABLE results
I'm getting an error when trting to update a table: ERROR 1034: Incorrect key file for table: 'Jan04'. Try to repair it So I ran CHECK TABLE, What does this result mean: mysql CHECK TABLE Jan04 QUICK; +---+---+--++ | Table | Op| Msg_type | Msg_text | +---+---+--++ | logs.Jan04 | check | warning | Table is marked as crashed | | logs.Jan04 | check | warning | 4 clients is using or hasn't closed the table properly | | logs.Jan04 | check | error| Can't read key from filepos: 22400088064 | | logs.Jan04 | check | error| Corrupt | +---+---+--++ 4 rows in set (14 min 23.80 sec) Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Update statement
Hello, I have a db that a script failed to run against, and becasue of that a column did not get updated. I have a varchar col that has unixtime timstamp in it. That column gets converted to be readable. My problem is I have many rows that need to be updated, but I dont want to re-convert the times that have already been converted. I know from about 61500 and up that the records are partially updated. mysql select start from table where ID =61500; +-+ | start | +-+ | 2003-12-28 00:45:16 | +-+ 1 row in set (0.00 sec) mysql mysql SELECT start,ID FROM table ORDER BY id DESC LIMIT 1; ++---+ | start | ID| ++---+ | 1072603517 | 617168732 | ++---+ 1 row in set (0.01 sec) mysql So now I want to run my update statment, how do I not update the times that have been converted??? Help my current update statement: from script: OID=`echo select ID from $TBLE order by ID desc limit 1; | $MSQL -u$UI -p$PD -h$HT $DB|grep -v ID` echo update $TBLE set start = from_unixtime(start) where ID '$OID';| $MSQL -u$UI -p$PD -h$HT $DB Thanks for your help up front Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
? about user and db table
Hi, I'm trying to uderstand the difference between these tables (mysql.user, mysql.db) v.4.0. I want user usera from hostname1 to connect to hostname2 which is where the Mysql db is running and have only select priv on db db_1. Hostname1 has mysql clients on it. Host: mysql select * from user where Host = 'hostname1'; +---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ | hostname1 | usera | 016 | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N | ANY || | | 0 | 0 | 0 | +---+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ 1 row in set (0.00 sec) mysql db: mysql select * from db where Host = ' hostname1'; +---++-+-+-+-+-+-+---++-+++---+--+ | Host | Db | User| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | +---++-+-+-+-+-+-+---++-+++---+--+ | hostname1 | db_1 | usera | Y | N | N | N | N | N | N | N | N | N | N | N| +---++-+-+-+-+-+-+---++-+++---+--+ 1 row in set (0.00 sec) mysql Which table gives user usera from hostname1 permission to just do select statements on the table db_1 on hostname2? I think its db? But since user says 'N' for all options for this user from hostname1 does that override the db table options?? I'v looked at the manual, but I'm just not getting it... Can someone break it down for me... Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL with SSL enabled
Hello, I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL enabled. Does this feature come enabled with the binary download? Or do I need to download the source and compile it? Any good links ot setting up MySQL with SSL enabled? Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tune ?
Hello, Is there any RH 9 kernel tuning tips for system running Mysql 4.0 db? Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on Query
If you have a varchar column (late) that holds a timestamp like: 2003-11-01 08:45:12 Can you use this select statement: select * from table where DATE_FORMAT(`late`, '%Y-%m-%d') '2003-11-01'; Even though the varchar column is not at datetime,timestamp column... Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create own auto increment number
I have a request to create an auto increment field that increments like so: 1-112403 2-112403 3-112403 4-112503 etc... Is that possible??? Any variant of that is fine the big issue is they want the Date with the ID number of the record. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYI file
Hello, I have had some Error 127 on my system so I ran myisamchk -rf when mysqld was down. Now I noticed my .MYI file is at 1024K: -rw-rw1 mysqlmysql1024 Nov 11 16:33 table.MYI My .MYD, .frm are still there: -rw-rw1 mysqlmysql8802 Nov 6 07:04 table.frm -rw-rw1 mysqlmysql8812359152 Nov 11 01:25 table.MYD Is there a way to get my indexes/MYI back?? Thank You Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA ?
Hello, My Load data command seems not to load data into my db: mysql load data local infile '/opt/week/ip.0311100440' into table logs.Nov03 ignore 2 lines; Query OK, 1 row affected (0.66 sec) Records: 48273 Deleted: 0 Skipped: 48272 Warnings: 48273 mysql I have plenty of space where my data files are.. Can someone point me in the right direction to see why it is skipping those the table looks ok mysql show table status; ++++---++-+-+--+---++-+-+-+-+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++-+-+--+---++-+-+-+-+-+ | Nov03 | MyISAM | Dynamic| 72426930 |112 | 8140043192 | 1099511627775 | 5872661504 | 112 | 83656882 | 2003-11-06 07:19:13 | 2003-11-12 12:23:12 | 2003-11-12 10:04:36 | max_rows=10 | Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 127 = Record-file is crashed
Hello, I have mysql-standard-4.0.16-pc-linux-i686 installed on a 4gig mem, 2cpu system, RH 9. I have a large table (Data records: 72426930) that is now giving me this error Error 127 when doing selects... This is my first crash of any kind with Mysql. So I'm a liitle confused on what I should do. The manual states use myisamchk, then you read further and it says use REPAIR TABLE... I'm confused can someone give me a 1 to end step on repairing this table?? At this point I brought my mysqld down. Tried running this: [root]# myisamchk -r Nov03 - recovering (with keycache) MyISAM-table 'Nov03' Data records: 72426930 myisamchk: error: Can't create new tempfile: '/opt/logs/Nov03.TMD' MyISAM-table 'Nov03' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option So I tried this: [root]# myisamchk -rf Nov03 [EMAIL PROTECTED] ip_logs]# myisamchk -rf Nov03 - recovering (with keycache) MyISAM-table 'Nov03' Data records: 72426930 5939000 It looks like its doing something my data dir and the table in question: -rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD -rw-rw1 mysqlmysql5346325504 Nov 11 14:52 Nov03.MYI -rw-rw1 mysqlmysql631242752 Nov 11 14:52 Nov03.TMD Please advise... Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
