Re: Table/select problem...
I had this same issue a while back and solved it by writing my events to a disk-based file and periodically importing them into the event log MyISAM table. This way, even if your select statements lock the table, it won't affect the performance of your application. Of course, this may require some rewriting of your application code, depending on how events are logged. You could avoid the locking with InnoDB, but I did not choose that solution because MyISAM seems like a better fit for a logging situation, and they can later be used in Merge tables. I wonder if any others have used InnoDB for large logging tables and what the performance has been? Steve Musumeche CIO, Internet Retail Connection st...@internetretailconnection.com 1-800-248-1987 ext 802 On 2/4/2011 11:29 AM, Andy Wallace wrote: Greetings, all... I'm having an issue with a SELECT in our system. We have an event log table, with about 9 million rows in it. Inserts happen with some pretty high frequency, and these selects happen periodically. The event_log table is MyISAM, the rest of the tables are InnoDB. What's happening is that, periodically, when this select gets run, the whole damn thing locks up, and that pretty much shuts us down (since many things insert events into the table, and the table gets locked, so all the inserts hang). The statement and the explain for it are below. the enduser table has about a million rows in it, the event_type table 35 rows. The weird part is that, if I strip down the query to use no joins, the explain wants to return about 17,000 rows, but the query itself does the table locking thing. Should we perhaps change the event log to InnoDB to avoid table locking? Might the table itself be corrupt in some way? Any thoughts? thanks, andy EXPLAIN SELECT EL.event_log_id, EL.event_time, DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted', ET.event_type_id, ET.description, EL.csr_name, EL.enduser_acnt, EL.csr_name, EL.referer, EL.mls_id, EL.mls_no, EL.ss_id, EL.details, E.fname, E.lname, E.email, E.phone1 FROM event_log EL JOIN event_type ET ON EL.event_type_id = ET.event_type_id JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt WHERE EL.acnt = 'AR238156' AND EL.enduser_acnt != '' AND EL.event_type_id = 'EndUserLogin' AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW() ORDER BY EL.event_time DESC *** 1. row *** id: 1 select_type: SIMPLE table: ET type: const possible_keys: PRIMARY key: PRIMARY key_len: 92 ref: const rows: 1 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: EL type: index_merge possible_keys: agent,enduser,event_log_ibfk_1 key: agent,event_log_ibfk_1 key_len: 62,92 ref: NULL rows: 1757 Extra: Using intersect(agent,event_log_ibfk_1); Using where *** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 8:00 AM, Robinson, Eric wrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: corrupted tables
How about your disk space? I had a similar problem on a large table and it ended up being caused by filling up the disk. Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Octavian Rasnita wrote: From: Steve Edberg [EMAIL PROTECTED] Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use repair table but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. You'll have to give us some more information...at least: * What MySQL version, OS platform, and file system used for database? I am using MySQL 5, under Fedora Core 4, installed with its default options. * Does this happen at a regular time, or apparently randomly? It happends apparently randomly. Sometimes I just see that the programs are not working. Sometimes I can do some simple queries in the table with problems (like select count(*) from table_name), and the query works fine, but only when trying some more complex queries I can find that the table is corrupt and I need to fix it. Sometimes after fixing the table no records are deleted, but sometimes one or more records are deleted after fixing it. * Does this happen to the same tables all the time, or is that random as well? I found that it happends in more tables, but especially with one of them. That table has more than 2 million records and it is a MyISAM table. Should I use InnoDB instead? (Or another storage system?) That table is updated by a single program which runs continuously a few hours every day, and the program add (just addings and no updates) aproximately 1 records in those few hours... so they are not very very many. But other programs query that table very often. * Is this a precompiled binary from MySQL or did you build it yourself? It is a precompiled version from MySQL. I could see that if you compiled it yourself against some buggy libraries you could have problems; perhaps a cronjob is doing some copy/restore process on the underlying files without shutting mysql down or flushing logs; perhaps a lot of things...more information is needed. I have also seen (in most of the tables if not all) that after using check table table_name for the first time, I receive the message that the table was not closed by a few processes (from 2 to 6 processes). If I use that query a second time, I receive the message that the table is ok, and that message doesn't appear again. It has been my experience (on Windows NT, Solaris and Linux platforms) that MySQL has been one of the more reliable programs out there. Even after system crashes I haven't lost any data; a repair table and index rebuild fixed things. Yes in some cases it is the same for me, but after reparing a table, sometimes it tells me that some records were deleted because before that repair query the number of records reported is bigger. steve Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how would mysqld restart affect dynamically set global variables?
I believe it would revert back to the settings in your my.cnf file. If you want the change to be permanent, then set it there. Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Bing Du wrote: Hi, We're running mysql 4.1.20. If I understand the manual correctly, I can change max_connections while mysqld is running without restart mysqld to make the change take effect. But what if mysqld restarts later in some other situations, like machine reboot, would my (global) change on max_connections remain? I'm just very clear when to make dynamic changes and when is better to put changes in my.cnf. Please advise. Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making varchar field to act like numeric field
I think this method will work, however, when trying these queries, I get
a SQL syntax error.
mysql select cast('34' AS decimal);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'decimal)' at line 1
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Douglas Sims wrote:
You can use CAST or CONVERT to see the data as a numeric type.
If the table is very big and you're going to be querying it intensely,
you might want to create a separate column to store the numeric data.
mysql select cast('34' AS decimal);
+---+
| cast('34' AS decimal) |
+---+
| 34.00 |
+---+
1 row in set (0.00 sec)
mysql select cast('hi' AS decimal);
+---+
| cast('hi' AS decimal) |
+---+
| 0.00 |
+---+
1 row in set, 1 warning (0.00 sec)
Douglas Sims
[EMAIL PROTECTED]
On Sep 27, 2006, at 10:24 PM, [EMAIL PROTECTED] wrote:
I am looking for any suggestions to this problem. I have a table with a
varchar field. This field can hold textual or numeric data, but it is
stored in a varchar field so the database sees it all as text.
I need to be able to search and sort this field as if it were numeric.
For example, here is some sample data
2.5
4
2
6
7
6.2
3.4
6
I need to be able query the table to get the rows within a certain
range,
for example, between 4 and 7:
select * from table where field1=4 and field1=7
This doesn't work because the column is not a numeric data type. Is
there
anyway to dynamically cast the data to a numeric format so I can use
MySQL's numeric sorting?
I can't change the field's data type because it also needs to be able to
hold textual data. Thank you for your help.
--Steve Musumeche
CIO, Internet Retail Connection
--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]
Re: making varchar field to act like numeric field
Dan,
Thank you, that works! FYI, you can treat the field like a number,
including sorting, numeric functions, etc.
For example,
select * from table order by (text_field+0.0)
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Dan Nelson wrote:
In the last episode (Sep 28), Steve Musumeche said:
I think this method will work, however, when trying these queries, I get
a SQL syntax error.
mysql select cast('34' AS decimal);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'decimal)' at line 1
The CAST function in mysql 4.1 and older can't cast to DECIMAL. 5.0
and newer can. One easy way to convert strings to numbers in older
mysql's is to add 0 to them:
mysql select (1.10 + 0);
++
| 1.10 + 0 |
++
|1.1 |
++
Re: Distinct select over 2 fields?
Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
You could try using CONCAT: select distinct(CONCAT(lat, long)) from table where ... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Lat lon are two different fields. Either can be duplicated, but not both. On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --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] --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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to find duplicate rows
Select COUNT(*) as num_entries, url from table WHERE num_entries1 GROUP BY url Untested, but the concept should work for you. Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Peter Van Dijck wrote: Hi all, a though query problem for me... I have a table with 2 rows that matter: url and id If url and id are the same in 2 rows, then that's no good (bad data). I need to find all the rows that are duplicates. I can't think of how to approach the sql for this.. any pointers? Thanks! Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table permissions - before the table is created?
If you are manually editing the grant tables, don't forget to FLUSH PRIVILEDGES after you add the new tables. Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Chris Jones wrote: Thanks for that. Will create the two tables ahead of time which won't affect the existing application. At 11:57 30/08/2006, [EMAIL PROTECTED] wrote: Chris Jones wrote: Can the mysql administrator grant permissions on a table in an existing database if that table doesn't exist yet? This is part of planning for an upgrade to an existing application. The mysql administrator won't be there when I add the table to the existing database and I don't have grant privileges. You can't do this using a grant statement because it is bound by existing tables. You may be able to manipulate the mysql db tables directly - I was able to insert a row in mysql.tables_priv for a nonexistent table - but I don't know how this will play out once the table actually is created. Perhaps give it a try on a test db...? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Chris Jones, P.Eng. 14 Oneida Avenue Toronto, ON M5J2E3 Tel. 416 203-7465 Fax. 416 946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlcc / mysql query browser
I use EMS MySQL Manager. You can buy it here: http://www.sqlmanager.net/en/products/mysql/manager/buy No, I am not affiliated with them, but I use the product everyday and I love it. Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Pooly wrote: Hi, MysqlCC not being in developement anymore and not working properly with a server 5.0, we are trying to use MySQL Query browser, but there are few things which are less than efficient compared to mysqlCC. - you can't execute several queries ! The query tab executes them one by one, and if you use a script tab, you don't have any results displayed... quite annoying. - edition of results are a pain, instead of a double-click, one need to click on edit, then double-click on the cell to edit (spacebar would have been quicker) and press enter. and you can't use the arrows to move around cells when you are editing (!). To commit you need to press Apply changes. It would be nice to have be able to edit cells without clicking on Edit and be able to move with the arrows (Apply changes is a good one though). - to open a connection on another server you need to do New instance connection, it would be great to have the same thing than MySQLCC, where you can see others server in the sidebar. It's easier to move around several servers and run query through them, instead of having several separate windows. What are your usual workarounds for these ? Is there any better interface to do some queries ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
