forum.vmspython.org Forum Index forum.vmspython.org
Forum system
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Foreign Key/Indexes problems - 5.1.22-rc-log

 
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> MySQL for OpenVMS
View previous topic :: View next topic  
Author Message
icl



Joined: 26 Oct 2007
Posts: 29
Location: London, UK

PostPosted: Mon Feb 07, 2011 1:29 pm    Post subject: Foreign Key/Indexes problems - 5.1.22-rc-log Reply with quote

Working with Server version: 5.1.22-rc-log on Itanium 8.3-1H1

We have seen a problem when working with Foreign Keys.

Adding a foreign key creates an index which generally works fine for a while - but at some point (it looks to be when the table gets above 100 MB) we start getting rubbish when using it as a reference in an INNER JOIN to the source table.

By "rubbish" I mean that queries asking for text from the joined table bring back two characters, typically something like "VÄ" rather than, say, "message".

The two characters returned for the same query always change - so it looks like the query is pulling random characters from memory.

If we remove the index from the table then the problem goes away - but returns if the index is once again added.

Any ideas on what could be causing this?

-------------------

MY.CNF definition shown below:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
datadir=/mysql051_root/data/
lower_case_table_names = 1
sync_binlog = 1
back_log = 50
max_connections=100
max_connect_errors = 10
table_cache = 128
max_allowed_packet = 2M
binlog_cache_size = 1M
max_heap_table_size = 4M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 8
thread_concurrency()
thread_concurrency = 4
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = InnoDB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 4M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /mysql051_root/mysql_server/tmp/
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 64M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_data_home_dir = /mysql051_root/data/
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /mysql051_root/data/
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Tue Feb 08, 2011 8:59 am    Post subject: Reply with quote

Foreign key support on MySQL may be buggy, you can try to upgrade to a newer version.

Also be sure to use InnoDB.

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
icl



Joined: 26 Oct 2007
Posts: 29
Location: London, UK

PostPosted: Tue Feb 08, 2011 3:40 pm    Post subject: Reply with quote

Good news JF - I have found the bug causing issues with Foreign Keys.

It is all to do with the handling of temporary tables whenever tables are altered.

The bug is caused by the fact that in our port, the code doesn't identify these temporary tables because it is looking for a search string "/#sql" rather than "/_sql".

the offending function is: row_is_mysql_tmp_table_name() in row0mysql.c

really we should change the function to use: tmp_file_prefix as defined in mysql_priv.h

I think this might cause other issues to do with handling temporary tables so a good bug to fix.
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Tue Feb 08, 2011 3:50 pm    Post subject: Reply with quote

Great Exclamation

As only ODS5 is supported, may be tmp_file_prefix should be revert to it default value: "#sql"

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Wed Feb 09, 2011 4:35 pm    Post subject: Reply with quote

I have put online new kits with tmp_file_prefix reverted to it default value: "#sql"

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
icl



Joined: 26 Oct 2007
Posts: 29
Location: London, UK

PostPosted: Wed Feb 09, 2011 5:07 pm    Post subject: Reply with quote

Thanks for that JF. Does this include an IA64 binary built with SSL 1.3? I have yet to install 1.4 and probably won't for a few years yet.
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Wed Feb 09, 2011 5:33 pm    Post subject: Reply with quote

1.4, so you will need to build the server yourself...
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> MySQL for OpenVMS All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group