Re: Fwd: why must user variable in EXECUTE USING clause ?
it seems none of you hava answered my question.
On Tue, Mar 27, 2012 at 7:01 AM, Walter Tross wal...@waltertross.comwrote:
Harald,
keep in mind that
a) this mailing list is badly set up: unless you do a reply all, the
reply goes only to the poster (happened to me too)
b) Microsoft products have made inline (bottom) answering almost
impossible. I, like many others, am forced to used Outlook at the office,
and therefore I had to give up, and now I stick to top-posting :-(
ciao
W.
P.S. the only thing I have not given up yet is not using empty lines to
avoid the feature (?) of Outlook that glues lines together. This
feature can be disabled/reverted (although this is not widely known)
At 21.38 26/03/2012 +0800, Cifer Lee wrote:
-- Forwarded message --
From: Reindl Harald mailto:h.rei...@thelounge.net
h.rei...@thelounge.net
Date: Mon, Mar 26, 2012 at 9:17 PM
Subject: Re: why must user variable in EXECUTE USING clause ?
To: Cifer Lee mailto:mantia...@gmail.commantia...@gmail.com
would you PLEASE send to the list instead off-list
and put your answer BELOW instead to-posting?
is it really so difficult to use mailing-lists?
Am 26.03.2012 14:34, schrieb Cifer Lee:
thanks for reply
and .sorry for my poor English ...
I wrote a procedure program which contains prepare clause , please see
below
CREATE PROCEDURE `iter_table`(IN type int)
BEGIN
DECLARE tablename VARCHAR(24) DEFAULT '';
DECLARE shop_id int DEFAULT 0;
DECLARE count int DEFAULT 0;
DECLARE row_count int DEFAULT 0;
DECLARE x varchar(24);
DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
SELECT COUNT(*) INTO row_count FROM shop;
OPEN cur1;
REPEAT
FETCH cur1 INTO shop_id;
SET @shop_id := shop_id;
SET @type := type;
SET tablename= CONCAT('shop',@shop_id);
SET @sqlstr = CONCAT('SELECT
`id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
',tablename,' WHERE `type`=?;');
PREPARE stat FROM @sqlstr;
EXECUTE stat USING type; - Must be EXECUTE stat USING
@type ;
SET count=count+1;
UNTIL count = row_count
END REPEAT;
CLOSE cur1;
END
I got an error at the red line when I creating this procedure
and the solution is replace the 'type' with '@type'
I found here http://dev.mysql.com/doc/refman/5.5/en/execute.html
http://dev.mysql.com/doc/refman/5.5/en/execute.html
and knows that
/you must supply a |USING| clause that lists user variables containing
the values to be bound to the parameters.
Parameter values can be supplied only by user variables/,
but, I don't know why . why must parameter be user variables ? why
can't be local variables , as declared in
DECLARE clause..
thanks!
On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald mailto:
h.rei...@thelounge.neth.rei...@thelounge.net mailto:
h.rei...@thelounge.net wrote:
Am 26.03.2012 14:13, schrieb Cifer Lee:
why can't be local variable which declared in DECLARE clause?
what are you speaking about?
keep in mind that we can not read your thoughts
Content-Type: application/pgp-signature; name=signature.asc
Content-Disposition: attachment; filename=signature.asc
X-Attachment-Id: 2caae85bfd1f082d_0.1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
- Original Message - From: Halász Sándor h...@tbbs.net input for Word (yes, I have to work MySQL CSV-ish output into real CSV, ensuring that there are no escapes in it, nor 'NULL' either). And this is why it's beneficial to specify your goal along with your question :-) MySQL does have a proper CSV engine; and it's mystifying why it isnt' accessible in any other way. I say engine, and you can take that literally: just like you have MyISAM and InnoDB engines, CSV is also an engine that is applicable to tables. Just do a create table myexport engine=CSV as select * from mytable; and a myexport.CSV file will magically appear in the data directory. You'll need the proper privileges to read it, of course; and you should use drop table myexport; to delete it instead of doing so from the file manager; but it will be proper CSV. Note that CSV means comma separated value, but that MS Excel, for one, tends to use semicolons instead. Shouldn't be a major hurdle in importing, though. /johan -- 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
Re: why must user variable in EXECUTE USING clause ?
- Original Message - From: Cifer Lee mantia...@gmail.com it seems none of you hava answered my question. Yes, that happens :-) I'm slightly confused as to the difference between user variables and local variables, though. Are you saying you couldn't just declare @myvar and use it in your code? -- 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
mysqld got signal 6 (problem why bigger than I initially realised)
Hey Guys Yesterday I sent an email, about '1 client is using or hasn't closed the table properly', but the problem is actually bigger than I realised. We run Mysql replication, and on the second node, Mysql is crashing with 'mysqld got signal 6' every so often. Other than the drives, we have replaced the hardware, and still the problem persists. Googling, I thought it was a memory exhaustion issue, so I started playing with the following variables and values. key_buffer_size=8384512 max_connections=41 But it still crashes Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size=8384512 Mar 27 12:36:53 xyz-web02 mysqld: read_buffer_size=131072 Mar 27 12:36:53 xyz-web02 mysqld: max_used_connections=3 Mar 27 12:36:53 xyz-web02 mysqld: max_threads=41 Mar 27 12:36:53 xyz-web02 mysqld: thread_count=1 Mar 27 12:36:53 xyz-web02 mysqld: connection_count=1 Mar 27 12:36:53 xyz-web02 mysqld: It is possible that mysqld could use up to Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 97828 K bytes of memory xyz-web02:# free -m total used free sharedbuffers cached Mem: 12038 11966 71 0117 11378 -/+ buffers/cache:469 11568 Swap: 2047 0 2047 We running Debian Squeeze 64bit. I have attached the full crash message. If anyone can help, I would be *most* grateful (If you are in South Africa, I will buy you a meal, let alone a beer :) Kindest Regards Brent Clark *** glibc detected *** /usr/sbin/mysqld: double free or corruption (!prev): 0x7f5e38003b60 *** === Backtrace: = /lib/libc.so.6(+0x71bd6)[0x7f5e4575dbd6] /lib/libc.so.6(cfree+0x6c)[0x7f5e4576294c] /usr/sbin/mysqld(_ZN15Query_log_eventD0Ev+0x20)[0x7f5e474f2ed0] /usr/sbin/mysqld(handle_slave_sql+0x835)[0x7f5e47567255] /lib/libpthread.so.0(+0x68ca)[0x7f5e46c738ca] /lib/libc.so.6(clone+0x6d)[0x7f5e457bb86d] === Memory map: 7f5e3000-7f5e30021000 rw-p 00:00 0 7f5e30021000-7f5e3400 ---p 00:00 0 7f5e3800-7f5e38158000 rw-p 00:00 0 7f5e38158000-7f5e3c00 ---p 00:00 0 7f5e3e019000-7f5e3e01a000 ---p 00:00 0 7f5e3e01a000-7f5e3e81a000 rw-p 00:00 0 7f5e3e81a000-7f5e3e81b000 ---p 00:00 0 7f5e3e81b000-7f5e3f01b000 rw-p 00:00 0 7f5e3f01b000-7f5e3f01c000 ---p 00:00 0 7f5e3f01c000-7f5e3f81c000 rw-p 00:00 0 7f5e3f81c000-7f5e3f81d000 ---p 00:00 0 7f5e3f81d000-7f5e4001d000 rw-p 00:00 0 7f5e4001d000-7f5e4001e000 ---p 00:00 0 7f5e4001e000-7f5e40a24000 rw-p 00:00 0 7f5e40c47000-7f5e40c48000 ---p 00:00 0 7f5e40c48000-7f5e41448000 rw-p 00:00 0 7f5e41448000-7f5e41449000 ---p 00:00 0 7f5e41449000-7f5e41c49000 rw-p 00:00 0 7f5e41c49000-7f5e41c4a000 ---p 00:00 0 7f5e41c4a000-7f5e448c4000 rw-p 00:00 0 7f5e448c4000-7f5e448d r-xp fd:00 328894 /lib/libnss_files-2.11.3.so 7f5e448d-7f5e44acf000 ---p c000 fd:00 328894 /lib/libnss_files-2.11.3.so 7f5e44acf000-7f5e44ad r--p b000 fd:00 328894 /lib/libnss_files-2.11.3.so 7f5e44ad-7f5e44ad1000 rw-p c000 fd:00 328894 /lib/libnss_files-2.11.3.so 7f5e44ad1000-7f5e44adb000 r-xp fd:00 328896 /lib/libnss_nis-2.11.3.so 7f5e44adb000-7f5e44cda000 ---p a000 fd:00 328896 /lib/libnss_nis-2.11.3.so 7f5e44cda000-7f5e44cdb000 r--p 9000 fd:00 328896 /lib/libnss_nis-2.11.3.so 7f5e44cdb000-7f5e44cdc000 rw-p a000 fd:00 328896 /lib/libnss_nis-2.11.3.so 7f5e44cdc000-7f5e44ce3000 r-xp fd:00 328892 /lib/libnss_compat-2.11.3.so 7f5e44ce3000-7f5e44ee2000 ---p 7000 fd:00 328892 /lib/libnss_compat-2.11.3.so 7f5e44ee2000-7f5e44ee3000 r--p 6000 fd:00 328892 /lib/libnss_compat-2.11.3.so 7f5e44ee3000-7f5e44ee4000 rw-p 7000 fd:00 328892 /lib/libnss_compat-2.11.3.so 7f5e44eeb000-7f5e44eec000 ---p 00:00 0 7f5e44eec000-7f5e456ec000 rw-p 00:00 0 7f5e456ec000-7f5e45845000 r-xp fd:00 328847 /lib/libc-2.11.3.so 7f5e45845000-7f5e45a44000 ---p 00159000 fd:00 328847 /lib/libc-2.11.3.so 7f5e45a44000-7f5e45a48000 r--p 00158000 fd:00 328847 /lib/libc-2.11.3.so 7f5e45a48000-7f5e45a49000 rw-p 0015c000 fd:00 328847 /lib/libc-2.11.3.so 7f5e45a49000-7f5e45a4e000 rw-p 00:00 0 7f5e45a4e000-7f5e45a64000 r-xp fd:00 328801 /lib/libgcc_s.so.1 7f5e45a64000-7f5e45c63000 ---p 00016000 fd:00 328801 /lib/libgcc_s.so.1 7f5e45c63000-7f5e45c64000 rw-p 00015000 fd:00 328801
Re: mysqld got signal 6 (problem why bigger than I initially realised)
HI Brent, Can you please paste your configuration file settings (my.cnf) ? It will help to identify where things went wrong. Is there any other memory consuming application running on the server beyond mysql ? Regards, KarthiK.P.R On Tue, Mar 27, 2012 at 4:17 PM, Brent Clark brentgclarkl...@gmail.comwrote: Hey Guys Yesterday I sent an email, about '1 client is using or hasn't closed the table properly', but the problem is actually bigger than I realised. We run Mysql replication, and on the second node, Mysql is crashing with 'mysqld got signal 6' every so often. Other than the drives, we have replaced the hardware, and still the problem persists. Googling, I thought it was a memory exhaustion issue, so I started playing with the following variables and values. key_buffer_size=8384512 max_connections=41 But it still crashes Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size=8384512 Mar 27 12:36:53 xyz-web02 mysqld: read_buffer_size=131072 Mar 27 12:36:53 xyz-web02 mysqld: max_used_connections=3 Mar 27 12:36:53 xyz-web02 mysqld: max_threads=41 Mar 27 12:36:53 xyz-web02 mysqld: thread_count=1 Mar 27 12:36:53 xyz-web02 mysqld: connection_count=1 Mar 27 12:36:53 xyz-web02 mysqld: It is possible that mysqld could use up to Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 97828 K bytes of memory xyz-web02:# free -m total used free sharedbuffers cached Mem: 12038 11966 71 0117 11378 -/+ buffers/cache:469 11568 Swap: 2047 0 2047 We running Debian Squeeze 64bit. I have attached the full crash message. If anyone can help, I would be *most* grateful (If you are in South Africa, I will buy you a meal, let alone a beer :) Kindest Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Difference between internal data dictionary and table definition file
- Original Message - From: Kinaan Khan Sherwani mr.sherw...@gmail.com that MySQL server keeps data dictionary information in .frm files which it stores in the database directories, whereas InnoDB also stores the information into its own data dictionary inside the table space files. I want to know where comes the difference between InnoDB internal data dictionary and table definition ( .frm ) files . Do they store the same information about a table ? InnoDB still stores .frm files for the benefit of the MySQL daemon. I'm no master of the InnoDB internals, but it wouldn't be surprised to find that the internal DD is a superset of what gets stored in the .frm files. Perhaps the MySQL devel mailing list might be a better place to look for information on the guts of InnoDB? -- 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
Re: mysqld got signal 6 (problem why bigger than I initially realised)
Good day KarthiK.P.R Other than the replication settings in '/etc/mysql/conf.d/replication.cnf' and our /etc/mysql/conf.d/custom.cnf xyz-web02:/data# cat /etc/mysql/conf.d/custom.cnf [mysqld] innodb_file_per_table bind-address = 0.0.0.0 datadir = /data binlog_format=mixed key_buffer_size=8384512 max_connections=41 The original /etc/mysql/my.cnf is untouched. The only other service we have running is DRBD (active / active) and apache, nothing is hitting apache as this is the standby node. And there is no load, or anything consuming resources. Thanks Brent On 27/03/2012 14:04, P.R.Karthik wrote: HI Brent, Can you please paste your configuration file settings (my.cnf) ? It will help to identify where things went wrong. Is there any other memory consuming application running on the server beyond mysql ? Regards, KarthiK.P.R On Tue, Mar 27, 2012 at 4:17 PM, Brent Clark brentgclarkl...@gmail.com mailto:brentgclarkl...@gmail.com wrote: Hey Guys Yesterday I sent an email, about '1 client is using or hasn't closed the table properly', but the problem is actually bigger than I realised. We run Mysql replication, and on the second node, Mysql is crashing with 'mysqld got signal 6' every so often. Other than the drives, we have replaced the hardware, and still the problem persists. Googling, I thought it was a memory exhaustion issue, so I started playing with the following variables and values. key_buffer_size=8384512 max_connections=41 But it still crashes Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size=8384512 Mar 27 12:36:53 xyz-web02 mysqld: read_buffer_size=131072 Mar 27 12:36:53 xyz-web02 mysqld: max_used_connections=3 Mar 27 12:36:53 xyz-web02 mysqld: max_threads=41 Mar 27 12:36:53 xyz-web02 mysqld: thread_count=1 Mar 27 12:36:53 xyz-web02 mysqld: connection_count=1 Mar 27 12:36:53 xyz-web02 mysqld: It is possible that mysqld could use up to Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 97828 K bytes of memory xyz-web02:# free -m total used free sharedbuffers cached Mem: 12038 11966 71 0117 11378 -/+ buffers/cache:469 11568 Swap: 2047 0 2047 We running Debian Squeeze 64bit. I have attached the full crash message. If anyone can help, I would be *most* grateful (If you are in South Africa, I will buy you a meal, let alone a beer :) Kindest Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
New Fast MySQL Compatible Server
Hi, I created Parallel Universe which is a MySQL 5.5 compatible server with fast query execution. Speed is achieved by processing tables in parallel utilizing multi core/CPU server hardware. Pre-release field evaluation is being conducted and MySQL users are solicited to try out the new server for feedback. Please go to www.paralleluniverse-inc.com to download. Thank you, Hiromichi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: New Fast MySQL Compatible Server
The license of this software is unclear. The binary is clearly derived from the MySQL server with InnoDB, but no source code is provided. Is the software GPL-licensed? If so, where can I get a copy of the source code? - Baron On Tue, Mar 27, 2012 at 10:26 AM, Hiromichi Watari hiromichiwat...@yahoo.com wrote: Hi, I created Parallel Universe which is a MySQL 5.5 compatible server with fast query execution. Speed is achieved by processing tables in parallel utilizing multi core/CPU server hardware. Pre-release field evaluation is being conducted and MySQL users are solicited to try out the new server for feedback. Please go to www.paralleluniverse-inc.com to download. Thank you, Hiromichi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: New Fast MySQL Compatible Server
Thanks Baron. I downloaded it and found it was dervied from the MySQL server as well. I was looking for the source and found nothing. Mr. Watari, is there source code available? Since you are using GPL source code in your code, you must provide the source code. S On Tue, Mar 27, 2012 at 10:40, Baron Schwartz ba...@xaprb.com wrote: The license of this software is unclear. The binary is clearly derived from the MySQL server with InnoDB, but no source code is provided. Is the software GPL-licensed? If so, where can I get a copy of the source code? - Baron On Tue, Mar 27, 2012 at 10:26 AM, Hiromichi Watari hiromichiwat...@yahoo.com wrote: Hi, I created Parallel Universe which is a MySQL 5.5 compatible server with fast query execution. Speed is achieved by processing tables in parallel utilizing multi core/CPU server hardware. Pre-release field evaluation is being conducted and MySQL users are solicited to try out the new server for feedback. Please go to www.paralleluniverse-inc.com to download. Thank you, Hiromichi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Pythian proud winner of Oracle North America Titan Award for Exadata Solution...watch the video on pythian.com
Re: big character constant
2012/03/27 00:22 +0200, Walter Tross it looks like it's as simple as this: insert into mytable (mycolumn) values (0xE29C94), (0xE29C98) In some contexts it might be necessary to force the character set like this, though: convert(0xE29C94 using utf8) Yes! and with this I can use it for a constant in the middle of a query, which I really want: IF(IFNULL(ReceivesFlyer, 1), CONVERT(X'E29C94' USING utf8), '') . (I use the old PL1 constructs over the new C-constructs when they work alike.) Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqld got signal 6 (problem why bigger than I initially realised)
Do you have 12GB of RAM? Is this a 64-bit mysqld? Let's see SHOW VARIABLES LIKE '%buffer%'; SHOW GLOBAL STATUS LIKE 'Max%'; SHOW GLOBAL STATUS LIKE 'Up%'; What is Apache's MaxClients? On 3/27/12 6:25 AM, Brent Clark wrote: Good day KarthiK.P.R Other than the replication settings in '/etc/mysql/conf.d/replication.cnf' and our /etc/mysql/conf.d/custom.cnf xyz-web02:/data# cat /etc/mysql/conf.d/custom.cnf [mysqld] innodb_file_per_table bind-address = 0.0.0.0 datadir = /data binlog_format=mixed key_buffer_size=8384512 max_connections=41 The original /etc/mysql/my.cnf is untouched. The only other service we have running is DRBD (active / active) and apache, nothing is hitting apache as this is the standby node. And there is no load, or anything consuming resources. Thanks Brent On 27/03/2012 14:04, P.R.Karthik wrote: HI Brent, Can you please paste your configuration file settings (my.cnf) ? It will help to identify where things went wrong. Is there any other memory consuming application running on the server beyond mysql ? Regards, KarthiK.P.R On Tue, Mar 27, 2012 at 4:17 PM, Brent Clarkbrentgclarkl...@gmail.commailto:brentgclarkl...@gmail.com wrote: Hey Guys Yesterday I sent an email, about '1 client is using or hasn't closed the table properly', but the problem is actually bigger than I realised. We run Mysql replication, and on the second node, Mysql is crashing with 'mysqld got signal 6' every so often. Other than the drives, we have replaced the hardware, and still the problem persists. Googling, I thought it was a memory exhaustion issue, so I started playing with the following variables and values. key_buffer_size=8384512 max_connections=41 But it still crashes Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size=8384512 Mar 27 12:36:53 xyz-web02 mysqld: read_buffer_size=131072 Mar 27 12:36:53 xyz-web02 mysqld: max_used_connections=3 Mar 27 12:36:53 xyz-web02 mysqld: max_threads=41 Mar 27 12:36:53 xyz-web02 mysqld: thread_count=1 Mar 27 12:36:53 xyz-web02 mysqld: connection_count=1 Mar 27 12:36:53 xyz-web02 mysqld: It is possible that mysqld could use up to Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 97828 K bytes of memory xyz-web02:# free -m total used free sharedbuffers cached Mem: 12038 11966 71 0117 11378 -/+ buffers/cache:469 11568 Swap: 2047 0 2047 We running Debian Squeeze 64bit. I have attached the full crash message. If anyone can help, I would be *most* grateful (If you are in South Africa, I will buy you a meal, let alone a beer :) Kindest Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Rick James - MySQL Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: New Fast MySQL Compatible Server
Please elaborate on what conditions your server will/won't process queries in parallel. On 3/27/12 7:26 AM, Hiromichi Watari wrote: Hi, I created Parallel Universe which is a MySQL 5.5 compatible server with fast query execution. Speed is achieved by processing tables in parallel utilizing multi core/CPU server hardware. Pre-release field evaluation is being conducted and MySQL users are solicited to try out the new server for feedback. Please go to www.paralleluniverse-inc.com to download. Thank you, Hiromichi -- Rick James - MySQL Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
2012/3/27 Halász Sándor h...@tbbs.net: 2012/03/27 00:22 +0200, Walter Tross it looks like it's as simple as this: insert into mytable (mycolumn) values (0xE29C94), (0xE29C98) In some contexts it might be necessary to force the character set like this, though: convert(0xE29C94 using utf8) Yes! and with this I can use it for a constant in the middle of a query, which I really want: IF(IFNULL(ReceivesFlyer, 1), CONVERT(X'E29C94' USING utf8), '') This seems backwards from what I expected: mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): ✔ convert(0xE29C94 using utf8): ? 1 row in set (0.00 sec) I expected the second one to print the check mark and the first one to have the problem because there is no heavy check mark in latin1. What am I missing, why is it printing out the correct value using latin1 but not using utf8 ? ...Todd -- Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Martin Golding -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Group expansion as part of the result
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: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqld got signal 6 (problem why bigger than I initially realised)
Hi Rick Thank you for replying. Please see my answers to your questions. On Tue, Mar 27, 2012 at 7:58 PM, Rick James rja...@yahoo-inc.com wrote: Do you have 12GB of RAM? total used free sharedbuffers cached Mem: 12038 11959 78 0139 11234 -/+ buffers/cache:586 11451 Swap: 2047 0 2047 Is this a 64-bit mysqld? Linux hostname 2.6.32-5-amd64 #1 SMP Mon Jan 16 16:22:28 UTC 2012 x86_64 GNU/Linux Let's see SHOW VARIABLES LIKE '%buffer%'; +-+-+ | Variable_name | Value | +-+-+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072 | | key_buffer_size | 8384512 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size| 131072 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | sql_buffer_result | OFF | +-+-+ SHOW GLOBAL STATUS LIKE 'Max%'; +--+---+ | Variable_name| Value | +--+---+ | Max_used_connections | 3 | +--+---+ SHOW GLOBAL STATUS LIKE 'Up%'; +---+---+ | Variable_name | Value | +---+---+ | Uptime| 18492 | | Uptime_since_flush_status | 18492 | +---+---+ What is Apache's MaxClients? MaxClients 1000 Thank you again. Brent On 3/27/12 6:25 AM, Brent Clark wrote: Good day KarthiK.P.R Other than the replication settings in '/etc/mysql/conf.d/replication.cnf' and our /etc/mysql/conf.d/custom.cnf xyz-web02:/data# cat /etc/mysql/conf.d/custom.cnf [mysqld] innodb_file_per_table bind-address = 0.0.0.0 datadir = /data binlog_format=mixed key_buffer_size=8384512 max_connections=41 The original /etc/mysql/my.cnf is untouched. The only other service we have running is DRBD (active / active) and apache, nothing is hitting apache as this is the standby node. And there is no load, or anything consuming resources. Thanks Brent On 27/03/2012 14:04, P.R.Karthik wrote: HI Brent, Can you please paste your configuration file settings (my.cnf) ? It will help to identify where things went wrong. Is there any other memory consuming application running on the server beyond mysql ? Regards, KarthiK.P.R On Tue, Mar 27, 2012 at 4:17 PM, Brent Clarkbrentgclarkl...@gmail.commailto:brentgclarkl...@gmail.com wrote: Hey Guys Yesterday I sent an email, about '1 client is using or hasn't closed the table properly', but the problem is actually bigger than I realised. We run Mysql replication, and on the second node, Mysql is crashing with 'mysqld got signal 6' every so often. Other than the drives, we have replaced the hardware, and still the problem persists. Googling, I thought it was a memory exhaustion issue, so I started playing with the following variables and values. key_buffer_size=8384512 max_connections=41 But it still crashes Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size=8384512 Mar 27 12:36:53 xyz-web02 mysqld: read_buffer_size=131072 Mar 27 12:36:53 xyz-web02 mysqld: max_used_connections=3 Mar 27 12:36:53 xyz-web02 mysqld: max_threads=41 Mar 27 12:36:53 xyz-web02 mysqld: thread_count=1 Mar 27 12:36:53 xyz-web02 mysqld: connection_count=1 Mar 27 12:36:53 xyz-web02 mysqld: It is possible that mysqld could use up to Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 97828 K bytes of memory xyz-web02:# free -m total used free shared buffers cached Mem: 12038 11966 71 0 117 11378 -/+ buffers/cache: 469 11568 Swap: 2047 0 2047 We running Debian Squeeze 64bit. I have attached the full crash message. If anyone can help, I would be *most* grateful (If you are in South Africa, I will buy you a meal, let alone a beer :) Kindest Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Rick James - MySQL Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqld got signal 6 (problem why bigger than I initially realised)
MaxClients 1000 could overwhelm max_connections = 41 . Strongly recommend you decrease MaxClients to less than max_connections (not the other way around). Uptime| 18492 Not very long to see stuff. | innodb_buffer_pool_size | 8388608 | | key_buffer_size | 8384512 | Much too small for a 12GB machine. See http://mysql.rjweb.org/doc.php/memory for tuning advice. Back to signal 6 (Abort) -- was anything useful in mysqld.err ? On 3/27/12 2:06 PM, Brent Clark wrote: Hi Rick Thank you for replying. Please see my answers to your questions. On Tue, Mar 27, 2012 at 7:58 PM, Rick Jamesrja...@yahoo-inc.com wrote: Do you have 12GB of RAM? total used free sharedbuffers cached Mem: 12038 11959 78 0139 11234 -/+ buffers/cache:586 11451 Swap: 2047 0 2047 Is this a 64-bit mysqld? Linux hostname 2.6.32-5-amd64 #1 SMP Mon Jan 16 16:22:28 UTC 2012 x86_64 GNU/Linux Let's see SHOW VARIABLES LIKE '%buffer%'; +-+-+ | Variable_name | Value | +-+-+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072 | | key_buffer_size | 8384512 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size| 131072 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | sql_buffer_result | OFF | +-+-+ SHOW GLOBAL STATUS LIKE 'Max%'; +--+---+ | Variable_name| Value | +--+---+ | Max_used_connections | 3 | +--+---+ SHOW GLOBAL STATUS LIKE 'Up%'; +---+---+ | Variable_name | Value | +---+---+ | Uptime| 18492 | | Uptime_since_flush_status | 18492 | +---+---+ What is Apache's MaxClients? MaxClients 1000 Thank you again. Brent On 3/27/12 6:25 AM, Brent Clark wrote: Good day KarthiK.P.R Other than the replication settings in '/etc/mysql/conf.d/replication.cnf' and our /etc/mysql/conf.d/custom.cnf xyz-web02:/data# cat /etc/mysql/conf.d/custom.cnf [mysqld] innodb_file_per_table bind-address = 0.0.0.0 datadir = /data binlog_format=mixed key_buffer_size=8384512 max_connections=41 The original /etc/mysql/my.cnf is untouched. The only other service we have running is DRBD (active / active) and apache, nothing is hitting apache as this is the standby node. And there is no load, or anything consuming resources. Thanks Brent On 27/03/2012 14:04, P.R.Karthik wrote: HI Brent, Can you please paste your configuration file settings (my.cnf) ? It will help to identify where things went wrong. Is there any other memory consuming application running on the server beyond mysql ? Regards, KarthiK.P.R On Tue, Mar 27, 2012 at 4:17 PM, Brent Clarkbrentgclarkl...@gmail.commailto:brentgclarkl...@gmail.comwrote: Hey Guys Yesterday I sent an email, about '1 client is using or hasn't closed the table properly', but the problem is actually bigger than I realised. We run Mysql replication, and on the second node, Mysql is crashing with 'mysqld got signal 6' every so often. Other than the drives, we have replaced the hardware, and still the problem persists. Googling, I thought it was a memory exhaustion issue, so I started playing with the following variables and values. key_buffer_size=8384512 max_connections=41 But it still crashes Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size=8384512 Mar 27 12:36:53 xyz-web02 mysqld: read_buffer_size=131072 Mar 27 12:36:53 xyz-web02 mysqld: max_used_connections=3 Mar 27 12:36:53 xyz-web02 mysqld: max_threads=41 Mar 27 12:36:53 xyz-web02 mysqld: thread_count=1 Mar 27 12:36:53 xyz-web02 mysqld: connection_count=1 Mar 27 12:36:53 xyz-web02 mysqld: It is possible that mysqld could use up to Mar 27 12:36:53 xyz-web02 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 97828 K bytes of memory xyz-web02:# free -m total used free sharedbuffers cached Mem: 12038 11966 71 0117 11378 -/+ buffers/cache:469 11568 Swap: 2047 0 2047 We running Debian Squeeze 64bit. I have attached the full crash message. If anyone can help, I would be *most* grateful (If you are in South Africa, I will buy you a meal, let alone a beer :) Kindest Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: New Fast MySQL Compatible Server
Guys, really... Is this a binary release? Need to sudo to root? Not on my watch! On Tue, March 27, 2012 16:40, Baron Schwartz wrote: The license of this software is unclear. The binary is clearly derived from the MySQL server with InnoDB, but no source code is provided. Is the software GPL-licensed? If so, where can I get a copy of the source code? - Baron On Tue, Mar 27, 2012 at 10:26 AM, Hiromichi Watari hiromichiwat...@yahoo.com wrote: Hi, I created Parallel Universe which is a MySQL 5.5 compatible server with fast query execution. Speed is achieved by processing tables in parallel utilizing multi core/CPU server hardware. Pre-release field evaluation is being conducted and MySQL users are solicited to try out the new server for feedback. Please go to www.paralleluniverse-inc.com to download. Thank you, Hiromichi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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: mysqld got signal 6 (problem why bigger than I initially realised)
Am 27.03.2012 23:24, schrieb Rick James: MaxClients 1000 could overwhelm max_connections = 41 . Strongly recommend you decrease MaxClients to less than max_connections (not the other way around). totally wrong! not every http-connection implies a myql-connection a website typically contains a lot of static files like images, javascripts, stylesheets and you bring down your httpd if MaxClients is set too low and you are unable to serve waiting clients additionally if KeepAlive is used there are open httpd-connections with NO db-connection after serving a dynamic page using one out of MaxClients for a timewindow based on KeepAliveTimeout MaxClients on httpd should never be lower than max_connections max_connections is primary a question of RAM and typical load mysqltuner.pl: [--] Total buffers: 2.4G global + 3.2M per thread (200 max threads) [OK] Maximum possible memory usage: 3.0G (37% of installed RAM) _ however - 1000 is much too high if we are speak about a preforked server on most hardware - keep in mind that a worker process with mod_php consumes some MB of memory in a typical environment but this is all not mysql related signature.asc Description: OpenPGP digital signature
