bug/enhancement request
sql query
fyi... Section 6.4.1 of the manual says:
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name]
[FOR UPDATE | LOCK IN SHARE MODE]]
In addition to being able to specify an update lock or a shared mode
lock, one should be able to specify no lock. This is the moral
equivalent of the Sybase noholdlock option. This is useful if you
normally want to run in a high isolation environment but are, say,
running a transaction where you don't need repeatable reads for some
select in that transaction.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating database
Yes, it is possible unless you insist on using an unimaginative, strict, interpretation of what Info wrote. Mechanism 1: Create a separate database server for each database. You can create servers wherever you want. Mechanism 2, which might not work, but might be worth a try: assuming a single server with multiple databases; after creating the database, move its directory to the desired location and then create a symlink from the new location to the old location. Cs Alexander Barkov wrote: This is not possible. Info wrote: sql,query How can i have each users database to be stored in it's home dir ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
undocumented change in behavior?
There appears to be an undocumented change from 3.23.49 to 3.23.51, possibly for the worse. Consider two sessions executing the following sequence of commands on an innodb table (assume maximum ACID properties): session 1 begin; session 1 update table set field = field + 1 where primary_key = 1; session 2 select * from table where primary_key = 1; In version 3.23.49, session 2 returns a row relatively quickly. In version 3.23.51, session 2 blocks. The 3.23.49 behavior is reasonable -- the 2nd session is not required to block because the database is allowed to choose an ordering of transactions when transactions are concurrent and it is reasonable to choose the order where session 2 completes before session 1. So, it appears that version 3.23.51 has different behavior and I don't see this documented in the list of changes. And it could be argued that the 3.23.49 behavior was better. [fyi... one other undocumented change would be the change to the client libraries so that when an application tries to parse a group out of a configuration file, there won't be a core dump. I think this was a bug fix to libmysql.c, adding a '*' to the statement 'for (end= *option ; *(end= strcend(end,'_')) ; )'.] chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
possible bug: alter table trashed foreign key constraints in innodbtables
sql query In version 3.23.49a when using an innodb table, alter table appears to corrupt foreign key constraints. Try the following test case: create table test_base ( base_id int not null, primary key (base_id) ) type = innodb; create table test_ref ( base_id int not null, ref_id int not null, primary key (base_id, ref_id), foreign key (base_id) references test_base (base_id) ) type = innodb; insert test_base (base_id) values (1); insert test_ref (base_id, ref_id) values (1, 1); alter table test_base add column value int not null; insert test_ref (base_id, ref_id) values (1, 2); The final insert fails with mysql insert test_ref (base_id, ref_id) values (1, 2); ERROR 1216: Cannot add a child row: a foreign key constraint fails which suggests that the foreign key constraint has become hosed? Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
performance bug? using binlog disables group commits
sql query I'm using innodb tables with transactions flushed on commit, version 3.23.49. It looks to me that when binlogs are being generated (which are required for the hotbackup feature or for replication), then group commits are effectively disabled. Basically, there is a coarse granularity lock being held in sql/log.cc -- the LOCK_log lock. Chuck [apologies if a previous version of this email somehow made it to the list -- I didn't see it on the list and was using an incorrect address...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
minor enhancement request -- adding files to innodb
As a minor enhancement, the innodb database could look at improving its handling of data files. Specifically: 1) Provide mechanisms to allow data and log files to be filled with zeroes while normal operations occur, thus minimizing downtime when the database is grown. 2) Don't store the list of data and log files in the my.cnf configuration file, but provide separate commands to add and remove files or partitions from these lists, and store the list within the database. 3) Provide more robust error recovery so that when we, say, run out of disk space while initializing the data and log files, we don't have to delete all the files and re-create them. Chuck sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
minor enhancement request - multiple servers
The current mysql interfaces don't support multiple servers in a fantastic fashion -- either multiple servers on a single machine or multiple servers on multiple machines. It may be there is no great way to do this, but... Sybase provides an interfaces file (that needs to be propagated to at least every client machine), which basically allows one to specifiy a server name that is mapped to an ip address and port number. This seems slightly easier to use than having individual users build their own translations. Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb bug
Bert -- Your problem is not the same as Shakeel's. For you, the database is saying that it couldn't allocate memory. For Shakeel, it is saying that an assert failed. At about line 213, there is a right shift (X Y) that is occuring. The behavior of a right shift is different depending on whether the value being shifted is signed or unsigned. The value is supposed to be unsigned, but the programmers forgot to tell the compiler. This effectively means that mysql cannot allocate more than 2GB of ram. block = buf_pool_get_nth_block(buf_pool, (ptr - frame_zero) UNIV_PAGE_SIZE_SHIFT); ut_a(block = buf_pool-blocks); Chuck Bert VdB wrote: Hi, I'm sort of glad we're not the only one having this problem. Yesterday we had kind of the same error message on an Solaris 8 machine with 512Mb of ram. Our buffer_pool_size was set to 250Mb, because the other 250Mb is used by the orion-web-server. Today I will perform crash-tests on another machine and try to find out the problem. Fyi, our error log: = /opt/nusphere/mysql-max-3.23.49-sun-solaris2.8-sparc/bin/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=16 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 020625 15:39:58 mysqld restarted 020625 15:40:34 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 0 272046313 InnoDB: Fatal error: cannot allocate 2310548 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 334012166 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. 020625 15:40:37 mysqld ended -Original Message- From: Shakeel Sorathia [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 25, 2002 21:01 To: [EMAIL PROTECTED] Subject: innodb bug I've been having a problem with innodb lately. We just upgraded one of our machine to have 4 GB of ram in it. However, whenever I make the innodb_buffer_pool_size greater then 2048M It crashes with the following in the error log. It's 3.23.51 running on a Solaris 8 Ultrasparc II machine with 4 GB ram. Is the limit 2gb of ram, or is there something that I'm doing wrong? Thanks for the help! --shak 020625 12:57:14 mysqld started InnoDB: Assertion failure in thread 1 in file ../include/buf0buf.ic line 214 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=8388600 record_buffer=131072 sort_buffer=1048568 max_used_connections=0 max_connections=1024 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1187831 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 020625 12:57:54 mysqld ended - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
