DATE problem
Hy all I would like to get date in my web page. There are three different fields. YEAR MONTH and DAY I thought in SQL possible create table like this /*from my book*/ create table dates ( input_year DATETIME YEAR TO YEAR, input_month DATETIME MONTH TO MONTH, input_day DATETIME DAY TO DAY); In this example I can store year,month and day values in different fields. Mysql doesn't accept this create statement. Joe - Yahoo! Messenger NEW - crystal clear PC to PC calling worldwide with voicemail
R: how use sql_slave_skip_counter to restore slave replication
I already tried the option --slave-skip-errors, but i didn't work as expected. When this option is enabled, the slave didn't report the error message, but the slave is stopped due the error. The target I need to reech is to get two server A e B, each of them have its own db server and a set of application on top of it. Only one server should be active ( suppose A ), while on the other (B), an application detect that A is already running and configure at run-time B as slave. When A crash or for some other reason stop working, an application on B , which loose the communication with A, configure B as master. When A restart, it check B , and verify that B is the master, so it configure itself as slave. All these should work automatically, that is without manual operations. For this application the critical point is that one of this server must always be running. If I loose a little amount of data (mainly log data), this should be considered a little problem. What I have to resolve is the lots of duplicate data I receive. Also this is not clear, because I should undertand that if both the server for some reason works at the same time for a while but I can't undertand while the same error for the same keys happened so many times. It seems that somewhere remains this commands which are aplways resubmitted, also if I clear both master and slave. -Messaggio originale- Da: sheeri kritzer [mailto:[EMAIL PROTECTED] Inviato: lunedì 9 gennaio 2006 19.46 A: mysql@lists.mysql.com Oggetto: Re: how use sql_slave_skip_counter to restore slave replication (still catching up, sorry about the old issues) Don't know what version is being used here, but one thing you might want to use, if you're just going to skip the errors anyway (hopefully you understand WHY you're getting them), is to start replication with the --slave-skip-errors option -- you can skip only the errors you want or all errors. See: http://dev.mysql.com/doc/refman/5.1/en/replication-options.html I'd recommend getting replication up to speed with the slave-skip-errors option, then stopping the slave, taking out that option, so that new errors will show up. . I'm also going to guess that the original poster did not lock all tables for the duration of the backup, getting a *snapshot* backup of the database, and that's why the replication errors happened. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I keep character_set_client value after MyODBC auto reconnect?
Hi, did you try SET CHARACTER SET utf8 as Initial Statement under Connection Options of your MyODBC DSN? HTH, Dusan - Original Message - From: 古雷 [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, January 09, 2006 10:19 AM Subject: How can I keep character_set_client value after MyODBC auto reconnect? Hello: I found that MyODBC use mysql_ping to check connection and reconnect. After reconnect by using mysql_ping character_set_client, character_set_connection and character_set_results go back to latin1. But I need them to be utf8. What can I do with MyODBC? Regards, gu lei 祝 事业有成，家庭和睦，身体健康，一切吉祥 古雷 --- 中企动力科技集团 技术事业发展部___技术架构部 \__企业IP通讯部 电话：010 58022278-302 地址：北京亦庄经济技术开发区北工大软件园 （地盛北街1号）A区3号楼 邮编：100176 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Geographical advice
Hi, I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. I have been reading the Geographic Spacial extensions to MySQL. Which enable me to store the coordinates in a far more useful form. But do not seem to offer me the type of index I need. This must be a common problem, is there any person who can help me? Thanks in advance, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: how use sql_slave_skip_counter to restore slave replication
Hello. I already tried the option --slave-skip-errors, but i didn't work as expected. When this option is enabled, the slave didn't report the error message, but the slave is stopped due the error. May be server dies due an error message which was not included in the slave-skip-errors list? With all option server should run regardless of what happens. AESYS S.p.A. [Enzo Arlati] wrote: I already tried the option --slave-skip-errors, but i didn't work as expected. When this option is enabled, the slave didn't report the error message, but the slave is stopped due the error. The target I need to reech is to get two server A e B, each of them have its own db server and a set of application on top of it. Only one server should be active ( suppose A ), while on the other (B), an application detect that A is already running and configure at run-time B as slave. When A crash or for some other reason stop working, an application on B , which loose the communication with A, configure B as master. When A restart, it check B , and verify that B is the master, so it configure itself as slave. All these should work automatically, that is without manual operations. For this application the critical point is that one of this server must always be running. If I loose a little amount of data (mainly log data), this should be considered a little problem. What I have to resolve is the lots of duplicate data I receive. Also this is not clear, because I should undertand that if both the server for some reason works at the same time for a while but I can't undertand while the same error for the same keys happened so many times. It seems that somewhere remains this commands which are aplways resubmitted, also if I clear both master and slave. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE problem
Hello. MySQL doesn't support this syntax. See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html If you want to automatically extract the year (month, day) part from the inserted value, you may want to use TRIGGERS, however, in my opinion, it is better to redesign your table structure. You can use VIEWS to make the design of your database more flexible. See: http://dev.mysql.com/doc/refman/5.0/en/views.html http://dev.mysql.com/doc/refman/5.0/en/triggers.html Mester József wrote: Hy all I would like to get date in my web page. There are three different fields. YEAR MONTH and DAY I thought in SQL possible create table like this /*from my book*/ create table dates ( input_year DATETIME YEAR TO YEAR, input_month DATETIME MONTH TO MONTH, input_day DATETIME DAY TO DAY); In this example I can store year,month and day values in different fields. Mysql doesn't accept this create statement. Joe - Yahoo! Messenger NEW - crystal clear PC to PC calling worldwide with voicemail -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cannot connect to mysql
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/error-access-denied.html ghislain groulx wrote: im quite new to mysql and here is the problem. I simply cannot connect to mysql through phpmyadmin or anything else. Lets say i enter the url to connect (localhost/phpmyadmin/index.html) i end up in the welcome page but it says access denied for user [EMAIL PROTECTED] (using password: NO) when i do the phpinfo test, everything seems to be ok with the server (i run apache) and mysql and php are installed and running, but i simply cannot connect. i guess the problem is the username and password or something like in the configuration of mysql that but i cant figure it out. Can someone tell me what i am missing ? thanks a lot !! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections crashing MySQL?
Hello. Please, could you provide a resolved stack trace. I know sometimes, it is difficult in a heavy loaded production environment, but check if the problem still exists on the official binaries of the latest release. Have a look here as well: http://bugs.mysql.com/bug.php?id=15868 sheeri kritzer wrote: We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagno= se the problem, but since we have already crashed, something is definitely wro= ng and this may fail. key_buffer_size=3D335544320 read_buffer_size=3D131072 max_used_connections=3D2049 max_connections=3D2048 threads_connected=3D371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =3D 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir =3D /tmp/ datadir =3D /var/lib/mysql socket =3D /var/lib/mysql/mysql.sock port=3D 3306 key_buffer =3D 320M max_allowed_packet =3D 16M table_cache =3D 1024 thread_cache=3D 80 ft_min_word_len =3D 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size =3D 32M query_cache_type =3D 2 # Log queries taking longer than long_query_time seconds long_query_time =3D 4 log-slow-queries =3D /var/lib/mysql/slow-queries.log log-error =3D /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency =3D 12 interactive_timeout =3D 28800 wait_timeout =3D 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections =3D 2048 max_connect_errors =3D 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id =3D 15 max_binlog_size =3D 2G # InnoDB tables innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ innodb_buffer_pool_size =3D 4G innodb_additional_mem_pool_size =3D 40M innodb_log_file_size =3D 160M innodb_log_buffer_size =3D 80M innodb_flush_log_at_trx_commit =3D 0 innodb_lock_wait_timeout =3D 50 innodb_thread_concurrency =3D 8 innodb_file_io_threads =3D 4 ---= - Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to define utf8 function
Hello. This is still a bug. See: http://bugs.mysql.com/bug.php?id=16330 Hirofumi Fujiwara wrote: Hi, Hello. This should be fixed in 5.0.18. See: http://bugs.mysql.com/bug.php?id=13909 17.2.1. CREATE PROCEDURE and CREATE FUNCTION http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html says ... - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - As of MySQL 5.0.18, the server uses the data type of a routine parameter or function return value as follows. These rules also apply to local routine variables created with the DECLARE statement (Section 17.2.9.1, $B!H(BDECLARE Local Variables$B!I(B). * Assignments are checked for data type mismatches and overflow. Conversion and overflow problems result in warnings, or errors in strict mode. * For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation are used. (These are given by the values of the character_set_database and collation_database system variables.) * Only scalar values can be assigned to parameters or variables. For example, a statement such as SET x = (SELECT 1, 2) is invalid. Before MySQL 5.0.18, parameters, return values, and local variables are treated as items in expressions, and are subject to automatic (silent) conversion and truncation. Stored functions ignore the sql_mode setting. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I thought that this explanation means function controls CHARACTER SET clause properly from 5.0.18. So, I checked on version 5.0.18, but situation is same mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 5.0.18-standard-log | +-+ 1 row in set (0.01 sec) mysql SET NAMES utf8; Query OK, 0 rows affected (0.00 sec) ## I tried to make function tokyo() which returns string 'Tokyo' in ## Japanase. _utf8 X'E69DB1E4BAAC' means Tokyo in Japanese. mysql DELIMITER // mysql CREATE FUNCTION tokyo() RETURNS VARCHAR(20) CHARACTER SET utf8 - DETERMINISTIC RETURN _utf8 X'E69DB1E4BAAC'; - // Query OK, 0 rows affected (0.00 sec) mysql DELIMITER ; mysql SELECT tokyo(); +-+ | tokyo() | +-+ | ?? | +-+ 1 row in set, 1 warning (0.00 sec) mysql SELECT HEX(tokyo()); +--+ | HEX(tokyo()) | +--+ | 3F3F | +--+ 1 row in set, 1 warning (0.00 sec) mysql SELECT CHARSET(tokyo()); +--+ | CHARSET(tokyo()) | +--+ | binary | +--+ 1 row in set (0.00 sec) mysql SHOW CREATE FUNCTION tokyo\G *** 1. row *** Function: tokyo sql_mode: Create Function: CREATE FUNCTION `tokyo`() RETURNS varchar(20) DETERMINISTIC RETURN _utf8 X'E69DB1E4BAAC' 1 row in set (0.00 sec) mysql Returned charater type of function is binary. And, SHOW CREATE FUNCTION removed CHARACTER SET utf8 part. Why? Hirofumi Fujiwara[EMAIL PROTECTED], [EMAIL PROTECTED] Time Intermedia Corporationhttp://www.timedia.co.jp/ Corporate Strategy Department Knowledge Engineering Center 26-27 Saka-machi Shinjuku-ku, Tokyo 160-0002 Japan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing table
In a new setup I had the users table in MySQL. A few hours later I went to setup a new account and found out that the user table is missing. Is there a way to get it back? I can still login as root and myself. I wanted to add another user to the database. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to update record obtained from a query result?
Hi, Thanks for your help. Regards, Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geographical advice
If you visualise your search area as a circle around your 'target' coordinates, then you can eliminate many of the irrelevant rows by search for coordinates that fall within a square surrounding that circle. So, imagine a simple grid with target coordinates of 6,8 and a search radius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 pm + 10/1/06, Ben Clewett wrote: I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary table issue
Hi, all I am trying to use this with error: drop temporary tabel temp_a if exists 'temp_a'; it said syntax error. Could anybody tell me the right syntax? I didn't find the answer after googling a while. Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Geographical advice
James is right. I use this method on a table with a combined index on 50 million rows and it's almost instantaneous. Performance was vastly improved after I did an alter table order by x Andy -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: 10 January 2006 14:27 To: Ben Clewett Cc: mysql@lists.mysql.com Subject: Re: Geographical advice If you visualise your search area as a circle around your 'target' coordinates, then you can eliminate many of the irrelevant rows by search for coordinates that fall within a square surrounding that circle. So, imagine a simple grid with target coordinates of 6,8 and a search radius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 pm + 10/1/06, Ben Clewett wrote: I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
smime.p7s
Description: S/MIME Cryptographic Signature
Re: temporary table issue
DROP TEMPORARY TABLE IF EXISTS `temp_a`; Xiaobo Chen wrote: Hi, all I am trying to use this with error: drop temporary tabel temp_a if exists 'temp_a'; it said syntax error. Could anybody tell me the right syntax? I didn't find the answer after googling a while. Thanks in advance. Xiaobo -- Bill Dodson Parkline, Inc. http://www.parkline.com phone: 304-586-2113 x149 fax: 304-586-3842 email: [EMAIL PROTECTED] Email Disclaimer The information in any email is confidential and may be legally privileged. It is intended solely for the addressee. Access to the email message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. If you have received an email message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Hi, Jigal
Thanks a lot. It works.
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
--
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections crashing MySQL?
I would have provided a resolved stack trace if there was one referred to in the mysqld.err. I believe it's what Alex said: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB uses more memory than I have. To the MySQL folks, can the crash error be changed? I appreciate that it's in the docs, and I should know them back and forth of course, but when the crash error says that the total possible memory is key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size) that's actually wrong. Thanx for everyone's help! -Sheeri On 1/10/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Please, could you provide a resolved stack trace. I know sometimes, it is difficult in a heavy loaded production environment, but check if the problem still exists on the official binaries of the latest release. Have a look here as well: http://bugs.mysql.com/bug.php?id=15868 sheeri kritzer wrote: We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagno= se the problem, but since we have already crashed, something is definitely wro= ng and this may fail. key_buffer_size=3D335544320 read_buffer_size=3D131072 max_used_connections=3D2049 max_connections=3D2048 threads_connected=3D371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =3D 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir =3D /tmp/ datadir =3D /var/lib/mysql socket =3D /var/lib/mysql/mysql.sock port=3D 3306 key_buffer =3D 320M max_allowed_packet =3D 16M table_cache =3D 1024 thread_cache=3D 80 ft_min_word_len =3D 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size =3D 32M query_cache_type =3D 2 # Log queries taking longer than long_query_time seconds long_query_time =3D 4 log-slow-queries =3D /var/lib/mysql/slow-queries.log log-error =3D /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency =3D 12 interactive_timeout =3D 28800 wait_timeout =3D 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections =3D 2048 max_connect_errors =3D 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id =3D 15 max_binlog_size =3D 2G # InnoDB tables innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ innodb_buffer_pool_size =3D 4G innodb_additional_mem_pool_size =3D 40M innodb_log_file_size =3D 160M innodb_log_buffer_size =3D 80M innodb_flush_log_at_trx_commit =3D 0 innodb_lock_wait_timeout =3D 50 innodb_thread_concurrency =3D 8 innodb_file_io_threads =3D 4 ---= - Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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:
Thank you for your email support request
Thank you for your email support request. Our support team will respond with the recommended solution in the quickest time possible, but may be delayed due to the complexity of the problem or volume of requests for support. Once we have replied to your initial question, please be sure to use your e-mail's Reply feature to respond to Technical Support e-mails. By doing so, we keep the information from previous messages, which will assist the Technicians in resolving your problem in a timely manner. If this email is concerning WebDemo or WebInteractive you can call support at 800-200-2766 or you can go to the Live Support link located on the support page of www.linktivity.com. Please be at the machine that is having the problem. If this is a new install then connect from the server. If this email concerns serial numbers or verification keys the matter needs to be handled by our Customer Service Department. To expedite your request, please call them at 800-846-9726, as Technical Support does not respond to these requests. If you are an International Customer the request will be forwarded to our International Sales Representative to handle. Many answers can be found be searching our support site for Technical Notes, Bulletins, and FAQs. http://www.converging-technologies.com/ask.nsf/$$Search - Search Technical Notes http://www.converging-technologies.com/tsfaq.nsf - Search Bulletins, FAQs and more Thank you. Linktivity Technical Support
Stored procedure work badly with binlog
I found a problem using stored procedure and bin-log enabled.
Suppose I stored procedure like this:
==
DELIMITER $$;
DROP PROCEDURE IF EXISTS `pmv_manager`.`pAggiornaStatusNotificaPMV`$$
CREATE PROCEDURE `pAggiornaStatusNotificaPMV`( ipAddrPMV varchar(16),
ipAddrST varchar(16), ipAddrSNMP varchar(16))
BEGIN
declare ifound int default -1;
-- --
select ipAddrPMV, ipAddrST, ipAddrSNMP;
-- --
select count(ip_addr_pmv) into ifound from status_notifica_pmv
where ip_addr_pmv = ipAddrPMV;
if( ifound = 0 ) then
insert into status_notifica_pmv ( ip_addr_pmv, ip_addr_srv_st,
ip_addr_srv_snmp, dt_mod )
values( ipAddrPMV, ipAddrST , ipAddrSNMP,
current_timestamp );
else
update status_notifica_pmv
set ip_addr_srv_st = ipAddrST,
ip_addr_srv_snmp = ipAddrSNMP,
dt_mod = current_timestamp
where ip_addr_pmv = ipAddrPMV;
end if;
END$$
DELIMITER ;$$
==
where tablke status_notifica_pmv are defined as:
==
CREATE TABLE `status_notifica_pmv`
`ip_addr_pmv` varchar(16) NOT NULL,
`ip_addr_srv_st` varchar(16) default NULL,
`ip_addr_srv_snmp` varchar(16) default NULL,
`dt_mod` timestamp NULL default NULL,
PRIMARY KEY (`ip_addr_pmv`),
CONSTRAINT `status_notifica_pmv_ibfk_1` FOREIGN KEY
(`ip_addr_pmv`) REFERENCES `lista_pmv` (`IPAddress`) ON DELETE CASCADE ON
UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
==
if I call the procedure the task is performed as expected
it create a new record or modify it if present with the right value
==
call pAggiornaStatusNotificaPMV( '192.168.200.222', '192.168.200.218',
'192.168.200.218' );
- ipAddrPMVipAddrST ipAddrSNMP
--- --- ---
192.168.200.222 192.168.200.218 192.168.200.218
==
but if I look inside the binlog file I found corrupted data
==
pmv_manager_log_bin.011864 Query1 644
use `pmv_manager`; update status_notifica_pmv
set ip_addr_srv_st = NAME_CONST('ipAddrST',4îQîQDñQ$ú_
WRONG DATA
==
==
Maybe I wrong something or should be a bug ?
Enzo Arlati
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
checking for corruption?
After a power outage, we had a large table (~2GiB, 23e6 records) get corrupted. Only a few rows were actually affected, so nobody noticed for a few weeks though. A repair fixed the problem. We are now considering running the check table command regularly (every 15 minutes?) and plugging that into our monitoring system, i.e. if check table reports an error, our script beeps/emails someone. My question is this: is it okay to run check table frequently? What impact will it have on production systems? How quickly will it check very large tables? Is there an alternative (better) solution? thanks, Thomas -- N.J. Thomas [EMAIL PROTECTED] Etiamsi occiderit me, in ipso sperabo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Thanks for the excellent advise.
In the mean while a friend Googled me an article suggesting that this
can be done using the POINT and SPATIAL INDEX. But I prefer your ideas.
I'll show what I found because I want to see if anybody has an opinion
on problems with either method:
Here I Store coordinates as POINT data types. Adding a SPATIAL INDEX on
the field. I can then select a rectangle against the index to include
all points I want:
SELECT AsText(point_field) FROM ...
WHERE MBRContains(
GeomFromText(
'polygon((x0 y0,x1 y0,x1 y1,x0 y1,x0 y0))'
), point_field);
This does use the SPATIAL INDEX. Therefore if my subset of points is in
a massive table, this *should* be the most efficient way.
But the index does not seem nearly as fast as liner (normal) indexes.
Hence using a less effective liner index may be better...
After this method I still need select a radius within these data points,
more effort.
Another answer I got suggests a third method just to make things worse:
Divide the area into cells. Give each cell an ID. Store data against
that ID. A bit like a hash-table for 2D data. For the area you want,
calculate all the ID's you need and select against them. Then sort the
data afterwards. Select a size of cell to suit most effective queries.
I will have to benchmark...
Thanks for the help,
Ben
Jigal van Hemert wrote:
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
--
Ben Clewett
+44(0)1923 46
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Geographical advice
Sorry, subject corruption, my last email should have had this subject, not 'temporary table issue'... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Ben Clewett wrote: But the index does not seem nearly as fast as liner (normal) indexes. Hence using a less effective liner index may be better... After this method I still need select a radius within these data points, more effort. This could be an excellent case for using HAVING expr The WHERE can use an index and be very fast selecting the records that might fall within the circle. The HAVING clause will be executed after the record set is built and remove the records outside the circle, but within the square. If the number of records you select is pretty limited (a few hundred or so) the POW() calculations will not ask too much from the server, but the full table scan is avoided... Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: checking for corruption?
Hi , Alternate solution : Do show table status on that database . In comment field you can find info about corrupted table . This will be faster than check table . Thanks Praj - Original Message - From: N.J. Thomas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 10, 2006 9:00 PM Subject: checking for corruption? After a power outage, we had a large table (~2GiB, 23e6 records) get corrupted. Only a few rows were actually affected, so nobody noticed for a few weeks though. A repair fixed the problem. We are now considering running the check table command regularly (every 15 minutes?) and plugging that into our monitoring system, i.e. if check table reports an error, our script beeps/emails someone. My question is this: is it okay to run check table frequently? What impact will it have on production systems? How quickly will it check very large tables? Is there an alternative (better) solution? thanks, Thomas -- N.J. Thomas [EMAIL PROTECTED] Etiamsi occiderit me, in ipso sperabo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geogrphic Advise
Jigal, Thanks, I'll give this a go... Ben Jigal van Hemert wrote: Ben Clewett wrote: But the index does not seem nearly as fast as liner (normal) indexes. Hence using a less effective liner index may be better... After this method I still need select a radius within these data points, more effort. This could be an excellent case for using HAVING expr The WHERE can use an index and be very fast selecting the records that might fall within the circle. The HAVING clause will be executed after the record set is built and remove the records outside the circle, but within the square. If the number of records you select is pretty limited (a few hundred or so) the POW() calculations will not ask too much from the server, but the full table scan is avoided... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5 - disk bound ?
Hi All, I have a question for you all. Working with 2 innodb tables. One is a table of cdrs (call detail records) with 33 columns and 7 million + rows. Second table is a clone of the first, meant as a work table. From the mysql command line client, I gave the query : insert into table2 select * from table1 where time_epoch between xx and yy; (I did not do a BEGIN/COMMIT) Time_epoch is a numeric field which is indexed. This took 13+ minutes for 1,130,000 records. It seems to me that 13 minutes is a little high. During this time, a load data infile command into table1 (using begin/commit) was stalled out waiting for the the insert into table2 The load data infile command - 1110 This is a HP 1U server, dual P4 3 Gig, 4 GB of ram, optimized for disk IO. Hyperthreading is enabled. This is running suse 9.3, 2.6.11.4-20a-smp kernel. Raw .idb files : 11G comp_cdr.ibd(table1 above) 1.1Gtemp_comp_cdr.ibd (table2 above) Mysql is running with almost 2 GB ram, no swapping seems to be going on... Tasks: 65 total, 1 running, 64 sleeping, 0 stopped, 0 zombie Cpu(s): 8.7% us, 4.6% sy, 3.1% ni, 75.7% id, 7.9% wa, 0.0% hi, 0.0% si Mem: 3960896k total, 3845864k used, 115032k free,11260k buffers Swap: 4200956k total, 2764k used, 4198192k free, 1830060k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4515 mysql 16 0 2258m 1.9g 4296 S 99.9 49.1 1174:55 mysqld my.cnf: [mysqld] local-infile=1 bulk_insert_buffer_size = 512M big-tables port= 3306 socket = /tmp/mysql.sock max_connections = 200 skip-locking key_buffer = 512M max_allowed_packet = 16M table_cache = 2048 sort_buffer_size = 8M join_buffer_size = 8M read_buffer_size = 2M myisam_sort_buffer_size = 128M thread_cache = 32 query_cache_size = 96M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # 2 cpu x 2 (hyperthreading) x 2 tmp_table_size = 256M # Replication Master Server (default) # binary logging is required for replication #log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_log_arch_dir = /usr/local/mysql/data ## You can set .._buffer_pool_size up to 50 - 80 % ## of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 256M ## Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 150 #skip-innodb innodb_thread_concurrency = 8 innodb_file_per_table any help is appreciated :) TIA George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal number to binary and have the result be returned as a separate field for each bit? For example, what I'd like to do is, Select ConvertToBin(245); And have a result that looked like this +---+---+---+---+---+---+---+---+ | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 | +---+---+---+---+---+---+---+---+ | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 | +---+---+---+---+---+---+---+---+ - Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting decimal to binary
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
-MID(CONV('A5',16,2),2,1) AS `6`,
-MID(CONV('A5',16,2),3,1) AS `5`,
-MID(CONV('A5',16,2),4,1) AS `4`,
-MID(CONV('A5',16,2),5,1) AS `3`,
-MID(CONV('A5',16,2),6,1) AS `2`,
-MID(CONV('A5',16,2),7,1) AS `1`,
-MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting decimal to binary
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
-MID(CONV('A5',16,2),2,1) AS `6`,
-MID(CONV('A5',16,2),3,1) AS `5`,
-MID(CONV('A5',16,2),4,1) AS `4`,
-MID(CONV('A5',16,2),5,1) AS `3`,
-MID(CONV('A5',16,2),6,1) AS `2`,
-MID(CONV('A5',16,2),7,1) AS `1`,
-MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally privileged. It
is intended solely for the addressee. Access to the email message by anyone
else is unauthorized. If you are not the intended recipient, any disclosure,
copying, or distribution of the message, or any action or omission taken by you
in reliance on it, is prohibited and may be unlawful. If you have received an
email message in error, please notify the sender immediately by email,
facsimile or telephone and return and/or destroy the original message.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
localhost vs. 127.0.0.1 error: 'Lost connection to MySQL server during query'
OK here goes I've had a problem for a few days that I can't resolve. At the OS level If I ping the name localhost I get a good return and if I ping 127.0.0.1 I also get a good return as noted below. [EMAIL PROTECTED]:~# ping localhost -c 2 PING localhost (127.0.0.1) 56(84) bytes of data. 64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.034 ms 64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.028 ms --- localhost ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 999ms rtt min/avg/max/mdev = 0.028/0.031/0.034/0.003 ms [EMAIL PROTECTED]:~# ping 127.0.0.1 -c 2 PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data. 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.031 ms 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.029 ms --- 127.0.0.1 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 999ms rtt min/avg/max/mdev = 0.029/0.030/0.031/0.001 ms when I run the restart script I get the following in syslog Jan 10 15:35:51 www mysqld[2334]: 060110 15:35:51 [Note] /usr/sbin/mysqld: Normal shutdown Jan 10 15:35:51 www mysqld[2334]: Jan 10 15:35:51 www mysqld[2334]: 060110 15:35:51 InnoDB: Starting shutdown... Jan 10 15:35:54 www mysqld[2334]: 060110 15:35:54 InnoDB: Shutdown completed; log sequence number 0 43884 Jan 10 15:35:54 www mysqld[2334]: 060110 15:35:54 [Note] /usr/sbin/mysqld: Shutdown complete Jan 10 15:35:54 www mysqld[2334]: Jan 10 15:35:54 www mysqld_safe[2590]: ended Jan 10 15:35:55 www mysqld_safe[2649]: started Jan 10 15:35:55 www mysqld[2652]: 060110 15:35:55 InnoDB: Started; log sequence number 0 43884 Jan 10 15:35:55 www mysqld[2652]: /usr/sbin/mysqld: ready for connections. Jan 10 15:35:55 www mysqld[2652]: Version: ' 4.1.11-Debian_4sarge2-log' socket: '/var/run/mysqld/mysqld.sock' port: 43306 Source distribution All looks good so far here is my my.cnf file [EMAIL PROTECTED]:/etc/mysql# grep -v ^# my.cnf [client] port= 43306 socket= /var/run/mysqld/mysqld.sock [mysqld_safe] socket= /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket= /var/run/mysqld/mysqld.sock port= 43306 basedir= /usr datadir= /var/lib/mysql tmpdir= /tmp language= /usr/share/mysql/english skip-external-locking key_buffer= 16M max_allowed_packet= 16M thread_stack= 128K query_cache_limit= 1048576 query_cache_size= 16777216 query_cache_type= 1 log-bin= /var/log/mysql/mysql-bin.log max_binlog_size= 104857600 skip-bdb [mysqldump] quick quote-names max_allowed_packet= 16M [mysql] [isamchk] key_buffer= 16M here is the variables from mysqladmin -u root -p variables +- +--+ | Variable_name | Value | +-+--+ | back_log| 50 | | basedir | /usr/ | | bdb_cache_size | 8388600 | | bdb_home| | | bdb_log_buffer_size | 0 | | bdb_logdir | | | bdb_max_lock| 1 | | bdb_shared_data | OFF | | bdb_tmpdir | | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON
RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
-MID(CONV('A5',16,2),2,1) AS `6`,
-MID(CONV('A5',16,2),3,1) AS `5`,
-MID(CONV('A5',16,2),4,1) AS `4`,
-MID(CONV('A5',16,2),5,1) AS `3`,
-MID(CONV('A5',16,2),6,1) AS `2`,
-MID(CONV('A5',16,2),7,1) AS `1`,
-MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Well I solved the problem by using LPAD but it would be nice if there was a
more elegant way of handling this problem.
Thanks for the help.
Ed Reed [EMAIL PROTECTED] 1/10/06 2:52 PM
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.
Thank you.
--
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]
InnoDB per-table table spaces
Hi, My innodb installation is using per-table table spaces for every table on the system. I originally configured 4Gig for the shared table space. However when I do a show table status I see the following. Comment: InnoDB free: 6144 kB So 6Meg free, I assumed this was 6Meg free in the shared table space ? So anyway I added another 6Gig to the shared tablespace so we have a total of 10Gig. But this figure does not change. Is this correct behaviour ? To skip back a bit, the reason I did this was because on my slave database replication failed due to the innodb table being full, however if there is enough disk space on the system why would it do this if I am on per-table table spaces ? It should grow to what it likes. I don't have any 2 Gig file size limit. Any information on this would be great. Thanks. Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: InnoDB per-table table spaces
At 23:44 + 1/10/06, Marvin Wright wrote: Hi, My innodb installation is using per-table table spaces for every table on the system. I originally configured 4Gig for the shared table space. However when I do a show table status I see the following. Comment: InnoDB free: 6144 kB So 6Meg free, I assumed this was 6Meg free in the shared table space ? It's the free space for the tablespace that the table belongs to. See: http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 5 - disk bound ?
RE: mysql 5 - disk bound ?Replying to my own message. Both of these tables have several indexes. As mentioned before, My myisam_sort_buffer_size is fairly large : myisam_sort_buffer_size | 134217728 alter table xxx disable keys (But this table is INNODB) I tried alter table xx disable keys and then enable keys. On enable keys I get : | 3 | root | localhost | wholesale | Query | 810 | Repair by sorting | ALTER TABLE incomp_cdr ENABLE KEYS | 810 seconds as I mentioned before, much slower :( Anyone feedback on Mysql support contracts, I might be heading that way -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 10, 2006 1:14 PM To: mysql@lists.mysql.com Subject: mysql 5 - disk bound ? Hi All, I have a question for you all. Working with 2 innodb tables. One is a table of cdrs (call detail records) with 33 columns and 7 million + rows. Second table is a clone of the first, meant as a work table. From the mysql command line client, I gave the query : insert into table2 select * from table1 where time_epoch between xx and yy; (I did not do a BEGIN/COMMIT) Time_epoch is a numeric field which is indexed. This took 13+ minutes for 1,130,000 records. It seems to me that 13 minutes is a little high. During this time, a load data infile command into table1 (using begin/commit) was stalled out waiting for the the insert into table2 The load data infile command - 1110 This is a HP 1U server, dual P4 3 Gig, 4 GB of ram, optimized for disk IO. Hyperthreading is enabled. This is running suse 9.3, 2.6.11.4-20a-smp kernel. Raw .idb files : 11G comp_cdr.ibd(table1 above) 1.1Gtemp_comp_cdr.ibd (table2 above) Mysql is running with almost 2 GB ram, no swapping seems to be going on... Tasks: 65 total, 1 running, 64 sleeping, 0 stopped, 0 zombie Cpu(s): 8.7% us, 4.6% sy, 3.1% ni, 75.7% id, 7.9% wa, 0.0% hi, 0.0% si Mem: 3960896k total, 3845864k used, 115032k free,11260k buffers Swap: 4200956k total, 2764k used, 4198192k free, 1830060k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4515 mysql 16 0 2258m 1.9g 4296 S 99.9 49.1 1174:55 mysqld my.cnf: [mysqld] local-infile=1 bulk_insert_buffer_size = 512M big-tables port= 3306 socket = /tmp/mysql.sock max_connections = 200 skip-locking key_buffer = 512M max_allowed_packet = 16M table_cache = 2048 sort_buffer_size = 8M join_buffer_size = 8M read_buffer_size = 2M myisam_sort_buffer_size = 128M thread_cache = 32 query_cache_size = 96M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # 2 cpu x 2 (hyperthreading) x 2 tmp_table_size = 256M # Replication Master Server (default) # binary logging is required for replication #log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_log_arch_dir = /usr/local/mysql/data ## You can set .._buffer_pool_size up to 50 - 80 % ## of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 256M ## Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 150 #skip-innodb innodb_thread_concurrency = 8 innodb_file_per_table any help is appreciated :) TIA George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
And another one is (in inverse order for laziness):
select
(8 1) AS `0`
, (8 2 1) AS `1`
, (8 4 1) AS `2`
, (8 8 1) AS `3`
, (8 16 1) AS `4`
, (8 32 1) AS `5`
, (8 64 1) AS `6`
, (8 128 1) AS `7`
;
Ed Reed wrote:
Well I solved the problem by using LPAD but it would be nice if there was a
more elegant way of handling this problem.
Thanks for the help.
Ed Reed [EMAIL PROTECTED] 1/10/06 2:52 PM
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Francesco Riosa wrote: And another one is (in inverse order for laziness): select (8 1) AS `0` , (8 2 1) AS `1` , (8 4 1) AS `2` , (8 8 1) AS `3` , (8 16 1) AS `4` , (8 32 1) AS `5` , (8 64 1) AS `6` , (8 128 1) AS `7` ; but this one looks better: select (8 1) AS `0` , (8 1 1) AS `1` , (8 2 1) AS `2` , (8 3 1) AS `3` , (8 4 1) AS `4` , (8 5 1) AS `5` , (8 6 1) AS `6` , (8 7 1) AS `7` ; http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I keep character_set_client value after MyODBC auto reconnect?
Thanks a lot. I needs SET AUTOCOMMIT=0, SET NAMES utf8 and SET TRANSACTION ISOLATION LEVEL READ COMMITTED after a new connection. Further more, MyODBC3.51.10 reconnects automatically when connection lost but MyODBC3.51.12 doesn't. I think automatic reconnection is not bad when it can keep variables' value or can give me a chance to run more than one SQL statement to init my session. Another question: when will new verison of MyODBC3.5 be released? One of our application often disconnect and connect MySQL and then MyODBC often core dump. Best regards, gulei - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: 古雷 [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Tuesday, January 10, 2006 6:34 PM Subject: Re: How can I keep character_set_client value after MyODBC auto reconnect? Hi, did you try SET CHARACTER SET utf8 as Initial Statement under Connection Options of your MyODBC DSN? HTH, Dusan - Original Message - From: 古雷 [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, January 09, 2006 10:19 AM Subject: How can I keep character_set_client value after MyODBC auto reconnect? Hello: I found that MyODBC use mysql_ping to check connection and reconnect. After reconnect by using mysql_ping character_set_client, character_set_connection and character_set_results go back to latin1. But I need them to be utf8. What can I do with MyODBC? Regards, gu lei 祝 事业有成，家庭和睦，身体健康，一切吉祥 古雷 --- 中企动力科技集团 技术事业发展部___技术架构部 \__企业IP通讯部 电话：010 58022278-302 地址：北京亦庄经济技术开发区北工大软件园 （地盛北街1号）A区3号楼 邮编：100176
