Skipping versions in MySQL upgrade?
In the mysql documentation on upgrading on the mysql website it is said that it's a better idea to upgrade on a step-by-step bases using the direct-followup versions of MySQL every time and not to upgrade jumping a couple versions (ex. from 3.22 to 4.0, skipping 3.23). I would like to know what the impact would be If I upgraded directly from version 3.23 to version 4.1 and why it is said that it is not a good idea to do so. -- Petrus Venter Hetzner Africa Tel: +27 21 970 2000 Fax: +27 21 970 2001 Email Disclaimer: http://www.hetzner.co.za/index.php?id=245 [ * Awarded Top 50 ICT Company in South Africa for the period 2003/4 by the Corporate Research Foundation] [ * Named National Top 300 High Growth Companies by DTI for the period 2004/5] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Release date for MySQL 4.1 Official verison
Does anybody know what the planned release date is for MySQL 4.1 Official release (not Gamma). -- Petrus Venter Hetzner Africa Tel: +27 21 970 2000 Fax: +27 21 970 2001 Email Disclaimer: http://www.hetzner.co.za/index.php?id=245 [ * Awarded Top 50 ICT Company in South Africa for the period 2003/4 by the Corporate Research Foundation] [ * Named National Top 300 High Growth Companies by DTI for the period 2004/5] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Like - Problem
Hello there,
I was wondering how I could make a specific type of search when the string
has more than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each word
with the fields. The problem is that I can't control how many fields should
be compared.
Also don't know how to compare each word. The following syntax doesn't work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
Cheer's
Rui Monteiro
Working with 160M entries table
Hi, I'm doing a query on the following table: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | time_utc | int(11) | | PRI | 0 | | | prefix | varchar(18) | | PRI | | | +--+-+--+-+-+---+ The query is: - select prefix, ((time_utc-1004127737) div 86400),count(*)from t129_250_0_11 group by prefix,((time_utc-1004127737) div 86400); - I'm doing it on a dual processor 2Mhz Athlon 64-bit machine. The only problem is that the table has about 160,000,000 (160M) entries and i'm afraid that disk i/o looking for indexes is taking most of time. The query has been running for almost 10 hours now. Do you have any sugestions that might speed up the query? Do you think removing the indexes will speed up things? Thanks in advance for your help! --Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: Two more Gmail invites
Just to let people know. And BTW, you have to say that you want the account. Reply off list. Onlist, I'd like to know how most people back up their Mysql dbs? XML or direct SQL file? I prefer the latter, although I'd like to hear from proponents of the former. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Two more Gmail invites
On Thu, 23 Sep 2004, John Meyer wrote: Just to let people know. And BTW, you have to say that you want the account. Reply off list. Onlist, I'd like to know how most people back up their Mysql dbs? XML or direct SQL file? I prefer the latter, although I'd like to hear from proponents of the former. I use mysqldump to dump entire databases to simple ASCII text files that can then be compressed. Then I can simply pipe the file into the mysql client to restore create them, eg: mysqladmin -u root -pxx create sound_sources cat sound_sources.dump | mysql -u root -pxx sound_sources The nice thing about working with mysqldump fiels is they're editable with any text editor, so you can massage tables, delete tables, etc or you can simply restore a single table instead of all of them. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom Auto-Increment Problem
In article [EMAIL PROTECTED], Dan Tappin [EMAIL PROTECTED] writes: -Original Message- From: Harald Fuchs Sent: Wednesday, September 22, 2004 9:39 AM To: [EMAIL PROTECTED] Subject: Re: Custom Auto-Increment Problem The only difference is when you delete rows inbetween. In this case id_calc will be less than id. I stand corrected. Yes it can be calculated but however the deletion of rows makes the calculation unreliable. The id number has to be constant regardless of row deletion. I can't have the id changing thus that's why I am storing it. But this means that the ids for each id_client are not any more consecutive, they're only unique. If you don't require that the ids are always consecutive, then why not use a single auto_increment id column which guarantees unique ids? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Still can't get a response from MySQL AB
Last week, I asked this list had others had problems contacting MySQL
AB. I promptly received a short note from
[EMAIL PROTECTED]
It suggested I could respond to that address which I tried to do, but
I get this:
- The following addresses had transient non-fatal errors -
[EMAIL PROTECTED]
That later became permanent. For some unexplained reason, the name
server is timing out. Something similar happened when I tried
replying to email from this address:
[EMAIL PROTECTED]
.. except the message was that the address doesn't exist at all --
despite the fact that I'd received mail from it. This isn't a man in
the middle attack of some kind is it -- or am I simply dead and don't
know it?
What could possibly explain why the mysql.com domain would have such a
problem when others don't? I can't for the life of me see what's
different between what works and what doesn't.
I'd prefer not to do the correspondence through this list which
already has lots of traffic.
Ideas are most welcome.
Thanx
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
great problem with questions
hello,
I have database with 30 tables [some have over 2000k some other over 4000k rec]
when I want to run a sql with a few conditions the answer is dramatically slow
[over 70 seconds!]
sql-s with one condition usually works well..
how is the corrcet way to optimize the database and sql questions?
the answer from sql should be under 1 second...
SHOW VARIABLES gives these informations:
Variable_name Value
back_log 50
basedir /usr/local/mysql/
bdb_cache_size 8388600
bdb_log_buffer_size 32768
bdb_home /dysk/mysql/data/
bdb_max_lock 1
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2...
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin2
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /dysk/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -()~*:|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb YES
have_crypt YES
have_innodb YES
have_isam YES
have_raid YES
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
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_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/local/mysql/share/mysql/polish/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /dysk/mysql/data/mysqld.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone CEST
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.20-max
version_comment Official MySQL-max binary
version_compile_os pc-linux
wait_timeout 28800
a good example of question is:
select distinct logo, klient.klientid, klientnazwa, struktura,
concat(kodpocztowy,' ',miejscowosc) miasto, aparatnumer, concat(ulicaskrot,'
',ulicanazwa,' ',posesja) ulica,concat('woj. ',wojewodztwo,' powiat: ',powiat)
wojpow, klientbranza branza, email, www, wizytowka
from klient, klientulice, klientulica, klientmiejscowosci, klientmiejscowosc,
branzaslowa, branzaslowo, klientbranza, klientslowa, klientslowo
where wojewodztwoid=7 AND klientulica.klientulica like'dwo%' AND klient.klientid
= klientulice.klientid AND klientulice.klientulicaid= klientulica.klientulicaid
AND klientmiejscowosc.klientmiejscowosc like'war%' AND klient.klientid =
klientmiejscowosci.klientid AND klientmiejscowosci.klientmiejscowoscid=
klientmiejscowosc.klientmiejscowoscid AND branzaslowo.branzaslowo like'sam%' AND
klient.klientid = klientbranza.klientid AND
klientbranza.branzaid=branzaslowa.branzaid AND branzaslowa.branzaslowoid=
branzaslowo.branzaslowoid AND (klientslowo.klientslowo LIKE 'sam%') AND
klient.klientid = klientslowa.klientid AND klientslowa.klientslowoid=
klientslowo.klientslowoid
can anyone help me with this?
with best regards
Darek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
A query to swap the data in two fields
Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds Do i need to temporarily store one of the fields (thinking like a programmer)? Can you suggest what query I need here pls? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
* DeRyl I have database with 30 tables [some have over 2000k some other over 4000k rec] when I want to run a sql with a few conditions the answer is dramatically slow [over 70 seconds!] sql-s with one condition usually works well.. how is the corrcet way to optimize the database and sql questions? the answer from sql should be under 1 second... I agree. For a start, show us the output of EXPLAIN select distinct logo, klient.klientid ... Then we will probably need the output of SHOW CREATE TABLE for some of the tables, depending on the output of the EXPLAIN command. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
This is probably due to having indexed only single columns. MySQL uses only
one index per column, meaning if you have a where with two conditions you
need a combined index for the columns used. If there are only indeces for the
single columns, only one index for one condition will be used, the rest
requires a full table scan (this is explained somewhere in the docs, have a
look).
Stefan
Am Thursday 23 September 2004 11:50 schrieb DeRyl:
hello,
I have database with 30 tables [some have over 2000k some other over 4000k
rec]
when I want to run a sql with a few conditions the answer is dramatically
slow [over 70 seconds!]
sql-s with one condition usually works well..
how is the corrcet way to optimize the database and sql questions?
the answer from sql should be under 1 second...
SHOW VARIABLES gives these informations:
Variable_name Value
back_log 50
basedir /usr/local/mysql/
bdb_cache_size 8388600
bdb_log_buffer_size 32768
bdb_home /dysk/mysql/data/
bdb_max_lock 1
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2...
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin2
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /dysk/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -()~*:|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb YES
have_crypt YES
have_innodb YES
have_isam YES
have_raid YES
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
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_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/local/mysql/share/mysql/polish/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /dysk/mysql/data/mysqld.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone CEST
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.20-max
version_comment Official MySQL-max binary
version_compile_os pc-linux
wait_timeout 28800
a good example of question is:
select distinct logo, klient.klientid, klientnazwa, struktura,
concat(kodpocztowy,' ',miejscowosc) miasto, aparatnumer,
concat(ulicaskrot,' ',ulicanazwa,' ',posesja) ulica,concat('woj.
',wojewodztwo,' powiat: ',powiat) wojpow, klientbranza branza, email, www,
wizytowka
from klient, klientulice, klientulica, klientmiejscowosci,
klientmiejscowosc, branzaslowa, branzaslowo, klientbranza, klientslowa,
klientslowo
where wojewodztwoid=7 AND klientulica.klientulica like'dwo%' AND
klient.klientid = klientulice.klientid AND klientulice.klientulicaid=
klientulica.klientulicaid AND klientmiejscowosc.klientmiejscowosc
like'war%' AND klient.klientid =
Re: great problem with questions
explain example sql question gives: table type possible_keys key key_len ref rows Extra klientslowo range PRIMARY,klientslowo klientslowo 40 NULL 351 Using where; Using temporary klientslowa ref klientslowoid klientslowoid 4 klientslowo.klientslowoid 19 Using index klient ref klientid,wojewodztwoid klientid 9 klientslowa.klientid 1 Using where klientmiejscowosci ref KLIENTMIEJSCOWOSCID,KLIENTID KLIENTID 9 klient.klientid 1 Using where; Distinct klientmiejscowosc eq_ref PRIMARY,KLIENTMIEJSCOWOSC PRIMARY 4 klientmiejscowosci.KLIENTMIEJSCOWOSCID 1 Using where; Distinct klientulice ref klientulicaid,klientid klientid 9 klient.klientid 1 Using where; Distinct klientbranza ref BRANZAID,KLIENTID KLIENTID 9 klient.klientid 2 Using where; Distinct branzaslowa ref branzaslowoid,branzaid branzaid 4 klientbranza.BRANZAID 3 Distinct branzaslowo eq_ref PRIMARY,branzaslowo PRIMARY 4 branzaslowa.branzaslowoid 1 Using where; Distinct klientulica eq_ref PRIMARY,klientulica PRIMARY 4 klientulice.klientulicaid 1 Using where; Distinct and the create table for explain tables: CREATE TABLE `branzaslowa` ( `branzaid` int(11) NOT NULL default '0', `branzaslowoid` int(11) NOT NULL default '0', KEY `branzaslowoid` (`branzaslowoid`), KEY `branzaid` (`branzaid`) ) TYPE=MyISAM; CREATE TABLE `branzaslowo` ( `branzaslowoid` int(11) NOT NULL auto_increment, `branzaslowo` varchar(32) default NULL, PRIMARY KEY (`branzaslowoid`), KEY `branzaslowo` (`branzaslowo`) ) TYPE=MyISAM AUTO_INCREMENT=1710 ; CREATE TABLE `klient` ( `klientid` bigint(14) default NULL, `klientnazwaorder` int(11) default NULL, `id_inst_nadrz` bigint(14) default NULL, `id_isnt_glown` bigint(14) default NULL, `wojewodztwoid` int(11) default NULL, `powiatid` int(11) default NULL, `gminaid` int(11) default NULL, `numerporzadkowy` int(11) default NULL, `klientnazwa` varchar(250) NOT NULL default '', `klientbranza` longtext, `ulicaskrot` varchar(20) default NULL, `ulicanazwa` varchar(255) default NULL, `posesja` varchar(100) default NULL, `miejscowosc` varchar(100) default NULL, `kodpocztowy` varchar(22) default NULL, `powiat` varchar(100) default NULL, `gmina` varchar(100) default NULL, `wojewodztwo` varchar(100) default NULL, `aparatnumer` longtext, `www` varchar(100) default NULL, `email` varchar(100) default NULL, `logo` varchar(100) default NULL, `wizytowka` varchar(100) default NULL, `wizytowkas` int(11) default NULL, `wizytowkaw` int(11) default NULL, `struktura` int(11) default NULL, `id_regionu` int(11) default NULL, `zrodlo` char(1) default NULL, `kwotareklam` double default NULL, KEY `klientid` (`klientid`), KEY `wojewodztwoid` (`wojewodztwoid`) ) TYPE=MyISAM; CREATE TABLE `klientbranza` ( `KLIENTID` bigint(14) default NULL, `BRANZAID` int(11) default NULL, `CZYPODSTAWOWA` int(11) default NULL, KEY `BRANZAID` (`BRANZAID`), KEY `KLIENTID` (`KLIENTID`) ) TYPE=MyISAM; CREATE TABLE `klientmiejscowosc` ( `KLIENTMIEJSCOWOSCID` int(11) NOT NULL default '0', `KLIENTMIEJSCOWOSC` char(64) default NULL, PRIMARY KEY (`KLIENTMIEJSCOWOSCID`), KEY `KLIENTMIEJSCOWOSC` (`KLIENTMIEJSCOWOSC`) ) TYPE=MyISAM; CREATE TABLE `klientmiejscowosci` ( `KLIENTID` bigint(14) default NULL, `KLIENTMIEJSCOWOSCID` int(11) default NULL, KEY `KLIENTMIEJSCOWOSCID` (`KLIENTMIEJSCOWOSCID`), KEY `KLIENTID` (`KLIENTID`) ) TYPE=MyISAM; CREATE TABLE `klientslowa` ( `klientid` bigint(14) NOT NULL default '0', `klientslowoid` int(11) NOT NULL default '0', KEY `klientslowoid` (`klientslowoid`,`klientid`) ) TYPE=MyISAM; CREATE TABLE `klientslowo` ( `klientslowoid` int(11) NOT NULL auto_increment, `klientslowo` varchar(40) NOT NULL default '', PRIMARY KEY (`klientslowoid`), KEY `klientslowo` (`klientslowo`) ) TYPE=MyISAM AUTO_INCREMENT=228453 ; CREATE TABLE `klientulica` ( `klientulicaid` int(11) NOT NULL default '0', `klientulica` char(96) default NULL, PRIMARY KEY (`klientulicaid`), KEY `klientulica` (`klientulica`) ) TYPE=MyISAM; CREATE TABLE `klientulice` ( `klientid` bigint(14) default NULL, `klientulicaid` int(11) default NULL, KEY `klientulicaid` (`klientulicaid`), KEY `klientid` (`klientid`) ) TYPE=MyISAM; --- Darek - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: DeRyl [EMAIL PROTECTED] Sent: Thursday, September 23, 2004 1:19 PM Subject: Re: great problem with questions * DeRyl I have database with 30 tables [some have over 2000k some other over 4000k rec] when I want to run a sql with a few conditions the answer is dramatically slow [over 70 seconds!] sql-s with one condition usually works well.. how is the corrcet way to optimize the database and sql questions? the answer from sql should be under 1 second... I agree. For a start, show us the output of EXPLAIN select distinct logo, klient.klientid ... Then we will probably need the output of SHOW CREATE TABLE for some of the tables, depending
Re: great problem with questions
Am Thursday 23 September 2004 13:22 schrieb Stefan Kuhn:
This is probably due to having indexed only single columns. MySQL uses only
one index per column, meaning if you have a where with two conditions you
Sorry, this should read one index per table...
need a combined index for the columns used. If there are only indeces for
the single columns, only one index for one condition will be used, the rest
requires a full table scan (this is explained somewhere in the docs, have a
look).
Stefan
Am Thursday 23 September 2004 11:50 schrieb DeRyl:
hello,
I have database with 30 tables [some have over 2000k some other over
4000k rec]
when I want to run a sql with a few conditions the answer is dramatically
slow [over 70 seconds!]
sql-s with one condition usually works well..
how is the corrcet way to optimize the database and sql questions?
the answer from sql should be under 1 second...
SHOW VARIABLES gives these informations:
Variable_name Value
back_log 50
basedir /usr/local/mysql/
bdb_cache_size 8388600
bdb_log_buffer_size 32768
bdb_home /dysk/mysql/data/
bdb_max_lock 1
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2...
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin2
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /dysk/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -()~*:|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb YES
have_crypt YES
have_innodb YES
have_isam YES
have_raid YES
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
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_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/local/mysql/share/mysql/polish/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /dysk/mysql/data/mysqld.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone CEST
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.20-max
version_comment Official MySQL-max binary
version_compile_os pc-linux
wait_timeout 28800
a good example of question is:
select distinct logo, klient.klientid, klientnazwa, struktura,
concat(kodpocztowy,' ',miejscowosc) miasto, aparatnumer,
concat(ulicaskrot,' ',ulicanazwa,' ',posesja) ulica,concat('woj.
',wojewodztwo,' powiat: ',powiat) wojpow, klientbranza branza, email,
www, wizytowka
from klient, klientulice, klientulica, klientmiejscowosci,
klientmiejscowosc, branzaslowa, branzaslowo, klientbranza,
Shared library Compiling error
Hi, I'm trying to compile my own mysql client program under solaris 9 without luck. I have the mysql libraries under : /usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib and has the following files: libdbug.a libmysqlclient.a libmysqld.a libmysys.a libmygcc.a libmysqlclient_r.a Don't I need to have the libmysqlclient.so.10 file here too? on my system the libmysqlclient.so.10 is found under directory :/usr/local/openv/lib \ Should I move it /usr/lib instread? Here is what I tried and the errors I get: When the makefile has: #L LD=gcc -lm -lmysqlclient the error is : symbol mysql_init: referenced symbol not found - When the makefile has: #L LD=gcc -lm -Wl,r/usr/local/openv/lib -lmysqlclient the error is : ld: fatal: file r/usr/local/openv/lib: open failed: No such file or directory When the makefile has: #L LD=gcc -lm -L/usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib -lmysqlclient the error is : symbol mysql_init: referenced symbol not found - any ideas to what I'm missing ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ResultSet NotUpdatable problem
Hello: I am having a problem with an (not)updatable ResultSet that I cannot figure out. The problem is not consistent in that the exact SQL statements and tables involved will work one time and then fail the next. There does not appear to be any rhyme or reason as to when or why it fails. Once it does fail, simply retrying (perhaps several times) get's me back to a working state. I have already looked extensivley for any sort of non-closed Connection, ResultSet, etc. and have found nothing. Additionally, I have isolated this in order to ensure there are no Threading considerations. Here is the table involved: CREATE TABLE OSECRegistration ( id VARCHAR(255) NOT NULL, hostName VARCHAR(255) NOT NULL, platformType VARCHAR(255) NOT NULL, registrationTime BIGINT NOT NULL, createdBy VARCHAR(255) NOT NULL, dateCreated BIGINT NOT NULL, modifiedBy VARCHAR(255) NOT NULL, dateModified BIGINT NOT NULL, PRIMARY KEY( id ), UNIQUE ( hostName, platformType ), INDEX id_index ( id ), INDEX PlatformType_index ( platformType ), FOREIGN KEY PlatformType_key ( platformType ) REFERENCES PlatformType ( platformType ) ) TYPE=InnoDB; The SQL that I am using in a PreparedStatement is as follows: String stmt = select id, registrationTime, modifiedBy, dateModified from OSECRegistration where ( hostName = ? ) AND platformType = ? for update; I prepare the statement as follows: ps = con.prepareStatement( stmt, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE ); I then resolve the '?' values and: ResultSet rs = ps.executeQuery(); and walk through the ResultSet modifying the required fields with statements like: rs.updateLong( i, ((Long) newValue).longValue() ); As far as I can tell, all of the above is correct. Yet, I still inconsistantly get errors such as the following: [junit] com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details. [junit] at com.mysql.jdbc.UpdatableResultSet.generateStatements(UpdatableResultSet.java:1770) [junit] at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1959) [junit] at com.mysql.jdbc.UpdatableResultSet.updateLong(UpdatableResultSet.java:1343) [junit] at com.ensuren.ose.common.registration.OSECRegistrationJDBCDao.updateResultSet(OSECRegistrationJDBCDao.java:2298) [junit] at com.ensuren.ose.common.registration.OSECRegistrationJDBCDao.readForUpdate(OSECRegistrationJDBCDao.java:724) [junit] at com.ensuren.ose.server.registration.RegistrationBean.register(RegistrationBean.java:140) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [junit] at java.lang.reflect.Method.invoke(Method.java:324) [junit] at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683) [junit] at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185) [junit] at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72) [junit] at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84) [junit] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:315) [junit] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:148) [junit] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:120) [junit] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191) [junit] at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122) [junit] at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331) [junit] at org.jboss.ejb.Container.invoke(Container.java:723) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [junit] at java.lang.reflect.Method.invoke(Method.java:324) [junit] at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:60) [junit] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:61) [junit]
Re: Having rows or fields that can't be modified once entered (NOT grant statement).
Thanks for the responses, but this isn't what I'm looking for. I'm looking for a way to make a field or record unchangeable for ANYONE, including the system administrator. That way, I can have a fairly high level of assurance that the record is valid and hasn't been tweaked in any way. Anyway to do this within the database itself? Thanks!! Jigal van Hemert wrote: I was wondering if there was some builtin mechanism I could use to ensure that a field or row in my database could not be altered after it was initially inserted. Is there anything in the database that would allow this type of functionality? Thanks!! You can give a user certain rights to do things with certain databases or tables. You cannot set the rights for a limited number of rows however. Take a look at http://dev.mysql.com/doc/mysql/en/GRANT.html If you do not GRANT (or if you REVOKE) the rights for this particular user to DELETE or UPDATE the table he/she can only insert new data and select things (and all other things he/she has the rights for). It's good practise to only grant the rights to a user he/she needs to do. For use with (web) applications it's also better to create a new user for that purpose and grant the necessary right. Regards, Jigal.
4.1.3-5. Bugs alive!
Hello mysql,
Using 'int not null' make next enum (using cp1251) corrupted in
mysqldump output on W2000.
Is this a known issue?
How-to-repeat:
---
drop table if exists t1;
drop table if exists t2;
create table t1 (a int, b enum('','')) DEFAULT CHARACTER SET cp1251;
create table t2 (a int not null, b enum('','')) DEFAULT CHARACTER SET cp1251;
show fields from t1 like 'b';
show fields from t2 like 'b';
+---+---+-+--+--+--+
| b | enum('','') | YES | | NULL | |
+---+---+-+--+--+--+
| b | enum('','') | YES | | NULL | | -- Ok
+---+---+-+--+--+--+
show create table t1;
show create table t2;
++
| t1 | CREATE TABLE `t1` (
`a` int(11) default NULL,
`b` enum('','') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
++
++
| t2 | CREATE TABLE `t2` (
`a` int(11) NOT NULL default '0',
`b` enum('?','?') default NULL -- Oops!
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
++
--
Best regards,
Juri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie green pea
- Original Message - From: B Wiley Snyder [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 23, 2004 2:09 AM Subject: newbie green pea Hello everyone, I know Rhino gave me a link or something a month ago but. can anyone give me a link to a tutorial on how to ...basically I have been creating my databases one command entry at a time. I just thought if someone knew of a site off the top of there list that could help me create a file of a database and deploy it. I've got a mysql BIBLE, if anything educate me on the terminology so I can look it up. Thanks for anyones generous and helpful response I think the word you're looking for is 'script'. A script is a file that contains a series of commands that are supposed to be executed; you type one command to run the script and all the commands within the script are executed as a result. Of course, a script can get more complicated: it could have conditional logic so that some commands are only executed if a particular condition or combination of conditions are true. Here is an example of a script that does a few different things, some of them conditionally: -- use NFL; drop table if exists Seasons; create table if not exists Seasons (season smallint not null, primary key(season), ) Type=InnoDB; drop table if exists Teams; create table if not exists Teams (team_code char(3) not null, team_name char(50) not null, team_conference char(3) not null, team_division char(5) not null, primary key(team_code), ) Type=InnoDB; insert into Seasons values(2002); insert into Seasons values(2003); insert into Seasons values(2004); select * from Seasons; load data infile '/home/rhino/MySQL/NFL/Teams.asc' replace into table Teams fields terminated by ';' optionally enclosed by '' escaped by '\\' lines starting by '' terminated by '\n'; select * from Teams; -- The script is in a file called Create_Teams.sql. The first statement, 'use NFL', tells the script that the database it is supposed to use is called NFL. The first Drop Table statement tells the script to drop an existing table named Seasons if it already exists; if it doesn't exist, the script doesn't drop anything and proceeds to the first Create Table statement. The first Create Table statement creates a table named 'Seasons' if it doesn't exist already; if the table does exist, the statement doesn't create anything and proceeds to the next statement. The second Drop Table statement works like the first Drop Table statement. The second Create Table statement works like the first Create Table statements. The three Insert statements each add a row to the Seasons table. 'select * from Seasons' displays the contents of the Seasons table. The 'load data' statement populates the Teams table from an external file named 'NFL.Teams.asc'. 'select * from Teams' displays the contents of the Teams table after the table has been loaded. To execute the script, you go to your command line and execute this command: mysql NFL -u yourid -p Create_Teams.sql Create_Teams.out Supply your password when you are prompted for it. The output from the script will be written to a file called 'Create_Teams.out'. If you imitate the techniques shown in this note, you should be in business. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
* DeRyl explain example sql question gives: [...] The first thing to notice: Using temporary... this is to be avoided, if possible. The first table read is klientslowo based on the criteria klientslowo.klientslowo LIKE 'sam%'. Is this a reasonable approach to solving the query, giving your knowledge of the database structure and the data distribution? MySQL estimates that 351 records matches this criteria, is this close to the truth? If not, run ANALYZE TABLE. URL: http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html You should not need the DISTINCT keyword. There is no PRIMARY KEY in the branzaslowa table, it seems to have duplicates? You use a lot of bigint's... an unsigned int is usually sufficient, it consumes half the disc/RAM space... this could be important for the speed of the indexes. Optimally you would want all your indexes to fit in RAM, i.e. the sum of the size of the indexes should be smaller than your index buffer size (key_buffer_size). I took a look at the variables from your previous posting. You should increase the key_buffer_size, it is only 8M (default). If you only use MyISAM tables, try setting it to half your total available RAM. This will not affect the speed of the first few queries you run after a restart, but when the caches get hot you get very improved speed. URL: http://dev.mysql.com/doc/mysql/en/Server_parameters.html URL: http://dev.mysql.com/doc/mysql/en/MyISAM_key_cache.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Working with 160M entries table
I don't think indexes have anything to do with it, unless you have an index on the prefix field, in which case it might use it for the grouping. But regardless, you are selecting all 160M records (no index used) AND doing 160M calculations (no index used) and then grouping 160M records (maybe an index used). That's a lot of stuff going on for which you should have lot of memory, fast I/O and the proper my.cnf settings. For instance, if you sort buffer setting in the config file is set pretty high, this may take quite a while. On Sep 23, 2004, at 3:35 AM, Ricardo Oliveira wrote: Hi, I'm doing a query on the following table: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | time_utc | int(11) | | PRI | 0 | | | prefix | varchar(18) | | PRI | | | +--+-+--+-+-+---+ The query is: - select prefix, ((time_utc-1004127737) div 86400),count(*)from t129_250_0_11 group by prefix,((time_utc-1004127737) div 86400); - I'm doing it on a dual processor 2Mhz Athlon 64-bit machine. The only problem is that the table has about 160,000,000 (160M) entries and i'm afraid that disk i/o looking for indexes is taking most of time. The query has been running for almost 10 hours now. Do you have any sugestions that might speed up the query? Do you think removing the indexes will speed up things? Thanks in advance for your help! --Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connecting on MySQL Sockets Using Connector/J
Hello guys... I trying to connect on LOCAL MYSQL SOCKET in /kiko_SMS/kart/mysql.sock using Connector/J but the sintax of URL only accept HOSTNAMES... how connect on Local MySQL Sockets using Connector/J My system is a Debian Box 3.1 using kernel 2.6.7 and Java ;) Tnks.. MySQL,Query,helll - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 296 -6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Working with 160M entries table
Ricardo, The best performance solution is to create another column to contain (time_utc-1004127737) div 86400 Update the table to set this value correctly for every row, then calculate the value for this column every time you insert more data. Create an index on prefix and the new column and this should become quick, especially if you tune sort buffers and cache sizes. This solution is obviously at the expense of more storage space, but it will speed things up. Andy -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 23 September 2004 13:48 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Working with 160M entries table I don't think indexes have anything to do with it, unless you have an index on the prefix field, in which case it might use it for the grouping. But regardless, you are selecting all 160M records (no index used) AND doing 160M calculations (no index used) and then grouping 160M records (maybe an index used). That's a lot of stuff going on for which you should have lot of memory, fast I/O and the proper my.cnf settings. For instance, if you sort buffer setting in the config file is set pretty high, this may take quite a while. On Sep 23, 2004, at 3:35 AM, Ricardo Oliveira wrote: Hi, I'm doing a query on the following table: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | time_utc | int(11) | | PRI | 0 | | | prefix | varchar(18) | | PRI | | | +--+-+--+-+-+---+ The query is: - select prefix, ((time_utc-1004127737) div 86400),count(*)from t129_250_0_11 group by prefix,((time_utc-1004127737) div 86400); - I'm doing it on a dual processor 2Mhz Athlon 64-bit machine. The only problem is that the table has about 160,000,000 (160M) entries and i'm afraid that disk i/o looking for indexes is taking most of time. The query has been running for almost 10 hours now. Do you have any sugestions that might speed up the query? Do you think removing the indexes will speed up things? Thanks in advance for your help! --Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: great problem with questions
The first thing to notice: Using temporary... this is to be avoided, if possible. ## how is the correct way to avoid that? The first table read is klientslowo based on the criteria klientslowo.klientslowo LIKE 'sam%'. Is this a reasonable approach to solving the query, giving your knowledge of the database structure and the data distribution? MySQL estimates that 351 records matches this criteria, is this close to the truth? If not, run ANALYZE TABLE. ## after analyze I saw: Table Op Msg_type Msg_text ikt.klientslowo analyze status Table is already up to date You should not need the DISTINCT keyword. There is no PRIMARY KEY in the branzaslowa table, it seems to have duplicates? ## it shouldn't so I'll try to select without DISTINCT You use a lot of bigint's... an unsigned int is usually sufficient, it consumes half the disc/RAM space... this could be important for the speed of the indexes. Optimally you would want all your indexes to fit in RAM, i.e. the sum of the size of the indexes should be smaller than your index buffer size (key_buffer_size). ## some of tables [like klient, klientslowa, klientulice, klientmiejscowosci etc...] uses in klientid field a numeric value which is 12,14 or 14 characters long so how to correct that? I took a look at the variables from your previous posting. You should increase the key_buffer_size, it is only 8M (default). If you only use MyISAM tables, try setting it to half your total available RAM. This will not affect the speed of the first few queries you run after a restart, but when the caches get hot you get very improved speed. ## how to change that from shell? I know Apache a little... regards in advance Darek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting by a comma list
Hi Everyone. I'm not sure if this is possible of not, but I want to sort a query by a comma list. Here is what I'm doing: SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) This returns the results ok but they are not in any particular order. I want the result in the order the ID's appear in the comma list in the IN statement. Is there a ORDER BY statement I can add to do this? So what I want to do is: SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) ORDER BY id in this order (4,1,3,6,8,2) Thanks Andrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like - Problem
This is the third time you have asked this, and it has been answered twice. Once is enough. Rui Monteiro wrote: Hello there, I was wondering how I could make a specific type of search when the string has more than one word. Ex.- String = Green Apple -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
The fact that you are joining 10 tables together in one query may be a major portion of your performance problem. Even if the *average* size of each table is only 10 rows, the MySQL engine will have to pour over 10x10x10x10x10x10x10x10x10x10 = 10 to the 10th power = 1e10 = 10,000,000,000 (10 billion) combinations of data rows before it gets to even think about some of your WHERE conditions. I have a few suggestions: FIRST - Rewrite your query to use the explicit INNER JOIN form of table joins and NOT the comma-join form you are currently using. The explicit form is much MUCH easier to double check that you have properly JOINed each table to each other table. Without properly defined join conditions you will create a Cartesian product between two or more of your tables. Cartesian products are rarely good things for query performance. SECOND - Check your indexes. Make sure that you are joining tables on columns that appear first in at least one index. Carefully consider creating a multiple column index or two (especially if this is a common query and/or you use those conditions often). THIRD - Consider evaluating this query in stages. Based on your re-write to eliminate the comma-joins, I can help you with that, too. FOURTH - Consider using table aliases. It may make your SELECT and WHERE clauses easier to read. I look forward to seeing the rewrite. Shawn Green Database Administrator Unimin Corporation - Spruce Pine DeRyl [EMAIL PROTECTED] wrote on 09/23/2004 05:50:21 AM: hello, I have database with 30 tables [some have over 2000k some other over 4000k rec] when I want to run a sql with a few conditions the answer is dramatically slow [over 70 seconds!] sql-s with one condition usually works well.. how is the corrcet way to optimize the database and sql questions? the answer from sql should be under 1 second... SHOW VARIABLES gives these informations: Variable_name Value back_log 50 basedir /usr/local/mysql/ bdb_cache_size 8388600 bdb_log_buffer_size 32768 bdb_home /dysk/mysql/data/ bdb_max_lock 1 bdb_logdir bdb_shared_data OFF bdb_tmpdir /tmp/ bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2... binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set latin2 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis... concurrent_insert ON connect_timeout 5 convert_character_set datadir /dysk/mysql/data/ default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_stopword_file (built-in) have_bdb YES have_crypt YES have_innodb YES have_isam YES have_raid YES have_symlink YES have_openssl NO have_query_cache YES init_file innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdown ON innodb_flush_method innodb_lock_wait_timeout 50 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_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8388600 language /usr/local/mysql/share/mysql/polish/ large_files_support ON license GPL local_infile ON locked_in_memory OFF log OFF log_update OFF log_bin OFF log_slave_updates OFF log_slow_queries OFF log_warnings ON long_query_time 10 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_insert_delayed_threads 20 max_heap_table_size 16777216 max_join_size 18446744073709551615 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit 1024 pid_file /dysk/mysql/data/mysqld.pid log_error port 3306 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_size 0 query_cache_type ON query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 131072 read_only OFF read_rnd_buffer_size 262144 rpl_recovery_rank 0 server_id 0 slave_net_timeout 3600 skip_external_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /tmp/mysql.sock
Re: Sorting by a comma list
In article [EMAIL PROTECTED], Andrew Dixon - MSO.net [EMAIL PROTECTED] writes: Hi Everyone. I'm not sure if this is possible of not, but I want to sort a query by a comma list. Here is what I'm doing: SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) This returns the results ok but they are not in any particular order. I want the result in the order the ID's appear in the comma list in the IN statement. Is there a ORDER BY statement I can add to do this? So what I want to do is: SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) ORDER BY id in this order (4,1,3,6,8,2) I think SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) ORDER BY field(id,4,1,3,6,8,2) should work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Like - Problem
Try the following sintax:
Select * from fruits
where
(fruits.color like '%Green%' or
fruits.color like '%Apple%')
or
(fruits.type like '%Green%' or
fruits.type like '%Apple%');
Regards,
Laercio.
-Original Message-
From: Rui Monteiro [mailto:[EMAIL PROTECTED]
Sent: quarta-feira, 22 de setembro de 2004 04:27
To: [EMAIL PROTECTED]
Subject: Like - Problem
Hello there,
I was wondering how I could make a specific type of search when the string
has more than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each word
with the fields. The problem is that I can't control how many fields should
be compared.
Also don't know how to compare each word. The following syntax doesn't work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
Cheer's
Rui Monteiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting by a comma list
It won't be fast, but SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) ORDER BY FIND_IN_SET(id, '4,1,3,6,8,2'); See the manual for more http://dev.mysql.com/doc/mysql/en/String_functions.html. Michael Andrew Dixon - MSO.net wrote: Hi Everyone. I'm not sure if this is possible of not, but I want to sort a query by a comma list. Here is what I'm doing: SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) This returns the results ok but they are not in any particular order. I want the result in the order the ID's appear in the comma list in the IN statement. Is there a ORDER BY statement I can add to do this? So what I want to do is: SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) ORDER BY id in this order (4,1,3,6,8,2) Thanks Andrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ResultSet NotUpdatable problem
Hi, help me in getting answer for the following What is the difference in MySql between transaction-safe tables (like Innodb or BDB) and non-safe Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: Sorting by a comma list
Excellent. Worked like a dream. Thanks. Andrew -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: 23 September 2004 15:14 To: [EMAIL PROTECTED] Subject: Re: Sorting by a comma list I think SELECT id, title, description FROM table1 WHERE id IN (4,1,3,6,8,2) ORDER BY field(id,4,1,3,6,8,2) should work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared library Compiling error
Nissim Lugasy wrote: Hi, I'm trying to compile my own mysql client program under solaris 9 without luck. I have the mysql libraries under : /usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib and has the following files: libdbug.a libmysqlclient.a libmysqld.a libmysys.a libmygcc.a libmysqlclient_r.a Don't I need to have the libmysqlclient.so.10 file here too? on my system the libmysqlclient.so.10 is found under directory :/usr/local/openv/lib \ Should I move it /usr/lib instread? I'm not sure why you'd have your libraries separated like that, but I'd make sure there wasn't a reason before moving one. Then I'd probably put all the mysql libs in the same place (i.e. move libmysqlclient.so.10), /usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib in your case. Here is what I tried and the errors I get: When the makefile has: #L LD=gcc -lm -lmysqlclient the error is : symbol mysql_init: referenced symbol not found It didn't find libmysqlclient.so.10. - When the makefile has: #L LD=gcc -lm -Wl,r/usr/local/openv/lib -lmysqlclient the error is : ld: fatal: file r/usr/local/openv/lib: open failed: No such file or directory It still didn't find libmysqlclient.so.10, apparently because you have the syntax wrong. When the makefile has: #L LD=gcc -lm -L/usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib -lmysqlclient the error is : symbol mysql_init: referenced symbol not found It didn't find libmysqlclient.so.10, because you told it to look in the wrong place. - any ideas to what I'm missing ? Thanks Try specifying the correct path with -L: #L LD=gcc -lm -L/usr/local/openv/lib -lmysqlclient You may also need -R /usr/local/openv/lib so that your executable knows where to find libmysqlclient.so.10 at run time. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Indexing
I'm interested in this, too. We have a logging table that sees hundreds of rows per second, and we do a ton of monthly reports. We just bit the bullet and added an indexed DATE column. Is there a better strategy? Eamon Daly - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 22, 2004 4:48 PM Subject: Date Indexing Hey all, I have a question about indexing part of a date field. I have a query that I run on a regular basis to retrieve monthly sales numbers: SELECT SUM(OrderSubTotal) FROM tblOrders WHERE DATE_FORMAT(ShipDate, '%Y-%m') = '2004-09'; ShipDate is a date field. My question is how I can phrase the query (or re-index ShipDate) so that MySQL uses the ShipDate index? I've tried figuring out how to index part of a date field, and I can't seem to find any way to do that. I've also tried changing the statement to ShipDate like '2004-09%', as well as the MONTH and YEAR functions, but none of them utilize the index. Is there another, more efficient way to phrase it so the index is used? I'm using MySQL 3.23.53a. The Explain: | table| type | possible_keys | key | key_len | ref | rows | Extra | | tblOrders | ALL | NULL | NULL |NULL | NULL | 122543 | where used | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP reports 3.23.49, command line reports 4.0.1
I installed PHP 4.3.1 via source and used the following option when configuring: ./configure --with-mysql The database has worked great for a couple of years, however I noticed a problem today. Phpinfo() returns MySQL Version 3.23 from a php script. Client API version 3.23.49 MYSQL_MODULE_TYPE builtin MYSQL_SOCKET/var/lib/mysql/mysql.sock MYSQL_INCLUDE no value MYSQL_LIBS no value However, issuing 'mysql -V' at the command line returns version 4.0.1. So I assume the version of MySQL bundled with PHP 4.3.1 is in fact 3.23.49 and this is overriding my standalone installation. Without recompiling, is there a way to upgrade the bundled client? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: great problem with questions
DeRyl [EMAIL PROTECTED] wrote on 09/23/2004 09:51:45 AM:
The fact that you are joining 10 tables together in one query may be a
major portion of your performance problem. Even if the *average* size of
each table is only 10 rows, the MySQL engine will have to pour over
10x10x10x10x10x10x10x10x10x10 = 10 to the 10th power = 1e10 =
10,000,000,000 (10 billion) combinations of data rows before it gets to
even think about some of your WHERE conditions.
## oh I understand...
I have a few suggestions:
FIRST - Rewrite your query to use the explicit INNER JOIN form of table
joins and NOT the comma-join form you are currently using. The explicit
form is much MUCH easier to double check that you have properly JOINed
each table to each other table. Without properly defined join conditions
you will create a Cartesian product between two or more of your tables.
Cartesian products are rarely good things for query performance.
## can you show me an example?
I'm not sure I understand correctly Your suggestion...
This is my attempt to convert your implicit comma joins into explicit
INNER JOIN statements. I also reformatted your query to get rid of
email-wrapping and applied some aliases to your table names.
SELECT DISTINCT
logo
, klient.klientid
, klientnazwa
, struktura
, concat(kodpocztowy,' ',miejscowosc) miasto
, aparatnumer
, concat(ulicaskrot,' ',ulicanazwa,' ',posesja) ulica
, concat('woj. ',wojewodztwo,' powiat:',powiat) wojpow
, klientbranza branza
, email
, www
, wizytowka
FROM klient
INNER JOIN klientulice
ON klient.klientid = klientulice.klientid
INNER JOIN klientulica
ON klientulice.klientulicaid= klientulica.klientulicaid
INNER JOIN klientmiejscowosci
ON klient.klientid = klientmiejscowosci.klientid
INNER JOIN klientmiejscowosc
ON
klientmiejscowosci.klientmiejscowoscid=klientmiejscowosc.klientmiejscowoscid
INNER JOIN klientbranza
ON klient.klientid = klientbranza.klientid
INNER JOIN branzaslowa
ON klientbranza.branzaid=branzaslowa.branzaid
INNER JOIN branzaslowo
ON branzaslowa.branzaslowoid=branzaslowo.branzaslowoid
INNER JOIN klientslowa
ON klient.klientid = klientslowa.klientid
INNER JOIN klientslowo
ON klientslowa.klientslowoid=klientslowo.klientslowoid
WHERE wojewodztwoid=7
AND klientulica.klientulica like 'dwo%'
AND klientmiejscowosc.klientmiejscowosc like'war%'
AND branzaslowo.branzaslowo like'sam%'
AND klientslowo.klientslowo LIKE 'sam%'
I staggered your sub-joins by one indention so that you could recognize
some possible points of creating little mini-joins later on a partial
result set. (See step 4)
SECOND - Check your indexes. Make sure that you are joining tables on
columns that appear first in at least one index. Carefully consider
creating a multiple column index or two (especially if this is a common
query and/or you use those conditions often).
## I usually use indexes on key fields [like klientid...] and on
launching
tables [like klientslowa] one index on both two fields...
THIRD - Consider evaluating this query in stages. Based on your re-write
to eliminate the comma-joins, I can help you with that, too.
## ? what do You mean?
I mean do not try to do everything in one massive query. There are times,
especially when performing more than about 6 or 7 joins in a single query
that breaking one larger operation into two smaller ones is the more
efficient way to go.
I wish I knew more polish as it would help me to break up this query
better. What you want to do in each stage is to build a temporary table
that holds just enough information in it to build the next stage.
CREATE TEMPORARY TABLE tmpStage1 (KEY(klientid))
Select klient.klientid
, ... (all of the computed values that rely on data from any of
the tables participating in this stage or values to be used in future
stages)...
FROM klient
INNER JOIN klientulice
ON klient.klientid = klientulice.klientid
INNER JOIN klientulica
ON klientulice.klientulicaid= klientulica.klientulicaid
WHERE wojewodztwoid=7
AND klientulica.klientulica like 'dwo%'
CREATE TEMPORARY TABLE tmpStage2 (KEY(klientid))
SELECT t1.klientid
, t1.logo
, t1.klientnazwa
, ... (other t1.fields) ...
, ...(computed fields using values from these tables and values to
combine with values from the next stage's tables )
FROM tmpStage1 t1
INNER JOIN klientmiejscowosci
ON t1.klientid = klientmiejscowosci.klientid
INNER JOIN klientmiejscowosc
ON
klientmiejscowosci.klientmiejscowoscid=klientmiejscowosc.klientmiejscowoscid
WHERE klientmiejscowosc.klientmiejscowosc like'war%'
... repeat until you have added together the data from all but the very
last tables. Then you just SELECT the results and drop all of
Re: Still can't get a response from MySQL AB
On Thursday 23 September 2004 04:44 am, Patrick Connolly wrote: Last week, I asked this list had others had problems contacting MySQL AB. I promptly received a short note from [EMAIL PROTECTED] It suggested I could respond to that address which I tried to do, but I get this: - The following addresses had transient non-fatal errors - Well, I am replying to this, with that email in the CC.. Will see if they get it.. If you dont see a reply, it worked fine for me. Jeff pgpGtYooYiukR.pgp Description: PGP signature
Using keys and left()
Hello, I have a db with abount 8 lines in it. I now like to count the lines where the first char of the id is d: mysql select count(id) from test where left(id,1) = d; +---+ | count(id) | +---+ | 0 | +---+ 1 row in set (1.83 sec) mysql explain select count(id) from test where left(id,1) = d; +-+---+---++-+--+---+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+---+---++-+--+---+-+ | test| index | NULL | id | 256 | NULL | 80352 | where used; Using index | +-+---+---++-+--+---+-+ 1 row in set (0.00 sec) mysql select count(*) from test; +--+ | count(*) | +--+ |80352 | +--+ 1 row in set (0.00 sec) What can I do to get a better result for my query? Thanks, Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A query to swap the data in two fields
On Thu, 23 Sep 2004 11:03:08 +0100, wrote: Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds do I need to store one of the fields temporarily? Hey chums you normally get me an answer in minutes, is my question too easy? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using keys and left()
Alexander Newald [EMAIL PROTECTED] wrote on 23/09/2004 15:57:51: Hello, I have a db with abount 8 lines in it. I now like to count the lines where the first char of the id is d: mysql select count(id) from test where left(id,1) = d; +---+ | count(id) | +---+ | 0 | +---+ 1 row in set (1.83 sec) mysql explain select count(id) from test where left(id,1) = d; +-+---+---++-+--+--- +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+---+---++-+--+--- +-+ | test| index | NULL | id | 256 | NULL | 80352 | where used; Using index | +-+---+---++-+--+--- +-+ 1 row in set (0.00 sec) mysql select count(*) from test; +--+ | count(*) | +--+ |80352 | +--+ 1 row in set (0.00 sec) What can I do to get a better result for my query? Does select count(id) from test where id like d% ; work any better? I would expect it to make better use of the index. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP reports 3.23.49, command line reports 4.0.1
I installed PHP 4.3.1 via source and used the following option when configuring: ./configure --with-mysql The database has worked great for a couple of years, however I noticed a problem today. Phpinfo() returns MySQL Version 3.23 from a php script. Client API version 3.23.49 MYSQL_MODULE_TYPE builtin MYSQL_SOCKET/var/lib/mysql/mysql.sock MYSQL_INCLUDE no value MYSQL_LIBS no value However, issuing 'mysql -V' at the command line returns version 4.0.1. So I assume the version of MySQL bundled with PHP 4.3.1 is in fact 3.23.49 and this is overriding my standalone installation. Without recompiling, is there a way to upgrade the bundled client? I need to restate my question to the list. First of all, is there a config file that tells PHP where to look for MySQL client files? If not, and I need to recompile PHP, how should I phrase my configuration option to use the standalone MySQL installation (now upgraded to 4.1.5 GA)? % ./configure --with-mysql=/what/directory/do/I/path/to?/ --with-apache=../apache-1.3.29 --with-informix=/opt/Informix 'mysql' is located in /usr/bin - is that the obvious answer? Thanks much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP reports 3.23.49, command line reports 4.0.1
On 09/23/2004 9:37 AM, Stembridge, Michael [EMAIL PROTECTED] wrote: I installed PHP 4.3.1 via source and used the following option when configuring: ./configure --with-mysql The database has worked great for a couple of years, however I noticed a problem today. Phpinfo() returns MySQL Version 3.23 from a php script. Client API version 3.23.49 MYSQL_MODULE_TYPE builtin MYSQL_SOCKET /var/lib/mysql/mysql.sock MYSQL_INCLUDE no value MYSQL_LIBS no value However, issuing 'mysql -V' at the command line returns version 4.0.1. So I assume the version of MySQL bundled with PHP 4.3.1 is in fact 3.23.49 and this is overriding my standalone installation. It's not overriding anything, it's doing what you asked: in your configure you told PHP to use its built-in (MYSQL_MODULE_TYPE) MySQL support. Without recompiling, is there a way to upgrade the bundled client? No - I believe you need to recompile PHP (this path assumes an RPM installation of MySQL): ./configure --with-mysql=/usr You also need to recompile when you upgrade MySQL, for example from 4.0.20 to 4.0.21. Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysqlclient.so.10()(64bit) not found
Problem: Trying to install php-mysql on a Fedora2 opteron box. error: Failed dependencies: libmysqlclient.so.10()(64bit) is needed by php-mysql-4.3.8-2.1 I know... Its an old one, but a good one. I thought I had jumped through all the backwards compatabile hoops... The thing is: rpm -qa | grep -i mysql returns: MySQL-client-4.1.5-0.glibc23 MySQL-shared-compat-4.0.21-0 --- MySQL-server-4.1.5-0.glibc23 MySQL-devel-4.1.5-0.glibc23 MySQL-shared-4.1.5-0.glibc23 php-mysql-4.3.8-2.1 ie, I have the backwards compat libmysqlclient.so.10 libs. They are here: /usr/lib/libmysqlclient.so.10.0.0 /usr/lib/libmysqlclient_r.so.10.0.0 /usr/lib/libmysqlclient.so.10 /usr/lib/libmysqlclient_r.so.10 /usr/lib64/mysql/libmysqlclient.so.10.0.0 /usr/lib64/mysql/libmysqlclient_r.so.10.0.0 /usr/lib64/mysql/libmysqlclient.so.10 /usr/lib64/mysql/libmysqlclient_r.so.10 I modified /etc/ld.so.conf to be: include ld.so.conf.d/*.conf /usr/X11R6/lib /usr/X11R6/lib64 /usr/lib64/qt-3.3/lib /usr/lib64/mysql and /sbin/ldconfig -v | grep libmysqlclient returns libmysqlclient_r.so.10 - libmysqlclient_r.so.10.0.0 libmysqlclient.so.10 - libmysqlclient.so.10.0.0 libmysqlclient_r.so.10 - libmysqlclient_r.so.10.0.0 libmysqlclient.so.12 - libmysqlclient.so.12.0.0 libmysqlclient_r.so.12 - libmysqlclient_r.so.12.0.0 libmysqlclient.so.10 - libmysqlclient.so.10.0.0 libmysqlclient_r.so.14 - libmysqlclient_r.so.14.0.0 libmysqlclient.so.14 - libmysqlclient.so.14.0.0 (updatedb has also been run.) mysql -V mysql Ver 14.6 Distrib 4.1.5-gamma, for unknown-linux (x86_64) is the mysql version i am running. (Which works fine) Basically what I am asking is, how do I tell the (php/perlDBD) rpm where to find the libmysqlclient.so.10.0.0? Thanks guys... Philip. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie ALTER syntax help
alter table keywords change key_id page_id int(10) unsigned NOT NULL PK auto_increment; ERROR 1064 (42000): 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 'PK auto_increment' at line 1 I'm trying to change the name of a column, but I have to also restate the type and sttribute on the column while i do that AFAIK. Can someone show me my syntax error? The statement above is exactlymy try from the mysql comd. line Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Erice course announcement
Please, NOTE : if you wish to have news about the 2005 Erice meeting, Evolving Methods in Macromolecular Crystallography, 12 to 22 May then click ontohttp://crystalerice.org Coming soon after : 2006: Structure and Function of Macromolecular Assemblies 2007: Engineering of Crystalline Materials Properties: State-of-the-Art in Modeling, Design, Applications 2008: From Molecules to Medicines : Integrating Crystallography 2009: Contribution to Modern Technology via High Pressure Crystallography (wording to be approved) 2010: Frontiers of Powder Diffraction 2011: Electron Crystallography - Structures of minute crystallites with exceptional properties Lodovico Riva di Sanseverino phone + 390 51 209 4912 Dept Earth Geo-Environmental Sciences fax + 390 51 209 4904 Piazza Porta S Donato 1email: 40126 Bologna, Italy [EMAIL PROTECTED] ** = ** SAVE TREES !! HOW ??? SIMPLE, BY USING EMAIL ** = ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP reports 3.23.49, command line reports 4.0.1
So I assume the version of MySQL bundled with PHP 4.3.1 is in fact 3.23.49 and this is overriding my standalone installation. It's not overriding anything, it's doing what you asked: in your configure you told PHP to use its built-in (MYSQL_MODULE_TYPE) MySQL support. Without recompiling, is there a way to upgrade the bundled client? No - I believe you need to recompile PHP (this path assumes an RPM installation of MySQL): Yes, MySQL is installed via RPM. So, when I recompile PHP I should leave -off- the '--with-mysql' configuration option altogether? If yes, how does PHP know MySQL exists on the system, or is this handled automatically behind the scenes? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL client hangs
I am running MySQL 4.0.17 on QNX 6.2.1B. When I run the mysql client, mysql, it hangs. There is no output even when I do: mysql -v -v -v I can see that the MySQL daemon is running: # ps -ef | grep mysql 0 901147 1 - Sep23 ?00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --ledir=/usr/local/mysql/libexec --datadir=/usr/local/mysql/var 01290268 221209 - 16:20 ?00:00:00 grep mysql 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock However, there is no pid file. Here is the output of the err file: 040923 15:39:52 mysqld started 040923 15:39:53 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 202150249 InnoDB: Doing recovery: scanned up to log sequence number 1 202150933 040923 15:39:54 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 7 9 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 # This same setup was working on this machine before I reformated the hard drive and reinstalled. In order to install, I copied the /usr/local/mysql directory from a working system. Stephen Rasku Tantalus Systems Corp. Your Power. Your Data. One Wireless Network. T: 604-299-0458 x220 F: 604-451-4111 www.tantalus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data migration question
I have a table that i am modifying the schema of, and am having a particular problem with my data migration program. The old format has a column defined as: Range char(3) not null default '', which is either'' or contains a number (1 or 2 digits), and is sometimes followed by a direction (N,S,E,W) In the schema that im migrating to, the definition has changed so that there are 2 columns: Range TINYINT UNSIGNED NOT NULL DEFAULT 0, Range_Direction char(1) I have a Perl script that is doing a SELECT INTO OUTFILE on the old table, and a LOAD DATA INFILE into the new table. I havent been able to figure out a SELECT clause that will properly break up the old Range. Using a SELECT clause query like: SELECT Range REGEXP ^[0-9]+, Range REGEXP [NSEW]$ FROM ... will tell me about whether the Range column value is actually what it should be, but i was looking for a function that would give me the specific string that matched the regex, for instance using the above regexes on a range value of '32E', the first part matches '32' and the second part matches 'E'. REGEXP just gives whether the pattern matched the expression. This is very easy in Perl, is there any way to do it in MySQL? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP reports 3.23.49, command line reports 4.0.1
Stembridge, Michael wrote: So I assume the version of MySQL bundled with PHP 4.3.1 is in fact 3.23.49 and this is overriding my standalone installation. It's not overriding anything, it's doing what you asked: in your configure you told PHP to use its built-in (MYSQL_MODULE_TYPE) MySQL support. Without recompiling, is there a way to upgrade the bundled client? No - I believe you need to recompile PHP (this path assumes an RPM installation of MySQL): Yes, MySQL is installed via RPM. So, when I recompile PHP I should leave -off- the '--with-mysql' configuration option altogether? no --with-mysql=/usr is the right to compile php using your standalone mysql installation. If yes, how does PHP know MySQL exists on the system, or is this handled automatically behind the scenes? Additionally I suggest to take a look at the output from ./configure --help in your php source tree and have a look at php.net/mysql and php.net/mysqli Regards Friedhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP reports 3.23.49, command line reports 4.0.1
On 09/23/2004 11:11 AM, Stembridge, Michael [EMAIL PROTECTED] wrote: Without recompiling, is there a way to upgrade the bundled client? No - I believe you need to recompile PHP (this path assumes an RPM installation of MySQL): Yes, MySQL is installed via RPM. So, when I recompile PHP I should leave -off- the '--with-mysql' configuration option altogether? No, leave it and include a path to MySQL (see below). If you specify '--with-mysql' you will use PHP's built-in MySQL support, and if you leave it off you will compile PHP without MySQL support. If yes, how does PHP know MySQL exists on the system, or is this handled automatically behind the scenes? The '/usr' part of '--with-mysql=/usr' tells PHP where to look: ./configure --with-mysql=/usr Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's wrong with the use of Between and datetime columns ?
Hi, I'm using Between to fetch rows whose date column is between two dates. The thing is, sometimes it brings all the rows including those with a date similar to the upper limit ( which is the correct behaviour, according to the manual) and in other cases it brings al the rows excluding those equal to the upper limit (this shouldn't happen) this is how I use it select * where date_col between startdate and enddate beeing startdate my lower limit and enddate the upper limit I'm using mysql 4.1.4 gamma but the same happened with 4.1.0 alpha the date columns are datetime type. First I would like to know why is this happening, and second how to avoid it. Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DELETE IGNORE fails for foreign key references with InnoDb
Hi there, I'm finding that DELETE IGNORE doesn't actually ignore all errors when using InnoDb and trying to remove rows that would result in a foreign key error. I've checked the docs and think that what I'm doing should work, but doesn't - I'm using server 4.1.4-gamma: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 820 to server version: 4.1.4-gamma-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql DROP TABLE IF EXISTS b,a; Query OK, 0 rows affected (0.02 sec) mysql CREATE TABLE a - ( - id INTEGER NOT NULL AUTO_INCREMENT, - PRIMARY KEY(id) - ) - ENGINE=InnoDb; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE b - ( - aid INTEGER NOT NULL, - PRIMARY KEY(aid), - FOREIGN KEY (aid) REFERENCES a(id) - ) - ENGINE=InnoDb; Query OK, 0 rows affected (0.02 sec) mysql INSERT INTO a() VALUES(); Query OK, 1 row affected (0.29 sec) mysql INSERT INTO b SELECT * FROM a; Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql INSERT INTO a(id) VALUES(1024); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM a; +--+ | id | +--+ |1 | | 1024 | +--+ 2 rows in set (0.00 sec) mysql SELECT * FROM b; +-+ | aid | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql DELETE IGNORE FROM a; ERROR 1105 (HY000): Unknown error mysql I'm actually being lazy and just trying to mop up rows that are not referenced anymore. I would use ON DELETE CASCADE, except that the constraint is pointing the wrong way around in my application (I may have to fix this). Anyone know if DELETE IGNORE should work? Will it work in later releases of 4.1, although I don't see reference to this problem in the 4.1.5-gamma changelog? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with the use of Between and datetime columns ?
Mauricio Pellegrini wrote: Hi, I'm using Between to fetch rows whose date column is between two dates. The thing is, sometimes it brings all the rows including those with a date similar to the upper limit ( which is the correct behaviour, according to the manual) and in other cases it brings al the rows excluding those equal to the upper limit (this shouldn't happen) this is how I use it select * where date_col between startdate and enddate beeing startdate my lower limit and enddate the upper limit I'm using mysql 4.1.4 gamma but the same happened with 4.1.0 alpha the date columns are datetime type. First I would like to know why is this happening, and second how to avoid it. This is happening because you are trying to find a 14 digit number between 2 8 digit numbers. Your datetime is 14 digits. Dates are only 8. Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using keys and left()
- Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 23, 2004 5:27 PM Subject: Re: Using keys and left() Alexander Newald [EMAIL PROTECTED] wrote on 23/09/2004 15:57:51: Hello, I have a db with abount 8 lines in it. I now like to count the lines where the first char of the id is d: mysql select count(id) from test where left(id,1) = d; +---+ | count(id) | +---+ | 0 | +---+ 1 row in set (1.83 sec) mysql explain select count(id) from test where left(id,1) = d; +-+---+---++-+--+--- +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+---+---++-+--+--- +-+ | test| index | NULL | id | 256 | NULL | 80352 | where used; Using index | +-+---+---++-+--+--- +-+ 1 row in set (0.00 sec) mysql select count(*) from test; +--+ | count(*) | +--+ |80352 | +--+ 1 row in set (0.00 sec) What can I do to get a better result for my query? Does select count(id) from test where id like d% ; work any better? I would expect it to make better use of the index. Alec Hello, yes it works better for sets of data with very few lines with an id starting with d. But it takes even longer when more lines are involved. I thought using an index will know the result?? Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A query to swap the data in two fields
At 10:04 AM 9/23/2004, you wrote: On Thu, 23 Sep 2004 11:03:08 +0100, wrote: Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds do I need to store one of the fields temporarily? Hey chums you normally get me an answer in minutes, is my question too easy? zzapper (vim, cygwin, wiki zsh) -- zzapper, Gee, have you paid your bill yet?g The only thing I've been able to come up with is: update table1 a, table1 b set a.beds1=b.beds2, a.beds2=b.beds1 where a.rcd_id=b.rcd_id You need to create a transitory value for one of the variables and this is done by joining the table onto itself (the b table won't get updated as table a gets updated). This solution should work on any column type. There is probably a faster more efficient way of doing it (without a table join), but only if we knew what the column types were. If they were String then perhaps subscripting out the strings would be possible. Mike P.S. Now about your bill... :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with the use of Between and datetime columns ?
Thanks,but I've also tried the date() function to compare only the date part of the column like this select * where date(date_col) between date(startdate) and date(enddate) and the result is the same. On Thu, 2004-09-23 at 11:04, gerald_clark wrote: Mauricio Pellegrini wrote: Hi, I'm using Between to fetch rows whose date column is between two dates. The thing is, sometimes it brings all the rows including those with a date similar to the upper limit ( which is the correct behaviour, according to the manual) and in other cases it brings al the rows excluding those equal to the upper limit (this shouldn't happen) this is how I use it select * where date_col between startdate and enddate beeing startdate my lower limit and enddate the upper limit I'm using mysql 4.1.4 gamma but the same happened with 4.1.0 alpha the date columns are datetime type. First I would like to know why is this happening, and second how to avoid it. This is happening because you are trying to find a 14 digit number between 2 8 digit numbers. Your datetime is 14 digits. Dates are only 8. Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A query to swap the data in two fields
On Thu, 23 Sep 2004 13:31:03 -0500, wrote: At 10:04 AM 9/23/2004, you wrote: On Thu, 23 Sep 2004 11:03:08 +0100, wrote: Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds do I need to store one of the fields temporarily? Hey chums you normally get me an answer in minutes, is my question too easy? zzapper (vim, cygwin, wiki zsh) -- zzapper, Gee, have you paid your bill yet?g The only thing I've been able to come up with is: update table1 a, table1 b set a.beds1=b.beds2, a.beds2=b.beds1 where a.rcd_id=b.rcd_id You need to create a transitory value for one of the variables and this is done by joining the table onto itself (the b table won't get updated as table a gets updated). This solution should work on any column type. There is probably a faster more efficient way of doing it (without a table join), but only if we knew what the column types were. If they were String then perhaps subscripting out the strings would be possible. Mike P.S. Now about your bill... :) Mike, cheque in the post! Sorry about my impertinence, I think I'm going to be a coward and create an extra column for the transitory value, as it's a one off operation. Just had a thought (dangerous I know) but couldn't I just rename the fields as that would do the same thing? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need to store a Guid as an Id
MySql doesn't support Guids but we were attempting to use a VarChar. We set the VarChar to binary, but from what I'm reading the binary setting doesn't affect storage just sorting and evaluations. I was able to get the binary storate I needed in a TinyBlob but I can't set this to a primary key. Is there a work around for this? TIA Dan
Two versions on same server?
Can I run two different versions of MySQL on the same server? I've got a commercial application for which the vendor will only support MySQL 3.x and makes no guarantees if running MySQL 4. But I'd like to migrate a number of our own web applications to MySQL 4. My choices are take my chances with the other app, run MySQL on a second machine (not an option at the current time) or else run two instances of MySQL, different versions, both on the same machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with the use of Between and datetime columns ?
Sorry , You were right. I was mistaken the results Between works just fine. It was me. I'm Very sorry Thank you all On Thu, 2004-09-23 at 15:45, Mauricio Pellegrini wrote: Thanks,but I've also tried the date() function to compare only the date part of the column like this select * where date(date_col) between date(startdate) and date(enddate) and the result is the same. On Thu, 2004-09-23 at 11:04, gerald_clark wrote: Mauricio Pellegrini wrote: Hi, I'm using Between to fetch rows whose date column is between two dates. The thing is, sometimes it brings all the rows including those with a date similar to the upper limit ( which is the correct behaviour, according to the manual) and in other cases it brings al the rows excluding those equal to the upper limit (this shouldn't happen) this is how I use it select * where date_col between startdate and enddate beeing startdate my lower limit and enddate the upper limit I'm using mysql 4.1.4 gamma but the same happened with 4.1.0 alpha the date columns are datetime type. First I would like to know why is this happening, and second how to avoid it. This is happening because you are trying to find a 14 digit number between 2 8 digit numbers. Your datetime is 14 digits. Dates are only 8. Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Two versions on same server?
Yes -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 3:44 PM To: [EMAIL PROTECTED] Subject: Two versions on same server? Can I run two different versions of MySQL on the same server? I've got a commercial application for which the vendor will only support MySQL 3.x and makes no guarantees if running MySQL 4. But I'd like to migrate a number of our own web applications to MySQL 4. My choices are take my chances with the other app, run MySQL on a second machine (not an option at the current time) or else run two instances of MySQL, different versions, both on the same machine. -- 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: newbie ALTER syntax help
At 12:12 -0400 9/23/04, leegold wrote: alter table keywords change key_id page_id int(10) unsigned NOT NULL PK auto_increment; ERROR 1064 (42000): 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 'PK auto_increment' at line 1 I'm trying to change the name of a column, but I have to also restate the type and sttribute on the column while i do that AFAIK. Can someone show me my syntax error? The statement above is exactlymy try from the mysql comd. line Thanks. The problem is shown by the error message: PK isn't valid. Do you mean PRIMARY KEY? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
warming the cache after restart
Good Afternoon. I'm interested if any DBAs on the list have a set of scripts they run after a server restart to pull commonly accessed data into the the query and key caches. I'm currently working on a script that will run various queries from our application against the database servers after restart, in hopes that it will shorten the amount of time it takes for the caches to fill and stabalize. I'm excited to see that MySQL 4.1.1 will have direct commands to do some of this, (see http://dev.mysql.com/doc/mysql/en/LOAD_INDEX.html), but that doesn't help me now... Is there anyone else on this list who has looked at this issue or can point me in the direction of more information about it? thanks a bunch, jenni -- Jennifer Snyder Database Administrator Tribe Networks, Inc. www.tribe.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two versions on same server?
See the manual http://dev.mysql.com/doc/mysql/en/Multiple_servers.html for details. Michael Tucker, Gabriel wrote: Yes -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 3:44 PM To: [EMAIL PROTECTED] Subject: Two versions on same server? Can I run two different versions of MySQL on the same server? I've got a commercial application for which the vendor will only support MySQL 3.x and makes no guarantees if running MySQL 4. But I'd like to migrate a number of our own web applications to MySQL 4. My choices are take my chances with the other app, run MySQL on a second machine (not an option at the current time) or else run two instances of MySQL, different versions, both on the same machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having rows or fields that can't be modified once entered (NOT grant statement).
On Thu, 23 Sep 2004 08:20:39 -0400, Jesse W. Asher [EMAIL PROTECTED] wrote: Thanks for the responses, but this isn't what I'm looking for. I'm looking for a way to make a field or record unchangeable for ANYONE, including the system administrator. That way, I can have a fairly high level of assurance that the record is valid and hasn't been tweaked in any way. Anyway to do this within the database itself? Thanks!! I did a quick google, and turned this up: http://dev.mysql.com/doc/mysql/en/myisampack.html If you pack the database, it can't be written to until it's unpacked. Seems a little labor intensive, and it's table scoped, so you can't just specify certain fields/rows. Also, it's only designed for MyISAM tables. Another way to keep an eye on changes would be to generate a checksum column, or table. Take the fields you want to remain unchanged, generate an MD5 (for example) checksum on it, and store it. Then check the data against it when you're ready to use it. These don't seem to be quite what you're looking for, but hopefully it's a step in the right direction. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie ALTER syntax help
On Thu, 23 Sep 2004 15:51:56 -0500, Paul DuBois [EMAIL PROTECTED] said: At 12:12 -0400 9/23/04, leegold wrote: alter table keywords change key_id page_id int(10) unsigned NOT NULL PK auto_increment; ERROR 1064 (42000): 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 'PK auto_increment' at line 1 I'm trying to change the name of a column, but I have to also restate the type and sttribute on the column while i do that AFAIK. Can someone show me my syntax error? The statement above is exactlymy try from the mysql comd. line Thanks. The problem is shown by the error message: PK isn't valid. Do you mean PRIMARY KEY? Yes. Fixed it, Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date BETWEEN Question
Everyone, Thanks for the help. I'm not sure why because I swear on everything holy that I didn't change anything, but the query now works as desired. Rather than using BETWEEN, I'm just using = and =. Perhaps I was missing a quote or something in my old query that was causing it to return the wrong thing. But in the end, I'm using the proper DATETIME formats for the criteria, which I should have been doing from square one: WHERE completed = '2004-07-21 00:00:00' AND completed = '2004-07-23 23:59:59' Thanks again for the assistance. Jeremy On Sep 22, 2004, at 11:29 AM, gerald_clark wrote: You still did not send your table definitions. Jeremy Brown [InfoSend] wrote: Michael, Thanks for the response. My query was kind of long, so I was just trying to simplify. Should have included it anyways. Here is my query: select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed = '2004-07-21 00:00:00' AND TRACKING.completed = '2004-07-23 23:59:59' order by TRACKING.jobid desc The BETWEEN statement is similar: select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed BETWEEN '2004-07-21 00:00:00' AND '2004-07-23 23:59:59' order by TRACKING.jobid desc Oddly enough, when I ran what you suggested: select * from TRACKING where completed = '2004-07-21' Empty set! But there are rows in the table with that date. That is expected if completed is a datetime field. You only supplied the date portion. try: select * from TRACKING where completed like '2004-07-21%' If I run either: select * from TRACKING where completed '2004-07-21' - or - select * from TRACKING where completed = '2004-07-21' I get the desired results, particularly in the last case where I get dates with 2004-07-21! I do have a standard INDEX on the completed field, would that have something to do with it? Again, completed is a DATETIME field. I have this same table duplicated on 3.23.58 and 4.0.17 with the same results. I'm probably just missing something small that I can't see, but frustrating nonetheless. Thanks, Jeremy -- 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: MySQL client hangs
Never mind. I installed MySQL from a different location and now it works. ...Stephen -Original Message- From: Stephen Rasku [mailto:[EMAIL PROTECTED] Sent: September 23, 2004 9:32 AM To: [EMAIL PROTECTED] Subject: MySQL client hangs I am running MySQL 4.0.17 on QNX 6.2.1B. When I run the mysql client, mysql, it hangs. There is no output even when I do: mysql -v -v -v I can see that the MySQL daemon is running: # ps -ef | grep mysql 0 901147 1 - Sep23 ?00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --ledir=/usr/local/mysql/libexec --datadir=/usr/local/mysql/var 01290268 221209 - 16:20 ?00:00:00 grep mysql 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock 1011056797 901147 - Sep23 ?00:40:39 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/smithers.pid --port=3306 --socket=/tmp/mysql.sock However, there is no pid file. Here is the output of the err file: 040923 15:39:52 mysqld started 040923 15:39:53 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 202150249 InnoDB: Doing recovery: scanned up to log sequence number 1 202150933 040923 15:39:54 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 7 9 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 # This same setup was working on this machine before I reformated the hard drive and reinstalled. In order to install, I copied the /usr/local/mysql directory from a working system. Stephen Rasku Tantalus Systems Corp. Your Power. Your Data. One Wireless Network. T: 604-299-0458 x220 F: 604-451-4111 www.tantalus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Indexing
On 23-Sep-2004 Eamon Daly wrote: I'm interested in this, too. We have a logging table that sees hundreds of rows per second, and we do a ton of monthly reports. We just bit the bullet and added an indexed DATE column. Is there a better strategy? I do something similar but with MEDIUMINT. hittime DATETIME NOT NULL DEFAULT '-00-00', prd MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, ... KEY idx_p (prd), ... UPDATE tbl SET prd=EXTRACT(YEAR_MONTH FROM hittime) ... Then query it with SELECT ... WHERE prd=200408 etc. -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared library Compiling error
First, you should keep threads on the list. Second, I'm confused. Didn't you say that you already have libmysqlclient.so.10 in /usr/local/openv/lib? If you need to get a new one, it apppears that to get it on Solaris, you need to build from source. Michael Nissim Lugasy wrote: where can I download mysql 4.0 for solaris9 that include the shared library? I downloaded mysql-max-4.0.21-sun-solaris2.9-sparc.tar.gz and I did not see libmysqlcleint.so.10 file in the mysql/lib directory. At 10:42 AM 9/23/2004 -0400, you wrote: Nissim Lugasy wrote: Hi, I'm trying to compile my own mysql client program under solaris 9 without luck. I have the mysql libraries under : /usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib and has the following files: libdbug.a libmysqlclient.a libmysqld.a libmysys.a libmygcc.a libmysqlclient_r.a Don't I need to have the libmysqlclient.so.10 file here too? on my system the libmysqlclient.so.10 is found under directory :/usr/local/openv/lib \ Should I move it /usr/lib instread? I'm not sure why you'd have your libraries separated like that, but I'd make sure there wasn't a reason before moving one. Then I'd probably put all the mysql libs in the same place (i.e. move libmysqlclient.so.10), /usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib in your case. Here is what I tried and the errors I get: When the makefile has: #L LD=gcc -lm -lmysqlclient the error is : symbol mysql_init: referenced symbol not found It didn't find libmysqlclient.so.10. - When the makefile has: #L LD=gcc -lm -Wl,r/usr/local/openv/lib -lmysqlclient the error is : ld: fatal: file r/usr/local/openv/lib: open failed: No such file or directory It still didn't find libmysqlclient.so.10, apparently because you have the syntax wrong. When the makefile has: #L LD=gcc -lm -L/usr/local/mysql-standard-4.0.20-sun-solaris2.9-sparc/lib -lmysqlclient the error is : symbol mysql_init: referenced symbol not found It didn't find libmysqlclient.so.10, because you told it to look in the wrong place. - any ideas to what I'm missing ? Thanks Try specifying the correct path with -L: #L LD=gcc -lm -L/usr/local/openv/lib -lmysqlclient You may also need -R /usr/local/openv/lib so that your executable knows where to find libmysqlclient.so.10 at run time. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL for detecting if Column/Index already exists?
At 4:00 7/27/04, Paul DuBois wrote:
At 16:22 -0500 7/26/04, Ghate, Shishir wrote:
I looked at the SHOW COLUMNS statement and they have what I want,
but I need to condition off them. For example, I don't want to
execute an ALTER TABLE command to add a column if that column is
already there. I've tried IF DOES NOT EXIST SHOW COLUMN ...
followed by the ALTER TABLE command, but the if check doesn't seem
to work on SHOW COLUMNS.
Am I missing something, or is this just something that is not supported?
You can process the output of SHOW COLUMNS in your application language
and use the result to construct the ALTER TABLE statement.
I assume that you're using some kind of programming API to access MySQL
here. If you're asking is this supported using SQL alone? the
answer is no.
Any chance 'IF EXISTS' being added to the 'ALTER TABLE' statements?
It sure would be nice to execute something like:
ALTER TABLE tmp ADD INDEX joy ( to, the, world ) IF EXISTS;
This seems much more elegant than (pseudo code):
$NewIndexName = joy;
$Results = run_query( show index from tmp );
$FoundIndex = 0;
while ( $Results.hasNext() )
{
$IndexName = $Results.getString( 3 );
if ( $IndexName == $NewIndexName )
{
$FoundIndex = 1;
}
}
if ( ! $FoundIndex )
{
run_update( alter table tmp add index joy( to, the, world );
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL for detecting if Column/Index already exists?
On Thu, 23 Sep 2004 17:20:17 -0700, Kyle Kirkland [EMAIL PROTECTED] wrote: Any chance 'IF EXISTS' being added to the 'ALTER TABLE' statements? It sure would be nice to execute something like: ALTER TABLE tmp ADD INDEX joy ( to, the, world ) IF EXISTS; Make that 'IF NOT EXISTS'. Sorry... ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OOT: Penawaran Untuk Subscriber Milis MySQL di Indonesia
Dear All, Siapa mau account gmail? Gratis 2 account.. Tanpa bayaran, tanpa uang.. Silahkan pm saya.. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need voluntary help regarding project
I was wondering if anyone wants to help me with this project. I already have a test database we can use, I just need help making the team pages. please take a look and reply if interested. thanks in advance I'm a noobie in regards of php but I am trying to learn. http://www.theufl.com/ufl_project.htm Karl James (TheSaint) [EMAIL PROTECTED] http://theufl.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL query performance test tool
Hi Haitao. I'm in the process of developing one of these for the company I work for. Feel Free to drop me a line and we'll see how we can get it going for you. Regards Ian Haitao Jiang wrote: Hi, We want to test our MYSQL (4.1.4g) server's query performance, and I just wondering if there is a tool that enable us sending a list of queries over HTTP or JDBC repeatedly and gather/display the statistics? Thanks HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
