Re: LEFT JOIN changes order of results
Hi Scott, I think you've been lucky so far... As you're only ordering on listdate, which is the same for both homes in your example you might definitely get different results once you put in the limit. A basic thing about rdb's is that you must never make assumptions that it returns resultsets in the same order unless you specify what to order by, of course quite often you'll get it back in the same order but you must never bank on it. I guess what happens in your case is that once you put in an extra LEFT JOIN your query gets optimised differently and tables are read in a different order which gives you a different reslutset. I guess you somehow have to include the mls_num in your second query to ensure that you get same resultset. /Johan Scott Gifford wrote: Hello, I have an application that does searches against a database of homes. A summary of initial search results is displayed by showing a few columns of information about each home. When the user clicks on one of the listings, it retrieves additional information (some from other tables) and displays more detailed information about the house. The summary listings are generated using a normal MySQL query. The detailed views are implemented by specifying which result row to display using a LIMIT clause. For example, if the user clicks on the 3rd listing on a page, the query will use this LIMIT clause: LIMIT 2,1 We do this instead of specifying a value for the primary key so we can have a Next and Previous button that will move between detailed listings. These result rows may pull information in from other tables for display. Sometimes the homes are sorted according to a particular column, and sometimes they aren't. Obviously this whole scheme depends on the homes staying in the same order between the summary queries and the detail queries, even if the ordering is ambiguous. We've had this running for several years, and it's always worked fine. We're now seeing some problems with it, possibly because of a move from a server running MySQL 3.x to one running 4.x. The problem we're seeing is that when additional tables are pulled in for the detailed view, the order is different from the summary view, so the wrong homes are displayed. Here's a simplified example. A summary query might ask: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++-++ | mls_num | num_images | address | listdate | +-++-++ | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | +-++-++ 1 row in set (0.00 sec) When I add one more LEFT JOIN clause (the second one below) to get additional fields for the detailed view, I get a different first home, even though none of the search parameters have changed, and the table hasn't changed: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = lutar_homes_stats.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++--++ | mls_num | num_images | address | listdate | +-++--++ | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | +-++--++ 1 row in set (0.02 sec) This change in ordering screws up my system, since if the user clicked on the first result in the summary view, the detailed view may display a completely different home. So my question is: Is this normal behavior for MySQL, or is it a bug? Any suggestions for dealing with it? Thanks! Please let me know if any of this is confusing, and I'll clarify. ---ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query - on long queries
Hello, Each time we run long queries (over around 5 seconds) the mySQL server fails and restarts (you can see it in the error log that starts with Database page corruption on disk or a fail.. and then there is a dump...) and the query return with Lost connection to MySQL server during query... Using Linux 9 with mySQL 4.1.11-standard the tables are InnoDB The error is happening from everywhere: mysql command line, Query browser, from local and remote computers, running selects, insert and even check table, EMS client . Java (see below the log) The problem is probably not in the network parameters, see list of the variables below The database is quit big around 5 GB After the error the server restart automatically and continue to run for the short queries We found one place where long queries work - when we run from the query browser the same select * from some big table. However running from the same place check table... reproduce the error !. Again running the same select * from some big table from all the other palaces cause the server to fall Amir Server parameters 'back_log', '50' 'basedir', '/' 'binlog_cache_size', '32768' 'bulk_insert_buffer_size', '8388608' 'character_set_client', 'utf8' 'character_set_connection', 'utf8' 'character_set_database', 'hebrew' 'character_set_results', 'utf8' 'character_set_server', 'hebrew' 'character_set_system', 'utf8' 'character_sets_dir', '/usr/share/mysql/charsets/' 'collation_connection', 'utf8_general_ci' 'collation_database', 'hebrew_general_ci' 'collation_server', 'hebrew_general_ci' 'concurrent_insert', 'ON' 'connect_timeout', '100' 'datadir', '/var/lib/mysql/' 'date_format', '%Y-%m-%d' 'datetime_format', '%Y-%m-%d %H:%i:%s' 'default_week_format', '0' 'delay_key_write', 'ON' 'delayed_insert_limit', '100' 'delayed_insert_timeout', '300' 'delayed_queue_size', '1000' 'expire_logs_days', '0' 'flush', 'OFF' 'flush_time', '0' 'ft_boolean_syntax', '+ -()~*:|' 'ft_max_word_len', '84' 'ft_min_word_len', '4' 'ft_query_expansion_limit', '20' 'ft_stopword_file', '(built-in)' 'group_concat_max_len', '1024' 'have_archive', 'NO' 'have_bdb', 'NO' 'have_blackhole_engine', 'NO' 'have_compress', 'YES' 'have_crypt', 'YES' 'have_csv', 'NO' 'have_example_engine', 'NO' 'have_geometry', 'YES' 'have_innodb', 'YES' 'have_isam', 'NO' 'have_ndbcluster', 'NO' 'have_openssl', 'NO' 'have_query_cache', 'YES' 'have_raid', 'NO' 'have_rtree_keys', 'YES' 'have_symlink', 'YES' 'init_connect', '' 'init_file', '' 'init_slave', '' 'innodb_additional_mem_pool_size', '14680064' 'innodb_autoextend_increment', '8' 'innodb_buffer_pool_awe_mem_mb', '0' 'innodb_buffer_pool_size', '1073741824' 'innodb_data_file_path', 'ibdata1:10M:autoextend' 'innodb_data_home_dir', '/var/lib/mysql' 'innodb_fast_shutdown', 'ON' 'innodb_file_io_threads', '4' 'innodb_file_per_table', 'OFF' 'innodb_flush_log_at_trx_commit', '1' 'innodb_flush_method', '' 'innodb_force_recovery', '0' 'innodb_lock_wait_timeout', '50' 'innodb_locks_unsafe_for_binlog', 'OFF' 'innodb_log_arch_dir', '' 'innodb_log_archive', 'OFF' 'innodb_log_buffer_size', '1048576' 'innodb_log_file_size', '5242880' 'innodb_log_files_in_group', '2' 'innodb_log_group_home_dir', './' 'innodb_max_dirty_pages_pct', '90' 'innodb_max_purge_lag', '0' 'innodb_mirrored_log_groups', '1' 'innodb_open_files', '300' 'innodb_table_locks', 'ON' 'innodb_thread_concurrency', '8' 'interactive_timeout', '100' 'join_buffer_size', '131072' 'key_buffer_size', '8388600' 'key_cache_age_threshold', '300' 'key_cache_block_size', '1024' 'key_cache_division_limit', '100' 'language', '/usr/share/mysql/english/' 'large_files_support', 'ON' 'license', 'GPL' 'local_infile', 'ON' 'locked_in_memory', 'OFF' 'long_query_time', '10' 'low_priority_updates', 'OFF' 'lower_case_file_system', 'OFF' 'lower_case_table_names', '1' 'max_allowed_packet', '1047552' 'max_binlog_cache_size', '4294967295' 'max_binlog_size', '1073741824' 'max_connect_errors', '10' 'max_connections', '100' 'max_delayed_threads', '20' 'max_error_count', '64' 'max_heap_table_size', '16777216' 'max_insert_delayed_threads', '20' 'max_join_size', '4294967295' 'max_length_for_sort_data', '1024' 'max_relay_log_size', '0' 'max_seeks_for_key', '4294967295' 'max_sort_length', '1024' 'max_tmp_tables', '32' 'max_user_connections', '0' 'max_write_lock_count', '4294967295' 'net_buffer_length', '16384' 'net_read_timeout', '30' 'net_retry_count', '10' 'net_write_timeout', '60' 'new', 'OFF' 'old_passwords', 'OFF' 'open_files_limit', '1024' 'pid_file', '/var/lib/mysql/mendelson.lotonet.local.pid' 'port', '3306' 'preload_buffer_size', '32768' 'protocol_version', '10' 'query_alloc_block_size', '8192' 'query_cache_limit', '67108864' 'query_cache_min_res_unit', '4096' 'query_cache_size', '67108864' 'query_cache_type', 'ON' 'query_cache_wlock_invalidate', 'OFF' 'query_prealloc_size', '8192' 'range_alloc_block_size', '2048' 'read_buffer_size', '131072' 'read_only', 'OFF' 'read_rnd_buffer_size', '262144' 'relay_log_purge',
Re: How do you become a MySQL DBA
Mark Ahlstrom wrote: Here's an odd question for you, how do you become a MySQL DBA? I've got enough Solaris/Linux Experience under my belt and I was a Jr. Oracle DBA for a year, which got me really interested in RDBMS. I try to work with MySQL as much as possible, but I work with one of those large telco's that does not like anything where they can't pay large amounts of money. This means I have to work it into my spare time. I've been trying to tie MySQL into basic services: ftp, DSPAM, pop3, AND offer help for what we do have: running backups and repairing the odd table when needed. But the question is, what else could I do to help develope DBA skills? Right now I have very little data that goes beyond 2 tables, so my query skills are withering. - DBA skills are more important than the MySQL skills. Two very useful books on the subject are AN INTRODUCTION TO DATABASE SYSTEMS and DATABASE IN DEPTH, both by CJ Date. For MySQL skills: there are plenty of books on the subject, and everyone no doubt has their favourite(s). Other than that: experience. You could try building yourself a MySQL database at home, for something like your book or DVD collection. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EXISTS
Hello everybody. I use mysql 4.0.24 as found in debian gnu/linux sarge. $ mysql -u root [...] mysql use mydb [...] Database changed mysql select * from SAMPLES S1 where exists (select * from SAMPLES S2 where S1.ID = S2.ID); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists (select * from SAMPLES S2 where S1.ID = S2.ID)' at line What's wrong with my query? Obviously the table SAMPLES and its column ID exist. TIA, Lucio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Advanced Data Generator 1.5.0 released
Dear ladies and gentlemen, Upscene Productions is happy to announce a new version of the database developer tool: Advanced Data Generator (version 1.5.0) A fast test-data generator tool that comes with a library of real-life data, can generate data to your database, SQL script or CSV files, many filling options, presets and much more. This new release consists of four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg Recent changes: V1.5.0 - - New: ability to use Stored Procedures as items to be used - New: ability to run custom (prepared) SQL statements instead of auto-generated statements - New: ability to generate INSERT INTO statements into file(s) - New: ability to generate CSV files - New: ability to run a project from the command line - New: ability to copy projects - New: projects can be saved with items not to be filled, but with settings for individual fields - Enhancement: better error reporting when things aren't alright for filling. - Enhancement: ADO connection detects MS SQL Server and executes SET IDENTITY INSERT if applicable. Cannot be done for ODBC. - Enhancement: when loading projects from file, you can merge them with the current project. - Enhancement: ability to delete all logs for a database - Enhancement: minor GUI enhancements - Enhancement: more diagnostic messages and better error reporting - Enhancement: updated documentation for new features - Fixed: IB/Fb: could not select connection characterset - Fixed: MySQL: fixed possible error with DELETE statement - Fixed: MySQL: Field 'Index_type' not found error for MySQL 4.0.low - Fixed: ODBC: escapes all identifiers, avoiding errors when SQL uses reserved words and spaces in names - Fixed: ODBC: Possible BLOB error fixed - Fixed: ODBC: error messages cut off at 255 characters - Fixed: ODBC: issue with N(VAR)CHAR columns fixed - Fixed: ADO: escapes all identifiers, avoiding errors when SQL uses reserved words and spaces in names - Fixed: Fixed item index error with using copy value from other column feature - Fixed: When using the German Streets data, a dialog would popup - Fixed: random length strings did not always honor the lower bound - Fixed: memory leak removed when closing Run Project window - Fixed: ADO/ODBC several other possible errors with regard to datatypes and parameters With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXISTS
On 31/05/2005, Lucio Crusca wrote: I use mysql 4.0.24 as found in debian gnu/linux sarge. mysql select * from SAMPLES S1 where exists (select * from SAMPLES S2 where S1.ID = S2.ID); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists (select * from SAMPLES S2 where S1.ID = S2.ID)' at line You need MySQL 4.1.x for subqueries. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN changes order of results
In article [EMAIL PROTECTED], Scott Gifford [EMAIL PROTECTED] writes: The problem we're seeing is that when additional tables are pulled in for the detailed view, the order is different from the summary view, so the wrong homes are displayed. Here's a simplified example. A summary query might ask: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++-++ | mls_num | num_images | address | listdate | +-++-++ | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | +-++-++ 1 row in set (0.00 sec) When I add one more LEFT JOIN clause (the second one below) to get additional fields for the detailed view, I get a different first home, even though none of the search parameters have changed, and the table hasn't changed: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = lutar_homes_stats.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++--++ | mls_num | num_images | address | listdate | +-++--++ | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | +-++--++ 1 row in set (0.02 sec) This change in ordering screws up my system, since if the user clicked on the first result in the summary view, the detailed view may display a completely different home. The ordering does not change. You told the syetem to order by listdate DESC, and that's exactly what the system does. Your problem is that the listdate ordering is not complete - there can be multiple homes with the same listdate. LIMIT is only stable if 1. the tables don't change and 2. the ordering is complete If the additional tables joined in don't generate multiple rows for one home, it's enough to add the home's primary key to your ORDER BY. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unable to start mysqld_multi
hi as per the online manual, i have configured mysql and able to run the server as mysqld_safe. but if i want to run mysqld_multi with the following configuration it doesnt work... the config file is like [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = mutli_admin password = admin_multi [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/var2/hostname.pid2 datadir= /usr/local/mysql/var2 user = amitg [mysqld3] socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/var3/hostname.pid3 datadir= /usr/local/mysql/var3 user = shann i am starting the server with the following command /usr/local/mysql/bin/mysqld_multi start this command doesnt throw any errors as such and the report i am getting my executing the command /usr/local/mysql/bin/mysqld_multi report is Reporting MySQL servers MySQL server from group: mysqld2 is not running MySQL server from group: mysqld3 is not running what am i doing wrong. please guide me regards shann ___ Have your own email and web address for life. http://www.homemaster.net - Homemaster. Come Together. Online. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ENCODE / DECODE
Hi, Is it possible to update all my encoded data with another password? Thanks for your advice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import dump (4.0 4.1) and collation problem
Hi, I recently upgraded from 4.0 to 4.1 because of the multilingual support. But importing the DB dump into the new 4.1 version all foreign characters (french, spanish, portoguese) have been replaced by ? character... I've been trying to understand the new collation feature but i seems quite difficult to setup. Anybody has already faced this problem ? Here's my current configuration: 1) my.cnf (only the mysqld section) [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 # Both location gets rotated by the cronjob. #log= /var/log/mysql.log log = /var/log/mysql/mysql.log basedir = /usr datadir = /db tmpdir = /tmp language= /usr/share/mysql/english 2) Output show variables like '%char%'; character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ 3) Out put show variables like '%coll%'; collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Thanks for any help Regards Roberto Jobet 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import dump (4.0 4.1) and collation problem
hi, your config supports european charcters : mysql select * from tst; +--+---+ | a| txt | +--+---+ |1 | Ceci est un test en Français | |1 | se facilitará el conocimiento de la evolución del | +--+---+ 2 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) have you tried some inserts to see if it is an import problem, or just the client display ? Mathias Selon [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi, I recently upgraded from 4.0 to 4.1 because of the multilingual support. But importing the DB dump into the new 4.1 version all foreign characters (french, spanish, portoguese) have been replaced by ? character... I've been trying to understand the new collation feature but i seems quite difficult to setup. Anybody has already faced this problem ? Here's my current configuration: 1) my.cnf (only the mysqld section) [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 # Both location gets rotated by the cronjob. #log= /var/log/mysql.log log = /var/log/mysql/mysql.log basedir = /usr datadir = /db tmpdir = /tmp language= /usr/share/mysql/english 2) Output show variables like '%char%'; character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ 3) Out put show variables like '%coll%'; collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Thanks for any help Regards Roberto Jobet 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- 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]
building php, using mysql for apache2
hi... trying to build php4 with mysql4.1-12, for use in apache2. i have the following ./compile that works for php5. however, when i try to use it for php4, i get a msg, stating that it can't find the MySQL Headers... can anybody provide any pointers as far as what the Headers are for mysql, and where they'd be located? if i knew the files, i could do a search to find where they're located. -- ./configure --with-apxs2=/usr/sbin/apxs --with-mysql=/usr/bin/mysql_config -- thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: building php, using mysql for apache2
hi, have you installed php4-mysql ? look at http://www.coagul.org/article.php3?id_article=169 for example. Mathias Selon bruce [EMAIL PROTECTED]: hi... trying to build php4 with mysql4.1-12, for use in apache2. i have the following ./compile that works for php5. however, when i try to use it for php4, i get a msg, stating that it can't find the MySQL Headers... can anybody provide any pointers as far as what the Headers are for mysql, and where they'd be located? if i knew the files, i could do a search to find where they're located. -- ./configure --with-apxs2=/usr/sbin/apxs --with-mysql=/usr/bin/mysql_config -- thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query - on long queries
Hi, Can you send some more output from the MySQL error log (normally located at /var/lib/mysq/[hostname].err) . Have you tried myisamchk to check the tables that are giving the errors? Also you might want to take a look at http://dev.mysql.com/doc/mysql/en/gone-away.html On Wednesday 01 June 2005 12:04, Amir Shay wrote: Hello, Each time we run long queries (over around 5 seconds) the mySQL server fails and restarts (you can see it in the error log that starts with Database page corruption on disk or a fail.. and then there is a dump...) and the query return with Lost connection to MySQL server during query... Using Linux 9 with mySQL 4.1.11-standard the tables are InnoDB The error is happening from everywhere: mysql command line, Query browser, from local and remote computers, running selects, insert and even check table, EMS client . Java (see below the log) The problem is probably not in the network parameters, see list of the variables below The database is quit big around 5 GB After the error the server restart automatically and continue to run for the short queries We found one place where long queries work - when we run from the query browser the same select * from some big table. However running from the same place check table... reproduce the error !. Again running the same select * from some big table from all the other palaces cause the server to fall Amir Server parameters 'back_log', '50' 'basedir', '/' 'binlog_cache_size', '32768' 'bulk_insert_buffer_size', '8388608' 'character_set_client', 'utf8' 'character_set_connection', 'utf8' 'character_set_database', 'hebrew' 'character_set_results', 'utf8' 'character_set_server', 'hebrew' 'character_set_system', 'utf8' 'character_sets_dir', '/usr/share/mysql/charsets/' 'collation_connection', 'utf8_general_ci' 'collation_database', 'hebrew_general_ci' 'collation_server', 'hebrew_general_ci' 'concurrent_insert', 'ON' 'connect_timeout', '100' 'datadir', '/var/lib/mysql/' 'date_format', '%Y-%m-%d' 'datetime_format', '%Y-%m-%d %H:%i:%s' 'default_week_format', '0' 'delay_key_write', 'ON' 'delayed_insert_limit', '100' 'delayed_insert_timeout', '300' 'delayed_queue_size', '1000' 'expire_logs_days', '0' 'flush', 'OFF' 'flush_time', '0' 'ft_boolean_syntax', '+ -()~*:|' 'ft_max_word_len', '84' 'ft_min_word_len', '4' 'ft_query_expansion_limit', '20' 'ft_stopword_file', '(built-in)' 'group_concat_max_len', '1024' 'have_archive', 'NO' 'have_bdb', 'NO' 'have_blackhole_engine', 'NO' 'have_compress', 'YES' 'have_crypt', 'YES' 'have_csv', 'NO' 'have_example_engine', 'NO' 'have_geometry', 'YES' 'have_innodb', 'YES' 'have_isam', 'NO' 'have_ndbcluster', 'NO' 'have_openssl', 'NO' 'have_query_cache', 'YES' 'have_raid', 'NO' 'have_rtree_keys', 'YES' 'have_symlink', 'YES' 'init_connect', '' 'init_file', '' 'init_slave', '' 'innodb_additional_mem_pool_size', '14680064' 'innodb_autoextend_increment', '8' 'innodb_buffer_pool_awe_mem_mb', '0' 'innodb_buffer_pool_size', '1073741824' 'innodb_data_file_path', 'ibdata1:10M:autoextend' 'innodb_data_home_dir', '/var/lib/mysql' 'innodb_fast_shutdown', 'ON' 'innodb_file_io_threads', '4' 'innodb_file_per_table', 'OFF' 'innodb_flush_log_at_trx_commit', '1' 'innodb_flush_method', '' 'innodb_force_recovery', '0' 'innodb_lock_wait_timeout', '50' 'innodb_locks_unsafe_for_binlog', 'OFF' 'innodb_log_arch_dir', '' 'innodb_log_archive', 'OFF' 'innodb_log_buffer_size', '1048576' 'innodb_log_file_size', '5242880' 'innodb_log_files_in_group', '2' 'innodb_log_group_home_dir', './' 'innodb_max_dirty_pages_pct', '90' 'innodb_max_purge_lag', '0' 'innodb_mirrored_log_groups', '1' 'innodb_open_files', '300' 'innodb_table_locks', 'ON' 'innodb_thread_concurrency', '8' 'interactive_timeout', '100' 'join_buffer_size', '131072' 'key_buffer_size', '8388600' 'key_cache_age_threshold', '300' 'key_cache_block_size', '1024' 'key_cache_division_limit', '100' 'language', '/usr/share/mysql/english/' 'large_files_support', 'ON' 'license', 'GPL' 'local_infile', 'ON' 'locked_in_memory', 'OFF' 'long_query_time', '10' 'low_priority_updates', 'OFF' 'lower_case_file_system', 'OFF' 'lower_case_table_names', '1' 'max_allowed_packet', '1047552' 'max_binlog_cache_size', '4294967295' 'max_binlog_size', '1073741824' 'max_connect_errors', '10' 'max_connections', '100' 'max_delayed_threads', '20' 'max_error_count', '64' 'max_heap_table_size', '16777216' 'max_insert_delayed_threads', '20' 'max_join_size', '4294967295' 'max_length_for_sort_data', '1024' 'max_relay_log_size', '0' 'max_seeks_for_key', '4294967295' 'max_sort_length', '1024' 'max_tmp_tables', '32' 'max_user_connections', '0' 'max_write_lock_count', '4294967295' 'net_buffer_length', '16384' 'net_read_timeout', '30' 'net_retry_count', '10' 'net_write_timeout', '60' 'new', 'OFF' 'old_passwords', 'OFF' 'open_files_limit', '1024' 'pid_file',
Re: Import dump (4.0 4.1) and collation problem
Salut, je vois que tu es français, j'aimerai bien continuer en français mais il vaut mieux en anglais si jamais il y avait quelqu'un avec le même problème ;-) So what I'm trying to achieve is to have UTF-8 databases in order to support multilingual content. But first I have to understand how to recover foreign characters in my DB. What do you mean by: try some inserts to see if it is an import problem or just the client display ? Merci d'avance Roberto Jobet hi, your config supports european characters : mysql select * from tst; +--+---+ | a| txt | +--+---+ |1 | Ceci est un test en Français | |1 | se facilitará el conocimiento de la evolución del | +--+---+ 2 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) have you tried some inserts to see if it is an import problem, or just the client display ? Mathias Selon [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi, I recently upgraded from 4.0 to 4.1 because of the multilingual support. But importing the DB dump into the new 4.1 version all foreign characters (french, spanish, portoguese) have been replaced by ? character... I've been trying to understand the new collation feature but i seems quite difficult to setup. Anybody has already faced this problem ? Here's my current configuration: 1) my.cnf (only the mysqld section) [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 # Both location gets rotated by the cronjob. #log= /var/log/mysql.log log = /var/log/mysql/mysql.log basedir = /usr datadir = /db tmpdir = /tmp language= /usr/share/mysql/english 2) Output show variables like '%char%'; character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ 3) Out put show variables like '%coll%'; collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Thanks for any help Regards Roberto Jobet 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
First In First Out stock calculation query
Hi,
Here is the table in question.
drop table `portfolio1`;
CREATE TABLE `portfolio1` (
`ccode` varchar(99) default NULL,
`symbol` varchar(99) default NULL,
`transaction_date` date default NULL,
`rate` decimal(11,2) default NULL,
`quantity` int(11) default NULL,
`transaction` enum('buy','sell') default NULL
) TYPE=MyISAM;
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-09-02', '345.92',
'350', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2003-03-25', '39.90',
'1000', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-12-05', '500.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2003-12-05', '900.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-05', '67.00',
'200', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-09', '87.00',
'500', 'sell');
query I know...
SELECT
sub1.ccode,
sub1.symbol,
SUM(sub1.quantity_buy) buy,
SUM(sub1.quantity_sell) sell,
(SUM(sub1.quantity_buy) - SUM(sub1.quantity_sell)) stock,
MAX(sub1.transaction_date) transaction_date
FROM
(
SELECT ccode, symbol, quantity quantity_buy, 0 quantity_sell, transaction_date
FROM `portfolio1` p
WHERE transaction = 'buy'
union all
SELECT ccode, symbol, 0, quantity quantity_sell, ''
FROM `portfolio1` p
WHERE transaction = 'sell'
) sub1
GROUP BY
sub1.ccode, sub1.symbol;
The results are as per my expectations.
ccode symbol buy sell stock transaction_date
soak A 300 275 25 2003-09-02
soak D 900 400 500 2003-03-25
But I do also want to know the corresponding quantity bought on that
particular transaction_date.
ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
and if it is possible, the second last entry if the stock bought like this...
ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
soak | D | 900 | 400 | 500 | 2002-02-25 | 100
soak | D | 900 | 400 | 500 | 2001-03-25 | 100
Note: even if 500 quantity was bought on 2001-03-25 I want to display only 100
in the bought column above because 400 units have already been sold off.
Thanks
Shantanu Oak
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import dump (4.0 4.1) and collation problem
i mean :
if you launch :
into into table (toto) values ('Ceci est un test en Français');
then :
select toto from table;
can you read it correctly ? And if the same row is imported, is there any
difference ?
A bientôt
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Salut,
je vois que tu es français, j'aimerai bien continuer en français mais il vaut
mieux en anglais si jamais il y avait quelqu'un avec le même problème ;-)
So what I'm trying to achieve is to have UTF-8 databases in order to support
multilingual content.
But first I have to understand how to recover foreign characters in my DB.
What do you mean by: try some inserts to see if it is an import problem or
just the client display ?
Merci d'avance
Roberto Jobet
hi,
your config supports european characters :
mysql select * from tst;
+--+---+
| a| txt |
+--+---+
|1 | Ceci est un test en Français |
|1 | se facilitará el conocimiento de la evolución del |
+--+---+
2 rows in set (0.00 sec)
mysql show variables like '%coll%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)
have you tried some inserts to see if it is an import problem, or just the
client display ?
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Hi,
I recently upgraded from 4.0 to 4.1 because of the multilingual support.
But importing the DB dump into the new 4.1 version all foreign characters
(french, spanish, portoguese) have been replaced by ? character...
I've been trying to understand the new collation feature but i seems
quite
difficult to setup.
Anybody has already faced this problem ?
Here's my current configuration:
1) my.cnf (only the mysqld section)
[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port= 3306
# Both location gets rotated by the cronjob.
#log= /var/log/mysql.log
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /db
tmpdir = /tmp
language= /usr/share/mysql/english
2) Output show variables like '%char%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
3) Out put show variables like '%coll%';
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
Thanks for any help
Regards
Roberto Jobet
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
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]
change field to auto increment
Hey list, I need a query that checks if a certain table column has type auto-increment If not set it to auto increment, else do nothing. Let's say I have the table objects (object_id, object_name) in which object_id is the primary field. Now check to see if object_id is auto_increment and if not set it auto_increment Can anyone help me on this? I've been looking into the manual but I can't get it right: Alter table objects change object_id type auto_increment Thx in advance, Reinhart
DBD::Oracle issue
ALL, Can someone help out with this error: DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: error possibly near * indicator at char 23 in 'select table_name from *all_tables') [for Statement select table_name from all_tables] at ./oracleTest.pl line 66. I have tried to set TWO_TASK environment variable to no avail. The oracle connection is a remote connection made. I have successfully looked up other information in the database with this script. The actual SQL command being run is select table_name from all_tables. If anyone needs the actual script that is being run please mail me. But I believe this error is specific to the select statement being run because other selects work just fine. ANY ideas / suggestions would be greatly appreciated, Chris Hood
RE: MySQL 5.0.6-beta has been released
Hi, I downloaded and tried to install from the sources. ./configure, make seem ok, however the make install exit very quickly with an error code 127 (perror gives record-file is crashed, no idea of what that means!). My old 5.0.4 istall is working fine, so I don't think the system is in cause,but in case of it's a mandriva cooker. Anybody has any clue? Thanks Melanie From: Matt Wagner [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: MySQL 5.0.6-beta has been released Date: Tue, 31 May 2005 18:01:48 -0500 Hi, A new version of MySQL Community Edition 5.0.6-beta Open Source database management system has been released. This version includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the third published Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual open and resolved bugs in this version. Changes in release 5.0.6: Functionality added or changed: * INCOMPATIBLE CHANGE: `MyISAM' and `InnoDB' tables created with `DECIMAL' columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump such tables with `mysqldump' before upgrading, and then reload them after upgrading. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (Bug #10465, Bug #10625) * Added `REFERENCED_TABLE_SCHEMA', `REFERENCED_TABLE_NAME', and `REFERENCED_COLUMN_NAME' columns to the `KEY_COLUMN_USAGE' table of `INFORMATION_SCHEMA'. (Bug #9587) * Added a `--show-warnings' option to `mysql' to cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode. In interactive mode, `\w' and `\W' may be used to enable and disable warning display. (Bug #8684) * Removed a limitation that prevented use of FIFOs as logging targets (such as for the general query log). This modification _does not apply_ to the binary log and the relay log. (Bug #8271) * Added a `--debug' option to `my_print_defaults'. * When the server cannot read a table because it cannot read the `.frm' file, print a message that the table was created with a different version of MySQL. (This can happen if you create tables that use new features and then downgrade to an older version of MySQL.) (Bug #10435) * `SHOW VARIABLES' now shows the `slave_compresed_protocol', `slave_load_tmpdir' and `slave_skip_errors' system variables. (Bug #7800) * Removed unused system variable `myisam_max_extra_sort_file_size'. * Changed default value of `myisam_data_pointer_size' from 4 to 6. This allows us to avoid `table is full' errors for most cases. * The variable `concurrent_insert' now takes 3 values. Setting this to 2 changes MyISAM to do concurrent inserts to end of table if table is in use by another thread. * New `/*' prompt for `mysql'. This prompt indicates that a `/* ... */' comment was begun on an earlier line and the closing `*/' sequence has not yet been seen. (Bug #9186) * If strict SQL mode is enabled, `VARCHAR' and `VARBINARY' columns with a length greater than 65,535 no longer are silently converted to `TEXT' or `BLOB' columns. Instead, an error occurs. (Bug #8295, Bug #8296) * The `INFORMATION_SCHEMA.SCHEMATA' table now has a `DEFAULT_COLLATION_NAME' column. (Bug #8998) * `InnoDB': When the maximum length of `SHOW INNODB STATUS' output would be exceeded, truncate the beginning of the list of active transactions, instead of truncating the end of the output. (Bug #5436) * `InnoDB': If `innodb_locks_unsafe_for_binlog' option is set and the isolation level of the transaction is not set to serializable then `InnoDB' uses a consistent read for select in clauses like `INSERT INTO ... SELECT' and `UPDATE ... (SELECT)' that do not specify `FOR UPDATE' or `IN
Re: DBD::Oracle issue
Hi,
I'll try even if this is not a neither oracle nor perl-dbd list.
1. verify that NLS_LANG is correct in your client env (and all the other ORA
vars)
2. verify the query is simply quoted ( needs \_ for _)
3. Put all oracle variables in BEGIN {ORACLe_HOME=}; in the perl script
4. verify that select * from dual is OK
Mathias
Selon [EMAIL PROTECTED]:
ALL,
Can someone help out with this error:
DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication
protocol error (DBD ERROR: error possibly near * indicator at char 23 in
'select table_name from *all_tables') [for Statement select table_name
from all_tables] at ./oracleTest.pl line 66.
I have tried to set TWO_TASK environment variable to no avail.
The oracle connection is a remote connection made.
I have successfully looked up other information in the database with this
script.
The actual SQL command being run is select table_name from all_tables.
If anyone needs the actual script that is being run please mail me. But I
believe this error is specific to the select statement being run because
other selects work just fine.
ANY ideas / suggestions would be greatly appreciated,
Chris Hood
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import dump (4.0 4.1) and collation problem
Voilà,
Everything works fine: insert, select and importing. I have always the same
text:
'Ceci est un test en Français'
So what could be the problem ? and how to recover foreign characters ?
Thanks
Roberto Jobet
i mean :
if you launch :
into into table (toto) values ('Ceci est un test en Français');
then :
select toto from table;
can you read it correctly ? And if the same row is imported, is there any
difference ?
A bientôt
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Salut,
je vois que tu es français, j'aimerai bien continuer en français mais il
vaut
mieux en anglais si jamais il y avait quelqu'un avec le même problème ;-)
So what I'm trying to achieve is to have UTF-8 databases in order to support
multilingual content.
But first I have to understand how to recover foreign characters in my DB.
What do you mean by: try some inserts to see if it is an import problem
or
just the client display ?
Merci d'avance
Roberto Jobet
hi,
your config supports european characters :
mysql select * from tst;
+--+---+
| a| txt |
+--+---+
|1 | Ceci est un test en Français |
|1 | se facilitará el conocimiento de la evolución del |
+--+---+
2 rows in set (0.00 sec)
mysql show variables like '%coll%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)
have you tried some inserts to see if it is an import problem, or just the
client display ?
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Hi,
I recently upgraded from 4.0 to 4.1 because of the multilingual support.
But importing the DB dump into the new 4.1 version all foreign
characters
(french, spanish, portoguese) have been replaced by ? character...
I've been trying to understand the new collation feature but i seems
quite
difficult to setup.
Anybody has already faced this problem ?
Here's my current configuration:
1) my.cnf (only the mysqld section)
[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port= 3306
# Both location gets rotated by the cronjob.
#log= /var/log/mysql.log
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /db
tmpdir = /tmp
language= /usr/share/mysql/english
2) Output show variables like '%char%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
3) Out put show variables like '%coll%';
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
Thanks for any help
Regards
Roberto Jobet
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 06:57:19
PM:
-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't completely inclusive...it would miss FIELD-1...
-- However, It seems to me that this kind of data manipulation
(cleanup) needs to happen BEFORE the data enters the databse.
Sometimes FIELD-1 is the ACTUAL data, with no erroneus
characters...and sometimes it is FIELD1 with an erroneous (or
unwanted) - character before the 1)...so cleaning the data would
actually be corrupting some of it.
I need to keep the data in it's original form, but also allow for
querying without worrying about the special characters inside the
column.
FYI, these are part numbers off of electronic components, many of them
coming from China...so, a Cisco part may have an MPN of RX321, or
RX321-TR...either is valid. Now, the corresponding Chinese part
number for the first one, may come back as RX32-1...which is out of my
control.
Cleaning the data would be the wrong approach, because it would
actually invalidate the second Cisco part number, which MEANT to
include the special characters.
For this reason, the user wants to be able to search for 'RX321' and
'RX321TR' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
04:38:40
PM:
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first
one,
'field2' returns the second, etc...basically disregard all
non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a
function. I
do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW,
why
*do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored
procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and
thestring is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the
string
char by char and build a new one, it's just less elegant. Thanks
in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
Thank you very much. I find this whole padding process very
counterintuitive. I have a few minor questions, if you don't mind. How
is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you
please
explain the theory behind why and when this kind of padding should be
done?
What problem does it solve and how is it a solution to that problem?
This
is completely baffling to me and I thought I had seen a lot of weird
data
before :-)
However, It seems to me that this kind of data manipulation (cleanup)
needs
to happen BEFORE the data enters the databse. What data import
tool/process
is your client using? Can you not change the import process to scrub
the
data and does it not have a better facility to interleave padding into
a
string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
I would suggest the following technique. Keep your original data (trimmed
of leading and trailing whitespace characters) in one field and a
cleansed version of the same information in a second field. By cleansing
you should eliminate all non-alphanumerics from your search string.
I have some direct insight to searching similar but different product
codes (same product/different codes different products/same codes). My
wife works for a company that consolidates medical inventories for major
hospital and health care organizations and there is usually more than one
vendor to the same product. This leads to the exact same
RE: Database design query
I think I have found the solution for my problem. I made the following changes: - I added a new field RecordID in GroupMemberInfo to make the records unique - Instead of MemberID and GroupID, I'm now using MemberName and GroupName. I made this change since in Active Directory every name is unique. - What I found out that in mysql, a FK field can refer to any index field in parent table and not necessarily only Primary Key field. - So instead of making MemberID and GroupID as primarykey, RecordID is primary key now and MemberID is just an index. I don't know if it's a bug in Mysql or it's an added feature that a FK field can refer to any index field in parent table. CREATE TABLE `groupinfo` ( `GroupID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberCount` int(11) default NULL, PRIMARY KEY (`GroupID`), UNIQUE KEY `i_GroupName` TYPE BTREE (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `groupmemberinfo` ( `RecordID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberName` varchar(128) NOT NULL default '', `MemberType` tinyint(4) NOT NULL default '0', PRIMARY KEY (`RecordID`), KEY `i_MemberName` TYPE HASH (`MemberName`), CONSTRAINT `FK_groupmemberinfo_GroupName` FOREIGN KEY (`GroupName`) REFERENCES `groupinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `hostinfo` ( `HostID` bigint(20) NOT NULL auto_increment, `HostName` varchar(128) NOT NULL default '', `Password` tinyblob NOT NULL default '', PRIMARY KEY (`HostID`), KEY `i_HostName` (`HostName`), CONSTRAINT `FK_hostinfo_HostName` FOREIGN KEY (`HostName`) REFERENCES `groupmemberinfo` (`MemberName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `userinfo` ( `UserID` bigint(20) NOT NULL default '0', `UserName` varchar(128) default NULL, `Password` tinyblob, PRIMARY KEY (`UserID`), KEY `i_UserName` (`UserName`), CONSTRAINT `FK_userinfo_UserName` FOREIGN KEY (`UserName`) REFERENCES `groupmemberinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I know for sure that this is not the best solution. But now both User and Host are referring to GroupMemberInfo. So I have constraints at DB level Thanks to all who replied, Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 01, 2005 8:39 AM To: 'rtroiana' Subject: RE: Database design query I know what you are trying to do and I can see the logic advantage of having a single table that defines the the group relationship for users hosts and groups. I just don't think the rules governing foreign keys will allow this. Your original thought of enforcing the relationships i.e. cascade delete/update etc. at the application vs the database is the only way I can see to make this happen. Otherwise you are back to three tables with the added code to find all members of a group across the 3 tables. I don't know that I've added much, but I enjoyed the dialog. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 4:23 PM To: 'Gordon' Subject: RE: Database design query I can have three different foreign key definitions on one field, but it expects the same value in all the three parent tables. So that's not the right way to implement it What I'm trying to do is: Member id as primary key and UserID, HostID and groupID as foreign keys But since in GroupMember table a member can be in more than 1 group, so I have combination of MemberID, GroupId and MemberType as primary key If I use these 3 as primary key, so UserID, HostID and groupID can't refer MemberID as primary key. That's what the problem is. ++Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 5:14 PM To: 'rtroiana'; mysql@lists.mysql.com Subject: RE: Database design query IF GroupID, HostID and UserID are unique between the three sets then your GroupMember table will work although I would still be tempted to add a MemberType in the GroupMember table. Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know if you can have three different foreign key definitions on one field. If not I think you are stuck with 3 tables instead of trying to do it in one. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 12:23 PM To: mysql@lists.mysql.com Subject: Database design query Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement
Re: ENCODE / DECODE
Is it possible to update all my encoded data with another password? Yes. Example for the encoded/decode functions: UPDATE table SET pass_colum = encode(decode(pass_column, old_password), new_password); -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table full on mysql-cluster
We have the following problem. Cluster means table 'TABLENAME' is full We have 11076890 rows in this table. Where is the limit defined ? Disk are Not full. RAM not full too. Table engine is NDBCLUSTER. Can anybody help ? --- Powered by: T-Systems Multimedia Solutions BlackBerry Enterprise Server
Find the biggest blobs
Hi! I've need to find the largest blobs in a table but I seem not to be able to figure out what it is. Could anyone please help me with this? Regards Roland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBD::Oracle issue
Wrong forum for MySQL but you're getting the error because unless you created a view/table for all_tables then it doesn't exist. I suspect you're trying to use the view USER_TABLES or DBA_TABLES (if you have the GRANT to view that DBA view. [EMAIL PROTECTED] wrote: ALL, Can someone help out with this error: DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: error possibly near * indicator at char 23 in 'select table_name from *all_tables') [for Statement select table_name from all_tables] at ./oracleTest.pl line 66. I have tried to set TWO_TASK environment variable to no avail. The oracle connection is a remote connection made. I have successfully looked up other information in the database with this script. The actual SQL command being run is select table_name from all_tables. If anyone needs the actual script that is being run please mail me. But I believe this error is specific to the select statement being run because other selects work just fine. ANY ideas / suggestions would be greatly appreciated, Chris Hood __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXISTS
You can use joins instead of subqueries if you cannot upgrade to 4.1.x of mysql. Actually in my opinion joins are more efficient than subqueries. Subqueries (more often than not) tend to become bottlenecks in the long run.. http://dev.mysql.com has a some reference material about converting subqueries to joins hope that helps, Anoop On 1 Jun 2005 08:37:03 -, Felix Geerinckx [EMAIL PROTECTED] wrote: On 31/05/2005, Lucio Crusca wrote: I use mysql 4.0.24 as found in debian gnu/linux sarge. mysql select * from SAMPLES S1 where exists (select * from SAMPLES S2 where S1.ID http://S1.ID = S2.ID http://S2.ID); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists (select * from SAMPLES S2 where S1.ID http://S1.ID = S2.ID http://S2.ID)' at line You need MySQL 4.1.x for subqueries. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop
Re: change field to auto increment
Reinhart Viane wrote: Hey list, I need a query that checks if a certain table column has type auto-increment If not set it to auto increment, else do nothing. Let's say I have the table objects (object_id, object_name) in which object_id is the primary field. Now check to see if object_id is auto_increment and if not set it auto_increment Can anyone help me on this? I've been looking into the manual but I can't get it right: Alter table objects change object_id type auto_increment Thx in advance, Reinhart You can find out if objects.object_id is set to AUTO_INCREMENT in a couple of ways. Use DESCRIBE tablename http://dev.mysql.com/doc/mysql/en/describe.html to see info about all the columns: mysql DESCRIBE objects; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | | PRI | NULL| auto_increment | | name | char(30) | YES | MUL | NULL|| +---+--+--+-+-++ 2 rows in set (0.00 sec) or use DESCRIBE tablename colname to see info about a specific column: mysql DESCRIBE objects id; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | | PRI | NULL| auto_increment | +---+--+--+-+-++ 1 row in set (0.01 sec) or use SHOW CREATE TABLE tablename http://dev.mysql.com/doc/mysql/en/show-create-table.html: mysql SHOW CREATE TABLE objects\G *** 1. row *** Table: objects Create Table: CREATE TABLE `objects` ( `id` int(10) unsigned NOT NULL auto_increment, `name` char(30) default NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The last is probably best for your purposes. To change a column definition using ALTER TABLE, you have to give the complete new column definition, you can't simply add an attribute. If objects.id in my example above had not been AUTO_INCREMENT: ALTER TABLE objects CHANGE id id int(10) unsigned NOT NULL auto_increment; That is, I took the line from SHOW CREATE TABLE for id, added the AUTO_INCREMENT attribute, and used that as the new column definition. See the manual for details on ALTER TABLE syntax http://dev.mysql.com/doc/mysql/en/alter-table.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: change field to auto increment
Great!! Thx I looked into the manual and the refer to the create syntax concerning auto_increment. The one thing I did not get was the fact I have to 'recreate' the column definition and can not just add something to it. It works great now! The for the effort of replying :) Greetings, Reinhart Viane Btw, how do you show your tables layout like you did in this mail? -Oorspronkelijk bericht- Van: Michael Stassen [mailto:[EMAIL PROTECTED] Verzonden: woensdag 1 juni 2005 16:47 Aan: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Onderwerp: Re: change field to auto increment Reinhart Viane wrote: Hey list, I need a query that checks if a certain table column has type auto-increment If not set it to auto increment, else do nothing. Let's say I have the table objects (object_id, object_name) in which object_id is the primary field. Now check to see if object_id is auto_increment and if not set it auto_increment Can anyone help me on this? I've been looking into the manual but I can't get it right: Alter table objects change object_id type auto_increment Thx in advance, Reinhart You can find out if objects.object_id is set to AUTO_INCREMENT in a couple of ways. Use DESCRIBE tablename http://dev.mysql.com/doc/mysql/en/describe.html to see info about all the columns: mysql DESCRIBE objects; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | | PRI | NULL| auto_increment | | name | char(30) | YES | MUL | NULL|| +---+--+--+-+-++ 2 rows in set (0.00 sec) or use DESCRIBE tablename colname to see info about a specific column: mysql DESCRIBE objects id; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | | PRI | NULL| auto_increment | +---+--+--+-+-++ 1 row in set (0.01 sec) or use SHOW CREATE TABLE tablename http://dev.mysql.com/doc/mysql/en/show-create-table.html: mysql SHOW CREATE TABLE objects\G *** 1. row *** Table: objects Create Table: CREATE TABLE `objects` ( `id` int(10) unsigned NOT NULL auto_increment, `name` char(30) default NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The last is probably best for your purposes. To change a column definition using ALTER TABLE, you have to give the complete new column definition, you can't simply add an attribute. If objects.id in my example above had not been AUTO_INCREMENT: ALTER TABLE objects CHANGE id id int(10) unsigned NOT NULL auto_increment; That is, I took the line from SHOW CREATE TABLE for id, added the AUTO_INCREMENT attribute, and used that as the new column definition. See the manual for details on ALTER TABLE syntax http://dev.mysql.com/doc/mysql/en/alter-table.html. Michael -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.322 / Virus Database: 267.3.3 - Release Date: 31/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the name of the last failed FK constraint
Frank Schröder [EMAIL PROTECTED] wrote on 05/31/2005 03:18:11 AM: Hello, I have an InnoDB table running on MySQL 4.1.11 with multiple FK constraints. I'm accessing it via JDBC from Java. When an FK constraint fails with error 1216 I need to know which of the constraints failed. SHOW INNODB STATUS returns the following output ... CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`) REFERENCES `u_device` (`DEVICE_ID`) ... Is there a way of getting to the name of the last failed FK constraint without using SHOW INNODB STATUS? What I need is the 'u_registration_ibfk_1' from the above example. Any help is highly appreciated -- Frank Have you looked at the results of SHOW INNODB STATUS; ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Import dump (4.0 4.1) and collation problem
Salut,
i don't see what doesn't work ! Where characters are replaced by ?, since you
say that insert,select and import work fine ? is it in the export ?
see http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html for migration.
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Voilà,
Everything works fine: insert, select and importing. I have always the same
text:
'Ceci est un test en Français'
So what could be the problem ? and how to recover foreign characters ?
Thanks
Roberto Jobet
i mean :
if you launch :
into into table (toto) values ('Ceci est un test en Français');
then :
select toto from table;
can you read it correctly ? And if the same row is imported, is there any
difference ?
A bientôt
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Salut,
je vois que tu es français, j'aimerai bien continuer en français mais il
vaut
mieux en anglais si jamais il y avait quelqu'un avec le même problème ;-)
So what I'm trying to achieve is to have UTF-8 databases in order to
support
multilingual content.
But first I have to understand how to recover foreign characters in my
DB.
What do you mean by: try some inserts to see if it is an import problem
or
just the client display ?
Merci d'avance
Roberto Jobet
hi,
your config supports european characters :
mysql select * from tst;
+--+---+
| a| txt |
+--+---+
|1 | Ceci est un test en Français |
|1 | se facilitará el conocimiento de la evolución del |
+--+---+
2 rows in set (0.00 sec)
mysql show variables like '%coll%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)
have you tried some inserts to see if it is an import problem, or just
the
client display ?
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Hi,
I recently upgraded from 4.0 to 4.1 because of the multilingual
support.
But importing the DB dump into the new 4.1 version all foreign
characters
(french, spanish, portoguese) have been replaced by ? character...
I've been trying to understand the new collation feature but i
seems
quite
difficult to setup.
Anybody has already faced this problem ?
Here's my current configuration:
1) my.cnf (only the mysqld section)
[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port= 3306
# Both location gets rotated by the cronjob.
#log= /var/log/mysql.log
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /db
tmpdir = /tmp
language= /usr/share/mysql/english
2) Output show variables like '%char%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
3) Out put show variables like '%coll%';
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
Thanks for any help
Regards
Roberto Jobet
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import dump (4.0 4.1) and collation problem
look at automatic character set conversion
http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html
mathias
Selon [EMAIL PROTECTED]:
Salut,
i don't see what doesn't work ! Where characters are replaced by ?, since you
say that insert,select and import work fine ? is it in the export ?
see http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html for migration.
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Voilà,
Everything works fine: insert, select and importing. I have always the same
text:
'Ceci est un test en Français'
So what could be the problem ? and how to recover foreign characters ?
Thanks
Roberto Jobet
i mean :
if you launch :
into into table (toto) values ('Ceci est un test en Français');
then :
select toto from table;
can you read it correctly ? And if the same row is imported, is there any
difference ?
A bientôt
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Salut,
je vois que tu es français, j'aimerai bien continuer en français mais
il
vaut
mieux en anglais si jamais il y avait quelqu'un avec le même problème
;-)
So what I'm trying to achieve is to have UTF-8 databases in order to
support
multilingual content.
But first I have to understand how to recover foreign characters in my
DB.
What do you mean by: try some inserts to see if it is an import
problem
or
just the client display ?
Merci d'avance
Roberto Jobet
hi,
your config supports european characters :
mysql select * from tst;
+--+---+
| a| txt |
+--+---+
|1 | Ceci est un test en Français |
|1 | se facilitará el conocimiento de la evolución del |
+--+---+
2 rows in set (0.00 sec)
mysql show variables like '%coll%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)
have you tried some inserts to see if it is an import problem, or
just
the
client display ?
Mathias
Selon [EMAIL PROTECTED] [EMAIL PROTECTED]:
Hi,
I recently upgraded from 4.0 to 4.1 because of the multilingual
support.
But importing the DB dump into the new 4.1 version all foreign
characters
(french, spanish, portoguese) have been replaced by ? character...
I've been trying to understand the new collation feature but i
seems
quite
difficult to setup.
Anybody has already faced this problem ?
Here's my current configuration:
1) my.cnf (only the mysqld section)
[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port= 3306
# Both location gets rotated by the cronjob.
#log= /var/log/mysql.log
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /db
tmpdir = /tmp
language= /usr/share/mysql/english
2) Output show variables like '%char%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
3) Out put show variables like '%coll%';
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
Thanks for any help
Regards
Roberto Jobet
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL
Re: MySQL 5.0.6-beta has been released
On Tue, 31 May 2005 18:01:48 -0500 Matt Wagner [EMAIL PROTECTED] wrote: snip This is the third published Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. Just curious - was there a 5.0.5-beta release? The last beta I had was 5.0.4 unless I missed a release announcement... Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Nevermind!] MySQL 5.0.6-beta has been released
On Wed, 1 Jun 2005 10:40:10 -0500 Josh Trutwin [EMAIL PROTECTED] wrote: On Tue, 31 May 2005 18:01:48 -0500 Matt Wagner [EMAIL PROTECTED] wrote: snip This is the third published Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. Just curious - was there a 5.0.5-beta release? The last beta I had was 5.0.4 unless I missed a release announcement... Just saw this in the release: Changes in release 5.0.5 (not released): Sorry for wasting bandwidth... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBD::Oracle issue
Well I sent this message to both the mysql mailing list and this perl
list, because I am using perl to accomplish this, and mysql to talk to.
But anyway, thank you very much your number 3 EXACTLY fixed my issues, so
the person with the right answer contacted me.
Again, thank you very much for your assistance.
Chris Hood
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 01, 2005 8:02 AM
To: Christopher L. Hood
Cc: mysql@lists.mysql.com; beginners@perl.org
Subject: Re: DBD::Oracle issue
Hi,
I'll try even if this is not a neither oracle nor perl-dbd list.
1. verify that NLS_LANG is correct in your client env (and all the other
ORA
vars)
2. verify the query is simply quoted ( needs \_ for _)
3. Put all oracle variables in BEGIN {ORACLe_HOME=}; in the perl script
4. verify that select * from dual is OK
Mathias
Selon [EMAIL PROTECTED]:
ALL,
Can someone help out with this error:
DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication
protocol error (DBD ERROR: error possibly near * indicator at char 23
in
'select table_name from *all_tables') [for Statement select
table_name
from all_tables] at ./oracleTest.pl line 66.
I have tried to set TWO_TASK environment variable to no avail.
The oracle connection is a remote connection made.
I have successfully looked up other information in the database with
this
script.
The actual SQL command being run is select table_name from all_tables.
If anyone needs the actual script that is being run please mail me. But
I
believe this error is specific to the select statement being run because
other selects work just fine.
ANY ideas / suggestions would be greatly appreciated,
Chris Hood
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.6-beta has been released
On Wednesday 01 June 2005 10:40 am, Josh Trutwin wrote: Just curious - was there a 5.0.5-beta release? The last beta I had was 5.0.4 unless I missed a release announcement... You need to read that email.. it says it was never released. Jeff pgpk9pJzWINTK.pgp Description: PGP signature
query help?
I have two tables, cutting out the extra stuff they boil down to: users: userID int, username varchar(11), realname varchar(40) logins: ID int, lastLogin timestamp So, what I am doing is: select user.id, username, realname, lastLogin from users left join logins on users.id = logins.id group by username order by lastLogin DESC What I want is all the users, no matter if they have logged in or not. That is what the left join does. But, if they have logged in, I want the last login date. Right now I get the first login date. Changing DESC to ASC only changes the display order of the return set. I have added DESC and ASC to the group by, but that doesn't work at all. Advice? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help?
[EMAIL PROTECTED] wrote on 06/01/2005 11:49:35 AM: I have two tables, cutting out the extra stuff they boil down to: users: userID int, username varchar(11), realname varchar(40) logins: ID int, lastLogin timestamp So, what I am doing is: select user.id, username, realname, lastLogin from users left join logins on users.id = logins.id group by username order by lastLogin DESC What I want is all the users, no matter if they have logged in or not. That is what the left join does. But, if they have logged in, I want the last login date. Right now I get the first login date. Changing DESC to ASC only changes the display order of the return set. I have added DESC and ASC to the group by, but that doesn't work at all. Advice? --ja -- For dates, latest = greatest value or MAX(). select user.id, username, realname, max(lastLogin) as lastLogin from users left join logins on users.id = logins.id group by user.id, username, realname; YOU MUST include all of your non-aggregated column in your group by statement. MySQL has a forgiveness built into this rule but other RDBMS systems would have rejected your query based on that very fact. What MySQL does is select any random record that falls into the GROUP BY condition and give you the values from it. In this case it was probably the first record in the logins table that matched each user. This is a well-known MySQL gotcha and it got you, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query help?
Hello all, My question is if unix Epoch time started on January 1st 1970 ,and mysql uses the same implementation of time , what is the logic mysql developers have used to offset it by 30 odd years that is the max date for mysql is 2068 and not 2038 18th January, i work in a Financial services firm where its important to calculate mortgages beyond 2038 , Linux and Windows are caught in trouble ,rather my more general question is what effect does The Y-2038 bug have on MySQL Any sort of pointers will help TIA digz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help?
Hi, try this : mysql select * from users; ++--+-+ | userID | username | realname| ++--+-+ | 1 | toto1| toto1 toto1 | | 2 | toto2| toto2 toto2 | | 3 | toto3| toto3 toto3 | | 4 | toto4| toto4 toto4 | ++--+-+ 4 rows in set (0.00 sec) mysql select * from logins; +--+-+ | ID | lastLogin | +--+-+ |1 | 2005-01-01 00:10:00 | |1 | 2005-06-01 12:00:00 | |2 | 2005-01-25 00:00:00 | |2 | 2005-05-24 01:00:00 | |3 | 2005-01-15 10:10:00 | |3 | 2005-06-25 14:00:00 | +--+-+ 6 rows in set (0.00 sec) mysql select userid, username, realname, max(lastLogin) lastLogin - from users left join logins on userid = logins.id -group by username - order by lastLogin DESC; ++--+-+-+ | userid | username | realname| lastLogin | ++--+-+-+ | 3 | toto3| toto3 toto3 | 2005-06-25 14:00:00 | | 1 | toto1| toto1 toto1 | 2005-06-01 12:00:00 | | 2 | toto2| toto2 toto2 | 2005-05-24 01:00:00 | | 4 | toto4| toto4 toto4 |NULL | ++--+-+-+ 4 rows in set (0.00 sec) Mathias Selon [EMAIL PROTECTED]: I have two tables, cutting out the extra stuff they boil down to: users: userID int, username varchar(11), realname varchar(40) logins: ID int, lastLogin timestamp So, what I am doing is: select user.id, username, realname, lastLogin from users left join logins on users.id = logins.id group by username order by lastLogin DESC What I want is all the users, no matter if they have logged in or not. That is what the left join does. But, if they have logged in, I want the last login date. Right now I get the first login date. Changing DESC to ASC only changes the display order of the return set. I have added DESC and ASC to the group by, but that doesn't work at all. Advice? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help?
Digvijoy Chatterjee [EMAIL PROTECTED] wrote on 01/06/2005 17:13:25: Hello all, My question is if unix Epoch time started on January 1st 1970 ,and mysql uses the same implementation of time , what is the logic mysql developers have used to offset it by 30 odd years that is the max date for mysql is 2068 and not 2038 18th January, i work in a Financial services firm where its important to calculate mortgages beyond 2038 , Linux and Windows are caught in trouble ,rather my more general question is what effect does The Y-2038 bug have on MySQL Any sort of pointers will help You should be using the DATETIME column thpe for this sort of calculation, not the timestamp. DATETIME has a suorted range from 1000AD to AD, which should be enough for your purposes. TIMESTAMP is, as its name applied, mostly used for timestamping records at create time. It should not generally be used for extensive chronological calculations. TIMESTAMP almost always point to the past. MySQL wil therefore have to take some action before about 2060 (to allow users a few years to upgrade). The obvious thing would be to implement a 64-bit LONGTIMESTAMP. This will become easier in a few years when 64-bit OSs become mor the norm. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-5.0.6 has been released
Hi!
InnoDB is the MySQL table type that supports foreign key constraints,
transactions, two-phase commit in XA, row-level locking, non-locking
consistent read (MVCC), all four SQL-1992 isolation levels of transactions,
multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a
non-free hot online backup tool.
MySQL-5.0.6 is a bugfix release of the 5.0 branch. The release is still
labeled as beta, because 5.0 contains many new features, and more real-world
testing is needed.
You can download MySQL-5.0.6 from
http://dev.mysql.com/downloads/mysql/5.0.html
Functionality added or changed:
* When the maximum length of SHOW INNODB STATUS output would be exceeded,
truncate the beginning of the list of active transactions, instead of
truncating the end of theoutput. (Bug #5436)
* If innodb_locks_unsafe_for_binlog option is set and the isolation level of
the transaction is not set to serializable then InnoDB uses a consistent
read for select in clauses like INSERT INTO ... SELECT and UPDATE ...
(SELECT) that do not specify FOR UPDATE or IN SHARE MODE. Thus no locks are
set to rows read from selected table.
Bugs fixed:
* MyISAM and InnoDB tables created with DECIMAL or NUMERIC columns in MySQL
5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump
suchtables with mysqldump before upgrading, and then reload them after
upgrading. (The same incompatibility will occur for these tables created in
MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (Bug #10465, Bug
#10625)
* Fixed a critical bug in InnoDB @code{AUTO_INCREMENT}: it could assign the
same value for several rows. (Bug #10359)
* All InnoDB bug fixes from 4.1.12 and earlier versions, and also the fixes
to bugs #10335 and #10607 listed in the 4.1.13 change notes.
Upgrading from 4.1:
* MyISAM and InnoDB tables created with DECIMAL or NUMERIC columns in MySQL
5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump
such tables with @command{mysqldump} before upgrading, and then reload them
after upgrading.
* Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers
the number of spaces that there were at the end of the string. Previously,
MySQL at storage trimmed end spaces from a VARCHAR. Tables created with
5.0.3 will remain to have the old VARCHAR semantics, while new tables will
have the new semantics.
* The sorting order for end-space in TEXT columns for InnoDB and MyISAM
tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as
space-padded at the end. If you have a non-unique index on a TEXT column,
you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check
reports errors. If you have a UNIQUE INDEX on a TEXT column, you should
rebuild the table with OPTIMIZE TABLE.
* The sorting order of BINARY and VARBINARY may still change in some 5.0.x
version.
* In 4.0 - 4.1.11 there is a bug in the InnoDB sorting order of ENUMs if the
collation of the ENUM is not latin1 or if there are more than about 100
different values for the ENUM.
* InnoDB Hot Backup 2.0.1 or earlier does not work with MySQL-5.0.3 or
later. A new version 2.1 will.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
Thanks.
On 6/1/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 06:57:19
PM:
-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't completely inclusive...it would miss FIELD-1...
-- However, It seems to me that this kind of data manipulation
(cleanup) needs to happen BEFORE the data enters the databse.
Sometimes FIELD-1 is the ACTUAL data, with no erroneus
characters...and sometimes it is FIELD1 with an erroneous (or
unwanted) - character before the 1)...so cleaning the data would
actually be corrupting some of it.
I need to keep the data in it's original form, but also allow for
querying without worrying about the special characters inside the
column.
FYI, these are part numbers off of electronic components, many of them
coming from China...so, a Cisco part may have an MPN of RX321, or
RX321-TR...either is valid. Now, the corresponding Chinese part
number for the first one, may come back as RX32-1...which is out of my
control.
Cleaning the data would be the wrong approach, because it would
actually invalidate the second Cisco part number, which MEANT to
include the special characters.
For this reason, the user wants to be able to search for 'RX321' and
'RX321TR' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
04:38:40
PM:
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function.
I
do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW,
why
*do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored
procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and thestring
is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks
in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
Thank you very much. I find this whole padding process very
counterintuitive. I have a few minor questions, if you don't mind. How
is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you
please
explain the theory behind why and when this kind of padding should be
done?
What problem does it solve and how is it a solution to that problem?
This
is completely baffling to me and I thought I had seen a lot of weird
data
before :-)
However, It seems to me that this kind of data manipulation (cleanup)
needs
to happen BEFORE the data enters the databse. What data import
tool/process
is your client using? Can you not change the import process to scrub the
data and does it not have a better facility to interleave padding into a
string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
I would suggest the following technique. Keep your original data (trimmed of
leading and trailing whitespace characters) in one field and a cleansed
version of the same information in a second field. By cleansing you should
eliminate all non-alphanumerics from your search string.
I have some direct insight to searching similar but different product codes
(same product/different codes different products/same codes). My wife
works for a
Re: How to get the name of the last failed FK constraint
[EMAIL PROTECTED] wrote: Frank Schröder [EMAIL PROTECTED] wrote on 05/31/2005 03:18:11 AM: Hello, I have an InnoDB table running on MySQL 4.1.11 with multiple FK constraints. I'm accessing it via JDBC from Java. When an FK constraint fails with error 1216 I need to know which of the constraints failed. SHOW INNODB STATUS returns the following output ... CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`) REFERENCES `u_device` (`DEVICE_ID`) ... Is there a way of getting to the name of the last failed FK constraint without using SHOW INNODB STATUS? What I need is the 'u_registration_ibfk_1' from the above example. Any help is highly appreciated -- Frank Have you looked at the results of SHOW INNODB STATUS; ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yes, as you can see from my original post I'm actually trying to figure out how to do this *without* SHOW INNODB STATUS as this reports the last FK failure for the entire engine and not just my session - at least that's how I interpret the documentation. The thing that's really a headscratcher for me is why its possible for me to set a name for a constraint if it isn't displayed in an error and I can't get to it. It's useless. I have a hard time believing that so I figure that I just haven't figured out how to get to it. I just didn't think that it was so hard. -- Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design of a Client-side MySQL Java Load Balancer
I'd love to get some feedback here: MySQL currently falls down by not providing a solution to transparent MySQL load balancing. There are some hardware solutions but these are expensive and difficult to configure. Also none of them provide any information about the current state of your MySQL configuration. For example they can't handle transparent query failover if a MySQL box fails. They also can't disconnect and reconnect to another host if the load grows too high. To that end I think it makes a lot of sense to have a MySQL client-side load balancer. This area is difficult to implement. There are a log of design issues. Also the issues WRT distributed connection management start to make the problem difficult. The other day I had a bit of an epiphany on this topic. http://peerfear.typepad.com/blog/2005/06/design_of_a_cli.html -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the name of the last failed FK constraint
Frank Schröder wrote: The thing that's really a headscratcher for me is why its possible for me to set a name for a constraint if it isn't displayed in an error and I can't get to it. It's useless. Same as setting a name for an index -- it allows you to alter or delete it later. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design of a Client-side MySQL Java Load Balancer
Kevin Burton [EMAIL PROTECTED] wrote on 06/01/2005 02:31:54 PM: I'd love to get some feedback here: MySQL currently falls down by not providing a solution to transparent MySQL load balancing. There are some hardware solutions but these are expensive and difficult to configure. Also none of them provide any information about the current state of your MySQL configuration. For example they can't handle transparent query failover if a MySQL box fails. They also can't disconnect and reconnect to another host if the load grows too high. To that end I think it makes a lot of sense to have a MySQL client-side load balancer. This area is difficult to implement. There are a log of design issues. Also the issues WRT distributed connection management start to make the problem difficult. The other day I had a bit of an epiphany on this topic. http://peerfear.typepad.com/blog/2005/06/design_of_a_cli.html -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 Wouldn't it make better sense to build on the NDB protocol and keep the native messaging infrastructure than it would be to build a similar wrapper from scratch? I mean to use the NDB communications on top of regular MySQL servers to provide for failover, hotswaps, and client registration. Haven't they already solved some or most of your client registration and reconnection issues during the development of that tool? There is also the Federated database engine that allows for queries to be run across more than one server at a time (not only can a query include a table from another database on the same server but also from a table in a database hosted on a different server). I think that between the two of those projects you have 80-90% of the wrapper you will need to run a MySQL cluster in a HA environment. The biggest problem I see is to prevent data collisions between servers (users connected to different servers trying to update the same record at the same time on different servers). Part of the NDB code has to contain the necessary cross-server locking messaging. And Federated servers will also do the same (at least to some degree). If you can get a JDBC version working, that would be great but I think the best dev path would be to re-use what is already there as much as possible. I am not discouraging the effort as much as I am suggesting another body of work to consider (or incorporate) in your design. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Design of a Client-side MySQL Java Load Balancer
[EMAIL PROTECTED] wrote: Wouldn't it make better sense to build on the NDB protocol and keep the native messaging infrastructure than it would be to build a similar wrapper from scratch? I mean to use the NDB communications on top of regular MySQL Biting off an NDB migration would be a LOT harder than implementing slave load balancing. NDB shows promise but I just don't think its there yet... Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
You guys have been so helpful with this, I'm hoping that I can ask for one more favor... The reason I needed the greatest(max()) functionality, was to run the following query...I can make it work from the command line, but everytime I run it from PHP, the MySQL service shuts down, and needs to be restarted manually. I'm calling a stored procedure 'selectAllRequests' which is the following query: SELECT r.id, r.partNumber, r.OtherFields, functionGetHighestValue(r.partNumber, r.qty) AS 'highestValue' FROM request r WHERE r.deleted=0 ORDER BY highestValue DESC, r.dateSent DESC; the function I'm calling is as follows: CREATE FUNCTION `functionGetHighestValue`(`MPNParam` varchar(60), `qtyParam` DOUBLE(10,4)) RETURNS DOUBLE(10,4) BEGIN DECLARE dHighest DOUBLE(10,4) DEFAULT 0; SELECT GREATEST(MAX(i.distySellCost), MAX(i.originalCost), MAX(i.unitCost), MAX(i.unitSellCost))*qtyParam FROM inventory i WHERE i.MPN = 'MPNParam' AND i.status=1 INTO dHighest; RETURN dHighest; END| As I say, I can call this procedure from the command line and it works, but calling it from PHP results in the MySQL service crashing on my Windows 2003 server. I'm using PHP 5.0.4 and MySQL 5.0.4. Any help is appreciated. Thanks. On 5/27/05, Scott Klarenbach [EMAIL PROTECTED] wrote: select greatest(max(col1), max(col2), max(col3), max(col4)) from table works the best, as Keith pointed toward initially. Remember, I forgot to mention that I wanted the greatest for the whole table, not just for each rowso, 10, 12, 8 is not what I wanted...out of 10 2 3 5 4 8 1 12 7 i want 12. thanks again. On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I forgot : 10, 12, 8 is not a row !!! Mathias Selon [EMAIL PROTECTED]: Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design of a Client-side MySQL Java Load Balancer
We have a client-side load balancer, as well as a central load balancer
for mySQL traffic. I've done many different models at a variety of
companies, all which still use the solutions. Building on top of the NDB
protocol would take a very long time to produce-a HA solution native to
mySQL, when other technologies already exist-free to do this for you.
For example use heartbeat from linux-ha.org for failover.
Now for client-side load balancing, memcache from danga.com has some
interesting ideas. DBI::Multiplex also has some interesting ideas.
One of the easiest solutions is to load a database definitions file on a
shared drive. Have an external program add or remove hosts from each
cluster of the shared definitions file. Then use a random algorithm to
pick a host from a list that is defined apart a cluster to connect to.
array ('mycluster' = array ( array ('host' = myhost1,
'db'='mydb',...), array ('host' = myhostN,));
If your algorithm is random enough and spread across enough servers, the
results coalesce into a even distribution among all the servers in a
list. Very basic, very easy to implement and works.
The monitor program, run out of cron-or something, can have logic of
what to keep in a pool or what to take out of a pool.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 01, 2005 1:21 PM
To: Kevin Burton
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Design of a Client-side MySQL Java oad Balancer
Kevin Burton [EMAIL PROTECTED] wrote on 06/01/2005 02:31:54 PM:
I'd love to get some feedback here:
MySQL currently falls down by not providing a solution to
transparent MySQL load balancing. There are some hardware
solutions
but these are expensive
and
difficult to configure. Also none of them provide any information
about the current state of your MySQL configuration. For example
they can't
handle
transparent query failover if a MySQL box fails. They also can't
disconnect and reconnect to another host if the load
grows too high.
To that end I think it makes a lot of sense to have a MySQL
client-side load balancer.
This area is difficult to implement. There are a log of
design issues.
Also the
issues WRT distributed connection management start to make the
problem difficult.
The other day I had a bit of an epiphany on this topic.
http://peerfear.typepad.com/blog/2005/06/design_of_a_cli.html
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
See irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG
fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
Wouldn't it make better sense to build on the NDB protocol
and keep the native messaging infrastructure than it would be
to build a similar wrapper from scratch? I mean to use the
NDB communications on top of regular MySQL servers to provide
for failover, hotswaps, and client registration. Haven't they
already solved some or most of your client registration and
reconnection issues during the development of that tool?
There is also the Federated database engine that allows for
queries to be run across more than one server at a time (not
only can a query include a table from another database on the
same server but also from a table in a database hosted on a
different server).
I think that between the two of those projects you have
80-90% of the wrapper you will need to run a MySQL cluster in
a HA environment. The biggest problem I see is to prevent
data collisions between servers (users connected to different
servers trying to update the same record at the same time on
different servers). Part of the NDB code has to contain the
necessary cross-server locking messaging. And Federated
servers will also do the same (at least to some degree).
If you can get a JDBC version working, that would be great
but I think the best dev path would be to re-use what is
already there as much as possible. I am not discouraging the
effort as much as I am suggesting another body of work to
consider (or incorporate) in your design.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design of a Client-side MySQL Java Load Balancer
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kevin Burton wrote: [EMAIL PROTECTED] wrote: Wouldn't it make better sense to build on the NDB protocol and keep the native messaging infrastructure than it would be to build a similar wrapper from scratch? I mean to use the NDB communications on top of regular MySQL Biting off an NDB migration would be a LOT harder than implementing slave load balancing. NDB shows promise but I just don't think its there yet... Kevin Kevin, Shawn, - From a _total_ client connectivity standpoint, NDB is probably a dead-end, as it's based on message-passing. What we want is to support both HA _and_ distribution/partitioning from a clients' perspective. Federated and partitioning on the server side are a useful tool, and would work for some situations, but other users are going to want/need partitioning and load distribution at the _application_ level, which I think is the problem Kevin's talking about. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCniQttvXNTca6JD8RAovMAJ43r0rHzQ63qk5UhOxD4MjRNhLZOwCgjKF3 B0S8uyjQocbK52hw13NPk3A= =aJUl -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Insert a constant string into a table?-Have Brain Freeze-
I'm having a brain freeze and can't seem to figure out why I can't insert a constant into a table using a column list. Here is a simple query that inserts blanks instead of the constant. insert into tmp (cust_id, custname) select cust_id, 'bob smith' from cust table It inserts the cust_id but custname is is a blank (it is not null). The select statement displays the cust_id and constant properly, so why won't it work with an Insert statement? There is no syntax error and it executes without error. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Insert a constant string into a table?-Have Brain Freeze-
At 04:58 PM 6/1/2005, you wrote: I'm having a brain freeze and can't seem to figure out why I can't insert a constant into a table using a column list. Here is a simple query that inserts blanks instead of the constant. insert into tmp (cust_id, custname) select cust_id, 'bob smith' from cust table It inserts the cust_id but custname is is a blank (it is not null). The select statement displays the cust_id and constant properly, so why won't it work with an Insert statement? There is no syntax error and it executes without error. TIA Mike Solved it. My ice-cap has melted. :) The custname column was an ENUM (I thought it was a Char) and of course the constant I was trying to insert wasn't one of the allowed values so it replaces it with '' instead. D'uh. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump
Greetings, I took a dump of (pretty chunk) database, the output is close to 45G. I am trying to reload this dump file onto a development server but it's taking long time to load the database. Is their a faster way to load the data in? I am using plain and simple mysql dump.sql syntax on a Mysql 4.0.24 server. Thanks in advance! Cheers Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] building php, using mysql for apache2
--with-mysql is supposed to be the directory in which configure can find the mysql header (.h) files and the mysql library (mysql.so) underneath that directory. /usr/bin/mysql_config is a program -- a binary if you will It's incredibly unlikely that your MySQL header files and the mysql.so library are in files underneath this binary, which isn't even a directory, much less the directory where your header and library files are living... Try --with-mysql=/usr or --with-mysql=/usr/local or... Do this: locate mysql.h locate mysql.so Then find the common portion of the directories where those files live. Silly Example: /elephant/snake/tiger/mysql.h /elephant/gorilla/pume/mysql.so You should use --with-mysql=/elephant because that's the common portion. In theory, configure will dig around in there and find the files it needs. In reality, it expects them to be in directories like 'include' and 'lib' with maybe an intervening directory like 'local' somewhere in the mess... On Wed, June 1, 2005 5:13 am, bruce said: hi... trying to build php4 with mysql4.1-12, for use in apache2. i have the following ./compile that works for php5. however, when i try to use it for php4, i get a msg, stating that it can't find the MySQL Headers... can anybody provide any pointers as far as what the Headers are for mysql, and where they'd be located? if i knew the files, i could do a search to find where they're located. -- ./configure --with-apxs2=/usr/sbin/apxs --with-mysql=/usr/bin/mysql_config -- thanks -bruce [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqldump
I took a dump of (pretty chunk) database, the output is close to 45G. I am trying to reload this dump file onto a development server but it's taking long time to load the database. Is their a faster way to load the data in? I am using plain and simple mysql dump.sql syntax on a Mysql 0.24 server. Make sure you use the -e (extended insert) option on your mysqldump - this speeds things up tremendously. Also, make sure you use -q on your mysqldump so it doesn't buffer the results in RAM during the dump. I did a ~10GB dump and it took around 2 hours to load on an Opteron 250, 4GB RAM, 8x15K RPM RAID 10 drive system. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
server and my client in different time zone
Is there a way to tell NOW() in mysql to be based on a time in the future? Bascially, I am in PST, and they are in EST, I have a ton of reports that show the time and date, and they want them in EST time, not the time the server is in. it is trivial to modify the display output of the time, but there are reports and other things that really need this taken into consideration, if there is a way to tell a certain single database to oprtate 3 hours ahead, I am pretty happy. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Dear Gleb:
The problem has occured again, and I grab some output.
This box run mysql 4.1.11 on FreeBSD 5.4 AMD64, with 8GB RAM
In this case,
The original query is INSERT INTO LOW_PRIORTY INTO `statistics`.`search`
(`userid`, `id`, `func`) VALUES('lty0105', '8', 2)
The strange query is 'INSERT LOW_PRIORITY INTO `statistics`.`search` (
`userid` , `id` , `func` ) VALUES ( 'lty0105', '8', 2p)
Slave server got a strange character 'p' after character '2'
Slave:
mysql show slave status\G;
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: db0
Master_User: repl
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: db0-bin.56
Read_Master_Log_Pos: 755005566
Relay_Log_File: db10-relay-bin.60
Relay_Log_Pos: 572461705
Relay_Master_Log_File: db0-bin.56
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: blog,album_database
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: album_database.%,blog.%
Last_Errno: 1054
Last_Error: Error 'Unknown column '2p' in 'field list'' on
query. Default database: ''. Query: 'INSERT LOW_PRIORITY INTO
`statistics`.`search` ( `userid` , `id` , `func` ) VALUES ( 'lty0105', '8',
2p)'
Skip_Counter: 0
Exec_Master_Log_Pos: 692584947
Relay_Log_Space: 634882324
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
Master:
mysql show master status\G;
*** 1. row ***
File: db0-bin.56
Position: 761127837
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Finally I dump the slave log:
[EMAIL PROTECTED] [ /home/mysql ] mysqlbinlog db10-relay-bin.60 | grep (
'lty0105', '8', 2p)
INSERT LOW_PRIORITY INTO `statistics`.`search` ( `userid` , `id` , `func` )
VALUES ( 'lty0105', '8', 2p);
[EMAIL PROTECTED] [ /home/mysql ]
With best regards,
Weicheng.
- Original Message -
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, May 30, 2005 5:20 PM
Subject: Re: Replication problem
Hello.
Not enough information to make a conclusion. What version of
MySQL do you use? Usually debugging of replication problems
begins from researching of binary logs with mysqlbinlog. See:
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html
http://dev.mysql.com/doc/mysql/en/slave-logs.html
http://dev.mysql.com/doc/mysql/en/binary-log.html
Please, next time include the output of SHOW SLAVE STATUS
(executed on the slave) and SHOW MASTER STATUS (executed on
the master) as well.
Weicheng Pan [EMAIL PROTECTED] wrote:
Hi all:
I have replication problem on my FreeBSD 5.4 AMD64 box.
Sometimes replication will stop and throw an error code 1054,
but the query is not the original one in the master.
A query like UPDATE table_a SET col_a = 'abc' where col_b = 234
the right most character will become a strange character, and cannot
print
out in error log.
I have twelve machines doing the replication work, but only got problem
on
this AMD64 machine.
Anyone has encounter this type problem? please give me some advise.
Thanks and have a nice day.
Regards,
Weicheng.
--
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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
