Operating system error number 995
Hi, I am running mysql 4.1.10 on windows 2000 professional operating system. Recently i upgraded the mysql version from 3.23.54 to the current version. Problem: the mysql server stops frequently. Upon checking the mysql logs i have found the following error lines: 050607 16:23:15 InnoDB: Operating system error number 995 in a file operation. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html InnoDB: File name .\ibdata1 InnoDB: File operation call: 'Windows aio'. InnoDB: Cannot continue operation. I looked up the net for more information and have found that lots of other users have encountered this error. Unfortunately, I could not find a solution for the same. Please help. Thank you in advance. Nirmal Shah. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
some thing wrong when I try to use innodb
050609 16:33:59 mysqld started 050609 16:34:00 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 43892 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 InnoDB: Error: trying to access a stray pointer c6727ff8 InnoDB: buf pool start is at 42134000, number of pages 4480 050609 16:34:00 InnoDB: Assertion failure in thread 1075189344 in file ../include/buf0buf.ic line 284 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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 against 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=0 max_connections=100 threads_connected=0 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 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffc41c, stack_bottom=0x7f8e0100, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x7e792600 is invalid pointer thd-thread_id=1635134836 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 1635134836 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 050609 16:34:00 mysqld ended My.cnf is like this: [mysqld] datadir=/var/lib/mysql #datadir=/home/jesse/victor/mydata socket=/var/lib/mysql/mysql.sock # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_home = /home/jesse/victor/ibdata innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid _ MSN Hotmail http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Operating system error number 995
A quick google search revealed the following link: http://bugs.mysql.com/bug.php?id=3139 Looks like it's one of those things related with Windoze! hence I would start by checking if the OS is updated with latest patches. HTH Manoj - Original Message - From: Nirmal Shah [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 09, 2005 2:59 PM Subject: Operating system error number 995 Hi, I am running mysql 4.1.10 on windows 2000 professional operating system. Recently i upgraded the mysql version from 3.23.54 to the current version. Problem: the mysql server stops frequently. Upon checking the mysql logs i have found the following error lines: 050607 16:23:15 InnoDB: Operating system error number 995 in a file operation. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html InnoDB: File name .\ibdata1 InnoDB: File operation call: 'Windows aio'. InnoDB: Cannot continue operation. I looked up the net for more information and have found that lots of other users have encountered this error. Unfortunately, I could not find a solution for the same. Please help. Thank you in advance. Nirmal Shah. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
I would say this is not a bug. You declared an enum for the column. So
therefore it sorts in enum order. Makes perfect sense. To me MySql is
working correctly. If it did not sort an enum in the order declared for
the enum then i would be annoyed. Enums are not strings.
Declare the column as varchar if you wish to sort _alphabetically_.
On Wed, 08 Jun 2005 23:38:18 +0100, Daevid Vincent [EMAIL PROTECTED]
wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22
Distrib
4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Access')
If I SELECT, and ORDER BY Type, it is ordering in the order defined by
the
the ENUM, not _alphabetically_ as a sane person would expect. UGH!
Please tell me there is a fix or work around.
--
alex
tel
02380 48 8273
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
Or put the members alphabetically in the enum definition in the first
place ...
--
felix
On 09/06/2005, Eric Bergen wrote:
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum
name to a string like this select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22
Distrib 4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Acces
s')
If I SELECT, and ORDER BY Type, it is ordering in the order defined
by the the ENUM, not alphabetically as a sane person would expect.
UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestions on db server configuration - Replication load balancing or Clustering??
Ed Pauley II wrote: I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? - check out http://www.ultramonkey.org/3/ It's not the perfect solution to your problem(s), but it might help. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
Or put the members alphabetically in the enum definition in the first
place ...
Better yet - drop the ENUM al together :-)
Use a lookup table.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
http://www.upscene.com
--
felix
On 09/06/2005, Eric Bergen wrote:
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum
name to a string like this select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22
Distrib 4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Acces
s')
If I SELECT, and ORDER BY Type, it is ordering in the order defined
by the the ENUM, not alphabetically as a sane person would expect.
UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]LEFT JOIN?
Thanks guys it worked!!! I really appreciate your help. this is the one that worked: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null and u.UserID != '$userid'; Michael Stassen wrote: [EMAIL PROTECTED] wrote: Michael's last answer: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Should do all of what you want except exclude the original user (so that the user cannot become their own buddy). To do that I would change it to read SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null and u.UserID != '$userid'; Can you show us some sample data and the result of either of these queries and explain what's wrong? I agree with Michael that this should work for what you need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I was just writing to say the same thing. I would only add that since userId is numeric, we should all drop the quotes around $userid. SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = $userid WHERE u.isactive =1 AND bl.userID is null AND u.UserID != $userid; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Kevin Burton wrote: Jeff Smelser wrote: Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. WOW! That's just insane! This seriously has to be fixed in 5.0 or sooner... Chill out man. It is not like it is returning the wrong results. It is a performance issue and if it is really that bad, everybody will find it during testing. Also, let's not mistake the means for the goal. Using indexes is just a way to solve it and there may be other fixes. The goal is to improve performance. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Query Browser Administrator under Solaris 10
smime.p7m Description: S/MIME encrypted message
AW: column permission for user
Here the contents of my privileged tables: mysql.db table for dtk10mv after GRANT UPDATE (status_dtk10) on tim.tk25no_meld to 'dtk10mv'@'localhost': - db: - - host: - - user: Select_priv: Y - tables_priv: Table_name: tk25no_meld; Table_priv: - ; Column_priv: Update - columns_priv: Table_name: tk25no_meld; Column_name: status_dtk10; Column_priv: Update mysql.db table for dtk25mv after GRANT UPDATE (status_dtk25) on tim.tk25no_meld to 'dtk25mv'@'localhost': - db: - - host: - - user: Select_priv: Y - tables_priv: Table_name: tk25no_meld; Table_priv: - ; Column_priv: Update - columns_priv: Table_name: tk25no_meld; Column_name: status_dtk25; Column_priv: Update CREATE TABLE tk25no_meld ( tk25no_meld_id int(6) NOT NULL auto_increment, timestamp timestamp(12) NOT NULL, lfdnr int(4) NOT NULL default '0', unternr int(2) NOT NULL default '1', status_dtk10 char(1) NOT NULL default 'N', status_dtk25 char(1) NOT NULL default 'N', PRIMARY KEY (tk25no_meld_id) ) TYPE=MyISAM; INSERT INTO tk25no_meld VALUES (1, 050418093109, 1, 1, 'N', 'N'); INSERT INTO tk25no_meld VALUES (2, 050520110021, 2, 1, 'J', 'J'); User dtk25mv can update column status_dtk10 and I don´t know why. http://dev.mysql.com/doc/mysql/en/request-access.html didn´t help. I will try new MySQL release 4.1.12 and then I tell You. Regards, Wolfgang Hello. I can see such behavior in test database because mysql.db table has records which allows updates to any user, and database privileges ORs with global privileges. Send us the contents of your privileged tables. See: http://dev.mysql.com/doc/mysql/en/request-access.html Upgrade to the latest release 4.1.12 (4.0.24). Wolfgang Gliese [EMAIL PROTECTED] wrote: I tried the SHOW GRANTS statement SHOW GRANTS FOR 'dtk10mv'@ 'localhost' and got this: Grants for [EMAIL PROTECTED] GRANT SELECT, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '08862e71234184bc' GRANT UPDATE (column1) ON database1.table1 TO 'user1'@'localhost' GRANT UPDATE (column1) ON database1.table1 TO 'user1'@'localhost' There is no permission to update column2, but the user can do so. What can I do? I use MySQL 4.0.3-beta-nt. Perhaps this version is too old? (In http://dev.mysql.com/doc/mysql/en/show-grants.html there is an evidence.) To the answer from Jigal: When I delete Column_priv: update in tables_priv the user1 can´t update any column in the table. There is no exact explanation in documentation (http://dev.mysql.com/doc/mysql/en/grant.html). Regards, Wolfgang -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing implementation
Hello. Here is the answer from Ingo: It is even worse. The old index stays in place, but will never be used again. ENABLE INDEX creates a new index from scratch. See Bug#4692 - DISABLE/ENABLE KEYS waste a space. roi h [EMAIL PROTECTED] wrote: Hi, Another question, to help me better understand MySQL indexing: In MyISAM, does DISABLE INDEX followed by insertions and then ENABLE INDEX freeze the original index and batch-updates it, or does it drop it completely and recreate it from scratch?=20 --thanks, Roi -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innoDB and referential action
Hello. On my mysql-5.0.6 instance foreign key constraint from your example works. What output does the following statement produce: show variables like 'have_innodb'; Jan Bartholdy [EMAIL PROTECTED] wrote: Dear All, I have two tables entity1 and entity2; the second one should contain only data with the same PLZ as listed in table entity1. Unfortunately, I am able to insert in table entity2 data in the field = PLZ, different from those in the field PLZ in the table entity1. What is going wrong? Many thanks, Jan Create table Entity1 ( PLZ Char(20) NOT NULL, Ort Char(20), UNIQUE (PLZ), Primary Key (PLZ) ) ENGINE =3D InnoDB ROW_FORMAT =3D Default; Create table Entity2 ( PLZ Char(20) NOT NULL, Stra_e Char(20), Primary Key (PLZ), Constraint Relationship1 Foreign Key (PLZ) references Entity1 (PLZ) on delete restrict on update restrict ) ENGINE =3D InnoDB ROW_FORMAT =3D Default; Virus checked by G DATA AntiVirusKit Version: AVK 15.0.4951 from 19.05.2005 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: some thing wrong when I try to use innodb
Hello. In your previous letter you wrote that you had MySQL 4.0.22. Upgrade to the latest release 4.1.12 (4.0.24). It is possible that your ibdata files won't be readable by this version. So you should initialize a new database. If you have important data you should go to: http://dev.mysql.com/doc/mysql/en/forcing-recovery.html qin lei [EMAIL PROTECTED] wrote: 050609 16:33:59 mysqld started 050609 16:34:00 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 43892 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 InnoDB: Error: trying to access a stray pointer c6727ff8 InnoDB: buf pool start is at 42134000, number of pages 4480 050609 16:34:00 InnoDB: Assertion failure in thread 1075189344 in file ../include/buf0buf.ic line 284 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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 against 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=0 max_connections=100 threads_connected=0 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 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffc41c, stack_bottom=0x7f8e0100, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x7e792600 is invalid pointer thd-thread_id=1635134836 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 1635134836 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 050609 16:34:00 mysqld ended My.cnf is like this: [mysqld] datadir=/var/lib/mysql #datadir=/home/jesse/victor/mydata socket=/var/lib/mysql/mysql.sock # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_home = /home/jesse/victor/ibdata innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid _ $$$ MSN Hotmail$ http://www.hotmail.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vi binding
Hello, is there a way to switch from emacs binding to the vi binding in mysql? Thanks. Philippe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
xxx-bin.0000xxx files
My version of mysql creates all sorts of xxx-bin.xxx files in the /var/lib/mysql directory. I regularly remove the oldest files for space purposes. What exactly are these files and what is the proper way to manage them? Thanks. Philippe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-standard-4.1.12-pc-linux-gnu-i686 crashing
As I can't provide steps to reproduce the crash, I decided to mail my problem to this list. If this isn't the correct list, sorry. And sorry if this message comes twice as for some reason the mail bounced back twice already. Anywho, I'm running mysql-standard-4.1.12-pc-linux-gnu-i686 (statically linked version) to which I upgraded to just today from 4.0.2 because 4.0.2 seemed to be crashing randomly during some queries. The problem, however, was not fixed with the update. I'm running MySQL on a virtual server running Xen with 2.6.10-kernel. When using for example Coppermine, the database server crashes sometimes (about one time in six) when uploading images to the server. The same problem occurs with TikiWiki when adding images. The server gets about 7-8 queries/second but runs fast without any other problems even though the virtual server is limited to 128M of memory. The crash causes the following error log: --- clip --- 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 against 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 read_buffer_size=131072 max_used_connections=20 max_connections=100 threads_connected=12 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8964d48 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfddec78, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808b7a3 0x82e0a48 0x80c1baf 0x80bf91e 0x80bc1d1 0x80be77f 0x80bb916 0x809b78a 0x809fdef 0x809a2ce 0x8099c98 0x8099367 0x82de1fc 0x8307b8a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8a16bb0 = select * from bcategorylist where parent=1 thd-thread_id=58 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. --- clip --- The query that is reported in the crash varies, and is actually never a query to any of the image gallery applications but a high-traffic website that uses the same MySQL database although the crash occurs immediately after some operation is performed by the image gallery software. resolve_stack_dump on the backtrace gives the following info: --- clip --- 0x808b7a3 handle_segfault + 423 0x82e0a48 pthread_sighandler + 184 0x80c1baf get_best_combination__FP4JOIN + 163 0x80bf91e make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 4206 0x80bc1d1 optimize__4JOIN + 385 0x80be77f mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_ord erT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 711 0x80bb916 handle_select__FP3THDP6st_lexP13select_result + 150 0x809b78a mysql_execute_command__FP3THD + 1414 0x809fdef mysql_parse__FP3THDPcUi + 207 0x809a2ce dispatch_command__F19enum_server_commandP3THDPcUi + 1578 0x8099c98 do_command__FP3THD + 188 0x8099367 handle_one_connection + 615 0x82de1fc pthread_start_thread + 220 0x8307b8a thread_start + 4 --- clip --- My first instinct was that MySQL was running out of memory but decreasing the memory used by MySQL did not help and the crashes continued. I have ran myisamcheck on the tables and it seems to complain about some of the tables not being closed, but can this be the source of the problem? Any tips, pointers, rtfms are appreciated. Tuomas Rinta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: xxx-bin.0000xxx files
Philippe de Rochambeau wrote: My version of mysql creates all sorts of xxx-bin.xxx files in the /var/lib/mysql directory. I regularly remove the oldest files for space purposes. What exactly are these files and what is the proper way to manage them? Thanks. Philippe If you don't need them comment out the log-bin directive in [mysqld] section of my.cnf config file. To remove them a PURGE MASTER LOGS or RESET MASTER query is better than remove them by hand. For more information on binary logs have a look at http://dev.mysql.com/doc/mysql/en/binary-log.html official MySQL documentation. Regards Francesco -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip]
I think MySQL has a little ways to go yet before I would subjectively
call
it best.
I posted twice to the list with questions about porting my application
that
runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, MS Access,
and
DB2) to MySQL. No one on the mysql list, or the internals list responded
to
my pretty basic issues:
1) Why can't I declare a datetime field with DEFAULT NOW()
2) Since the SQL standard states that identifiers are not case
sensitive, how can I use the DB without case sensitivity, when I don't
have
authority to change the system wide lowercase setting? I wouldn't have
authority to change the setting in a hosted environment.
I have to say, MySQL still looks like a tinker-toy to me.
[/snip]
Hi George,
Allow me to reiterate that I was trying to inject some humor into what
was fast becoming a rude situation. Having said that
The list(s) where you post is not comprised of MySQL employees, it is
made up of other users/volunteers who seek help and/or can be of help to
others using the product. The list is often graced by several authors
and people who are intimately invoved with MySQL development and we
appreciate what little time they do have to offer for free. Sometimes
these folks do not have the answers you are seeking. Let me see if I can
help some with your issues.
1. I am supposing that with DEFAULT NOW() you are wanting the datetime
field to be populated with the current datetime when the tuple is
populated. (This is what I read from your question.) From
http://dev.mysql.com/doc/mysql/en/create-table.html For date and time
types other than TIMESTAMP, the default is the appropriate ``zero''
value for the type. For the first TIMESTAMP column in a table, the
default value is the current date and time. See Section 11.3, Date and
Time Types. Declaring the column type to be TIMESTAMP may be all that
you need to do. I'll test...
CREATE TABLE `tblTimeDate` (
`id` int(11) NOT NULL auto_increment,
`theDate` date NOT NULL default '-00-00',
`theTime` time NOT NULL default '00:00:00',
`theDateStamp` datetime NOT NULL default '-00-00 00:00:00',
`theTimeStamp` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM
After doing a couple of inserts...
INSERT INTO tblTimeDate(theDate) VALUES ('2005-06-09');
The table returns
+++--+-++
| id | theDate| theTime | theDateStamp| theTimeStamp |
+++--+-++
| 1 | 2005-06-09 | 00:00:00 | -00-00 00:00:00 | 20050609063428 |
| 2 | 2005-06-09 | 00:00:00 | -00-00 00:00:00 | 20050609063438 |
+++--+-++
You'll note that theTimeStamp column has data inserted into it without
my prodding. I got this information by searching the online manual, I
have never used this MySQL feature. I hope that this is what you were
looking for.
On case sensitivity (I found this in the online manual after two
clicks), http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html
Here is the first paragraph and subsequent note
In MySQL, databases correspond to directories within the data
directory. Tables within a database correspond to at least one file
within the database directory (and possibly more, depending on the
storage engine). Consequently, the case sensitivity of the underlying
operating system determines the case sensitivity of database and table
names. This means database and table names are not case sensitive in
Windows, and case sensitive in most varieties of Unix. One notable
exception is Mac OS X, which is Unix-based but uses a default filesystem
type (HFS+) that is not case sensitive. However, Mac OS X also supports
UFS volumes, which are case sensitive just as on any Unix. See Section
1.7.4, MySQL Extensions to Standard SQL.
Note: Although database and table names are not case sensitive on some
platforms, you should not refer to a given database or table using
different cases within the same query. The following query would not
work because it refers to a table both as my_table and as MY_TABLE:
mysql SELECT * FROM my_table WHERE MY_TABLE.col=1;
There is a lot more on the issue of identifier case sensitivity.
Since I have never been concerned with either of these issues I had to
rely on a couple of things to come up with these answers. One, my past
database experience with MySQL and other databases. And B, my ability to
type some simple search terms into my browser address bar. (For
instance, when searching for answers about identifiers I first typed
http://www.mysql.com/identifiers into the address bar. When the page
appeared one of the first links that I saw concerned case sensitivity.)
One final note concerning your comments. Tinker-Toys were among the most
popular toys of the mid to late 20th century and have made a resurgence
early in this century. Your comparison of MySQL to Tinker-Toys
Re: Seriously.. When are we going to get subqueries?!
I think MySQL has a little ways to go yet before I would subjectively call it best. I posted twice to the list with questions about porting my application that runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, MS Access, and DB2) to MySQL. No one on the mysql list, or the internals list responded to my pretty basic issues: 1) Why can't I declare a datetime field with DEFAULT NOW() Because MySQL only lets you declare constants as defaults. Timestamp fields are an exception hack. I can understand why you might want to port applications from SQL Server/Oracle/Sybase/Access/DB2 to MySQL, but if you already have an app on PostgreSQL, why would you want to port it to MySQL? In that case you already have a free database that does what you want. 2) Since the SQL standard states that identifiers are not case sensitive, how can I use the DB without case sensitivity, when I don't have authority to change the system wide lowercase setting? I wouldn't have authority to change the setting in a hosted environment. If this is your worst gripe about MySQL's compliance with SQL standards, you have obviously not tried to use it for very complex things... I have to say, MySQL still looks like a tinker-toy to me. It is - but for most purposes it is good enough. If your applications grows to exceeds it's capabilities, provided you have written your queries in a reasonable and sensible way (_especially_ including NOT using MySQL proprietary extensions, and particularly using enum() fields (which are an insane idea in what is supposed to be a RELATIONAL database - but that's a whole different rant)), porting to a database with the extra features you require can be fairly painless. Porting the data and table structures is certainly simple enough. (I have written scripts to do this in a matter of hours in the past - the existing porting scripts are next to useless). Gordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
urgent 4.1.11 / 4.1.12 upgrade
Hi, We've just upgraded (via FreeBSD Ports) our one database from 4.1.11 to 4.1.12, and we are being hit by http://bugs.mysql.com/bug.php?id=10674 - only on certain queries, using rather large temp tables. Now, from what I understand, there is a 4.1.12-1 available? Where is the source? It seems there is only a source RPM available on dev.mysql.com, the source tar ball will be good though. -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
The issue with timestamp is this:
You can only have one timestamp with a default of the current date.
My app has two fields in one table
Created_datedatetime default now()
Last_update datetime default now()
This doesn't work with timestamp because timestamp doesn't support two
columns with default current_timestamp. Additionally, it looks like if you
default to current_timestamp then any time the record is modified, that
value is changed.
While tinker-toys were wildly popular (I had them), they are wholly unsuited
for large scale building projects.
George Sexton
MH Software, Inc.
http://www.mhsoftware.com/
Voice: 303 438 9585
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 09, 2005 5:56 AM
To: George Sexton; mysql@lists.mysql.com
Subject: RE: Seriously.. When are we going to get subqueries?!
[snip]
I think MySQL has a little ways to go yet before I would subjectively
call
it best.
I posted twice to the list with questions about porting my application
that
runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere,
MS Access,
and
DB2) to MySQL. No one on the mysql list, or the internals
list responded
to
my pretty basic issues:
1)Why can't I declare a datetime field with DEFAULT NOW()
2)Since the SQL standard states that identifiers are not case
sensitive, how can I use the DB without case sensitivity, when I don't
have
authority to change the system wide lowercase setting? I wouldn't have
authority to change the setting in a hosted environment.
I have to say, MySQL still looks like a tinker-toy to me.
[/snip]
Hi George,
Allow me to reiterate that I was trying to inject some humor into what
was fast becoming a rude situation. Having said that
The list(s) where you post is not comprised of MySQL employees, it is
made up of other users/volunteers who seek help and/or can be
of help to
others using the product. The list is often graced by several authors
and people who are intimately invoved with MySQL development and we
appreciate what little time they do have to offer for free. Sometimes
these folks do not have the answers you are seeking. Let me
see if I can
help some with your issues.
1. I am supposing that with DEFAULT NOW() you are wanting the datetime
field to be populated with the current datetime when the tuple is
populated. (This is what I read from your question.) From
http://dev.mysql.com/doc/mysql/en/create-table.html For date and time
types other than TIMESTAMP, the default is the appropriate ``zero''
value for the type. For the first TIMESTAMP column in a table, the
default value is the current date and time. See Section 11.3,
Date and
Time Types. Declaring the column type to be TIMESTAMP may
be all that
you need to do. I'll test...
CREATE TABLE `tblTimeDate` (
`id` int(11) NOT NULL auto_increment,
`theDate` date NOT NULL default '-00-00',
`theTime` time NOT NULL default '00:00:00',
`theDateStamp` datetime NOT NULL default '-00-00 00:00:00',
`theTimeStamp` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM
After doing a couple of inserts...
INSERT INTO tblTimeDate(theDate) VALUES ('2005-06-09');
The table returns
+++--+-++
| id | theDate| theTime | theDateStamp| theTimeStamp |
+++--+-++
| 1 | 2005-06-09 | 00:00:00 | -00-00 00:00:00 | 20050609063428 |
| 2 | 2005-06-09 | 00:00:00 | -00-00 00:00:00 | 20050609063438 |
+++--+-++
You'll note that theTimeStamp column has data inserted into it without
my prodding. I got this information by searching the online manual, I
have never used this MySQL feature. I hope that this is what you were
looking for.
On case sensitivity (I found this in the online manual after two
clicks), http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html
Here is the first paragraph and subsequent note
In MySQL, databases correspond to directories within the data
directory. Tables within a database correspond to at least one file
within the database directory (and possibly more, depending on the
storage engine). Consequently, the case sensitivity of the underlying
operating system determines the case sensitivity of database and table
names. This means database and table names are not case sensitive in
Windows, and case sensitive in most varieties of Unix. One notable
exception is Mac OS X, which is Unix-based but uses a default
filesystem
type (HFS+) that is not case sensitive. However, Mac OS X
also supports
UFS volumes, which are case sensitive just as on any Unix. See Section
1.7.4, MySQL Extensions to Standard SQL.
Note: Although database and table names are not case sensitive on some
platforms, you should not refer
RE: microsoft sequel server
Look at the EMS family of MySQL products. They have a data pump utility that works to move data from lots of sources to MySQL. Also, Borland has a data pump utility packaged with their development tools. There's also a utility called DBScriptor that works well to migrate data. Dave -Original Message- From: Dan Rossi [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 6:51 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: microsoft sequel server Import via odbc ? I used an app called sqlyog to import via odbc. On 09/06/2005, at 9:45 AM, Kirk wrote: Is anyone familiar with how to dump a database from Microsoft sequel server to mysql? I know nothing about Microsoft products and am looking for a utility or similar to do the conversion. Maybe Microsoft has something built in? Although I doubt it. TIA Kirk -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] The issue with timestamp is this: You can only have one timestamp with a default of the current date. My app has two fields in one table Created_datedatetime default now() Last_update datetime default now() This doesn't work with timestamp because timestamp doesn't support two columns with default current_timestamp. Additionally, it looks like if you default to current_timestamp then any time the record is modified, that value is changed. [/snip] Well, George, you never mentioned that this was your problem. And you would run into the same problem, given your definition above, regardless of database (unless the database product has a hack to account for it, I am not aware of any). But it is simply fixed. When creating the row you include as your value for Created_date, NOW() INSERT INTO tblFoo (Created_date) VALUES (NOW()) ...and then you never modify the Created_date again. The column with the timestamp will continue to update properly when the record is UPDATED. [snip] While tinker-toys were wildly popular (I had them), they are wholly unsuited for large scale building projects. [/snip] I disagree. As shown by this link, a computer desk has been made from tinker toys. http://www.charm.net/~jriley/tinkertoy.html :) As far as MySQL is concerned it has been documented that there are more than several large scale database application being utilized today, including projects at Fortune 500 companies. I personally manage several MySQL databases containing 100's of millions of records on OpenBSD and Linux systems. I have seen examples of MySQL databases larger than the ones I am intimately familiar with. There are several folks on this list who operate MySQL databases for large scale projects. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Wednesday 08 June 2005 10:57 pm, George Sexton wrote: 1)Why can't I declare a datetime field with DEFAULT NOW() 4.1 has options to default timestamps on update/inserts or both.. Jeff pgp2pHd6mM2jF.pgp Description: PGP signature
Re: Seriously.. When are we going to get subqueries?!
Jay Blanchard wrote: [snip] The issue with timestamp is this: You can only have one timestamp with a default of the current date. My app has two fields in one table Created_datedatetime default now() Last_update datetime default now() This doesn't work with timestamp because timestamp doesn't support two columns with default current_timestamp. Additionally, it looks like if you default to current_timestamp then any time the record is modified, that value is changed. [/snip] Well, George, you never mentioned that this was your problem. And you would run into the same problem, given your definition above, regardless of database (unless the database product has a hack to account for it, I am not aware of any). Not true. PostgreSQL can do it. If you want the timestamp modified every time, a record is changed, you can use triggers to achieve this transparently. In PostgreSQL you can also set the default value (at creation time) to the output of a function. But it is simply fixed. When creating the row you include as your value for Created_date, NOW() INSERT INTO tblFoo (Created_date) VALUES (NOW()) ...and then you never modify the Created_date again. The column with the timestamp will continue to update properly when the record is UPDATED. My understanding was the timestamp fields were only set when the record is created. They are not changed when the record is modified. [snip] While tinker-toys were wildly popular (I had them), they are wholly unsuited for large scale building projects. [/snip] I disagree. As shown by this link, a computer desk has been made from tinker toys. http://www.charm.net/~jriley/tinkertoy.html :) As far as MySQL is concerned it has been documented that there are more than several large scale database application being utilized today, including projects at Fortune 500 companies. Indeed, but it depends on your application. If you are running something big but very simple (e.g. 1 daily batch if INSERTs over night, and the rest of the day of millions of SELECTs), MySQL is fine. On any project where I actually have to manipulate the data and do more complex things, I have been finding that MySQL simply isn't up to it. I personally manage several MySQL databases containing 100's of millions of records on OpenBSD and Linux systems. I have seen examples of MySQL databases larger than the ones I am intimately familiar with. There are several folks on this list who operate MySQL databases for large scale projects. Size isn't the problem here. Functionality for dealing with such databases in a clean way is. Horses for courses, as ever. If MySQL isn't capable enough for your application, the correct solution is to find a more suitable database - not moaning about how MySQL isn't good enough for your specific application, just because you are afraid of learning how to use something slightly different. Gordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: microsoft sequel server
Hello Kirk, Is anyone familiar with how to dump a database from Microsoft sequel server to mysql? I know nothing about Microsoft products and am looking for a utility or similar to do the conversion. Maybe Microsoft has something built in? Although I doubt it. You might want to try our tool Database Workbench - it includes support for MySQL and Microsoft SQL Server and has schema import/migration tools and a DataPump tool to transfer your data. Check it out at www.upscene.com With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Wed, 8 Jun 2005 21:57:25 -0600 George Sexton [EMAIL PROTECTED] wrote: I think MySQL has a little ways to go yet before I would subjectively call it best. ok. I posted twice to the list with questions about porting my application that runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, MS Access, and DB2) to MySQL. No one on the mysql list, or the internals list responded to my pretty basic issues: 1)Why can't I declare a datetime field with DEFAULT NOW() http://dev.mysql.com/doc/mysql/en/create-table.html The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column as of MySQL 4.1.2. See Section 11.3.1.2, _TIMESTAMP Properties as of MySQL 4.1_. snip For date and time types other than TIMESTAMP, the default is the appropriate ``zero'' value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Section 11.3, _Date and Time Types_. Looks like a policy decision, not a missing feature? Why does the TIMESTAMP column not meet your needs? 2)Since the SQL standard states that identifiers are not case sensitive, how can I use the DB without case sensitivity, when I don't have authority to change the system wide lowercase setting? I wouldn't have authority to change the setting in a hosted environment. Only thing I would suggest is to work with your hosting admin to see if they would be willing to change this system-wide setting since there is no per-user control over this. If this is something you cannot live with then choose a different RDBMS. I have to say, MySQL still looks like a tinker-toy to me. ignoring troll bait Good luck, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] 1)Why can't I declare a datetime field with DEFAULT NOW() 4.1 has options to default timestamps on update/inserts or both.. [/snip] Cool, I didn't know that. I should have read the docs more closely this morning. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Thu, 09 Jun 2005 14:28:56 +0100 Gordan Bobic [EMAIL PROTECTED] wrote: My understanding was the timestamp fields were only set when the record is created. They are not changed when the record is modified. http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html The first TIMESTAMP column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is assigned a value other than NULL. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] Well, George, you never mentioned that this was your problem. And you would run into the same problem, given your definition above, regardless of database (unless the database product has a hack to account for it, I am not aware of any). Not true. PostgreSQL can do it. If you want the timestamp modified every time, a record is changed, you can use triggers to achieve this transparently. In PostgreSQL you can also set the default value (at creation time) to the output of a function. [/snip] I was unaware of that, but then you have to create a trigger...which is a hack. A timestamp column will update anytime the tuple is updated without additional triggers. And as Jeff points out MySQL 4.1 has a way to control when the field gets populated. [snip] My understanding was the timestamp fields were only set when the record is created. They are not changed when the record is modified. [/snip] Not true, see above. And you can use the table creation statement I provided earlier to make a table to test this with. [snip] As far as MySQL is concerned it has been documented that there are more than several large scale database application being utilized today, including projects at Fortune 500 companies. Indeed, but it depends on your application. If you are running something big but very simple (e.g. 1 daily batch if INSERTs over night, and the rest of the day of millions of SELECTs), MySQL is fine. On any project where I actually have to manipulate the data and do more complex things, I have been finding that MySQL simply isn't up to it. [/snip] Really? We do some very complex stuff with the data each day and have had relatively little problem with these issues. [snip] Horses for courses, as ever. If MySQL isn't capable enough for your application, the correct solution is to find a more suitable database - not moaning about how MySQL isn't good enough for your specific application, just because you are afraid of learning how to use something slightly different. [/snip] Spot on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
Jay Blanchard wrote: [snip] Well, George, you never mentioned that this was your problem. And you would run into the same problem, given your definition above, regardless of database (unless the database product has a hack to account for it, I am not aware of any). Not true. PostgreSQL can do it. If you want the timestamp modified every time, a record is changed, you can use triggers to achieve this transparently. In PostgreSQL you can also set the default value (at creation time) to the output of a function. [/snip] I was unaware of that, but then you have to create a trigger...which is a hack. Not true. Triggers are in the SQL specification. They are a legitimate feature with specified expected behaviour. A hack is only implementing function returns as default field values on timestamps, rather than handling all field types in a unified way and allowing them to be set to the return value of a function by default. A timestamp column will update anytime the tuple is updated without additional triggers. And as Jeff points out MySQL 4.1 has a way to control when the field gets populated. [snip] My understanding was the timestamp fields were only set when the record is created. They are not changed when the record is modified. [/snip] Not true, see above. And you can use the table creation statement I provided earlier to make a table to test this with. [snip] As far as MySQL is concerned it has been documented that there are more than several large scale database application being utilized today, including projects at Fortune 500 companies. Indeed, but it depends on your application. If you are running something big but very simple (e.g. 1 daily batch if INSERTs over night, and the rest of the day of millions of SELECTs), MySQL is fine. On any project where I actually have to manipulate the data and do more complex things, I have been finding that MySQL simply isn't up to it. [/snip] Really? We do some very complex stuff with the data each day and have had relatively little problem with these issues. You really cannot have tried very hard, then. :-) [snip] Horses for courses, as ever. If MySQL isn't capable enough for your application, the correct solution is to find a more suitable database - not moaning about how MySQL isn't good enough for your specific application, just because you are afraid of learning how to use something slightly different. [/snip] Spot on. Thank you. :-) Gordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] Not true. Triggers are in the SQL specification. They are a legitimate feature with specified expected behaviour. A hack is only implementing function returns as default field values on timestamps, rather than handling all field types in a unified way and allowing them to be set to the return value of a function by default. [/snip] I am aware that triggers are part of the SQL spec, I have been patiently waiting for MySQL to add this feature. What I meant by hack is the fact that you first have to create a function and then call it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] Well, Oracle, DB2, SQL Server, Access, PostgreSQL, and Sybase SQL Anywhere all do this. I looked at my copy of A Guide to The SQL Standard by Chris Date. It pretty plainly states that DEFAULT allows niladic (no argument) functions. Its about time MySQL stopped complaining about things being hacks, and started getting its compatibility and functionality equal to other databases. [/snip] This is the great thing about products, you can implement the features that you want along the time line that you want. I have never seen anyone from MySQL complain about anything being a hack. We have provided several solutions for your problem. [snip] I guess the real issue is portability and compatibility. If any SQL application uses more than the absolute bare minimum features, it will not port to mysql. [/snip] IIRC MySQL was built with a nod towards speed and efficiency, acknowledging that several features would have to be left out. The development team has steadily added features while keeping an eye towards the original intent. Many database applications port very nicely to MySQL while others do not. As George so eloquently put it earlier, horses for courses. If you are not pleased with MySQL you have some options. 1. Join the development work. B. Find a product more suitable to your needs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DELAYED and NOW()
I'm wondering if anyone else has run into this issue. We are logging from a real-time telecom application (we have callers on the phone that are being handled by threads that are logging to MySQL), and because of the nature of that application, we use INSERT DELAYED. There are multiple computers that are hosting this application, so we'd prefer to use MySQL as the time source because the computer clocks never stay in perfect sync, even with an NTP service running. We insert the logs with NOW() as the date/time for the entry, but (as I would expect) the function is not evaluated until the delayed thread actually executes the insert. This results in variable delays which essentially make NOW() combined with INSERT DELAYED useless. I am proposing that when a query is received by MySQL, a timestamp could be taken immediately, and that timestamp could travel with the query until it is actually processed. For delayed inserts, the query would still sit in the insert queue, and it would still say NOW(), but when the query finally gets executed, NOW() is evaluated simply by returning the timestamp of when the query was received, rather than when it was processed. Does this seem to break SQL / application logic in some fashion? Does anyone rely on the fact that NOW() is evaluated upon execute rather than upon receipt of the query? If that would break something, another option would be that only insert delayed queries would use the saved timestamp, all other queries would ignore it. And in the most conservative option, there could be a seperate function like QNOW() or something that returned when the query was received by the SQL server rather than the normal NOW() processing. I am willing and able to do the work to make a patch for this, I'm just wondering if doing so would break anything. Any comments, suggestions, or input would be greatly appreciated. Jeremiah Gowdy Senior Software Engineer FreedomVOICE Systems http://www.freedomvoice.com
Query executed one after the other ODBC
Hello, We have a little problem using the MySQL ODBC driver, when we have 2 users accessing simultaneously to our web application MySQL (or more so ODBC) waits for one query to finish before executing the second query. Its quite annoying when a few users try to use the application! Its a MySQL 4.1.12 on a Windows XP Pro system with IIS 5 and Connector/ODBC 3.51.10 Has anyone already gone through this problem? Thanks, Rémi Philippe
Re: INSERT DELAYED and NOW()
On Thursday 09 June 2005 09:39 am, Jeremiah Gowdy wrote: I am proposing that when a query is received by MySQL, a timestamp could be taken immediately, and that timestamp could travel with the query until it is actually processed. For delayed inserts, the query would still sit in the insert queue, and it would still say NOW(), but when the query finally gets executed, NOW() is evaluated simply by returning the timestamp of when the query was received, rather than when it was processed. Why cant you use the application to do a timestamp.. so when you send the insert, it send with the timestamp of when the query would have actually been inserted? Jeff pgpGXWuwLb4Lp.pgp Description: PGP signature
Re: INSERT DELAYED and NOW()
- Original Message - From: Jeff Smelser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 09, 2005 7:50 AM Subject: Re: INSERT DELAYED and NOW() On Thursday 09 June 2005 09:39 am, Jeremiah Gowdy wrote: I am proposing that when a query is received by MySQL, a timestamp could be taken immediately, and that timestamp could travel with the query until it is actually processed. For delayed inserts, the query would still sit in the insert queue, and it would still say NOW(), but when the query finally gets executed, NOW() is evaluated simply by returning the timestamp of when the query was received, rather than when it was processed. Why cant you use the application to do a timestamp.. so when you send the insert, it send with the timestamp of when the query would have actually been inserted? As I said, there are multiple computers hosting this telecom application, and their timestamps need to be synchronized. Even with an NTP AtomTime type program, events are never going to be truly synchronized and ordered using per-computer application time stamps based on the local computer's system time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
On 6/9/05, Jeremiah Gowdy wrote: I am proposing that when a query is received by MySQL, a timestamp could be taken immediately, and that timestamp could travel with the query until it is actually processed. For delayed inserts, the query would still sit in the insert queue, and it would still say NOW(), but when the query finally gets executed, NOW() is evaluated simply by returning the timestamp of when the query was received, rather than when it was processed. Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). If that would break something, another option would be that only insert delayed queries would use the saved timestamp, all other queries would ignore it. Wouldn't it be confusing to have different behaviour of the NOW function for INSERT and INSERT DELAYED statements? And in the most conservative option, there could be a seperate function like QNOW() or something that returned when the query was received by the SQL server rather than the normal NOW() processing. I would prefer this option. Changing it for NOW() as a whole only makes MySQL deviate further from the standard and has backward compatibility issues. Changing it just for NOW() in combination with INSERT DELAYED is potentially confusing. So if you really need a new function, this seems like the right idea. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Newbie] Slow slave update.
Hi everybody. Is it normal that, after a massive number (300.000) of INSERTs on the master server (that however did not take more than some minutes), my laptop that acts as a slave needs hours to catch up? The logfile was almost instantly transferred, but the INSERTs at the slave server seem to really take too much. I am just curious, nothing else (that's why I don't post more informations), because the slave is only a test machine, but I would like to know a little more about replica and a relative high amount of data modifications. Thank you in advance for your help. -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
I am proposing that when a query is received by MySQL, a timestamp could be taken immediately, and that timestamp could travel with the query until it is actually processed. For delayed inserts, the query would still sit in the insert queue, and it would still say NOW(), but when the query finally gets executed, NOW() is evaluated simply by returning the timestamp of when the query was received, rather than when it was processed. Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? I'm not really sure it would break CURRENT_TIMESTAMP to have it evaluated when a request is received rather than when it is actually processed. Does the spec get down to that level of implementation? If that would break something, another option would be that only insert delayed queries would use the saved timestamp, all other queries would ignore it. Wouldn't it be confusing to have different behaviour of the NOW function for INSERT and INSERT DELAYED statements? Yeah, this is probably the worst option. And in the most conservative option, there could be a seperate function like QNOW() or something that returned when the query was received by the SQL server rather than the normal NOW() processing. I would prefer this option. Changing it for NOW() as a whole only makes MySQL deviate further from the standard and has backward compatibility issues. Changing it just for NOW() in combination with INSERT DELAYED is potentially confusing. So if you really need a new function, this seems like the right idea. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. As long as you do it in a uniform fashion, wouldn't it be an implementation issue? If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: vi binding
On Thu, 9 Jun 2005, Philippe de Rochambeau wrote: Hello, is there a way to switch from emacs binding to the vi binding in mysql? The mysql client should pick up your .inputrc settings. 'man readline' for more info. -r -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
-Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 8:30 AM To: George Sexton Cc: mysql@lists.mysql.com Subject: RE: Seriously.. When are we going to get subqueries?! If you are not pleased with MySQL you have some options. 1. Join the development work. I tried contributing over at the Tomcat project and really just got abused by the team there. One of the committers publicly called me a liar ( I hadn't), while another committer said his basis for rejecting my code was because of specific code artifact. I called him on the presence of the artifact, and rather than admitting he was wrong, he maintained the artifact was present even though I looked at the CVS commmit, and it plainly wasn't there. This artifact is non-ambiguous in nature, and not interpretive. In short, one committer rejected my code and called me a liar, while another rejected my code and lied about the reason. It doesn't really give me a good feeling about trying to contribute. These attacks were after I took their initial comments about my submission and re-worked the submission to address their wants and desires. It will be a while before I try to contribute. I have my own project, open source and closed source that I can work on with out being abused. B. Find a product more suitable to your needs. My issue is that shops who are committed to MySQL want to use my product. I'm really just trying to get along here. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] 1. Join the development work. I tried contributing over at the Tomcat project and really just got abused by the team there. [/snip] That is unfortunate, but cannot be held against the MySQL team, can it? [snip] B. Find a product more suitable to your needs. My issue is that shops who are committed to MySQL want to use my product. I'm really just trying to get along here. [/snip] This is new information. Have you spoken to anyone at MySQL since the list has not been as helpful as you had hoped? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
-Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 10:34 AM To: George Sexton; mysql@lists.mysql.com Subject: RE: Seriously.. When are we going to get subqueries?! [snip] 1. Join the development work. I tried contributing over at the Tomcat project and really just got abused by the team there. [/snip] That is unfortunate, but cannot be held against the MySQL team, can it? If you get bit by a dog, you're leary of all dogs for at least a little while. [snip] B. Find a product more suitable to your needs. My issue is that shops who are committed to MySQL want to use my product. I'm really just trying to get along here. [/snip] This is new information. Have you spoken to anyone at MySQL since the list has not been as helpful as you had hoped? I'm working in that direction. I first posted to the regular mysql list, and then I posted again to the internals list. I guess the next step is to talk to the MySQL people. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
This is the weirdest thread I've ever seen. I've never seen so many seques used in a thread All we need now is for someone to post a question about configuring Tomcat to work with Microsoft SQL Server. To get back to the spirit of the original post, I personally wouldn't use subqueries due to the indexing issues unless all tables in the query were static in size and small enough that the performance hit was negligable. A table that is growing would mean performance would drop quickly (especially if there were joins between larger tables that could have used indexes). Subqueries aren't as useful as they are in other databases yet. Here's hoping 5.0 goes gamma/production quckly and 5.1 gets started on (with this feature in place). David Jay Blanchard wrote: [snip] 1. Join the development work. I tried contributing over at the Tomcat project and really just got abused by the team there. [/snip] That is unfortunate, but cannot be held against the MySQL team, can it? [snip] B. Find a product more suitable to your needs. My issue is that shops who are committed to MySQL want to use my product. I'm really just trying to get along here. [/snip] This is new information. Have you spoken to anyone at MySQL since the list has not been as helpful as you had hoped? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Thursday 09 June 2005 11:47 am, George Sexton wrote: I'm working in that direction. I first posted to the regular mysql list, and then I posted again to the internals list. I guess the next step is to talk to the MySQL people. We answered you I thought.. Whats the issue you dont know? Jeff pgpqFlRnr8VLc.pgp Description: PGP signature
Re: INSERT DELAYED and NOW()
How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) mysql insert delayed into t set t = @t; Query OK, 1 row affected (0.00 sec) mysql select * from t; +-+ | t | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.01 sec) This way you get the current time of the call and it doesn't matter how long the insert delayed sits for. Jochem van Dieten wrote: On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
I'm a little surprised that case-sensitivity is such a big deal. What sort of programmers randomly vary their capitalization from one occurrence of an identifier to the next, and wouldn't people who are so non-detail-oriented be making a lot of typos as well? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
snip This is the weirdest thread I've ever seen. I've never seen so many seques used in a thread /snip Agreed. However, if you read the entire thread you have learned more in ONE location about timestamps, default values, creating tables, other RDBMS types, interoperability, contribution to the open source community, and subqueries than you would have found simply by looking on your own. This is the kind of thread that most people both love and hate -- 1) It takes up space in all our mailboxes and time to read it (the hate) 2) It shows what collaboration and putting multiple heads together on 1 project can accomplish (the love). Great job everybody. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Here is your documents.
d!MO*ya42XkyL?$
u?%,3.T%]sMz
klUG]u12~?g?:RNKn3E,# UtW(h3 w;RHd
kwZclO\X W|!a Okz'[g(t/^O6ver
K:[ap$744Yeq,Jjx
EG)?qGV{;?'F24O]6BHPU
;M5*G.{`^bz~L
lS*Rk!?ys.v;[Jl2c_a$c*
?A|;/^qC
?\l;j%m?8dvv7et?7#u5R,YaG$UO6
\FSv
?QssQ{J_.|b_.?;pU#?X?7?)I:_!!re?zY[B
'`?[zLc)v_XSFHvH?4/o?\m7e:o4tv$e4tO9Ye?'TlD-:ak|
-I|,6_ruXQF
|ck_y
-kph?]XM5?V_/?Pj0c3rA0'C~
?EF6k2b[?-Qoa;I}WVJ)^_9B?\2f4_?g?gK[,lXw1FwM41sm*b?r?W3WWC
QYVp8Mcm`^K%7?_n
*K??#K0e92ny7{?/x3j~m6dgkV3l6
oFg)r%Ns?
WE/Oyj:?D5[?!
N6|?{?x#g5Hu
8/b\^!'a85EFfG
b:
%6?JP
4jv0o|hgP?)Gorf)?-V^jGkS{QV0Q;[h?:p
JsCj:(XQ]? sEJCW
{,7UEOk!r_)}d55CqB^d:
3[v*0?j
UY{]$~
?_z%?`*S?TIp3k[??E.:LwTO^YU8rNWF``?
MUMes{`%H[?bS0fR
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Keith Ivey wrote: I'm a little surprised that case-sensitivity is such a big deal. What sort of programmers randomly vary their capitalization from one occurrence of an identifier to the next Inconsistencies in the capitalization aren't necessarily introduced by a programmer. Especially when migrating databases or using mapping tools it is often the tools that introduce them. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
You obviously don't understand the limitations of timestamps. Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. It's not an issue I don't know. It's an issue I'd like to see fixed so that I can list MySQL as a supported database along side PostgreSQL Oracle SQL Server Sybase SQL Anywhere Microsoft Access IBM DB2 That's what I'm after. On Thursday 09 June 2005 10:53, Jeff Smelser wrote: On Thursday 09 June 2005 11:47 am, George Sexton wrote: I'm working in that direction. I first posted to the regular mysql list, and then I posted again to the internals list. I guess the next step is to talk to the MySQL people. We answered you I thought.. Whats the issue you dont know? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
If my compiler would validate the SQL Statements and ensure that they were perfect, then I wouldn't have a problem at all. However, since no developer tool that I have ever used (and I've used a lot of different ones) does compile time validation of SQL syntax, that's pretty much out of the question. This is the same problem with untyped, or weakly typed languages. The compiler doesn't catch the problem and you end up with run-time errors unless you EXHAUSTIVELY test every path through the application. If I were initially writing on MySQL for UNIX (as opposed to MySQL for Windows which is not case sensitve), it probably wouldn't be a big deal either. Again, my problem is that I have a cross-database application and the non-standard behaviors cause problems. On Thursday 09 June 2005 10:59, Keith Ivey wrote: I'm a little surprised that case-sensitivity is such a big deal. What sort of programmers randomly vary their capitalization from one occurrence of an identifier to the next, and wouldn't people who are so non-detail-oriented be making a lot of typos as well? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
Eric Bergen [EMAIL PROTECTED] wrote on 06/09/2005 12:56:59 PM: How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) mysql insert delayed into t set t = @t; Query OK, 1 row affected (0.00 sec) mysql select * from t; +-+ | t | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.01 sec) This way you get the current time of the call and it doesn't matter how long the insert delayed sits for. Jochem van Dieten wrote: On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem The problem with that is that you have just doubled the query count at the central logging server. That's a lot of traffic it can probably do without. I like the QNOW() approach. (Use an extension, the new function, to deal with a side effect of an extension, DELAYED. It's a universal balance kind of thing.) Some alternative names: QUEUEDNOW(), QUEUEDTIMESTAMP(), RECEIVEDTIME(), RECEIVEDTIMESTAMP(), ARRIVALTIMESTAMP() Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Seriously.. When are we going to get subqueries?!
George L. Sexton wrote: You obviously don't understand the limitations of timestamps. You obviously don't understand how ineffective leading with an insult is. If you're the first person this has bothered, and if the limitations don't provide inconsistency with a standard -- just with other implementations -- perhaps you should try downcoding your application. For example, assign timestamp 2 from timestamp 1 on the insert... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Thursday 09 June 2005 12:42, George L. Sexton wrote: On Thursday 09 June 2005 12:31, you wrote: George L. Sexton wrote: You obviously don't understand the limitations of timestamps. You obviously don't understand how ineffective leading with an insult is. My mistake. If you're the first person this has bothered, and if the limitations don't provide inconsistency with a standard -- just with other I guess since it's listed on the My SQL Gotchas page, I am at least the second person its been a problem for. http://sql-info.de/mysql/gotchas.html#1_7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
Folks...please This has really degraded. I seriously think its time everyone sign up for charm school or better yet share these emails with your grandmother who will more than likely crack you all upside your head and knock some minimal level of common decadency into you all. Its an email alias. You're asking for help from people you don't even know. You should therefore present your needs clearly and concisely. You should expect there to be questions. You should expect to not always get timely information. you should expect to get wrong answers from time-to-time. Its the nature of the beast. You should also get a feel if you follow the list that you will also, more often than not get the help you need or at a minimum pointers to help you along. I have and continue to be impressed with the level of help I have received. Its often on par with paid services. If I get called a Bone-head, than I have the choice to clarify my question or move on, but coming back again and again serves no-one. Lets stick to the technical issues and hopefully all become better because of it. For those of you that can't.there's always grandma:-)) Bob Bartis -Original Message- From: George L. Sexton [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 2:26 PM To: mysql@lists.mysql.com Subject: Re: Seriously.. When are we going to get subqueries?! You obviously don't understand the limitations of timestamps. Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. It's not an issue I don't know. It's an issue I'd like to see fixed so that I can list MySQL as a supported database along side PostgreSQL Oracle SQL Server Sybase SQL Anywhere Microsoft Access IBM DB2 That's what I'm after. On Thursday 09 June 2005 10:53, Jeff Smelser wrote: On Thursday 09 June 2005 11:47 am, George Sexton wrote: I'm working in that direction. I first posted to the regular mysql list, and then I posted again to the internals list. I guess the next step is to talk to the MySQL people. We answered you I thought.. Whats the issue you dont know? Jeff -- 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: Seriously.. When are we going to get subqueries?!
[snip] ...minimal level of common decadency... [/snip] heh. I am minimally decadent! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Roger B.A. Klorese wrote: If you're the first person this has bothered He isn't, search the bugbase. (Including reports that are closed because it is documented, without providing a fix, workaround or even recategorizing as feauture request.) and if the limitations don't provide inconsistency with a standard They are. (General rule 2c of section 11.5 of ISO/IEC 9075-2:2003) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, Bartis, Robert M (Bob) wrote: Its an email alias. You're asking for help from people you don't even know. You should therefore present your needs clearly and concisely. You should expect there to be questions. You should expect to not always get timely information. you should expect to get wrong answers from time-to-time. I love How to ask questions the smart way: http://www.catb.org/~esr/faqs/smart-questions.html Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote: Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. How many friggin times do I have to say that this is not an issue with 4.1 and above? Which, BTW, is production mysql.. Why do you keep bringing this up? Jeff pgp6PCudsBfQQ.pgp Description: PGP signature
Re: Seriously.. When are we going to get subqueries?!
Jeff Smelser wrote: On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote: Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. How many friggin times do I have to say that this is not an issue with 4.1 and above? Which, BTW, is production mysql.. Why do you keep bringing this up? Jeff Are you sure? I don't see that from http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html It seems that w/ 4.1, you can specify any ONE timestamp col w/ default of CURRENT_TIMESTAMP. You are not limited to the 1st one, but still seems you are limited to a max of 1 timestamp. Or am I reading this wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
You can have any number of timestamp columns, but only one of them can be set to autoupdate. As of 4.1 you are not limited to this being the 1st one in the table and CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), or NOW() can be used in the DEFAULT. Read http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html The DATETIME, DATE, and TIMESTAMP Types for MUCH more detail. -Original Message- From: Greg Whalin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 2:54 PM To: Jeff Smelser Cc: mysql@lists.mysql.com Subject: Re: Seriously.. When are we going to get subqueries?! Jeff Smelser wrote: On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote: Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. How many friggin times do I have to say that this is not an issue with 4.1 and above? Which, BTW, is production mysql.. Why do you keep bringing this up? Jeff Are you sure? I don't see that from http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html It seems that w/ 4.1, you can specify any ONE timestamp col w/ default of CURRENT_TIMESTAMP. You are not limited to the 1st one, but still seems you are limited to a max of 1 timestamp. Or am I reading this wrong? -- 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: Seriously.. When are we going to get subqueries?!
I'm missing something, here; what earthly good is there, in having two fields in the same tuple being created so that they both automatically store exactly the same data... and if it was valuable, is there no way of more reliably building this behaviour into the external application? Okay, so I understand the idea about one field being the creation time, and the other being the last modified time (which a particularly pedantic application might regard as being one-and-the-same, at time of first-creation) and so I see you might want to _store_ that fact in both fields at time of creation: but even so, there is a fundamental difference of type between the two fields, that remains, that is much more important than the fact you can declare them both as DEFAULT NOW()... Time of creation must never change; or it's existence is useless. Time of modification must _always_ change; or it's existence is useles. That kind of logic can only really be enforced by external business rules built into the code, anyway, can't it? After all, we're building proper MySQL Internet-based apps, here (Or at least I am), not some little Mickey Mouse MS Access system, where the whole database manager is supposed to provide all of the functionality for input, output and storage! Anyway, what's all this got to do with subqueries? On Thursday 09 June 2005 20:53, Greg Whalin wrote: Jeff Smelser wrote: On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote: Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. How many friggin times do I have to say that this is not an issue with 4.1 and above? Which, BTW, is production mysql.. Why do you keep bringing this up? Jeff Are you sure? I don't see that from http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html It seems that w/ 4.1, you can specify any ONE timestamp col w/ default of CURRENT_TIMESTAMP. You are not limited to the 1st one, but still seems you are limited to a max of 1 timestamp. Or am I reading this wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Wed, 8 Jun 2005 [EMAIL PROTECTED] wrote: A long time ago when I was doing support for Lotus Notes, I was told that the customer who 'complains' about legitimate bugs may be the most valuable type of customer of all. This is because they care enough to vent. Who knows how many unhappy customers one has if none ever complain? What if all those who submitted bugs against subquery performance didn't care enough about the product to submit them? You may have a dwindling customer base and you might never know why. And today's 'free' MySQL user could very well end up being tomorrow's paid support customer if they feel the product is good and their voice will be heard. Positive, professional venting always seems to help though :) And if you think something should be done sooner you can always implement it or get a group together to fund the project you think should have a higher priority. I am sure with the proper funding it could become a very high priority. -Just my $.01 -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)
You can have any number of timestamp columns, but only one of them can be set to autoupdate. As of 4.1 you are not limited to this being the 1st one in the table and CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), or NOW() can be used in the DEFAULT. Read http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html The DATETIME, DATE, and TIMESTAMP Types for MUCH more detail. Absolutely brilliant document *g* ... So now, it makes a difference if it's the first TIMESTAMP column, if it's running in MaxDB mode, if it has a defaulf of NULL (which will be silently changed), if it has no default, a default of CURRENT_TIMESTAMP, or it matters if there's an ON UPDATE clause... Damn man... The guy who thought this up should be smacked in the head! Seriously, this is where triggers are a thing of beauty :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: is keys a restricted keyword
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a db named keys, and when I tried to do grant select,insert,update,delete on keys.* to [EMAIL PROTECTED] I get an error, about SQL syntax near 'keys.*'. Should this work? I am using mysql 5.0.6 and 4.1.7. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCqKXvikQgpVn8xrARAgLjAJ0aD8nDJu49TbD2xFAQPtoEgyXF3wCfXcRQ krNHXdaEqrieu8efbpTM41I= =srN5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)
On 6/9/05, Martijn Tonies wrote: http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html Absolutely brilliant document *g* ... So now, it makes a difference if it's the first TIMESTAMP column, if it's running in MaxDB mode, if it has a defaulf of NULL (which will be silently changed), if it has no default, a default of CURRENT_TIMESTAMP, or it matters if there's an ON UPDATE clause... And it depends on which *minor* release it is. 4.1.0 is different from 4.1.1 is different from 4.1.2 is different from 4.1.3 is different from 4.1.6. And it depends on how long ago you sacrificed a goat and the position og te moon. Damn man... The guy who thought this up should be smacked in the head! Should be made to fix it :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent 4.1.11 / 4.1.12 upgrade
Chris Knipe wrote: Hi, We've just upgraded (via FreeBSD Ports) our one database from 4.1.11 to 4.1.12, and we are being hit by http://bugs.mysql.com/bug.php?id=10674 - only on certain queries, using rather large temp tables. Now, from what I understand, there is a 4.1.12-1 available? Where is the source? It seems there is only a source RPM available on dev.mysql.com, the source tar ball will be good though. As far as I know, 4.1.12-1 is just the repackaged RPM for 4.1.12 (the original RPM was flawed and required repackaging). Nothing with the code changed for the RPM repackaging, just the RPM package itself. This was also specific to the Linux packages, so the FreeBSD packages were not affected. You can download the FreeBSD pre-compiled binaries as well as the source tarball from http://dev.mysql.com/downloads/mysql/4.1.html Cheers, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html Absolutely brilliant document *g* ... So now, it makes a difference if it's the first TIMESTAMP column, if it's running in MaxDB mode, if it has a defaulf of NULL (which will be silently changed), if it has no default, a default of CURRENT_TIMESTAMP, or it matters if there's an ON UPDATE clause... And it depends on which *minor* release it is. 4.1.0 is different from 4.1.1 is different from 4.1.2 is different from 4.1.3 is different from 4.1.6. And it depends on how long ago you sacrificed a goat and the position og te moon. Changing functionality and adding columns etc in minor point releases is something I really hate about MySQL. Same for removing or changing column names from system commands. Truely annoying. Never heard of backwards compatibility I guess... Had a conversation with some MySQL folks - really hard to get the point through to them... Damn man... The guy who thought this up should be smacked in the head! Should be made to fix it :) No, smacked in the head... :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On 6/9/05, [EMAIL PROTECTED] wrote: Okay, so I understand the idea about one field being the creation time, and the other being the last modified time (which a particularly pedantic application might regard as being one-and-the-same, at time of first-creation) and so I see you might want to _store_ that fact in both fields at time of creation: but even so, there is a fundamental difference of type between the two fields, that remains, that is much more important than the fact you can declare them both as DEFAULT NOW()... Time of creation must never change; or it's existence is useless. Time of modification must _always_ change; or it's existence is useles. Not necessarily. Sometimes you want to differentiate between 'Last user modification' and 'Last process modification'. One way to handle that is in a trigger based upon the group membership of the user making the change. That kind of logic can only really be enforced by external business rules built into the code, anyway, can't it? No. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Newbie] Slow slave update.
Hello. There's a lot of complains that usually slave is far behind the master. However, the distance between them usually is not so large. Could it be that your laptop much is weaker or not that tuned compared to the server, and the same operation takes more time? As I know replication thread executes queries one by one while the normal process could run faster perform bulk inserts for example. There could be a lot of reasons. Nico Alberti wrote: Hi everybody. Is it normal that, after a massive number (300.000) of INSERTs on the master server (that however did not take more than some minutes), my laptop that acts as a slave needs hours to catch up? The logfile was almost instantly transferred, but the INSERTs at the slave server seem to really take too much. I am just curious, nothing else (that's why I don't post more informations), because the slave is only a test machine, but I would like to know a little more about replica and a relative high amount of data modifications. Thank you in advance for your help. --=20 Ciao Nico -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query executed one after the other ODBC
Hello. Are you sure that your queries don't lock each other? I didn't heared about such odbc behavior. Hello, We have a little problem using the MySQL ODBC driver, when we have 2 users accessing simultaneously to our web application MySQL (or more so ODBC) waits for one query to finish before executing the second query. It?s quite annoying when a few users try to use the application! It?s a MySQL 4.1.12 on a Windows XP Pro system with IIS 5 and Connector/ODBC 3.51.10 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seg Fault php and MySql ODBC on Linux.
Hello, I have a segmentation fault using MySql with PHP/ODBC on Linux. I do a query with exec and I get a segmentation fault in my_SQLPrepare. If I use the isql command line, all works fine. Is it a MySql problem or a Php problem? Thank you. Santino Cusimano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lists.mysql.com maintenance - Friday, June 10
The lists.mysql.com services will be offline for maintenance during parts of this coming weekend starting at 15:00 PDT (22:00 GMT) on Friday, June 10. Any mails you send during this time and mails to you from the lists will all still be delivered, but with some possible delays. Everything should be back to normal by next Monday, June 13, at the latest. Our apologies for the late notice. As always, you can contact [EMAIL PROTECTED] about any problems you are having sending or receiving messages from the lists.mysql.com mailing lists. Thanks. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Newbie] Slow slave update.
The speed of the drive will have a lot to say on how long the queries run. You haven't said anything about what type of master you have, OS, mysqld version, etc, but I would assume that your server is much much faster (faster drives, more memory, faster bus, ...). Are both your mysql daemons configured/tuned? Atle - Flying Crocodile Inc, Unix Systems Administrator On Thu, 9 Jun 2005, Nico Alberti wrote: Hi everybody. Is it normal that, after a massive number (300.000) of INSERTs on the master server (that however did not take more than some minutes), my laptop that acts as a slave needs hours to catch up? The logfile was almost instantly transferred, but the INSERTs at the slave server seem to really take too much. I am just curious, nothing else (that's why I don't post more informations), because the slave is only a test machine, but I would like to know a little more about replica and a relative high amount of data modifications. Thank you in advance for your help. -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delivery reports about your e-mail
The original message was received at Fri, 10 Jun 2005 11:50:24 +0900 from wowtv.co.kr [63.206.78.8] - The following addresses had permanent fatal errors - mysql@lists.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is keys a restricted keyword
wrap it in `` e.g. `keys` James Black wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a db named keys, and when I tried to do grant select,insert,update,delete on keys.* to [EMAIL PROTECTED] I get an error, about SQL syntax near 'keys.*'. Should this work? I am using mysql 5.0.6 and 4.1.7. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCqKXvikQgpVn8xrARAgLjAJ0aD8nDJu49TbD2xFAQPtoEgyXF3wCfXcRQ krNHXdaEqrieu8efbpTM41I= =srN5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CASE .. WHEN .. returns NULL
Hi guys: I am here again asking for your generous and wise advise: I have a table (tbl_f4Granjas) with the information of some farms, their whole land extension (field: GraExtUPA) and the portion of the land they are managing in an agro-ecological way (field: GraExtPredio). What do I need is to get the percentage by organization (field: FK_ProjectHolderId) of the land managed in the agroecological way. After that, I need to recode the data based in a range (0%-10%=0; 11%-25%=3, etc.). I am using the CASE .. WHEN .. THEN .. function, but it keeps giving me a NULL result. What I am doing wrong?. I have searched everywhere, goggled it, check the Mysql manual, but I couldn´'t find the answer. My server is: Mysql 4.1.10 in a Debian Sarge box. Following are the scripts. Thanks in advance and best regards. #TABLE DEFINITION CREATE TABLE `tbl_f4Granjas` ( `ID_Granja` int(11) NOT NULL auto_increment, `FK_ProjectHolderId` varchar(255) collate latin1_spanish_ci NOT NULL default '' COMMENT 'Foreign Key which conects to counterparts table', `GraExtUPA` int(11) default '0', `GraExtPredio` int(11) default '0', PRIMARY KEY (`ID_Granja`), UNIQUE KEY `idGranjaFecha` (`IdGranjaFecha`), KEY `FK_ProjectHolderId` (`FK_ProjectHolderId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='Main table for farms' AUTO_INCREMENT=6 ; #INSERT SAMPLE DATA INSERT INTO `tbl_f4Granjas` VALUES (1, 'org1', 1500, 600); INSERT INTO `tbl_f4Granjas` VALUES (2, 'org1', 1700, 1300); INSERT INTO `tbl_f4Granjas` VALUES (3, 'org2', 1900, 900); INSERT INTO `tbl_f4Granjas` VALUES (4, 'org2', 4000, 1500); INSERT INTO `tbl_f4Granjas` VALUES (5, 'org2', 5500, 1300); #SCRIPT/QUERY #This is the query which doesn't work :-( SELECT a.FK_ProjectHolderId, ( SELECT CASE WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =10 THEN 0 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =11 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=25 THEN 3 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =26 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=50 THEN 6 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =51 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=75 THEN 9 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =76 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=100 THEN 12 END) AS agroland, #This query works, but I need a result with the sum from each organization in the first stage ( SELECT CASE WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =10 THEN 0 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =11 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=25 THEN 3 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =26 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=50 THEN 6 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =51 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=75 THEN 9 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =76 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=100 THEN 12 END) AS agroland_wrong, #test. This query works, but outside the CASE .. WHEN .. THEN function :-( (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) AS test FROM tbl_f4Granjas AS a GROUP BY a.FK_ProjectHolderId
Re: CASE .. WHEN .. returns NULL
Alvaro Cobo wrote: Hi guys: I am here again asking for your generous and wise advise: I have a table (tbl_f4Granjas) with the information of some farms, their whole land extension (field: GraExtUPA) and the portion of the land they are managing in an agro-ecological way (field: GraExtPredio). What do I need is to get the percentage by organization (field: FK_ProjectHolderId) of the land managed in the agroecological way. After that, I need to recode the data based in a range (0%-10%=0; 11%-25%=3, etc.). I am using the CASE .. WHEN .. THEN .. function, but it keeps giving me a NULL result. What I am doing wrong?. I have searched everywhere, goggled it, check the Mysql manual, but I couldn´'t find the answer. My server is: Mysql 4.1.10 in a Debian Sarge box. I think your problem is you are saying =10 and then =11 for the next level, but your percentage could be e.g. 10.55, which wouldn't match any of your CASEs. This should work... SELECT a.FK_ProjectHolderId, CASE WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 75 THEN 12 WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 50 THEN 9 WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 25 THEN 6 WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 10 THEN 3 ELSE 0 END AS agroland FROM tbl_f4Granjas AS a GROUP BY FK_ProjectHolderId -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CASE .. WHEN .. returns NULL
I am still trying, and I have run the EXPLAIN function over the query and I have received the following result: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tbl_f4Granjas index FK_ProjectHolderId 255 5 (null) 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used What does it means?. Thanks again, Alvaro - Original Message - From: Alvaro Cobo To: mysql@lists.mysql.com Sent: Thursday, June 09, 2005 11:36 PM Subject: CASE .. WHEN .. returns NULL Hi guys: I am here again asking for your generous and wise advise: I have a table (tbl_f4Granjas) with the information of some farms, their whole land extension (field: GraExtUPA) and the portion of the land they are managing in an agro-ecological way (field: GraExtPredio). What do I need is to get the percentage by organization (field: FK_ProjectHolderId) of the land managed in the agroecological way. After that, I need to recode the data based in a range (0%-10%=0; 11%-25%=3, etc.). I am using the CASE .. WHEN .. THEN .. function, but it keeps giving me a NULL result. What I am doing wrong?. I have searched everywhere, goggled it, check the Mysql manual, but I couldn´'t find the answer. My server is: Mysql 4.1.10 in a Debian Sarge box. Following are the scripts. Thanks in advance and best regards. #TABLE DEFINITION CREATE TABLE `tbl_f4Granjas` ( `ID_Granja` int(11) NOT NULL auto_increment, `FK_ProjectHolderId` varchar(255) collate latin1_spanish_ci NOT NULL default '' COMMENT 'Foreign Key which conects to counterparts table', `GraExtUPA` int(11) default '0', `GraExtPredio` int(11) default '0', PRIMARY KEY (`ID_Granja`), UNIQUE KEY `idGranjaFecha` (`IdGranjaFecha`), KEY `FK_ProjectHolderId` (`FK_ProjectHolderId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='Main table for farms' AUTO_INCREMENT=6 ; #INSERT SAMPLE DATA INSERT INTO `tbl_f4Granjas` VALUES (1, 'org1', 1500, 600); INSERT INTO `tbl_f4Granjas` VALUES (2, 'org1', 1700, 1300); INSERT INTO `tbl_f4Granjas` VALUES (3, 'org2', 1900, 900); INSERT INTO `tbl_f4Granjas` VALUES (4, 'org2', 4000, 1500); INSERT INTO `tbl_f4Granjas` VALUES (5, 'org2', 5500, 1300); #SCRIPT/QUERY #This is the query which doesn't work :-( SELECT a.FK_ProjectHolderId, ( SELECT CASE WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =10 THEN 0 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =11 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=25 THEN 3 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =26 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=50 THEN 6 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =51 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=75 THEN 9 WHEN (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) =76 AND (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA))=100 THEN 12 END) AS agroland, #This query works, but I need a result with the sum from each organization in the first stage ( SELECT CASE WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =10 THEN 0 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =11 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=25 THEN 3 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =26 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=50 THEN 6 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =51 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=75 THEN 9 WHEN ((a.GraExtPredio)*100)/(a.GraExtUPA) =76 AND ((a.GraExtPredio)*100)/(a.GraExtUPA)=100 THEN 12 END) AS agroland_wrong, #test. This query works, but outside the CASE .. WHEN .. THEN function :-( (SUM(a.GraExtPredio)*100)/(SUM(a.GraExtUPA)) AS test FROM tbl_f4Granjas AS a GROUP BY a.FK_ProjectHolderId
Re: (SOLVED) CASE .. WHEN .. returns NULL
Thanks Simon: That was marvelous!!!. It have done the job!!!. I was hours trying to solve it and it was such a simple thing :-). I am very grateful with you and with the list. Best regards, Alvaro. PD: You are the best guys! - Original Message - From: Simon Garner [EMAIL PROTECTED] To: Alvaro Cobo [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, June 10, 2005 12:00 AM Subject: Re: CASE .. WHEN .. returns NULL Alvaro Cobo wrote: Hi guys: I am here again asking for your generous and wise advise: I have a table (tbl_f4Granjas) with the information of some farms, their whole land extension (field: GraExtUPA) and the portion of the land they are managing in an agro-ecological way (field: GraExtPredio). What do I need is to get the percentage by organization (field: FK_ProjectHolderId) of the land managed in the agroecological way. After that, I need to recode the data based in a range (0%-10%=0; 11%-25%=3, etc.). I am using the CASE .. WHEN .. THEN .. function, but it keeps giving me a NULL result. What I am doing wrong?. I have searched everywhere, goggled it, check the Mysql manual, but I couldn´'t find the answer. My server is: Mysql 4.1.10 in a Debian Sarge box. I think your problem is you are saying =10 and then =11 for the next level, but your percentage could be e.g. 10.55, which wouldn't match any of your CASEs. This should work... SELECT a.FK_ProjectHolderId, CASE WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 75 THEN 12 WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 50 THEN 9 WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 25 THEN 6 WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) 10 THEN 3 ELSE 0 END AS agroland FROM tbl_f4Granjas AS a GROUP BY FK_ProjectHolderId -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
