Re: Why does a group_concat on a join change aggregate values?
Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp On Thu, May 7, 2015 at 4:11 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Paul, On 5/7/2015 10:17 AM, Paul Halliday wrote: Fighting a bit with this one... If I do something like (pseudo): SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1 returns something like: n c_types 1 t9 when I add a left join though: SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types, GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id returns something like: val c_types d_types 3t9,t9,t9 a2,a3,a9 I can have as many group_concats against the same table with varying results and they don't affect COUNT() but once I do that JOIN things start to fall apart. What is happening behind the scenes? Thanks! Here's a simple test. Change the query to no longer have the aggregate functions, then start counting rows by hand. SELECT val AS n , types AS c_types , two.types AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id The other thing that springs to mind is that you lack a GROUP BY in your query. It isn't required but they can often help get you to the correct answer. Best regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Paul Halliday http://www.pintumbler.org/
Why does a group_concat on a join change aggregate values?
Fighting a bit with this one... If I do something like (pseudo): SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1 returns something like: n c_types 1 t9 when I add a left join though: SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types, GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id returns something like: val c_types d_types 3t9,t9,t9 a2,a3,a9 I can have as many group_concats against the same table with varying results and they don't affect COUNT() but once I do that JOIN things start to fall apart. What is happening behind the scenes? Thanks! -- Paul Halliday http://www.pintumbler.org/
Help with REGEXP
I am trying to pick out a range of IP addresses using REGEXP but
failing miserably :)
The pattern I want to match is:
10.%.224-239.%.%
The regex I have looks like this:
AND INET_NTOA(src_ip) REGEXP '\d{1,3}\\.\d{1,3}\.(22[4-9]|23[0-9])\\.\d{1,3}'
but, go fish. Thoughts?
Thanks!
--
Paul Halliday
http://www.pintumbler.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Help with REGEXP
I don't think it accepts \d, or much of anything else I am used to
putting in expressions :)
This is what I ended up with and it appears to be working:
REGEXP '10.[[:alnum:]]{1,3}.(22[4-9]|23[0-9]).[[:alnum:]]{1,3}'
On Thu, Mar 19, 2015 at 11:10 AM, Michael Dykman mdyk...@gmail.com wrote:
Trying to pattern match ip addresses is a famous anti-pattern; it's one of
those things like you feel like it should work, but it won't.
Your case, however, is pretty specific. taking advantage of the limited
range (I will assume you only wanted 4 sections of IPv4)
this should come close:
10[.]\d{1,3}[.](224|225|226|227|228|229|23\d))[.]\d{1.3}
On Thu, Mar 19, 2015 at 9:39 AM, Paul Halliday paul.halli...@gmail.com
wrote:
I am trying to pick out a range of IP addresses using REGEXP but
failing miserably :)
The pattern I want to match is:
10.%.224-239.%.%
The regex I have looks like this:
AND INET_NTOA(src_ip) REGEXP
'\d{1,3}\\.\d{1,3}\.(22[4-9]|23[0-9])\\.\d{1,3}'
but, go fish. Thoughts?
Thanks!
--
Paul Halliday
http://www.pintumbler.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
Paul Halliday
http://www.pintumbler.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
InnoDB error 5
Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2013-11-21 08:47:26 1570 [ERROR] InnoDB: Tried to read 16384 bytes at offset 589824. Was only able to read -1. 2013-11-21 08:47:26 802808c00 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2013-11-21 08:47:26 802808c00 InnoDB: File operation call: 'read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? Thanks. -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB error 5
It was indeed corruption :/ what a day. I was able to move everything over to another partition and have managed to get mysql up and running again. There was a single file I could not, an .idb (the ,.frm is there). Is it possible to fix this from ibdata or the logs? Thanks. On Thu, Nov 21, 2013 at 9:46 AM, Manuel Arostegui man...@tuenti.com wrote: 2013/11/21 Reindl Harald h.rei...@thelounge.net Am 21.11.2013 13:51, schrieb Paul Halliday: Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2013-11-21 08:47:26 1570 [ERROR] InnoDB: Tried to read 16384 bytes at offset 589824. Was only able to read -1. 2013-11-21 08:47:26 802808c00 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2013-11-21 08:47:26 802808c00 InnoDB: File operation call: 'read' returned OS error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? i would look in the *system logs* because this pretty sure comes from the underlying operating system and is *not* mysql specific which is also in the message statet with returned OS error 105 http://lxr.free-electrons.com/source/include/uapi/asm-generic/errno-base.h#L8 Looks like a broken disk or FS corruption :-( Good luck! Manuel. -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
I am so, so glad that someone finally said what I think each time I see a message from you Mr. James. Original message From: Rick James rja...@yahoo-inc.com Date: 06-12-2013 8:45 PM (GMT-04:00) To: Daevid Vincent dae...@daevid.com,mysql@lists.mysql.com Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) I'm the ORIGINAL Rick James, B (And, I'm still alive.) LOL If you are using PHP, you might want to stop at 31 bits per INT/SET. PHP seems not to yet be in the 64-bit world. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Wednesday, June 12, 2013 11:26 AM To: mysql@lists.mysql.com Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) This idea is so fancy pants and clever I *wish* it could have worked for me. I checked and we actually have 65 genres currently (with more to come I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some bitmasks and this solution is so elegant. It's unfortunate there isn't a way to use more than 64-bits natively. You're RICK JAMES Bitch! :-p (please tell me you know the Dave Chappelles skit I'm referring to) -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, June 12, 2013 9:39 AM To: Daevid Vincent; mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) Thinking out of the box... (And posting my reply at the 'wrong' end of the email.)... Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED. AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) -- AND genre ((110) | (138)) != 0 AND genre ((122) | (161)) = 0 This would avoid having that extra table, and save a lot of space. If you have more than 64 genres, then Plan A: clump them into some kind of application grouping and use multiple INTs/SETs. Plan B: do mod div arithmetic to compute which genre field to tackle. For B, something like: AND (genre1 (10)) + (genre3 (18)) != 0 AND (genre2 (12)) + (genre6 (11)) = 0 (That's assuming 10 bits per genre# field. I would use 32 and INT UNSIGNED.) -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Tuesday, June 11, 2013 4:17 PM To: mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, On 6/11/2013 3:59 PM, Daevid Vincent wrote: I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10
mysql 5.6.10 won't start
Hi, I'm running mysql 5.6.10 on Fedora. when I try and boot mysql I get this: # /etc/init.d/mysql start Starting MySQL.The server quit without updating PID file (/[FAILED]xt/mysql/veritian.pid). Any ideas? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
query running very slow, need a little help
Hi, I'm running this query: mysql SELECT email FROM promoters where id NOT IN (SELECT promoter_id FROM credits WHERE success = 1 ) and active = 1; Empty set (31.89 sec) its returning an empty set and take over 30 seconds to return. mysql describe promoters; +---+--+--+-+---++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---++ | id| int(11) unsigned | NO | PRI | NULL | auto_increment | | company_name | varchar(40) | YES | | NULL || | first_name| varchar(40) | YES | | NULL || | last_name | varchar(40) | YES | | NULL || | address | varchar(40) | YES | | NULL || | zip | varchar(10) | YES | | NULL || | city | varchar(40) | YES | | NULL || | country | varchar(40) | YES | | NULL || | phone | varchar(20) | YES | | NULL || | email | varchar(100) | YES | UNI | NULL || | website | varchar(100) | YES | | NULL || | payments_id | varchar(10) | YES | MUL | NULL || | password | varchar(100) | YES | | NULL || | active| tinyint(1) | YES | MUL | NULL || | activation_key| varchar(50) | YES | | NULL || | new_email | varchar(100) | YES | | NULL || | new_email_activation_key | varchar(50) | YES | | NULL || | registered| timestamp| YES | | CURRENT_TIMESTAMP || | referral | int(10) unsigned | YES | | NULL || | whitelabel_beginner_modus | tinyint(1) | YES | | 1 || +---+--+--+-+---++ 20 rows in set (0.00 sec) mysql describe credits; ++-+--+-+---++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+---++ | id | int(11) unsigned| NO | PRI | NULL | auto_increment | | type | tinyint(1) unsigned | NO | | NULL || | credits| int(11) | YES | | NULL || | success| tinyint(1) | YES | MUL | NULL || | profit | float | NO | | NULL || | price | float | NO | | NULL || | date | timestamp | NO | MUL | CURRENT_TIMESTAMP || | user_id| int(11) unsigned| NO | | NULL || | promoter_id| int(10) unsigned| YES | MUL | NULL || | referrer | varchar(10) | YES | | NULL || | domain_id | int(11) unsigned| NO | | NULL || | string | varchar(100)| YES | | NULL || | client_info| varchar(200)| YES | | NULL || | promoter_paid | tinyint(1) | YES | | NULL || | status | tinyint(4) | YES | | NULL || | seconds| int(11) | YES | | NULL || | transaction_id | varchar(16) | YES | | NULL || ++-+--+-+---++ 17 rows in set (0.00 sec) Any ideas as to why the wuery is taking so long?? With kind regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Basic SELECT help
U can remove the type field it will work
On Nov 22, 2012 8:21 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.com
wrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Hi,
I'm struggling with what I think is a basic select but can't think how
to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2
and
5
Any ideas ?
Neil
Re: Query Optimization
SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN'); query will make it
faster, if the field is ENUM
On Fri, Nov 16, 2012 at 12:36 AM, Anupam Karmarkar
sb_akarmar...@yahoo.comwrote:
Hi All,
Consider a scenario, I have table XYZ which contains value follow
BLUE
RED
GREEN
NULL
following are queries we can use get this values
1. SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN');
2. SELECT * FROM XYZ WHERE VAL IS NOT NULL
3. SELECT * FROM XYZ WHERE VAL = 'BLUE' OR VAL='RED' OR VAL='GREEN'
and more
So which one is good in terms of optimization. I guess, 1 and 3 are
similar in term of formation.
--Anupam
--
Thanks Regards,
P.Benaya Paul
http://www.codeasearch.com
http://www.iwannasearch.com
Re: query help
On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete halukkaram...@gmail.com wrote: I've got a text field called source_recid. It stores half string half number like strings in it. Example shop.orders.32442 the syntax is DATABASENAME.TABLENAME.RECID My goal is to scan this col and find out the biggest RECID ( the integer) in it. So, in a case like this shop.orders.32442 shop.orders.82000 shop.orders.34442 It would be the record whose source_recid is shop.orders.82000. Why? Cause 82000 happens to be the largest integer. What SQL statement would get me that record? One option to this is to create a new column ( the_ids ) and move all the integers in it and then run something like this select source_recid from mytable where source_recid like 'shop.orders.%' order by the_ids DESC LIMIT 1 Is there a way to pull this off without going thru this step? Would substring work? SELECT SUBSTRING(recid, 13, column size) AS numbers FROM table ORDER BY numbers DESC -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Licensing question about mysql_com.h
If you own the code, you can license it under multiple licenses. Kind of like if you own a TV Show, you can license it in the US under one contract, and in other geographies under other more or less restrictive contracts. This is a painful reality to those of us in Canada, as we can't watch South Park clips online. :P On Tue, Apr 10, 2012 at 4:53 AM, James Ots my...@jamesots.com wrote: In their blog post, announcing the sharing of their work, they mention licensing it under BSD, but in the repository the COPYING file still contains the GPLv2 licence, so I'm not sure what's going on there. On 10 April 2012 02:32, Andrew Moore eroomy...@gmail.com wrote: So what's the deal with Twitter's mysql code...how can it be BSD licensed? I'm a bit unsure about the intricacies of licensing. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Discover the latest MySQL tips and tricks from Pythian’s top talent at this year’s MySQL Conference April 10-12. Details at pythian.com/news
Re: Group expansion as part of the result
On Tue, Mar 27, 2012 at 3:43 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Mar 27), Paul Halliday said: Say I have: SELECT COUNT(name), name, COUNT(DISTINCT(status)) FROM table GROUP BY name and it returns: 20 paul 5 19 john 2 75 mark 3 is there a way to return what comprises DISTINCT(status) as part of the result? so: 20 paul 2,3,1,20,9 19 john 20,9 75 mark 1,20,9 You want GROUP_CONCAT: Heh, and exactly how I wanted it formatted. Great! Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How do I use a dynamic filename for an into outfile statement
On Feb 29, 2012, at 10:43 AM, Ed Patterson wrote:
Be kind, I am by no means any type of DB expert.
I would like to eventually move this to a stored procedure but for now I am
using the \. to fire it off.
Here is what I have so far (generating a list of machines missing software)
select last_logon.host_name_short
from last_logon
left join mcafee on last_logon.host_name_short = last_logon.host_name_short
where mcafee.host_name_short is null
and last_logon.host_name_short like 'w%'
-- the above works
into outfile (select concat('Missing-',date_format(now(),'%Y%m%d%H%i'),'txt');
-- this line breaks it
The select concat() works from the command line
I can manually add the file name but would like to automate the process
Lastly, if it makes a difference, I don't use any graphical tools for DB
stuff. Inevitably someone says 'click here' :-)
Thanks for any help
Ed
-- create statement, assign to user variable
set @s = concat('
select last_logon.host_name_short
from last_logon
left join mcafee on last_logon.host_name_short = last_logon.host_name_short
where mcafee.host_name_short is null
and last_logon.host_name_short like \'w%\'
into outfile \'Missing-',date_format(now(),'%Y%m%d%H%i'),'.txt\'');
-- display so you can verify what it looks like
select @s;
-- prepare statement, execute it, discard it
prepare s from @s;
execute s;
deallocate prepare s;
Note: I added a '.' before 'txt' and a closing quote to the file name.
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html
--
Paul DuBois
Oracle Corporation / 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
Re: does the number of column affect performance
On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / 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
Delete from another table on update.
Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host=b1 (DELETE FROM another_table WHERE host=b1) ? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete all hosts using a wildcard
On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: hello list, I have a number of hosts that I would like to delete using a wildcard (%) symbol. Here is the query I am using: mysql delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html Query OK, 0 rows affected (0.00 sec) And I am attempting to delete all the hosts at the domain 'summitnjhome.com'... But as you can see I am unsuccessful: mysql select user,host from mysql.user; +--+-+ | user | host| +--+-+ | root | 127.0.0.1 | | repl | virtcent10.summitnjhome.com | | admin| virtcent11.summitnjhome.com | | repl | virtcent19.summitnjhome.com | | repl | virtcent23.summitnjhome.com | | repl | virtcent30.summitnjhome.com | +--+-+ I know I can delete them individually and this is what I am going to do. But I would like to use this as a learning opportunity to help me understand how the wildcard works. Thanks in advance.. Best regards, Tim -- Paul DuBois Oracle Corporation / 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
Re: hide server-version at connect?
On Jan 9, 2012, at 7:27 PM, Reindl Harald wrote: Nessus/OpenVAS Test detects the exact server version _ NVT: MySQL Detection (OID: 1.3.6.1.4.1.25623.1.0.100152) Overview: MySQL, a open source database system is running at this host. MySQL Version '5.5.19-log' was detected on the remote host. _ is there any way to not disclosure the mysqld-version for a anonymous connected client? For the case you give below, no authentication has yet taken place, so you don't know whether the client is anonymous or not. But the version is needed for proper client-server negotiation to take place, I believe. Even if that were not true, any client, anonymous or not, can use SELECT @@version or SELECT VERSION() to get the version. [harry@srv-rhsoft:~]$ telnet localhost 3306 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. N 5.5.19-logs+%b?QYO]g��ke8'Xg~e\}!(mysql_native_password -- Paul DuBois Oracle Corporation / 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
Re: Date and Time
On Jan 8, 2012, at 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? In DATE, DATETIME, and TIMESTAMP columns, you must specify the date part on year-month-day order. If you want to store a value in a different format, you must use some other data type such as VARCHAR. But then it won't be interpreted as a date. If you want to display a date from a DATE, etc. column in some other format, pass the value to DATE_FORMAT(). http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format If you want to reformat a date value in some other format to put it in year-month-day format so that you can store it in a DATE, etc. column, STR_TO_DATE() might be helpful. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE() can be useful, for example, when loading non year-month-day data into a table with LOAD DATA. You can use STR_TO_DATE() to reformat the values on the fly. LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t (name,@date,value) SET date = STR_TO_DATE(@date,'%m/%d/%y'); -- Paul DuBois Oracle Corporation / 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
Re: 'myisam_use_mmap' unstable like hell
On Dec 15, 2011, at 12:02 PM, Reindl Harald wrote: this is NOT a memory issue 'myisam_use_mmap' in mysqld is buggy since a long time http://bugs.mysql.com/bug.php?id=48726 This is fixed in 5.1.61, 5.5.20, 5.6.5: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html we are speaking of a HP ProLiant DL 380G7 in a VMware-Cluster with 36 GB ECC-RAM while there are machines using InnoDB with 'large-pages' and some GB buffer_pool_size on the same host and not about some customer hardware Am 15.12.2011 18:22, schrieb Andrés Tello: When I had memory issues, with something relatively stable, mostly is due faulty ram... Can you use or less ram or change fisically the ram? On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 15.12.2011 08:47, schrieb Rob Wultsch: To be brutally honest, if you want stability you should not be using MyISAM this is bullshit without 'myisam_use_mmap' i never saw mysqld crashing in the past 10 years, independent of the storage engine much less a not particularly commonly used feature. mmap is not rocket science, so i do not understnd why this is not properly debugged and DEFAULT on -- Paul DuBois Oracle Corporation / 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
how to optimize mysql in easy way
how to optimize mysql in easy way step, i know indexing, mapping other than that is any way. -- Thanks Regards, P.Benaya Paul http://www.codeasearch.com http://www.iwannasearch.com
Inconsistent query result.
I have the following query: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 5000 The part that is causing the strange result is probably this: AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') the value could be 'US' or 'UNITED STATES' depending on user input so I check both table fields against their input. On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux 5.1.54), 'US' appears in the results. Is there a better way to write this? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Inconsistent query result.
On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 10/11/2011 8:11 AM, Paul Halliday wrote: I have the following query: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 5000 The part that is causing the strange result is probably this: AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') the value could be 'US' or 'UNITED STATES' depending on user input so I check both table fields against their input. On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux 5.1.54), 'US' appears in the results. Is there a better way to write this? Is this what you mean? SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), map1. cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' AND map1.c_long != 'United States' LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' AND map2.c_long != 'United States' WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59' GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 5000 PB Is it OK to keep adding to those joins? In a view there could be say 50 countries. The user can keep on adding more to exclude. So would I just expand on the ANDs like so: AND map1.cc !='CA' AND map1.cc !='US' AND map1.cc !='LV'... ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: error log rotation problem
On Aug 11, 2011, at 2:30 PM, Keith Murphy wrote: Hey everyone, I have run across something that has me stumped. I have some systems that have very large error logs because we haven't moved from statement-based to mixed-based replication yet so they get a lot of warnings logged. I need to rotate the error logs and have started looking at it doing so. The problem is that on one system a normal course of action works perfectly, but on anther it does not. And these systems were installed from the same RPM packages (5.1.50 -- downloaded from the MySQL website). Here is what I do: log in with mysql client and 'flush logs' OR mysqladmin --flush-log It should rename the old log file to mysqld.log-old and start a new mysqld.log file. On one system it works perfectly On the other...nothing. I tried moving the error log (mv /var/log/mysqld/mysqld.log /var/log/mysqld.log.old) and then issuing the flush logs command...it stays writing to the old file and never makes a new one. If I were to restart mysqld it would solve the problem but this is a production system and that isn't very practical. These systems are very similar. my.cnfs have been checked for differences. I searched the interwebs and specifically bugs.mysql.com for something similar. Not finding anything. I would appreciate any ideas! There was a change to log flushing that affects the error log in 5.1.51/5.5.7. It might be the cause of what you're seeing. http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html Incompatible Change: Previously, if you flushed the logs using FLUSH LOGS or mysqladmin flush-logs andmysqld was writing the error log to a file (for example, if it was started with the --log-error option), it renamed the current log file with the suffix -old, then created a new empty log file. This had the problem that a second log-flushing operation thus caused the original error log file to be lost unless you saved it under a different name. For example, you could use the following commands to save the file: shell mysqladmin flush-logs shell mv host_name.err-old backup-directory To avoid the preceding file-loss problem, renaming no longer occurs. The server merely closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell mv host_name.err host_name.err-old shell mysqladmin flush-logs shell mv host_name.err-old backup-directory (Bug #29751) See also Bug #56821. -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
Substring confusion.
Does anyone know why this happens: mysql SELECT COUNT(*) AS count, SUBSTRING(timestamp,12,5) AS time FROM event WHERE timestamp BETWEEN '2011-07-15 03:00:00' AND '2011-07-16 02:59:59' GROUP BY time ORDER BY time limit 4; +---+---+ | count | time | +---+---+ | 5 | 03:00 | | 2 | 03:01 | | 2 | 03:02 | | 5 | 03:03 | +---+---+ mysql SELECT COUNT(*) AS count, SUBSTRING(timestamp,12,5) AS time FROM event WHERE timestamp BETWEEN '2011-07-14 03:00:00' AND '2011-07-15 02:59:59' GROUP BY time ORDER BY time limit 4; +---+---+ | count | time | +---+---+ | 8 | 00:00 | | 4 | 00:01 | | 3 | 00:02 | | 1 | 00:03 | +---+---+ First one returns what I expect, when I do it on a day in the past, it always starts at 00:00 instead of 03:00. If I change it a bit though, I get what I expect: mysql SELECT COUNT(*) AS count, SUBSTRING(timestamp,10,7) AS time FROM event WHERE timestamp BETWEEN '2011-07-14 03:00:00' AND '2011-07-15 02:59:59' GROUP BY time ORDER BY time limit 4; +---+-+ | count | time| +---+-+ | 4 | 4 03:00 | | 6 | 4 03:01 | | 1 | 4 03:02 | | 2 | 4 03:03 | +---+-+ Why is this? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help making tablespaces work for my application
I have an application for which data is being written to many disks simultaneously. I would like to use a MySQL table space on each disk. If one of the disks crashes it is tolerable to lose that data, however, I must continue to write to the other disks.My specific concerns are around the redo/undo log. It is my understanding that there is a separate undo log for each table space and a single redo log for the CLUSTER of all of the table spaces. Is that correct? 1.What happens if a disk containing a table space ( but not the redo log) crashes. Will MySql continue writing to the other disks. Will the redo log be corrupted at this point or would it continue to be valid with the understanding that there is no more data being added for the crashed disk. Could the redo log be used to restore the data on the crashed disk to the point of the crash? 2.What happens if the disk containing the signal redo log crashes. Does that halt the system until the disk is returned. Is there any way of allowing the system to run without a redo log in this situation? Is there any way to distribute the redo logs to the location of the tablespaces? If I were to put the redo log on a raid disk, I assume that would allow the system to continue running in these scenarios. I would prefer a solution that does not add this cost to the system. Thanks for your help Paul
Optimize query help.
Dear all, I have a query that takes a rather long time and was wondering if there is anyway to optimize it. Normally we removing duplicate records by phone number. This query takes about a second and it really slows down the process when we are importing several 1000 records a day. Here is the query: SELECT count(id) c FROM leads WHERE (phone_home = '(770) 512-8990' OR phone_work = '(770) 512-8990' OR phone_other = '(770) 512-8990' OR phone_mobile = '(770) 512-8990' OR phone_fax = '(770) 512-8990') AND date_entered DATE_SUB(NOW(),INTERVAL 45 DAY) AND deleted != '1'; This is the describe: DESCRIBE SELECT count( id ) c FROM leads WHERE ( phone_home = '(770) 512-8990' OR phone_work = '(770) 512-8990' OR phone_other = '(770) 512-8990' OR phone_mobile = '(770) 512-8990' OR phone_fax = '(770) 512-8990') AND date_entered DATE_SUB( NOW( ) , INTERVAL 45 DAY ) AND deleted != '1' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE leads ALL idx_del_user,phone_home,phone_mobile,phone_work,phone_other,phone_fax,date_entered NULL NULL NULL 636433 Using where Any thoughts? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Design question.
I maintain a little open source project that deals with IDS alert data. I want to add IP reputation to my event queries and I am stuck on how I should implement it. The user will have the option of bringing in lists from different providers and the limit will not be fixed. These lists will be a single column of IP addresses. list 1: IP listing list 2: IP listing list 3: IP listing ... There can, and most likely will be duplication of addresses across the different lists. The number of lists that a host is a member of will be an indication of its reputation. The desired result will be something like: event count | event signature | src ip | country | ip reputation | dst ip | country | ip reputation The lists will be updated once each day or on demand. I already have a mappings table that provides country information for ip's in the event table which is joined during the event queries. The mappings table contains a little under 500,000 addresses and grows slowly - say 50 to 100 addresses / day. As new ip's appear in the event table, they are mapped to a country. Questions: 1) Should I just create a new table for every list the user adds and then do joins on these? 2) Should I put the lists in 1 table somehow? 3) As the lists are done daily, should I just run a midnight task that parses each list and adds the information to the mappings table. I have no idea what the format would look like. I was thinking of creating a varchar and have something like: list1|list2|list9|list20 and then just breaking it out in the code. The entire table would of course need to be scanned each day to check whether or not an address had been taken off a list. (efficiency?) Any comments/suggestions would be greatly appreciated. Thanks. -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with query.
I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_ip, src_cc, dst_ip, dst_cc ORDER BY src_cc, dst_cc ASC; This would return something like this: +---+---++---++ | count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc | +---+---++---++ | 8 | 10.0.0.8 | NULL | 61.55.142.129 | CN | | 1 | 210.52.216.92 | CN | 10.0.0.2 | NULL | | 1 | 121.33.205.235| CN | 172.16.0.6| NULL | | 239 | 210.52.216.92 | CN | 10.0.0.2| NULL | | 2 | 121.33.205.235| CN | 172.16.0.15 | NULL | | 4 | 121.33.205.235| CN | 10.0.0.1| NULL | |39 | 210.52.216.92 | CN | 172.16.0.15 | NULL | | 1 | 121.33.205.235| CN | 172.16.0.14 | NULL | +---+---++---++ All I am interested in is the event count for each country, in this case: 295 CN ... Other countries.. I can do this in code, more work of course, but I am just curious if I can pull it off with a single query. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lowest non-zero number
On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge m...@good-stuff.co.uk wrote: Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? SELECT number FROM table WHERE number 0 ORDER BY number ASC LIMIT 1; ? -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error Unknown column in 'where clause'
Some discussion of causes for this is at: http://dev.mysql.com/doc/refman/5.0/en/join.html Look for the part beginning: Join Processing Changes in MySQL 5.0.12 On Nov 16, 2010, at 10:09 AM, Tompkins Neil wrote: Hi I've the following query : SELECT players_bids.players_bids_id, players_bids.players_id, players_bids.bid_date, players_bids.bid_type, players_bids.bid_value, (SELECT SUM(IF(home_users_id = players_bids.users_id_from,home_manager_points,away_manager_points)) FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from OR away_users_id = players_bids.users_id_from) AND worlds_id = 1) AS manager_points, players_bids.users_id_from, (SELECT ROUND((SUM(won_home)+SUM(won_away))/COUNT(*)*100,0) FROM (SELECT IF(home_goals away_goals, 1, 0) AS won_home ,0 AS won_away FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from) AND status = 'approved' UNION ALL SELECT 0 AS won_home ,IF(away_goals home_goals, 1, 0) as won_away FROM fixtures_results WHERE away_users_id = players_bids.users_id_from AND status = 'approved') s1) AS wins, players_bids.users_id_to, players_bids.worlds_id, players_bids.seasons_id, players_master.first_name, players_master.second_name, players_master.known_as, players_master.estimated_value, players_master.rating, players_master.positions_id, players.games_played, players_bids.teams_id_from, players_bids.teams_id_to, teams_master_from.team_name AS team_name_from, teams_master_to.team_name AS team_name_to FROM players_bids INNER JOIN players ON players_bids.players_id = players.players_id INNER JOIN players_master ON players_bids.players_id = players_master.players_id INNER JOIN teams_master teams_master_from ON players_bids.teams_id_from = teams_master_from.teams_id INNER JOIN teams_master teams_master_to ON players_bids.teams_id_to = teams_master_to.teams_id WHERE players_bids.bid_status = 'accepted' AND players_bids.players_id = 279 AND players_bids.worlds_id = 1 ORDER BY players_bids.bid_type ASC, players_bids.bid_value DESC, manager_points DESC, players_bids.bid_date ASC but the problem I have is that when referencing players_bids.users_id_from within the UNION I get back the following error : Error Code: 1054 Unknown column 'players_bids.users_id_from' in 'where clause' Any ideas how to overcome this problem ? Cheers Neil -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
Slave using SSL?
Hey can anyone tell me is my slave is utilizing SSL? I am unsure why the Master SSL Cipher is not listed under show slave status. Here's some info: mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: host Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.38 Read_Master_Log_Pos: 74709312 Relay_Log_File: slave-relay.000577 Relay_Log_Pos: 8579562 Relay_Master_Log_File: mysql-bin.38 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: crm Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 74709312 Relay_Log_Space: 8579562 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem Master_SSL_CA_Path: /etc/mysql/ssl/ Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/client-key.pem Seconds_Behind_Master: 0 1 row in set (0.00 sec) mysql SHOW VARIABLES LIKE 'have_ssl'; +---+---+ | Variable_name | Value | +---+---+ | have_ssl | YES | +---+---+ 1 row in set (0.01 sec) mysql SHOW STATUS LIKE 'Ssl_cipher'; +---++ | Variable_name | Value | +---++ | Ssl_cipher| DHE-RSA-AES256-SHA | +---++ 1 row in set (0.00 sec) Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL replication SSL
Dear All, I'm trying to get SSL connections for all mysql slave and masters. I have one box that will not use SSL for some reason. When I start this slave it can not connect because it's not using SSL. show slave status\G *** 1. row *** Slave_IO_State: Connecting to master Master_Host: myhost Master_User: rep_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.95 Read_Master_Log_Pos: 1095 Relay_Log_File: slave-relay.04 Relay_Log_Pos: 98 Relay_Master_Log_File: mysql-bin.95 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: crm Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1095 Relay_Log_Space: 98 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 Here is a portion of my.cnf. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 max_connections=500 max_connect_errors = 0 # replication server-id = 1 replicate-same-server-id = 0 auto-increment-increment =3 auto-increment-offset =1 master-host = myHost master-user = rep_user master-password = rep_passwd master-connect-retry = 60 replicate-do-db = crm log-bin= /var/log/mysql/mysql-bin.log binlog-do-db = crm relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M # end replication # SSL for replication ssl ssl-key=/etc/mysql/ssl/server-key.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-capath=/etc/mysql/ssl/ ssl-cipher=DHE-RSA-AES256-SHA [client] ssl port = 3306 socket = /var/lib/mysql/mysql.sock ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-cipher=DHE-RSA-AES256-SHA Anyone see any issues with this? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing results.
Presumably those records were absorbed into your 'group by' clause, since there was an entry, from a later time, which had the same values for all the group by fields. -- Simcha Younger sim...@syounger.com Geez, how obvious. I was thinking on a completely different plane. I feel pretty silly now. :) Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Verify replication slave is using SSL
Dear All, I just want to verify my rep slave is using ssl. When I run this command mysql \s I get: mysql \s -- mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 5 Current database: Current user: r...@localhost SSL:Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.0.77-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 9 min 48 sec And I can see the Cipher. But when I run a show slave status I don't see a cipher key: mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: my.sql.host Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.36 Read_Master_Log_Pos: 186677 Relay_Log_File: slave-relay.74 Relay_Log_Pos: 186814 Relay_Master_Log_File: mysql-bin.36 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: sugarcrm Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 186677 Relay_Log_Space: 186814 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem Master_SSL_CA_Path: /etc/mysql/ssl/ Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/client-key.pem Seconds_Behind_Master: 0 1 row in set (0.00 sec) Can any tell me if SSL is functioning or not please? Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL book recommendation?
On Oct 26, 2010, at 6:31 AM, Philip Riebold wrote: On 26 Oct 2010, at 11:49, MikeB wrote: I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? Thanks. The book I've been using is 'MySQL, The definitive guide to using, programming, and administering MySQL 4.1 and 5.0' ISBN 0-672-32673-6 (there may be a more recent version). If that's my book, it sounds like the third edition. The fourth edition is more recent. http://www.kitebird.com/mysql-book/ Well written, with a general introduction to SQL and (from my POV) very good sections on writing MySQL with C and PHP -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
Re: Can this query be done w/o adding another column?
After bashing at this for a while with no luck I replaced the inner with left and I got the desired result. Thanks for the help. On Tue, Oct 12, 2010 at 5:18 PM, Travis Ard travis_...@hotmail.com wrote: Sorry, try changing the column mappings.ip to use the table aliases (m.ip and m2.ip). -Travis From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 11:37 AM To: Travis Ard Cc: mysql@lists.mysql.com Subject: Re: Can this query be done w/o adding another column? On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause' -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
Can this be done with a single query?
I have 2 tables: events and mappings. what I want to do is something like: SELECT DISTINCT(src_ip) FROM event WHERE timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%' AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%' AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%'; but, within that somewhere also check to see if src_ip exists in mappings. If it does, do not return it. Is this possible? Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
Re: Can this be done with a single query?
On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers joh...@pixelated.netwrote:
I would try:
SELECT DISTINCT(e.src_ip)
FROM event AS e
LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%'
AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%'
AND INET_NTOA(e.src_ip) NOT LIKE '192.168.%.%'
AND m.src_ip IS NULL
;
I would also modify the where clause to use:
AND src_ip NOT BETWEEN INET_ATON('10.0.0.0') AND INET_ATON(10.255.255.255)
AND src_ip NOT BETWEEN INET_ATON('172.16.0.0') AND
INET_ATON(172.16.255.255)
AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND
INET_ATON(192.168.255.255)
instead of
AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%'
AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%'
AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%
You should also ensure there is an index on src_ip in events and mappings
tables.
Using the INET_NTOA() function on the src_ip column will prevent index
usage during the query.
This and the suggestion by Nathan both work.
Thanks for the help!
Can this query be done w/o adding another column?
Geez, really taking advantage of the list today :). This one is a little more complicated, well, in my head anyway. Same tables as before, event and mappings. Mappings is just IP to Country info. I want to be able to join both a src and dst but the problem is the mappings table just has one ip column. My initial query looks like this: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), INET_NTOA(dst_ip), signature, signature_id, ip_proto FROM event WHERE timestamp BETWEEN 2010-10-12 00:00:00 AND 2010-10-13 00:00:00 GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC; Which would return something like this: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | 173.193.202.69 | ET P2P Vuze BT UDP Connection | 2010144 | 17 Now I want to add the country info into the mix. I have made it this far: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), mappings.cc, INET_NTOA(dst_ip), mappings.cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings ON event.src_ip = mappings.ip OR event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC LIMIT 10; gives me: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | US | 173.193.202.69 | US | ET P2P Vuze BT UDP Connection | 2010144 | 17 which obviously isn't right ;) but is close. I was just going to change the columns in the mappings table to have src_ip and dst_ip just duplicating the ip column but I have a nagging feeling that that probably isn't necessary. Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
Re: Can this query be done w/o adding another column?
On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause'
Is conversion required?
I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of course; I missed the memo. I have been struggling to get everything back online. I just finished exporting a few Gigs of RRD's to XML so that I could use them :| My question: I was s/rushing/stupid so I just moved /var/mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? Thanks! -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
Re: Is conversion required?
On Sep 29, 2010, at 5:15 PM, Paul Halliday wrote: I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of course; I missed the memo. I have been struggling to get everything back online. I just finished exporting a few Gigs of RRD's to XML so that I could use them :| My question: I was s/rushing/stupid so I just moved /var/mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? If you're talking about the data, I wouldn't expect this change to cause issues, unless perhaps you're also updating to a different version of MySQL. That could be a problem, depending on how different the old and new versions are. Consult the upgrading section of the manual to see. http://dev.mysql.com/doc/refman/5.1/en/upgrading.html -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
Re: SHA1 returns binary value
On Sep 17, 2010, at 7:08 AM, Tompkins Neil wrote:
Hi
Why when I run the command (MySQL 5.1) SELECT SHA1('abc'); is it returned as
a binary value and not a string value ?
If you mean, why is it a binary rather than a nonbinary (character) string,
that's true of most of the encryption or compression functions in 5.1. This was
changed
in 5.5. http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html says:
Many encryption and compression functions return strings for which the result
might contain arbitrary byte values. If you want to store these results, use a
column with a VARBINARY or BLOB binary string data type. This will avoid
potential problems with trailing space removal or character set conversion that
would change data values, such as may occur if you use a nonbinary string data
type (CHAR,VARCHAR, TEXT).
Some encryption functions return strings of ASCII characters: MD5(),
OLD_PASSWORD(), PASSWORD(), SHA(),SHA1(). As of MySQL 5.5.3, their return value
is a nonbinary string that has a character set and collation determined by the
character_set_connection and collation_connection system variables. Before
5.5.3, these functions return binary strings. The same change was made for
SHA2() in MySQL 5.5.6.
--
Paul DuBois
Oracle Corporation / 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=arch...@jab.org
Re: Sequence of Execution in Non-Transactional Script
On Aug 30, 2010, at 12:09 PM, real...@areality.co.uk wrote: Hello, I'm working on a very simple stock tracking system on unix with MySQL 5 and PHP. Working under the assumption that all my updates would be executed sequentially in the order they appear in the PHP script I perform these operations: $sql=UPDATE shop_product SET working_stocklevel=working_stocklevel+.$_SESSION['prod'][$product_id]. WHERE id=$product_id; $result = mysql_query ($sql) or die(Error=.mysql_error().BR.$sql.br); Then I record this activity into a seperate journal table using a function call Then $sql=UPDATE shop_product SET working_stocklevel=working_stocklevel-.$newamount. WHERE id=$product_id; $result = mysql_query ($sql) or die(Error=.mysql_error().BR.$sql.br); My journal records that occasionally the second command is executed before the first - throwing all the figures out. Is this possible? My tables are MyISAM, indexed on ID and not particularly large (about 3000 rows). I knopw this probably needs to be transactional, but I thought updates would auto-commit, and as such be sequential. Can anyone give any advice? If the two UPDATE statements are issued in sequence within the same session (within the same connection), they should execute in the same order you issue them. Particularly since you are waiting for the result of the first before you proceed to the second. I suggest that when you observe an out of order entry in your journal, you compare it with the MySQL server's general query log or binary log. The general query log will show the order in which the server receives the statements. The binary log will show the order in which they finish executing. -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
Re: Importing User credentials from mysql.sql file???
On Aug 2, 2010, at 3:57 PM, Nunzio Daveri wrote: Hello Gurus, I just upgraded several MySQL 4.1 to 5.1 versions and also wanted to know how to extract the user name, password and credentials from the mysql.sql file (around 22 of them per server - have 8 servers total)? The contract admin emailed me a sql file which is a dump of the default mysql database from the 4.1 version and I am trying to see if I can just grep out of the mysql.sql file the INSERT INTO... from the .sql file and import that into the 5.1. When I tried it on our test box it keeps on saying: ERROR 1136 - Column count doesn't match value count at row 2? Can someone please tell me how I can extract the data out of the 4.1 mysql.sql file and inject it into the 5.1 version please? Ask the admin to re-dump the data, this time using mysqldump --complete-insert so that the INSERT statements include the column names. -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
[ANN] PBXT 1.5.02 Beta Released!
Hi All, I have just released PBXT 1.5.02 Beta. This version adds a 2nd level (SSD based) cache! For more information on the 2nd Level Cache, please check out my blog: http://pbxt.blogspot.com/2010/07/pbxt-1502-beta-adds-2nd-level-cache.html The new version is available for download from: http://primebase.org/download The documentation (http://primebase.org/documentation) has been updated to cover versions 1.0, 1.1 and 1.5. PBXT is an ACID compliant, transactional storage engine for MySQL. It features MVCC (multi-version concurrency control), row-level locking, referential integrity and has a log-based architecture. PBXT is an open source project, licensed under GPL 2.0. Development is done on Launchpad: https://launchpad.net/pbxt. The current stable release version of PBXT is 1.0.11 Pre-GA, which can be downloaded primebase.org or from Launchpad. Bugs can be reported here: https://bugs.launchpad.net/pbxt. If you have any questions or comments, please let me know. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [ANN] PBXT 1.5.02 Beta Released!
Hi Johan, I understand what you are saying, but this is certainly not the announcement of a commercial product. It is also not spam, because the announcement of the release of a open source MySQL Storage Engine is relevant to this list. Nevertheless, I prefix my e-mails with [ANN] (for announcement), so that you can filter it out if you don't want to see it. Best regards, Paul On Jul 16, 2010, at 4:41 PM, Johan De Meersman wrote: Can I somehow unsubscribe (that is opt-out , as opposed to the more civilised opt-in) from the myriad of commercial announcements on this list, or do I just have to spambrand all senders ? Some weeks it feels as if there's more productspamming than posting going on. On Fri, Jul 16, 2010 at 4:25 PM, Paul McCullagh paul.mccull...@online.de wrote: Hi All, I have just released PBXT 1.5.02 Beta. This version adds a 2nd level (SSD based) cache! For more information on the 2nd Level Cache, please check out my blog: http://pbxt.blogspot.com/2010/07/pbxt-1502-beta-adds-2nd-level-cache.html The new version is available for download from: http://primebase.org/download The documentation (http://primebase.org/documentation) has been updated to cover versions 1.0, 1.1 and 1.5. PBXT is an ACID compliant, transactional storage engine for MySQL. It features MVCC (multi-version concurrency control), row-level locking, referential integrity and has a log-based architecture. PBXT is an open source project, licensed under GPL 2.0. Development is done on Launchpad: https://launchpad.net/pbxt. The current stable release version of PBXT is 1.0.11 Pre-GA, which can be downloaded primebase.org or from Launchpad. Bugs can be reported here: https://bugs.launchpad.net/pbxt. If you have any questions or comments, please let me know. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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=arch...@jab.org
Re: FLUSH LOCAL LOGS
On Jun 9, 2010, at 11:59 AM, Darvin Denmian wrote: Hello ! Is there some diference between : FLUSH LOCAL LOGS and FLUSH LOGS ? Yes. http://dev.mysql.com/doc/refman/5.1/en/flush.html says: By default, FLUSH statements are written to the binary log so that they will be replicated to replication slaves. Logging can be suppressed with the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL. Note FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not written to the binary log in any case because they would cause problems if replicated to a slave. -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
Re: Why UTF8 need 24bit in MySQL?
On Jun 7, 2010, at 11:44 AM, Warren Young wrote: On 6/7/2010 9:57 AM, Ryan Chan wrote: http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html Since MySQL only support BMP, so in fact 16 bit is needed actually? I imagine they were thinking they'd extend the support to full Unicode in the future and didn't want you to have to dump and reload your databases when that happened. The Unicode consortium has stated that Unicode will never require more than 21 bits per character[*], and 24 bits is the next even multiple of 8 up from that. [*] Why 21? Because that's the maximum number of bits you can express in 4 bytes with UTF-8 encoding. If Unicode were allowed to use all 2^32 code points as originally envisioned, it would require up to 6 bytes per character in UTF-8 encoding. This promise makes UTF-8 code easier to write and easier to future-proof without bad performance penalties. Supplemental Unicode characters (4-byte) are supported as of MySQL 5.5.3: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode.html http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html -- Paul DuBois Oracle Corporation / 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=arch...@jab.org
mysqldump backup
Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
Re: mysqldump backup
I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. On Fri, May 28, 2010 at 8:06 AM, Anand.S anand@gmail.com wrote: redirect your standard errors to some log file.. mysqldump --all-databases --flush-logs --master-data=2 /bk01/all_databases_`date +%a`.sql 2 /tmp/test.log i follow the above syntax for logging the errors in my script. Thanks Anand On Fri, May 28, 2010 at 5:44 PM, Angelina Paul arshup...@gmail.comwrote: Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
[ANN] PBXT 1.0.11 Pre-GA Released!
Hi All, I have just released PBXT 1.0.11, which I have titled Pre-GA. Going by our internal tests, and all instances of PBXT in production and testing by the community this is a GA version! However, although PBXT has 1000's of instances in production, it is not used in very diverse applications. So I am waiting for wider testing and usage before removing the Pre prefix. You can download the source code from http://primebase.org, or pull it straight from https://launchpad.net/pbxt. Here are instructions how to compile and build the engine with MySQL: http://primebase.org/download/#qg_source . PBXT builds with MySQL 5.1.46 GA (http://primebase.org/download/mysql-5.1.46.tar.gz ), and earlier 5.1 versions. If you don't want to compile it yourself, PBXT 1.0.11 will soon be available in the 5.1.46 release of MariaDB: http://askmonty.org/wiki/MariaDB:Download . And, for the more adventurous, PBXT 1.1 is included in Drizzle: https://launchpad.net/drizzle . A complete list of all the changes in this version are in the release notes: http://primebase.org/download/ChangeLog. If you are testing PBXT and have any questions send me an e-mail. I will be glad to help. Otherwise, if you are looking for development or production support for MySQL/MariaDB and PBXT then please write to: support-at-primebase- dot-org. We are working together with Percona (http://www.percona.com) and Monty Program Ab (http://askmonty.org/wiki/Main_Page) to provide the service level you require. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql transaction log
Please help me to understand more about the mysql transaction log ( mysqlbinlog) file and its contents. Will it support only the innodb or all the storage engine types like MyISAM, InnoDB? Thanks, Arsh Paul
InnoDB Default Storage Engine
I want to change the mysql default storage engine from MyISAM to InnoDB. What are the steps involved .Is it edit my.cnf file and add a line default-storage-engine=innodb and restart the mysql server? How I can bring my databases with mixed storage engine down without any data loss. What steps I have to take if I encounter a page corruption in innodb tables. why I am getting a message *Error*: *No query specified* when I run a show engines\g commands -version (5.0.45) Thanks, Arsh Paul
mysql-bin log file
How can I remove old mysql-bin log file in log directory? A mysql full backup will clear the old mysql bin log file or not? Thanks, Arshu Paul
Re: declare multiple 'Definer'
The syntax allows a single DEFINER clause. On Feb 22, 2010, at 7:45 AM, Steven Staples wrote: Well... let me finish... LOL (hit send some how...) I want to be able to have 2 different users access to a stored procedure... I've tried multiple ways to write it, and none of them seem to work. Does anyone here know how to do this? CREATE (definer=`use...@`%`, definer=`sstapl...@`localhost`) PROCEDURE `sstest`() . CREATE definer=`use...@`%` OR definer=`sstapl...@`localhost` PROCEDURE `sstest`() . CREATE definer=`use...@`%` definer=`sstapl...@`localhost` PROCEDURE `sstest`() I can't think how it would be possible? Or is it? Steve -Original Message- From: Steven Staples [mailto:sstap...@mnsi.net] Sent: February 22, 2010 8:42 AM To: mysql@lists.mysql.com Subject: declare multiple 'Definer' Good morning! I have been struggleing with creating a store procedure, that will allow 2 users -- 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=arch...@jab.org
Re: set max_allowed_packet
On Feb 20, 2010, at 11:22 AM, Vikram A wrote: Hi experts, When I try to set the packet size to some other value, it is not getting updated. show variables; set max_allowed_packet = 2097152; set global max_allowed_packet = 2097152; it resulting, Query is ok, 0 rows afected (0.00 sec) That's okay. What does this query return: mysql select @@global.max_allowed_packet; +-+ | @@global.max_allowed_packet | +-+ | 2097152 | +-+ 1 row in set (0.06 sec) Can you suggest me how set this value to higher one. By default it is 1 MB. Thank you VIKRAM A -- 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=arch...@jab.org
Re: how things get messed up
plug Just like to mention that http://www.blobstreaming.org was created to solve this problem in MySQL. The Launchpad project is here: https://launchpad.net/pbxt /plug On Feb 16, 2010, at 3:23 PM, Johan De Meersman wrote: On Mon, Feb 15, 2010 at 10:53 AM, Martijn Tonies m.ton...@upscene.comwrote: databases are made for storing data - it saves you on both database and PHP requests, as (from a web point of view) you can't return the image data inside your HTML - it requires a second HTTP call. Filesystem image serving, Doesn't an image always required additional http calls from the IMG tag? Yes, that's what I'm saying. However, there's a significant difference between an HTTP call that only needs to pump a file on to the network, and an HTTP call that needs to start a PHP process that then in turn needs to connect to the DB, which then needs to parse and execute a query. Offloading static files (images, css, whatnot) to a separate server that runs a lightweight, threaded httpd without PHP and whatnot compiled in, also means that you'll need less hardware to serve the same amount of requests - our PHP machines typically run 400 apache processes, but a static server on the same hardware could easily serve a few thousand threads - suddenly it's not memory/cpu but disk/network bandwidth that becomes your bottleneck. If you do go for BLOBs, though, for god's sake keep them in a separate table, lest you fragment your datafiles. Split records are a disaster for performance. I agree -for MySQL-, but this is a MySQL limitation!! Not a DBMS one. True. I think (but am too lazy to verify) that even in MySQL this is mainly an issue with MyISAM, not InnoDB. Given how MyISAM is the default engine, though, I thought it worthwile to mention. Separate LOB storage still leaves the overhead of query parsing and other generic DBMS stuff which isn't there on a filesystem, though, not to mention that each image requests takes up a database connection for as long as it takes to transfer the (potentially huge) data. Don't forget that in a well-tuned database, network transfer is often a significant part of your total connection lifetime for select statements. -- 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=arch...@jab.org
Re: Help with the World.sql sample database
On Feb 11, 2010, at 11:47 AM, kebede teferi wrote: Could any one lead me to a true link where I can download the world.sql sample database? Thanks. Go to: http://dev.mysql.com/doc/ Click on the Other Docs tab to get to: http://dev.mysql.com/doc/index-other.html You'll see an entry for the world database. Direct links are: http://downloads.mysql.com/docs/world.sql.gz http://downloads.mysql.com/docs/world.sql.zip -- 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=arch...@jab.org
Re: Selecting Dates
On Jan 31, 2010, at 7:35 PM, ML wrote: Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; No = after BETWEEN. or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Need order_date =, not just =. Neither of these work. What am I missing? -ML -- 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=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Shawn Green wrote: 23. There are fewer and less sophisticated tools for administration. MySQL doesn't need them. That alone should tell you something about our reliability. This speaks to simplicity-- both in terms of easy to use and in terms of more limited features. It says nothing about reliability. 45. Replication is asynchronous and has many limitations and edge cases. For example, it is single-threaded, so a powerful slave can find it hard to replicate fast enough to keep up with a less powerful master. Yes, it is asynchronous. This is a distinct advantage to many read-heavy applications and it allows MySQL to scale out better than most, if not all, other RDBMS systems. http://www.mysql.com/why-mysql/white-papers/mysql_wp_scaleout.php http://www.mysql.com/why-mysql/scaleout/booking.html There is a lot of truth to what the original poster says about MySQL replication edge cases, including those involving data integrity/data loss. These edge cases are by design, since it is the binlogs replicated, and not the particular storage engine's commit logs. It's one thing to scale out well when we're talking about comments to cat videos, as there is no harm done if my comment is lost or is slow to replicate around. It's another when we're talking financial transactions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
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=arch...@jab.org
MySQL 5.1+ Upgrade on Solaris 10
Hello, Would appreciate if any you post the steps to upgrade MySQL 5.0 to higher versions (5.1, 5.4)on Solaris 10 ,X_64 box. Thanks Regards, S Paul
Re: REGEXP and unicode weirdness
On Jan 21, 2010, at 9:27 AM, John Campbell wrote: I want to find rows that contain a word that matches a term, accent insensitive: I am using utf8-general collation everywhere. attempt 1: SELECT * FROM t WHERE txt LIKE '%que%' Matches que qué, but also matches 'queue' attempt 1.5: SELECT * FROM t WHERE txt LIKE '% que %' OR LIKE 'que %' OR LIKE '% que'; Almost, but misses que! or 'que...' attempt2: SELECT * FROM t WHERE txt REGEXP '[[::]]que[[::]]' Matches que, not queue, but doesn't match qué. attempt3 SELECT * FROM t WHERE txt REGEXP '[[::]]q[uùúûüũūŭůűųǔǖǘǚǜ][eèéêëēĕėęě][[::]]' Matches que, queue, qué. (I have no idea why this matches queue, but the Regex behavior is bizarre with unicode.) Does anyone know why the final regex acts weird? It there a good solution? http://dev.mysql.com/doc/refman/5.1/en/regexp.html: Warning The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal. -- 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=arch...@jab.org
Re: upgrading mysql
On Jan 13, 2010, at 1:28 PM, Lawrence Sorrillo wrote: The issue is that in theory this should work given the facts announced by MySQL regarding binary logging and replication. I can certainly do it the way you propose, but to my mind I should also be able to do it using the fact that both machines are fully synced and hence at that point I should be able to to local respective dumps and restores and still be in sync. Anyone knows anything special about position 106? It seems to be the very initial position in MySQL 5.1 servers? It's not. 4 is still the initial position, as shown by the at 4 in your mysqlbinlog output below. The 106 that you observe is the position *after* the server writes the initial event to the binary log. It writes this event immediately after opening the file, even before executing any statements. If you want the gory details: This event is the format description event that identifies in the binary log file the server version and other information. See http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log#Binary_Log_Versions if you have a high tolerance for pain. :-) mysql show master status; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | X-bin.01 | 106 | | | +---+--+--+--+ 1 row in set (0.00 sec) r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog mssdb2-bin.01 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #100113 13:50:40 server id 5 end_log_pos 106 Start: binlog v 4, server v 5.1.42-log created 100113 13:50:40 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; r...@:/usr/local/mysql/data ] ~Lawrence Tom Worster wrote: Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- 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=arch...@jab.org
Re: upgrading mysql
On Jan 12, 2010, at 12:36 PM, Lawrence Sorrillo wrote: Hi: I want to ensure that right after the reload that the same data is present in both the master and the slave. They are in perfect sync. Then I think its safe to consider starting binary logging and replication etc. And after these are started, changes can start? And in setting up replication in this manner I would not use the CHANGE MASTER... I will just master-host=xxx.xxx.xxx.xxx master-connect-retry=60 master-user=auser master-password=apassword in the my.cnf file and restart the slave server. From there it should start reading the binary logs and committing changes properly. Is this correct? You're upgrading to MySQL 5.1, for which several of those options no longer have any effect. Better to use CHANGE MASTER. See: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html -- 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=arch...@jab.org
[ANN] PBXT 1.0.10 RC4 Released
Hi All! I have just released PBXT 1.0.10 RC4. The major feature of this release is the implementation of the pbxt_flush_log_at_trx_commit system variable. This variable allows a trade off between the durability of most recently written transactions, and overall update speed of the engine. Further details are provided in the documentation: http://primebase.org/documentation/index.php#sysvar , and my blog: http://pbxt.blogspot.com Information on all other changes are in the release notes: http://primebase.org/download/ChangeLog The new version can be downloaded from: http://primebase.org/download. It is also available from Launchpad as the RC4 series: bzr branch lp:pbxt/rc4. The PBXT storage engine is sponsored by primebase.org, where you will find further information and documentation. Any feedback is welcome! Bugs should be reported here: https://bugs.launchpad.net/pbxt. You can ask questions on Launchpad at https://answers.launchpad.net/ pbxt, or post to the PBXT mailing list: https://launchpad.net/~pbxt- discuss. I wish all a HAPPY NEW YEAR!! :) Best regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join on a where clause.
On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi everybody! Neil Aggarwal wrote: Paul: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN 'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; I am surprised by the quotes you have around the start_ip and end_ip columns; to me, this makes that look like strings. That because I don't know what I am doing :). No quotes on integers; got it! From your posting, I see the result you hope to get but not the one you actually get. IMO, just dropping the single quotes around the two column names should produce the data you want to get. Or what is the result you receive? Removing the quotes does work. The query however took 1h15m to complete. Yuck. I am guessing this is because even though there is a limit, it is still doing the lookup on everything past 20. Also, if the first address has a count of say 2000, it would be doing the lookup 2000 times for a single address. Is that right? If it is I guess I will have to post process the results. Which is fine, I just like to keep as much in the queries as I can. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join on a where clause.
I have 2 tables:
1) Event Data
2) Mappings
The query should return something like this:
Hits IP Country Code
20213.136.52.29 SE
I am trying this:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN
'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER
BY count DESC LIMIT 20;
Am I supposed to do a join somewhere? Do joins even apply in a where
clause? or am I totally off the mark.
Singularly, the queries look like this:
SELECT cc FROM mappings WHERE INET_ATON('src_ip') BETWEEN start_ip AND end_ip;
SELECT COUNT(src_ip) AS count, INET_NTOA(src_ip) FROM event WHERE
timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' GROUP
BY src_ip ORDER BY count DESC LIMIT 20;
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: START TRANSACTION COMMIT ROLLBACK
If all the tables are InnoDB, XA isn't needed. It doesn't matter whether all tables are in the same database. On Oct 28, 2009, at 5:48 AM, Martijn Tonies wrote: Ah, works for InnoDB I see. Nice. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com Looks to me we should use XA transaction syntax instead. Check this: http://dev.mysql.com/doc/refman/5.0/en/xa.html Thanks, YY 2009/10/28 Martijn Tonies m.ton...@upscene.com Michael, Does MySQL support multi-db transactions? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com That is correct. Many db interfaces off programmatic abstractions of these facilities, but you may certainly just issue the statments. START TRANSACTION INSERT that UPDATE that on success: COMMIT on error: ROLLBACK - michael dykman On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil mosza...@gmail.com wrote: Hello Everyone, I am a newbie using innodb. How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update two tables that are located in two different databases. Would a single START TRANSACTION be sufficient ? Any help would be appreciated. TIA Mos -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- 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=arch...@jab.org
[ANN] PBXT 1.0.09 RC3 Released
Hi All! I have just released PBXT 1.0.09 RC3. Besides bug fixes, this version includes 2 Beta features: * XA/2-Phase Commit support * Native online backup Driver Details of all changes are in the release notes: http://primebase.org/download/ChangeLog Be aware of the fact that you may run into MySQL bug #47134 (http://bugs.mysql.com/bug.php?id=47134 ), when trying out the XA support. The new version can be downloaded from: http://primebase.org/download. It is also available from Launchpad as the RC3 series: bzr branch lp:pbxt/rc3. The PBXT storage engine is sponsored by primebase.org, where you will find further information and documentation. Please report bugs here: https://bugs.launchpad.net/pbxt Any feedback is welcome! You can ask questions on Launchpad at https://answers.launchpad.net/pbxt , or post to the PBXT mailing list: https://launchpad.net/~pbxt-discuss. I look forward to hearing from you! :) Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
On Aug 27, 2009, at 5:11 PM, Adrian Aitken wrote: Hi Scott, it's not the values I have a problem with, it's the fieldnames themselves. As an example the mysql.user table has 'Host' but when I do an update setting 'host' to a value it fails to update. I have to enter 'Host'. The mysql.com link seems to only talk about field values. That should not happen. Column names are not case sensitive in MySQL. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html: Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Can we see your query? Regards Adrian - Original Message - From: Scott Haneda To: Adrian Aitken Cc: mysql@lists.mysql.com Sent: Thursday, August 27, 2009 11:04 PM Subject: Re: Getting mySQL to ignore case sensitivity of field names http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. -- 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=arch...@jab.org
Re: Renaming a Database
On Aug 17, 2009, at 4:22 PM, Matt Neimeyer wrote: I know the best way to rename a database is to use mysqldump, extract the database and then reload to the new database. (At least based on what I can find in the 12.1.32. RENAME DATABASE Syntax section of the documentation) That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. -- 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=arch...@jab.org
Table advice.
I have a database that I am (will) be using to track URL's. The table structure looks like this: CREATE TABLE event ( eid INT UNSIGNED NOT NULL AUTO_INCREMENT, timestamp INT(10) UNSIGNED NOT NULL DEFAULT 0, ipINT(10) UNSIGNED NOT NULL DEFAULT 0, fqdn VARCHAR(255), domainVARCHAR(63), tld VARCHAR(63), actionVARCHAR(4), request TEXT, referrer TEXT, clientVARCHAR(255), INDEX eid (eid), INDEX timestamp (timestamp), INDEX ip (ip), INDEX fqdn (fqdn), INDEX domain (domain), INDEX tld (tld) ); The is no real logic behind the indexes, the table was hobbled together looking at examples. Currently I am trying queries on about 300 million records and the results are pretty crappy. for example, a query like this: select domain,count(domain) as count from event where domain like '%facebook%' group by domain order by count desc; takes about 5 minutes to complete. Most of the queries will be like that above but probably with additional filters like date constraints or IP constraints or a mixture of both. I can also see searches through the requests for filetypes etc. Any suggestions or comments would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] PBXT 1.0.08 RC2 Released!
Hi All, The second Release Candidate of PBXT, version 1.0.08, has just been released. As I have mentioned in my previous blogs (http://pbxt.blogspot.com/2009/03/improving-pbxt-dbt2-performance.html and http://pbxt.blogspot.com/2009/03/solving-pbxt-dbt2-scaling-problem.html) , I did a lot to improve performance for this version. At the same time I am confident that this release is stable as we now have a large number of tests, including functionality, concurrency and crash and recovery. But even more important, the number of users of PBXT has increased significantly since the last RC release, and that is the best test for an engine. So there has never been a better time to try out PBXT! :) You can download the source code, and selected binaries from here: http://primebase.org/download . Vladimir and I have made a lot of changes, for details checkout the release notes (http://primebase.org/download/ChangeLog). Bugs can be reported on Launchpad, here: https://bugs.launchpad.net/pbxt There is also a new PBXT mailing list, https://launchpad.net/~pbxt- discuss, so if you have any questions this is the best place for them. PBXT is a high-performance, MVCC-based, transactional storage engine for MySQL. The project is open source (GPL) and hosted on Launchpad. PBXT supports referential integrity, row-level locking and is fully ACID compliant. For more information please go to the PBXT home at: http:// primebase.org. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why is MySQL using /tmp?
This might help: http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html On Jun 11, 2009, at 12:51 AM, Mike Spreitzer wrote: I find my MySQL Community Edition 5.1.34 server running out of space on /tmp (which is indeed small). Why is it using /tmp? How much free space do I need on /tmp? Can/should I make the server use a different location instead of /tmp? Thanks, Mike Spreitzer -- 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=arch...@jab.org
Re: Upgrade Mysql
Hi, I don't know what Linux distro you're using, but I'd make a backup of /var/lib/mysql dir before you do anything (in case the mysql package decides to nuke your stuff). If you have a dump of your dbs, that's fine too. And maybe a backup of your my.cnf. Just install the new mysql package, then start it. Then you'll need to run mysqlupgrade. Depending on the size of your database and type of tables you are using it can take a while. For InnoDB tables, for example, upgrade simply means copy to tmp table... that's really slow if you have a large table. Once mysqlupgrade runs without a hitch, you should be back in business. -Paul Webmaster Studio Informatica wrote: Hi, I need to upgrade Mysql 4 to Mysql 5 on Linux. I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. Thank You. -- 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 deadlocks
Hi Mattia, On Mar 9, 2009, at 6:21 PM, Mattia Merzi wrote: Hi everyone, I've got some problems with deadlocks on InnoDB tables. On paragraph 13.6.8.10. How to Cope with Deadlocks of the mysql 5.1 version, the last sentence states: -- Another way to serialize transactions is to create an auxiliary “semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock. With MySQL table-level locks, the timeout method must be used to resolve deadlocks. -- Just two very simple questions: - using this method, transactions will be serialized so the deadlock problem will never come up again? Yes. But transactions will also no longer run in parallel which will reduce the throughput of the server if it is accessed by multiple clients. This seems clear reading that sentence, the only thing that makes me humble is the statement: Note that the InnoDB instant deadlock detection algorithm also works in this case ... can someone briefly explain me this concept? - if I create a semaphore table and I start any deadlock-prone transaction issuing a lock table write on that table and an unlock tables immediately after the commit, will the effect be the same? Yes, this will work the same. 'Cause the last sentence of the manual says: With MySQL table-level locks, the timeout method must be used to resolve deadlocks This is true, but is only a problem if deadlocks are possible. However, deadlocks are not possible if you start every transaction with a lock table write. will this mean that if I use this LOCK TABLE method I can get timeouts instead of deadlocks on the very same transactions? Yes, this can happen. But, only if a deadlock is possible. By exclusive locking a single resource (a row or a table), at the start of each transaction, you explicitly make deadlocks impossible. However, it is recommended to UPDATE a single row in the new table, instead of using lock table write. This has the same affect, but with the benefit that deadlock detection will still work in the case that you do not modify all transactions as suggested. This might happen because: - your code is vast, and you miss one, or - you add a new transaction and forget to add the exclusive locking UPDATE, or - you leave out certain transaction on purpose because you have never had deadlocking problems with them before. All good reasons not to serialize all transactions, and therefore a good reason to use the method that continues to support deadlock detection. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to execute prepare statement when the placeholder is a specific table name?
On Feb 14, 2009, at 8:00 PM, Moon's Father wrote:
Thanks for your fast reply.
Do you know MySQL will support this feature in the future?
It doesn't need to. Baron indicated how you can solve this problem.
If you look at prepared statements in general (not just for MySQL),
placeholders are for data values, not identifiers.
On Sun, Feb 15, 2009 at 12:59 AM, Baron Schwartz ba...@xaprb.com
wrote:
Hello,
On Sat, Feb 14, 2009 at 11:15 AM, Moon's Father
yueliangdao0...@gmail.com wrote:
Here is my routine.
DELIMITER $$
CREATE
PROCEDURE `t_girl`.`sp_join2`()
BEGIN
set @a = 'a';
set @b = 'g';
set @stmt = concat('select * from ?,? where a.id = g.id');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
END$$
DELIMITER ;
But it didn't work for me.
So what I want to know is how to table name when there're a
placeholder
within sproc.
You can't use placeholders for identifiers, only for literal values.
So you will need to use CONCAT() to build the string with the
identifiers already in it, before you PREPARE.
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
--
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=arch...@jab.org
Re: mysql full tutorial download or any book
On Feb 14, 2009, at 5:49 PM, mos wrote: At 05:20 PM 2/14/2009, mos wrote: At 04:07 AM 2/14/2009, Andy Shellam wrote: To be honest, I got started straight from the MySQL manual. There is a tutorial section in the manual as well, and it's also available to download as a PDF. Links: Manual (English): http://dev.mysql.com/doc/refman/5.1/en/index.html Manual (English PDF US Letter): http://downloads.mysql.com/docs/refman-5.1-en.pdf Tutorial (English): http://dev.mysql.com/doc/refman/5.1/en/tutorial.html Substitute 5.1 with whatever version you're running. HTH, Andy Andy, I agree, the manual is well written. Ironically, the hyperlinks in the PDF file do NOT WORK so it is difficult to navigate. Mike Oops, my bad. I was using Foxit v2.x reader and that was the problem. I've upgraded to Foxit v3 and the hyperlinks work fine in the MySQL manual. Whew! I was about to panic. :-) -- 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=arch...@jab.org
Re: Installing MySQL from source
On Feb 13, 2009, at 8:52 AM, Joerg Bruehe wrote: Michel, michel wrote: From the different documentation I was reading that after going to the root directory I should execute /.configure; but there is no configure file in the root source directory, just configure.in there is a difference between Installing MySQL from the current development source tree and Installing MySQL from the source tarball of some released version. In the development source tree, we have files which need to be processed by the GNU autotools (autoconf, automake, aclocal), whereas a source tarball is created from the result of these tools. The manual should cover that, but I don't have a specific URL handy. It's http://dev.mysql.com/doc/refman/5.1/en/installing-source-tree.html The development tree contains a file BUILD/autorun.sh which runs these tools. HTH, Jörg -- 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=arch...@jab.org
Re: Install problem: log file issue
On Feb 12, 2009, at 6:28 PM, csego...@gmail.com wrote: Andy, Michael, and Walter - thank you! Adding a [mysqld_safe] group to my.cnf gets me further but the start still fails. The good thing is that the failure is no longer due to the inability to write the log file. The [mysqld_safe] section of my.cnf reads: [mysqld_safe] 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 datadir = /app/mysql/data #data_file_path = /app/mysql/data #log = /app/mysql/log/mysqld.log log-bin = /app/mysql/log/mysql-bin.log log-output = FILE general_log = 1 general_log_file = /app/mysql/log/msyql_general.log socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log Luckily, I now have an error log which reads: 090212 18:24:04 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data InnoDB: Log scan progressed past the checkpoint lsn 0 39548 090212 18:24:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 46409 090212 18:24:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 090212 18:24:04 InnoDB: Started; log sequence number 0 46409 090212 18:24:04 [ERROR] /app/mysql/libexec/mysqld: Can't create/ write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 090212 18:24:04 [ERROR] Can't start server: can't create PID file: No such file or directory 090212 18:24:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended Now I need to figure out how to point PID file creation the appropriate directory. Sounds like you want: pid-file = /app/mysql/log/mysql.pid http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_pid-file -- 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=arch...@jab.org
Re: How to get Error Number and Error Message 2nd Try
On Feb 10, 2009, at 11:26 PM, Al wrote: I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, SHOW WARNINGS might help. http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html -- 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=arch...@jab.org
Re: Are dates stored as String? Or Integer?
On Jan 27, 2009, at 11:31 PM, mos wrote: Are date columns stored as String or Integer in a MyISAM table? I am trying to squeeze more speed from my application and a date column is used in most of the indexes for my tables. I'm wondering if changing the Date data type to an Integer is going to speed the queries up. I'm using Delphi and internally it represents dates as float so using integers will speed up the Delphi code. But the main slow down I have is with executing the queries. The dates are used in the indexes, sorting, and in a few table joins. So is there a speed advantage of switching the dates to integer? Dates are stored numerically. See: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html Beginning with The storage requirements shown in the table arise from the way that MySQL represents temporal values ... -- 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=arch...@jab.org
Re: Setting the value for the EDITOR variable
On Jan 27, 2009, at 5:44 PM, Xristos Karvouneas wrote: Dear All, I would like to be able to do that from the cnf file, as I want to have a different editor for MySQL commands than the one I use when writing shell scripts. When I put the following in the .my.cnf file, [shell] EDITOR=/usr/local/bin/pico export EDITOR I get no error messages when mysql starts up, but the value of the EDITOR variable does not change. Is there any way I can achieve that from the cnf file? No. MySQL option files are for setting MySQL program options, not environment variables. -- 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=arch...@jab.org
Re: Setup a replication slave without stopping master
Jed Reynolds wrote: If you are using LVM, you might consider snapshotting, however, doing a live snapshot without stopping mysql server would only work if you were copying only myisam tables. Mysql-hot-copy would probably be better, but either way, you need to flush your tables, which will briefly lock them, so they can get onto disk. In contrast, InnoDB actually needs to shut down to cleanly close its table structures before you can physically copy the filesystem. If you can do an LVM snapshot on the dir(s) holding InnoDB files, then you should actually be able to do a live backup. Once you restore from the snapshot on a different host, mysql will behave as if it's recovering from a crash. Then you can tell from the .err file where the last binlog position was: InnoDB: Last MySQL binlog file position 0 1574672, file name /blah/blah/mysql_binlog/binlog.091206 Then you can use mysqlbinlog to apply binlogs until you are caught up. The caveat is, again, that you have to do a snapshot on the entire innodb_data_home_dir and innodb_log_group_home_dir. Hence both InnoDB data file and log. This approach is known to work with Solaris ZFS and should work the same way with LVM. -Paul Choi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to design book db
Recent books that I've looked at have 2 ISBN's - one the older 10 digit, and also the newer 13 digit version. Both printed on the same book (both on the back cover at the bottom and inside). Of course, a hard cover will have a different ISBN again. Hooker -- If ignorance is bliss, politicians should be orgasmic! -Original Message- From: bruce [mailto:bedoug...@earthlink.net] Sent: Tuesday, January 06, 2009 8:52 AM To: 'PJ'; 'mos' Cc: mysql@lists.mysql.com Subject: RE: how to design book db hey phil... are you sure that a book can have multiple ISBN numbers. I was under the impression that a book/version has a single ISBN number. care to share where you have derived your understanding... i believe bowkers/new jersy is responsible for allocating ISBN blocks for US authors/publishers... thanks -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Monday, January 05, 2009 3:06 PM To: mos Cc: mysql@lists.mysql.com Subject: Re: how to design book db mos wrote: At 08:17 AM 12/29/2008, you wrote: I am rather fresh to MySQL and am trying to fix and update a website - modifying from just plain html to css, php and MySQL. I'm working on FreeBSD 7.0, MySQL 5.1,30, PHP5.28 Apache 2.2.11. I need figure out how to set up (design) a database of books which gets rather complicated since I must implement searches of the database based on key words including categories, ISBN numbers, authors, dates, etc. etc. The problem is how to deal with duplication of the data - In other words, a book may have not only several authors, but also several ISBN numbers, fall under several categories, different dates (year of publication), several publishers I probably haven't yet seen all of the variables. I certainly do not want to enter the same book many times with just one of each different variable. I suppose that one way to do it is to enter one row with a lot of columns to store all the the different variables; a search would probably be simpler this way if the search criteria are limited to 1 word. Or would it? I rather do think that the search should be limited to 1 word anyway. :-) If the search would be for a category, for instance, would it make sense to use a column for category with an input of keywords for the different categories?; rather than a column for each category or another table of categories? Multiple publication years could probably be different row entries since there would not be more than 2 or would be a different publisher, language, or country. I really with to K.I.S.S this undertaking and would appreciate any help or suggestions. If it helps, you can see the site as it is at present http://www.ptahhotep.com - but since it is rather messed up at the moment, it is best viewed with IE. Some of the links and jscripts don't work on FireFox. TIA, PJ You can of course simplify things by putting the alternate ISBN number in the description of the book and put a full text index on it. Same with alternate authors etc.. It would be a catch all for items that you don't have columns for. How do I do that? I think the best line of attack is to work from an existing model. Why re-invent the wheel? You're right... I appreciate the suggestion and the links... There are a few bookstore/library schemas here: http://www.databaseanswers.org/data_models/ http://www.edumax.com/oracle-basics-06-normalization-and-sample-schema-creat ion-normalization-and-sample-schema-creation.html Mike Sorry for the long holiday delay in continuing... I checked the links below I think they will help ... but there are some things that are not clear in my mind: 1. I understand what the abbreviations PK and FK (primary key and foreign key) are but what is PF? (primary field??? - this is in the link http://www.databaseanswers.org/data_models/ uner Libraries and books 2. How can I deal with a primary key for books? ISBN would be great, except for the fact that it was only implemented at a certain date and books published before that date do not have an ISBN number. 3. And what about books that were written by several authors? 4. What do I need to fix in the tables below? +---+ | Tables_in_biblane | +---+ | authors | | books | | books_by_author | | books_by_category | | categories| +---+ mysql DESCRIBE authors; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | author_id | tinyint(4) | NO | PRI | NULL| | | auth_first_name | varchar(15) | NO | | NULL| | | auth_last_name | varchar(32) | NO | | NULL| | +-+-+--+-+-+---+ mysql DESCRIBE books; ++-+--+-+-++ | Field | Type
[ANN] PBXT 1.0.07 RC Released!
Hi All, I have just posted the first Release Candidate version of the PBXT transactional storage engine for MySQL. For more information on PBXT, please check out the home page: http://www.primebase.org My blog has a some more details on the release: http://pbxt.blogspot.com/2008/12/pbxt-goes-rc.html PBXT can be downloaded from here: http://www.primebase.org/download Or you can get the bleeding edge from: https://launchpad.net/pbxt To prove we have been busy, here are the release notes: http://www.primebase.org/download/ChangeLog And lets not forget the awesome new new performance analysis tool 'xtstat': http://pbxt.blogspot.com/2008/12/xtstat-tells-you-exactly-what-pbxt-is.html Bug reports gladly accepted here: https://bugs.launchpad.net/pbxt Questions and comments are also welcome :) My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do! Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Guru Needed!!
And you may also try: Ronald Bradford: http://42sql.com Another frequent conference speaker that really knows his stuff! Ask him about mentoring program... On Dec 12, 2008, at 8:21 PM, Ewen Fortune wrote: As an alternative you also have www.percona.com www.pythian.com www.openquery.com.au www.provenscaling.com Percona's minimum billing unit is 15 minutes, not sure about the rest On Dec 12, 2008, at 17:35, bruce bedoug...@earthlink.net wrote: Hi. I've got a situation where I need to reach out/talk to a mysql guru every now and then. For the most part, the questions are probably 5-10 minutes for the right person, but they might take me hours/days to cobble together a good solution. (I'm not a mysql guru!!) As an example, I have a situation now where I've been trying to figure out a solution for a day now... I'm looking for someone that I can talk to periodically if I have questions. I'm willing to drop something in a paypal acct for this function. Posting to the email list, or the IRC chat isn't always expedient for my needs. Thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=paul.mccull...@primebase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: REGEXP help Finding phone numbers (nnn) nnn-nnnn format SOLVED
This seems to do it:
SELECT phone_work FROM leads WHERE phone_work REGEXP '[(]{1}([0-9]){3}[)]{1}[
]?([^0-1]){1}([0-9]){2}[ ]?[-]?[ ]?([0-9]){4}'
- Original Message
From: Paul Nowosielski [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, December 3, 2008 2:39:54 PM
Subject: REGEXP help Finding phone numbers (nnn) nnn- format
Hi,
Please, can anyone lend a hand in helping pullout
phone numbers from the DB that only match
the format (nnn) nnn- ?
SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?'
I've been trying to lick this for hours now with
no avail.
Thank you,
Paul
--
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]
REGEXP help Finding phone numbers (nnn) nnn-nnnn format
Hi, Please, can anyone lend a hand in helping pullout phone numbers from the DB that only match the format (nnn) nnn- ? SELECT phone_work FROM leads WHERE phone_work REGEXP 'the_expression?' I've been trying to lick this for hours now with no avail. Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
regular expressions matching only numeric characters in order
Dear All, I'm trying to create a regular expression query to match phone numbers in a database field. My issue is this , the numbers have no set standard for input in the db. So the number in the db could be in multiple formats. EX: 333.333. (333)333- 333-333- 33 So I am wanting to find matches and ignore any non-numeric values and need some help with the expression. The query would be something like: SELECT tel FROM leads WHERE do_not_call = '1' AND tel REGEXP 'the expression' The number I am looking for will already have all the non numerics stripped and will be in the format of nn. Any help would be appreciated. Thank you, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: regular expressions matching only numeric characters in order
Hi, Seems to work! But the number is a zero you have to add an extra escape: (\\0+\3+\\0+\9+) or you get ERROR 1139 (42000): Got error 'parentheses not balanced' from regexp Thank you!! Paul - Original Message From: Bartis, Robert M (Bob) [EMAIL PROTECTED] To: Paul Nowosielski [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, December 1, 2008 6:42:19 PM Subject: RE: regular expressions matching only numeric characters in order Hi I am a bit of novice at Regexp, but I believe this will work for you (\d+\d+\d+).*(\d+\d+\d+).*(\d+\d+\d+\d+) Robert M. Bartis EMAIL: [EMAIL PROTECTED] -Original Message- From: Paul Nowosielski [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 3:32 AM To: mysql@lists.mysql.com Subject: regular expressions matching only numeric characters in order Dear All, I'm trying to create a regular expression query to match phone numbers in a database field. My issue is this , the numbers have no set standard for input in the db. So the number in the db could be in multiple formats. EX: 333.333. (333)333- 333-333- 33 So I am wanting to find matches and ignore any non-numeric values and need some help with the expression. The query would be something like: SELECT tel FROM leads WHERE do_not_call = '1' AND tel REGEXP 'the expression' The number I am looking for will already have all the non numerics stripped and will be in the format of nn. Any help would be appreciated. Thank you, Paul -- 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]
[ANN] PBXT 1.0.06 Beta Released
Hi All, On friday we released the second Beta version of PBXT. PBXT is a transactional storage engine for MySQL 5.1 and 6.0. You can find out more about the engine at http://www.primebase.org. PBXT is pluggable, so it can be built separately from the MySQL tree, and loaded dynamically at runtime using the LOAD PLUGIN statement. You can download PBXT from here: http://www.primebase.org/download. A quick guide to building and installing the plugin is provided. I have also updated the documentation for this version. There are no major new features in this release because we are working towards the RC version. But we wrote some release notes (http://www.primebase.org/download/ChangeLog ) to prove we have been busy :) There is now also a version of PBXT available for Drizzle: http:// http://drizzleproject.org. You will find the source code here: https://code.launchpad.net/ ~drizzle-pbxt/drizzle/pbxt. Please e-mail me if you have any questions or if you need help with PBXT. Bugs reports can be submitted on Launchpad: https://bugs.launchpad.net/pbxt . Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment problem
Anybody know if there's a way to change a primary key field that is not auto-incremented, turning on auto-increment but preserving the values that are currently in it? TIA, Paul W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Book: MySQL, Fourth Edition, now available
The book MySQL, Fourth Edition (aka Doorstop IV) has been published. More information is available at the book's Web site: http://www.kitebird.com/mysql-book/ -- 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/[EMAIL PROTECTED]
Re: [ANN] PBXT Beta Version Released!
Hi Mike, The architectures of the engines are radically different, so you can expect different performance characteristics. But the main differences and advantages will show in the future, as the engines are developed further. I spoke about the future directions of PBXT at the last user's conference, here is the presentation: http://www.primebase.org/download/pbxt-uc-2008.pdf Some difference as the moment: PBXT has foreign key support like InnoDB, which Falcon does not (yet). And PBXT can stream BLOB data to and from the database, which no other engine can do (yet). This functionality is provided by the BLOB Streaming engine from: www.blobstreaming.org . Best regards, Paul On Sep 2, 2008, at 9:12 PM, mos wrote: Paul McCullagh, What's the advantage of using PBXT over InnoDb or the new Falcon transactional engine? TIA Mike Hi All! I am pleased to announce that the Beta version of PBXT has just been released. PBXT is a fast, lightweight, transactional storage engine for MySQL. More information is available at http://www.primebase.org. You can download the source code at http://www.primebase.org/ download. I have also updated the documentation for this version (http://www.primebase.org/documentation ). Configuring and building the engine is easier than ever now. To configure PBXT all you have to do is specify the path to the MySQL source code tree (after building MySQL), for example: ./configure --with-mysql=/home/foo/mysql/mysql-5.1.26-rc The PBXT configure command will retrieve all required options from the MySQL build. For example whether to do a debug or optimized build and where to install the plugin are determined automatically, depending on how you configured MySQL. This was a source of some mistakes when building the plugin, so I think it is really cool! My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do! Let me know if you have any comments or questions, I'll be glad to help. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- 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] -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] PBXT Beta Version Released!
Hi All! I am pleased to announce that the Beta version of PBXT has just been released. PBXT is a fast, lightweight, transactional storage engine for MySQL. More information is available at http://www.primebase.org. You can download the source code at http://www.primebase.org/download. I have also updated the documentation for this version (http://www.primebase.org/documentation ). Configuring and building the engine is easier than ever now. To configure PBXT all you have to do is specify the path to the MySQL source code tree (after building MySQL), for example: ./configure --with-mysql=/home/foo/mysql/mysql-5.1.26-rc The PBXT configure command will retrieve all required options from the MySQL build. For example whether to do a debug or optimized build and where to install the plugin are determined automatically, depending on how you configured MySQL. This was a source of some mistakes when building the plugin, so I think it is really cool! My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do! Let me know if you have any comments or questions, I'll be glad to help. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
