|
|
|
|
|
|
| Author |
Message |
David Logan
*nix forums beginner
Joined: 18 Feb 2005
Posts: 27
|
 Posted: Sun Jun 18, 2006 10:47 pm Post subject:
Re: Alter table command don't work
|
|
|
Graham Reeds wrote:
| Quote: | Quentin Bennett wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
From MySQL 4.1.2 on, if you want to change the table default
character set and all character columns (CHAR, VARCHAR, TEXT) to a
new character set, use a statement like this: ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name;
That is the syntax I used. What about prior to 4.1.2?
Perhaps it was not available? See |
http://www.dev.mysql.com/doc/refman/4.1/en/charset-table.htm
You may have to dump the data and then reload into a new table with the
correct charset. You may be able to copy it using an INSERT .... SELECT
(not sure though, haven't had to do it)
Regards
--
David Logan
South Australia
when in trouble, or in doubt
run in circles, scream and shout
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
 |
Quentin Bennett
*nix forums beginner
Joined: 19 Apr 2006
Posts: 14
|
| Posted: Mon Jun 19, 2006 11:01 pm Post subject:
RE: Alter table command don't work
|
|
|
I don't think you can do it prior to 4.1:
http://dev.mysql.com/doc/refman/4.1/en/charset-general.html
MySQL 4.1 can do these things for you:
* Store strings using a variety of character sets
* Compare strings using a variety of collations
* Mix strings with different character sets or collations in the same server, the same database, or even the same table
* Allow specification of character set and collation at any level
In these respects, not only is MySQL 4.1 far more flexible than MySQL 4.0, it also is far ahead of most other database management systems. However, to use these features effectively, you need to know what character sets and collations are available, how to change the defaults, and how they affect the behavior of string operators and functions.
-----Original Message-----
From: David Logan [mailto:david@lcscreative.com]
Sent: Monday, 19 June 2006 10:36 a.m.
To: Graham Reeds
Cc: mysql@lists.mysql.com
Subject: Re: Alter table command don't work
Graham Reeds wrote:
| Quote: | Quentin Bennett wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
From MySQL 4.1.2 on, if you want to change the table default
character set and all character columns (CHAR, VARCHAR, TEXT) to a
new character set, use a statement like this: ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name;
That is the syntax I used. What about prior to 4.1.2?
Perhaps it was not available? See |
http://www.dev.mysql.com/doc/refman/4.1/en/charset-table.htm
You may have to dump the data and then reload into a new table with the
correct charset. You may be able to copy it using an INSERT .... SELECT
(not sure though, haven't had to do it)
Regards
--
David Logan
South Australia
when in trouble, or in doubt
run in circles, scream and shout
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=quentin.bennett@infinity.co.nz
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Peter Lauri
*nix forums beginner
Joined: 25 Nov 2005
Posts: 40
|
| Posted: Wed Jun 21, 2006 8:42 am Post subject:
RE: if else statement
|
|
|
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
-----Original Message-----
From: Thomas Lundström [mailto:zodin@home.se]
Sent: Wednesday, June 21, 2006 7:51 PM
To: Song Ken Vern-E11804
Cc: mysql@lists.mysql.com
Subject: Re: if else statement
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
| Quote: | Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=lists@dwsasia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
George Law
*nix forums addict
Joined: 06 Jan 2006
Posts: 54
|
| Posted: Wed Jun 21, 2006 8:57 pm Post subject:
RE: reclaim disk space
|
|
|
I had to do some disk space recovery mysql last month...
I have backed up some of the raw .MYI, .MYD files and deleted them from
the data directory. Running 5.0.18, I had to shut down mysql and
restart before it freed up the space.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Gabriel PREDA
*nix forums addict
Joined: 04 Feb 2005
Posts: 72
|
| Posted: Thu Jun 22, 2006 8:50 am Post subject:
Problems with: MySQL 5.0.21 - 64bit
|
|
|
Hi list,
Since we bought a better hardware for our dedicated MySQL Server we
have been running into some problems.
We are using:
- Fedora Core 3 - 64bit version
- Kernel: 2.6.9-1.667smp - x86_64
- MySQL 5.0.21-standard - for 64bit
- RAM: 4 GB
- RAID 5 matrix with 3 SCSI disks at 15k rotations
We are using InnoDB tables (with one or 2 exceptions... for some
FullText indexes)...
We are not using transactions... yet !
I'll drop config. details lower...
Still at given moments MySQL hangs... it does not accept connections anymore...
We can't kill the process... with KILL command... the only thing we
can do is ask the hosting provider to do a HARDWARE reset... and
someone goes to the machine and pushes the reset button... this hppens
at least once a week...
Does this happened to someone else ?
What was the problem ?
Thanx in advance !
--
Gabriel PREDA
Senior Web Developer
-----------------------------------------------------------------------------------------------------
------------------- CONFIG DATA
----------------------------------------------------------
-----------------------------------------------------------------------------------------------------
- PROCESORS from 0 to 7:
processor : 0 (up to processor 7 the specs. are the same)
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 8
cpu MHz : 2793.261
cache size : 16 KB
physical id : 0
siblings : 4
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm
pni monitor ds_cpl est cid cx16 xtpr ts
bogomips : 5537.79
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
TOP output:
top - 00:59:44 up 10:14, 1 user, load average: 0.46, 0.54, 0.64
Tasks: 82 total, 1 running, 81 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 0.3% us, 0.3% sy, 0.0% ni, 99.3% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu2 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu4 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu5 : 6.3% us, 1.7% sy, 0.0% ni, 91.4% id, 0.0% wa, 0.3% hi, 0.3% si
Cpu6 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu7 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 4038412k total, 1614864k used, 242
Mem: 4038412k total, 1617176k used, 2421236k free, 135060k buffers
Swap: 2048276k total, 0k used, 2048276k free, 367664k cached
MY.CNF:
[mysqld]
set-variable = max_connections=900
safe-show-database
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=64M
set-variable = query-cache-size=500M
set-variable = query_cache_limit=30M
set-variable = long_query_time=3
set-variable = table_cache=600
set-variable = thread_cache_size=32
set-variable = thread_concurrency=8
set-variable = key_buffer_size=32M
set-variable = interactive_timeout=60
set-variable = wait_timeout=60
set-variable = max_allowed_packet=3M
set-variable = sort_buffer_size=6M
set-variable = ft_min_word_len=3
set-variable = binlog_cache_size=0
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_thread_concurrency=8
set-variable = innodb_buffer_pool_size=400M
set-variable = innodb_flush_log_at_trx_commit=0
set-variable = innodb_autoextend_increment=50M
set-variable = innodb_fast_shutdown=0
set-variable = innodb_log_buffer_size=4M
set-variable = innodb_max_dirty_pages_pct=75
set-variable = innodb_status_file
SHOW VARIABLES:
+---------------------------------+-----------------------------------------------+
| Variable_name | Value
|
+---------------------------------+-----------------------------------------------+
| automatic_sp_privileges | ON
|
| back_log | 50
|
| basedir | /
|
| binlog_cache_size | 4096
|
| bulk_insert_buffer_size | 8388608
|
| completion_type | 0
|
| concurrent_insert | 1
|
| connect_timeout | 5
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| div_precision_increment | 4
|
| engine_condition_pushdown | OFF
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 0
|
| group_concat_max_len | 1024
|
| have_archive | YES
|
| have_compress | YES
|
| have_crypt | YES
|
| have_geometry | YES
|
| have_innodb | YES
|
| have_query_cache | YES
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| innodb_additional_mem_pool_size | 20971520
|
| innodb_autoextend_increment | 1000
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 419430400
|
| innodb_checksums | ON
|
| innodb_commit_concurrency | 0
|
| innodb_concurrency_tickets | 500
|
| innodb_data_file_path | ibdata1:10M:autoextend
|
| innodb_data_home_dir |
|
| innodb_doublewrite | ON
|
| innodb_fast_shutdown | 0
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | OFF
|
| innodb_flush_log_at_trx_commit | 0
|
| 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 | 4194304
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | ./
|
| innodb_max_dirty_pages_pct | 75
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_support_xa | ON
|
| innodb_sync_spin_loops | 20
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 8
|
| innodb_thread_sleep_delay | 10000
|
| interactive_timeout | 60
|
| join_buffer_size | 131072
|
| key_buffer_size | 33554432
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| large_files_support | ON
|
| large_page_size | 0
|
| large_pages | OFF
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_bin | OFF
|
| log_bin_trust_function_creators | OFF
|
| log_error |
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_warnings | 1
|
| long_query_time | 3
|
| low_priority_updates | OFF
|
| max_allowed_packet | 3144704
|
| max_binlog_cache_size | 18446744073709551615
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 900
|
| max_delayed_threads | 20
|
| max_error_count | 64
|
| max_heap_table_size | 67107840
|
| max_insert_delayed_threads | 20
|
| max_join_size | 18446744073709551615
|
| max_length_for_sort_data | 1024
|
| max_prepared_stmt_count | 16382
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 18446744073709551615
|
| max_sort_length | 1024
|
| max_sp_recursion_depth | 0
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 18446744073709551615
|
| multi_range_count | 256
|
| myisam_data_pointer_size | 6
|
| myisam_max_sort_file_size | 9223372036854775807
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 8388608
|
| myisam_stats_method | nulls_unequal
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | OFF
|
| open_files_limit | 4510
|
| optimizer_prune_level | 1
|
| optimizer_search_depth | 62
|
| pid_file | /var/lib/mysql/db02.pid
|
| prepared_stmt_count | 0
|
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 31457280
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 524288000
|
| 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 | ON
|
| relay_log_space_limit | 0
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| server_id | 0
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /var/lib/mysql/mysql.sock
|
| sort_buffer_size | 6291448
|
| sql_mode |
|
| sql_notes | ON
|
| sql_warnings | ON
|
| storage_engine | MyISAM
|
| sync_binlog | 0
|
| sync_frm | ON
|
| system_time_zone | MST
|
| table_cache | 600
|
| table_lock_wait_timeout | 50
|
| table_type | MyISAM
|
| thread_cache_size | 32
|
| thread_stack | 262144
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| tmp_table_size | 67108864
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| updatable_views_with_limit | YES
|
| version | 5.0.21-standard
|
| version_comment | MySQL Community Edition - Standard
(GPL) |
| version_compile_machine | x86_64
|
| version_compile_os | unknown-linux-gnu
|
| wait_timeout | 60
|
+---------------------------------+-----------------------------------------------+
SHOW STATUS:
Variable_name Value
Aborted_clients 100753
Aborted_connects 1
Bytes_received 78
Bytes_sent 76
Compression OFF
Connections 343641
Created_tmp_disk_tables 0
Created_tmp_files 330
Created_tmp_tables 1
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Innodb_buffer_pool_pages_data 14663
Innodb_buffer_pool_pages_dirty 52
Innodb_buffer_pool_pages_flushed 27043
Innodb_buffer_pool_pages_free 10548
Innodb_buffer_pool_pages_latched 2
Innodb_buffer_pool_pages_misc 389
Innodb_buffer_pool_pages_total 25600
Innodb_buffer_pool_read_ahead_rnd 7
Innodb_buffer_pool_read_ahead_seq 97
Innodb_buffer_pool_read_requests 1865339266
Innodb_buffer_pool_reads 9478
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 97431
Innodb_data_fsyncs 16857
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 241750016
Innodb_data_reads 10072
Innodb_data_writes 36074
Innodb_data_written 895450112
Innodb_dblwr_pages_written 27043
Innodb_dblwr_writes 3674
Innodb_log_waits 0
Innodb_log_write_requests 9569
Innodb_log_writes 6490
Innodb_os_log_fsyncs 9506
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 7760384
Innodb_page_size 16384
Innodb_pages_created 41
Innodb_pages_read 14622
Innodb_pages_written 27043
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 397
Innodb_rows_inserted 5497
Innodb_rows_read 1730152157
Innodb_rows_updated 9485
Key_blocks_not_flushed 0
Key_blocks_unused 18519
Key_blocks_used 8514
Key_read_requests 107424953
Key_reads 231661
Key_write_requests 18975369
Key_writes 18544
Last_query_cost 0.000000
Max_used_connections 202
Not_flushed_delayed_rows 0
Open_files 116
Open_streams 0
Open_tables 335
Opened_tables 0
Qcache_free_blocks 7737
Qcache_free_memory 482530304
Qcache_hits 6020261
Qcache_inserts 411651
Qcache_lowmem_prunes 0
Qcache_not_cached 295697
Qcache_queries_in_cache 19782
Qcache_total_blocks 48210
Questions 19314726
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 1
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 1092432
Table_locks_waited 836
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 21
Threads_connected 116
Threads_created 2710
Threads_running 2
Uptime 36881
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Dilipkumar
*nix forums beginner
Joined: 08 May 2006
Posts: 18
|
| Posted: Thu Jun 22, 2006 12:10 pm Post subject:
Re: Problems with: MySQL 5.0.21 - 64bit
|
|
|
Hi,
Please let us know what all variables you have configured in your my.cnf
files.
what is your total size of memory in box.
eg : max_connections
key_buffer_size
sort_buffer_size.
Gabriel PREDA wrote:
| Quote: | Hi list,
Since we bought a better hardware for our dedicated MySQL Server we
have been running into some problems.
We are using:
- Fedora Core 3 - 64bit version
- Kernel: 2.6.9-1.667smp - x86_64
- MySQL 5.0.21-standard - for 64bit
- RAM: 4 GB
- RAID 5 matrix with 3 SCSI disks at 15k rotations
We are using InnoDB tables (with one or 2 exceptions... for some
FullText indexes)...
We are not using transactions... yet !
I'll drop config. details lower...
Still at given moments MySQL hangs... it does not accept connections
anymore...
We can't kill the process... with KILL command... the only thing we
can do is ask the hosting provider to do a HARDWARE reset... and
someone goes to the machine and pushes the reset button... this hppens
at least once a week...
Does this happened to someone else ?
What was the problem ?
Thanx in advance !
|
--
Thanks & Regards,
Dilipkumar
DBA Support
********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to
Sify Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail & notify us
immediately at admin@sifycorp.com
Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com
Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Dan Buettner
*nix forums Guru Wannabe
Joined: 13 Oct 2005
Posts: 119
|
| Posted: Thu Jun 22, 2006 2:50 pm Post subject:
Re: Problems with: MySQL 5.0.21 - 64bit
|
|
|
Gabriel, in your SHOW VARIABLES, I see a handful of settings that are
much, much larger than normal:
| max_binlog_cache_size | 18446744073709551615
| max_join_size | 18446744073709551615
| max_seeks_for_key | 18446744073709551615
| max_write_lock_count | 18446744073709551615
| myisam_max_sort_file_size | 9223372036854775807
These values appear insanely large to me, especially given you've got
just 4 GB RAM. I'd adjust these down and see what effect that has.
Also, I noticed that your threads_created value seems fairly large
(2701) for about 10 hours of uptime. You might consider upping your
thread_cache size a bit. Doubt this is causing the hangs though.
When the MySQL software hangs, other processes on the machine are still
responsive? Anything of interest in your system log? You mention this
is new hardware ... wonder if you could have a problem with it.
Dan
Gabriel PREDA wrote:
| Quote: | Hi list,
Since we bought a better hardware for our dedicated MySQL Server we
have been running into some problems.
We are using:
- Fedora Core 3 - 64bit version
- Kernel: 2.6.9-1.667smp - x86_64
- MySQL 5.0.21-standard - for 64bit
- RAM: 4 GB
- RAID 5 matrix with 3 SCSI disks at 15k rotations
We are using InnoDB tables (with one or 2 exceptions... for some
FullText indexes)...
We are not using transactions... yet !
I'll drop config. details lower...
Still at given moments MySQL hangs... it does not accept connections
anymore...
We can't kill the process... with KILL command... the only thing we
can do is ask the hosting provider to do a HARDWARE reset... and
someone goes to the machine and pushes the reset button... this hppens
at least once a week...
Does this happened to someone else ?
What was the problem ?
Thanx in advance !
|
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Gabriel PREDA
*nix forums addict
Joined: 04 Feb 2005
Posts: 72
|
| Posted: Tue Jun 27, 2006 3:28 pm Post subject:
Re: Problems with: MySQL 5.0.21 - 64bit
|
|
|
Yup... For now the problem stopped...
These humongos values were because they were initialized at MAX_INT on
that 64bit machine...
In the my.cnf file they were not mentioned at all !
2^64 - 1 == 18446744073709551615
Now look at the values below !
--
Gabriel PREDA
Senior Web Developer
On 6/22/06, Dan Buettner <danb@thelittlemacshop.com> wrote:
| Quote: | Gabriel, in your SHOW VARIABLES, I see a handful of settings that are
much, much larger than normal:
| max_binlog_cache_size | 18446744073709551615
| max_join_size | 18446744073709551615
| max_seeks_for_key | 18446744073709551615
| max_write_lock_count | 18446744073709551615
| myisam_max_sort_file_size | 9223372036854775807
|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Gabriel PREDA
*nix forums addict
Joined: 04 Feb 2005
Posts: 72
|
| Posted: Sun Jul 02, 2006 4:43 pm Post subject:
Re: Problems with: MySQL 5.0.21 - 64bit
|
|
|
Spoken too soon...
Yesterday MySQL died again... there is nothing in the log files... (be
it mysqld.log, .err, or /var/log/messages).
The hosting provider is running an application called 'big-brother'
and a lot of sef-faults appear in the logs from this. I don't know if
thins is the cause... if this application is faulting mysql also...
The behaviour: the whole machine freezes... all that can be done is to
hit the RESET button on the machine !
Friday we downgraded from 5.0 to 4.1. At first we compiled the MySQL
server ourselvs... next we deleted it and installed a RPM version...
none worked...
It appears we hit bug 15815... (http://bugs.mysql.com/bug.php?id=15815)...
So be aware with innodb_thread_concurency on 64bit machines...
Below are some values as an orientation... for
innodb_thread_concurency value per machine workload (at arround 600
simultaneous connections... all used):
innodb_thread_concurency / CPU-workload
8 / 90%
4 / 75 - 85%
3 / 60 - 70%
2 / 25%
So set it lower... lower... lower... when I left the office friday my
colleagues were still working at this... I'll keep you all updated !
--
Gabriel PREDA
Senior Web Developer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Jeremy Cole
*nix forums addict
Joined: 04 Feb 2005
Posts: 50
|
| Posted: Sun Jul 02, 2006 7:17 pm Post subject:
Re: Problems with: MySQL 5.0.21 - 64bit
|
|
|
Hi Gabriel,
| Quote: | Yesterday MySQL died again... there is nothing in the log files... (be
it mysqld.log, .err, or /var/log/messages).
The hosting provider is running an application called 'big-brother'
and a lot of sef-faults appear in the logs from this. I don't know if
thins is the cause... if this application is faulting mysql also...
The behaviour: the whole machine freezes... all that can be done is to
hit the RESET button on the machine !
|
If the whole machine freezes, this sounds like bad memory, bad cpu, or
similar, rather than a MySQL problem. Regardless of how poorly an
application could be written, it should not be possible for it to freeze
the machine.
Regards,
Jeremy
--
high performance mysql consulting
www.provenscaling.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Chris White
*nix forums beginner
Joined: 21 Apr 2006
Posts: 43
|
| Posted: Mon Jul 03, 2006 2:34 pm Post subject:
Re: PostgreSQL or mySQL
|
|
|
On Sunday 02 July 2006 12:22 pm, Kirti S. Bajwa wrote:
| Quote: | I have very little knowledge of either PostgreeSQL or mySQL. Please advise
me as to which of these two software package to use? I need some specific
examples as to superiority of one package over the other. I prefer using
the package which has a goof GUI database design.
|
I hate to say this but, if you want objective/non-biased answers, you're
better off looking for a general database list, then posting to 2 lists that
are very database implementation specific. In fact, why not just google
around for "Postgres MySQL comparison" or something of the like.
--
Chris White
PHP Programmer/DBlameSomeoneElse
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
John Meyer
*nix forums beginner
Joined: 20 Dec 2005
Posts: 31
|
| Posted: Mon Jul 03, 2006 3:07 pm Post subject:
Re: PostgreSQL or mySQL
|
|
|
Chris White wrote:
| Quote: | On Sunday 02 July 2006 12:22 pm, Kirti S. Bajwa wrote:
I have very little knowledge of either PostgreeSQL or mySQL. Please advise
me as to which of these two software package to use? I need some specific
examples as to superiority of one package over the other. I prefer using
the package which has a goof GUI database design.
I hate to say this but, if you want objective/non-biased answers, you're
better off looking for a general database list, then posting to 2 lists that
are very database implementation specific. In fact, why not just google
around for "Postgres MySQL comparison" or something of the like.
|
I thinki you're (not you Chris) are missing the point as well, "good GUI
design"? I think you're looking for a prettier front end than you are
in the specific bank-end database.
--
Online library -- http://pueblonative.110mb.com
138 books and counting.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Karl Larsen
*nix forums beginner
Joined: 16 Jun 2006
Posts: 21
|
| Posted: Mon Jul 03, 2006 8:48 pm Post subject:
Re: PostgreSQL or mySQL
|
|
|
Chris White wrote:
| Quote: | On Sunday 02 July 2006 12:22 pm, Kirti S. Bajwa wrote:
I have very little knowledge of either PostgreeSQL or mySQL. Please advise
me as to which of these two software package to use? I need some specific
examples as to superiority of one package over the other. I prefer using
the package which has a goof GUI database design.
I hate to say this but, if you want objective/non-biased answers, you're
better off looking for a general database list, then posting to 2 lists that
are very database implementation specific. In fact, why not just google
around for "Postgres MySQL comparison" or something of the like.
http://www.devx.com/dbzone/Article/20743 |
The above URL is a comparison of MySQL, PostgeSQL and Oracle and DB.
It says in many words that your OK with either Open Source engine
provided your storage capability stays below 10 GBytes. If the DB is
larger than this then the commercial software is better.
So it appears that you can store a large companies data with the
Open Source software and in fact it is happening Right Now! Both
software on Linux is in big companies and is being used daily.
I think this is a fine comparison and worth reading.
Karl Larsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Jesse
*nix forums addict
Joined: 14 Dec 2005
Posts: 56
|
| Posted: Tue Jul 04, 2006 2:42 pm Post subject:
Re: More Stored Procedure Issues
|
|
|
VB is not case sensitive, so changing the case didn't make any difference.
I'm still getting the same non-sensical error message.
Jesse
----- Original Message -----
From: "DG @ NEFACOMP" <dg@nefacomp.net>
To: "Jesse" <jlc@msdlg.com>; "MySQL . Net List" <dotnet@lists.mysql.com>
Sent: Tuesday, July 04, 2006 10:23 AM
Subject: Re: More Stored Procedure Issues
| Quote: | On my side your code works (in C#) but I had to change one line
Cmd.Parameters.Add("?cInvNo",MySQLDBType.VarChar)
I changed it to:
Cmd.Parameters.Add("?cInvNo", MySqlDbType.VarChar)
(The difference is on the casing of <<<MySqlDbType>>>)
Hope this helps
Emery
----- Original Message -----
From: "Jesse" <jlc@msdlg.com
To: "MySQL . Net List" <dotnet@lists.mysql.com
Sent: Tuesday, July 04, 2006 15:20
Subject: More Stored Procedure Issues
OK, same SP, different problem. I did some very minor editing of my SP,
and got rid of some space between parameters, and moved a parenthesis,
made sure that my user had Execute permissions for SP's, and now, I'm
getting a different error. I get the error, "42000Incorrect number of
arguments for PROCEDURE bpa.sp_GetNextInv; expected 2, got 0", which
makes absolutely no sense to me, because I HAVE defined 2
parameters....!!!! Here is my ASP.Net code:
%@ Import Namespace="System.Data"%
Script Runat="Server"
function GetNextInv(nChapterID As Integer) As String
Dim Cmd As MySQLCommand, Conn AS MySQLConnection
Conn = OpenConn
Cmd = New MySQLCommand("sp_GetNextInv", Conn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("?nChapterID",nChapterID)
Cmd.Parameters("?nChapterID").Direction = ParameterDirection.Input
Cmd.Parameters.Add("?cInvNo",MySQLDBType.VarChar)
Cmd.Parameters("?cInvNo").Direction = ParameterDirection.Output
Cmd.ExecuteNonQuery()
GetNextInv = CStr(Cmd.Parameters("?cInvNo").Value)
Cmd=Nothing
Conn.Close
Conn=Nothing
end function
/script
here is the stored procedure:
CREATE PROCEDURE `sp_GetNextInv`(
in nChapterID Int,
out cInvNo VarChar(7)
)
BEGIN
Declare cPrefix VarChar(1);
Declare cNextInv VarChar(7);
Set cInvNo = '';
IF nChapterID > 0 THEN
SELECT TempInvNo INTO cInvNo FROM Chapters WHERE ID=nChapterID;
END IF;
IF (cInvNo = '') or (cInvNo IS NULL) THEN
SELECT NextInvoiceNo INTO cInvNo FROM Config;
SET cNextInv = Right('0000000' + CONVERT(CONVERT(cInvNo, UNSIGNED) + 1,
CHAR), 7);
UPDATE Config SET NextInvoiceNo=cNextInv;
IF nChapterID = -1 THEN
Set cInvNo = CONCAT('L',Right(CONCAT('000000',cInvNo),6));
END IF;
IF nChapterID = -2 THEN
Set cInvNo = CONCAT('C',Right(CONCAT('000000',cInvNo),6));
END IF;
IF nChapterID > 0 THEN
SELECT CT.InvPrefix INTO cPrefix FROM ChapterType CT, Chapters C
WHERE C.ID=nChapterID AND CT.ChapterType=C.ChapterType;
Set cInvNo = CONCAT(cPrefix,Right(CONCAT('000000',cInvNo),6));
UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
END IF;
END IF;
END
If anyone has any clue why it's telling me I haven't defined parameters
when I have, I would greatly appreciate it.
Thanks,
Jesse
--
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/dotnet
To unsubscribe: http://lists.mysql.com/dotnet?unsub=dg@nefacomp.net
|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Harini Raghavan
*nix forums beginner
Joined: 04 Oct 2005
Posts: 7
|
| Posted: Thu Jul 06, 2006 6:12 am Post subject:
special char in full-text search
|
|
|
Hi All,
I am using the MySQL full text search capability in the search workflow
in my appplication. I found that MySQL treats special character like
*./,* etc. as tokenizers if they are not specified within a
phrase(inside double quotes). For ex. If the search string entered is
M.B.A or 24/7 then it searches for M, B, and A sepearately. I can't
include the quotes for all search strings as wild char(*) search would
not work when specified within a phrase. Is there a way to avoid MySQL
from tokenizing the search criteria?
Any ideas?
Thanks,
Harini
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Tue Dec 02, 2008 6:30 am | All times are GMT
|
|
Auto Loans | Loans | Internet Advertising | Personal Loans | Cheap Plane Tickets
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
MySQL
