RE: move column position
Sorry about that. You are correct using AFTER or FIRST with ALTER TABLE ... MODIFY is not supported until version 4. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, RE: > I believe this is what you are looking for: > ALTER TABLE Images MODIFY Imcreatedat datetime NOT NULL default > '-00-00 00:00:00' AFTER Imccdtemp; Thanks, Tom for the clue. Yes, this is what I tried, but it did not work. Seems like modify does not accept the "AFTER" part. I tried it again, and the same failure: ERROR 1064: You have an error in your SQL syntax Maybe this is due to mysql-3.23.52 (i.e. not mysql-4)? Gaspar -- 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: Fixing "the worst InnoDB corruption bug in 3 years" - when
Bruce, 5.0.3 is the next in line. I am not sure whether 4.0.9 comes after that. You should consider buying a MySQL support contract. MySQL Gold and Platinum Premier Support customers are entitled to custom builds: http://www.mysql.com/support/premier.html Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Bruce Dembecki" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, December 30, 2004 9:51 AM Subject: Re: Fixing "the worst InnoDB corruption bug in 3 years" - when Thanks Heikki, I understand the bug, and I know you fixed it, for which I a= m very pleased, as always problems when identified are fixed quickly :-) I guess the question I was trying to ask of MySQL is when will 4.1.9 be built... It's very frustrating knowing that a problem has been fixed (particularly a serious problem such as this) but having to sit on our hand= s and wait perhaps two months for a MySQL "Official" Binary to be built. Heikki you have definitely done your part in finding and fixing this thing rapidly. I understand MySQL doesn't want to make new releases every week, they need to set some guidance for their users that their releases will not be made too frequently. On the other hand this isn't something that is a bi= t annoying, or some queries don't work... This corrupts the database. We can't grab a snapshot and compile our own version (well we could), because if we do it won't be a "MySQL Official Binary" and if we have problems with the database our clients wouldn't understand why we weren't using a MySQL sanctioned version. On the other hand if we do use the Official binary, our data will become corrupt. The problem I am experiencing is not the delay in fixing the problem, but the delay in releasing the fix. The two month between releases that seems common at the moment isn't unreasonable in most cases, except where there's a corrupting bug uncovered and fixed, right after a release - February is too long to wait for this fix to be included in an official binary. In my mind (as I am directly affected by this bug) this one is serious enough to release a new build asap. As a side note with demonstrated performance increases when using innodb_file_per_table why aren't more people using it? Best Regards, Bruce On 12/29/04 10:22 PM, Heikki Tuuri wrote: Bruce, =20 It is the bug "innodb_file_per_table corrupts secondary indexes". =20 I fixed it with several changesets on Sunday: =20 http://lists.mysql.com/internals =20 Thus, it is fixed in the current 4.1 bk tree. =20 This is indeed the worst InnoDB corruption bug since the BLOB update bug = of summer 2001. Fortunately, the bug affects few users, because not too man= y are running with innodb_file_per_table. =20 Regards, =20 Heikki =20 =20 On 12/28/04 2:38 PM, "Bruce Dembecki" <[EMAIL PROTECTED]> wrote: In the MySQL Manual under InnoDB in the "Using Per-Table Tablespace" sec= tion it says clearly at the top: =20 NOTE: CRITICAL BUG in 4.1 if you specify innodb_file_per_table in `my.cn= f'! If you shut down mysqld, then records may disappear from the secondary indexes of a table. See (Bug #7496) for more information and workaround= s. =20 Following the link to Bug 7496 (http://bugs.mysql.com/bug.php?id=3D7496) w= e are told two important things: =20 1. This is the worst InnoDB corruption bug in 3 years. 2. Will be fixed in 4.1.9. =20 So thanks to Heikki for finding and fixing this. So now to the question... =20 As a person in the process of migrating from 4.0 to 4.1 and having alrea= dy scheduled the downtime with my clients for this Friday morning, and havi= ng to do a full dump and import already as part of the migration process I'd l= ike to know WHEN the fix will be available. I don=B9t have a lot of opportuni= ties for a full dump and import, so this is a crucial time for me, and there = are some benefits with innodb_file_per_table that are important to us. =20 If we go with history then we should expect a new version of the current MySQL products every 2 months approximately. Having just received 4.1.8 = I'd not like to see MySQL leave InnoDB's worst corruption bug in three years= sit for two months when a fix has already been written. =20 Can we have a new build with this fix included please? When can we have = it? The "grab it from the nightly snapshots and compile it yourself" answer = won't cut it when we have to deploy into production and MySQL's company line i= s to only use MySQL official binaries in production. =20 If not 4.1.9 can we call it 4.1.8b and get it shipped (there's already a 4.1.8a). Best regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQ
Re: BIG InnoDB problems!
Joshua, please show what the FIRST InnoDB error in the .err log was. The first error is always the important thing to report. Please follow these instructions: 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 Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "jsf" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, December 31, 2004 12:26 AM Subject: BIG InnoDB problems! I've been struggling with this problem for the last few days. I've enlisted the help of some colleagues on the NYLUG (NY Linux User's Group) list but finally we figured this is the best place to look for some help. We have a server running SLES 9.0 (SuSE Linux Enterprise Server 9.0) and: mysqladmin Ver 8.41 Distrib 4.1.8a, for pc-linux-gnu on i686 There are 5 MySQL databases on the server. The smallest has 5 tables, the largest 14 tables. All the tables in all the databases are myISAM tables. There is ONE database on the server that we are trying to create/work with that is all InnoDB tables. We are having serious problems with these tables. There are indications in the error logfile regarding what to do to try and discover the root of these problems and fix them. I will begin pursuing those options shortly after posting this but as: 1) We're under a deadline with the application in question that requires the InnoDB tables and 2) Although I'm the most qualified person, from a technical standpoint, at my institution to try and get this fixed, that's not saying much as I'm not THAT deeply technical. I thought I'd risk posting some of the logfile here to see what the experts have to say. Please accept my apologies for just coming here and dumping this on the list's lap. I will try to figure it out myself but if anyone can help guide me towards a solution in the meantime I'd be much obliged. Many thanks in advance. Joshua Here is the output of 'tail -100' on the error logfile: --snip-- InnoDB: log sequence number 0 241346488. InnoDB: Doing recovery: scanned up to log sequence number 0 241346521 InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.52 041230 16:43:20 InnoDB: Flushing modified pages from the buffer pool... 041230 16:43:20 InnoDB: Started; log sequence number 0 241346521 InnoDB: !!! innodb_force_recovery is set to 5 !!! 041230 16:43:20 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 041230 16:43:20 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/local/libexec/mysqld: ready for connections. Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB: Error: trying to access page number 940269659 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 041230 16:46:01InnoDB: Assertion failure in thread 1123867568 in file fil0fil.c line 3729 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. 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=16777216 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x89441a8 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=0x42fcb1ac, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x815f0cf 0xe420 0x82e71d5 0x82e71d5 0x82db68f 0x830479f 0x8304cc8 0x82be800 0x82d14a6 0x82ccafb 0x82cd865 0x826232b 0x827915a 0x81fe924
Re: BIG InnoDB problems!
Thanks Eric, We already tried levels 1, 3 and 5... no soap. I'm on the verge of thinking it's a bug. :-( J. On Fri, 31 Dec 2004 00:06:28 +, Eric Bergen <[EMAIL PROTECTED]> wrote: > It looks like your tablespace is corrupt. Check this doc out: > http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html > > On Thu, 30 Dec 2004 17:21:40 -0500, jsf <[EMAIL PROTECTED]> wrote: > > I've been struggling with this problem for the last few days. I've > > enlisted the help of some colleagues on the NYLUG (NY Linux User's > > Group) list but finally we figured this is the best place to look for > > some help. > > > > We have a server running SLES 9.0 (SuSE Linux Enterprise Server 9.0) and: > > > > mysqladmin Ver 8.41 Distrib 4.1.8a, for pc-linux-gnu on i686 > > > > There are 5 MySQL databases on the server. The smallest has 5 tables, > > the largest 14 tables. All the tables in all the databases are myISAM > > tables. > > > > There is ONE database on the server that we are trying to create/work > > with that is all InnoDB tables. > > > > We are having serious problems with these tables. > > > > There are indications in the error logfile regarding what to do to try > > and discover the root of these problems and fix them. I will begin > > pursuing those options shortly after posting this but as: > > > > 1) We're under a deadline with the application in question that > > requires the InnoDB tables and > > > > 2) Although I'm the most qualified person, from a technical > > standpoint, at my institution to try and get this fixed, that's not > > saying much as I'm not THAT deeply technical. > > > > I thought I'd risk posting some of the logfile here to see what the > > experts have to say. Please accept my apologies for just coming here > > and dumping this on the list's lap. > > > > I will try to figure it out myself but if anyone can help guide me > > towards a solution in the meantime I'd be much obliged. > > > > Many thanks in advance. > > > > Joshua > > > > Here is the output of 'tail -100' on the error logfile: > > > > --snip-- > > > > InnoDB: log sequence number 0 241346488. > > InnoDB: Doing recovery: scanned up to log sequence number 0 241346521 > > InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.52 > > 041230 16:43:20 InnoDB: Flushing modified pages from the buffer pool... > > 041230 16:43:20 InnoDB: Started; log sequence number 0 241346521 > > InnoDB: !!! innodb_force_recovery is set to 5 !!! > > 041230 16:43:20 [Warning] mysql.user table is not updated to new > > password format; Disabling new password usage until > > mysql_fix_privilege_tables is run > > 041230 16:43:20 [Warning] Can't open and lock time zone table: Table > > 'mysql.time_zone_leap_second' doesn't exist trying to live without > > them > > /usr/local/libexec/mysqld: ready for connections. > > Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source > > distribution > > InnoDB: Error: trying to access page number 940269659 in space 0, > > InnoDB: space name ./ibdata1, > > InnoDB: which is outside the tablespace bounds. > > InnoDB: Byte offset 0, len 16384, i/o type 10 > > 041230 16:46:01InnoDB: Assertion failure in thread 1123867568 in file > > fil0fil.c line 3729 > > InnoDB: We intentionally generate a memory trap. > > InnoDB: Submit a detailed bug report to http://bugs.mysql.com. > > InnoDB: If you get repeated assertion failures or crashes, even > > InnoDB: immediately after the mysqld startup, there may be > > InnoDB: corruption in the InnoDB tablespace. Please refer to > > InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html > > InnoDB: about forcing recovery. > > 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=16777216 > > read_buffer_size=131072 > > max_used_connections=2 > > max_connections=100 > > threads_connected=1 > > It is possible that mysqld could use up to > > key_buffer_size + (read_buffer_size + > > sort_buffer_size)*max_connections = 80383 K > > bytes of memory > > Hope that's ok; if not, decrease some variables in the equation. > > > > thd=0x89441a8 > > 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=0x42fcb1ac, backtrace may not be correct. > > Stack range sanity check OK, backtrace follows: > > 0x815f0cf > > 0xe420 > > 0x82e71d5 > > 0x82e71d5 > > 0x82db68f > > 0x830479f > > 0x8304cc8 > > 0x82be800 > > 0x82d14a6 > > 0x82ccafb > > 0x82cd865 > > 0x826232b > > 0x827915a > > 0x8
RE: move column position
Hi, RE: > I believe this is what you are looking for: > ALTER TABLE Images MODIFY Imcreatedat datetime NOT NULL default '-00-00 > 00:00:00' AFTER Imccdtemp; Thanks, Tom for the clue. Yes, this is what I tried, but it did not work. Seems like modify does not accept the "AFTER" part. I tried it again, and the same failure: ERROR 1064: You have an error in your SQL syntax Maybe this is due to mysql-3.23.52 (i.e. not mysql-4)? Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [PHP] How to argue with ASP people...
Sorry, wrong list!
-Mensagem original-
De: mail.pmpa [mailto:[EMAIL PROTECTED]
Don't mean to start a discussion whatsoever, I love php, but one thing i
can't do in php is Response.Redirect("page.asp") .
Apart from that no complains so far :)
* Happy New Year *
Pedro Almeida.
-Mensagem original-
De: Tony Di Croce [mailto:[EMAIL PROTECTED]
I am fairly new to PHP, but I am loving it... I have recently gotten
involved in a business venture and I have been using PHP so far...
Recently I have taken on a partner, and he is a big ASP guy...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [PHP] How to argue with ASP people...
Don't mean to start a discussion whatsoever, I love php, but one thing i
can't do in php is Response.Redirect("page.asp") .
Apart from that no complains so far :)
* Happy New Year *
Pedro Almeida.
-Mensagem original-
De: Tony Di Croce [mailto:[EMAIL PROTECTED]
I am fairly new to PHP, but I am loving it... I have recently gotten
involved in a business venture and I have been using PHP so far...
Recently I have taken on a partner, and he is a big ASP guy...
I am not totally against ASP, but it would have to be pretty good to
get me to switch at this point (PHP seems to do everything I need)...
But I will need to convince him of this...
What points can I bring up in PHP's favor? In what areas does PHP trounce
ASP?
--
td
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reccommend host w/recent MYQL version
Having a hard time finding a server/host who supports MYSQL 4.1.3 or above. Does anyone know and can recommend a host who offers this? Thanks, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: export from older version
If you can use mysqldump to export the data from 3.23 with the -Q switch to quote all db, table, and column names. On Thu, 30 Dec 2004 11:31:42 -0800, Don Stefani <[EMAIL PROTECTED]> wrote: > Hello, > > I am trying to import a table structure from ver 3.23.52 to ver > 4.1.7-max and I am getting an error: > > --- error - > > |#1064 - You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use > near 'wish_list_id` int(6) NOT NULL auto_increment, > `Client_Name` varchar(255) defau' at line 2 > > - end > > I am using phpMyAdmin to do the export, and I have tried using it and > mysql> source to run the file but I get the same error from both. > > You help would be appreciated. > > Thanks, > > - dstefani > | > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BIG InnoDB problems!
It looks like your tablespace is corrupt. Check this doc out: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html On Thu, 30 Dec 2004 17:21:40 -0500, jsf <[EMAIL PROTECTED]> wrote: > I've been struggling with this problem for the last few days. I've > enlisted the help of some colleagues on the NYLUG (NY Linux User's > Group) list but finally we figured this is the best place to look for > some help. > > We have a server running SLES 9.0 (SuSE Linux Enterprise Server 9.0) and: > > mysqladmin Ver 8.41 Distrib 4.1.8a, for pc-linux-gnu on i686 > > There are 5 MySQL databases on the server. The smallest has 5 tables, > the largest 14 tables. All the tables in all the databases are myISAM > tables. > > There is ONE database on the server that we are trying to create/work > with that is all InnoDB tables. > > We are having serious problems with these tables. > > There are indications in the error logfile regarding what to do to try > and discover the root of these problems and fix them. I will begin > pursuing those options shortly after posting this but as: > > 1) We're under a deadline with the application in question that > requires the InnoDB tables and > > 2) Although I'm the most qualified person, from a technical > standpoint, at my institution to try and get this fixed, that's not > saying much as I'm not THAT deeply technical. > > I thought I'd risk posting some of the logfile here to see what the > experts have to say. Please accept my apologies for just coming here > and dumping this on the list's lap. > > I will try to figure it out myself but if anyone can help guide me > towards a solution in the meantime I'd be much obliged. > > Many thanks in advance. > > Joshua > > Here is the output of 'tail -100' on the error logfile: > > --snip-- > > InnoDB: log sequence number 0 241346488. > InnoDB: Doing recovery: scanned up to log sequence number 0 241346521 > InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.52 > 041230 16:43:20 InnoDB: Flushing modified pages from the buffer pool... > 041230 16:43:20 InnoDB: Started; log sequence number 0 241346521 > InnoDB: !!! innodb_force_recovery is set to 5 !!! > 041230 16:43:20 [Warning] mysql.user table is not updated to new > password format; Disabling new password usage until > mysql_fix_privilege_tables is run > 041230 16:43:20 [Warning] Can't open and lock time zone table: Table > 'mysql.time_zone_leap_second' doesn't exist trying to live without > them > /usr/local/libexec/mysqld: ready for connections. > Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source > distribution > InnoDB: Error: trying to access page number 940269659 in space 0, > InnoDB: space name ./ibdata1, > InnoDB: which is outside the tablespace bounds. > InnoDB: Byte offset 0, len 16384, i/o type 10 > 041230 16:46:01InnoDB: Assertion failure in thread 1123867568 in file > fil0fil.c line 3729 > InnoDB: We intentionally generate a memory trap. > InnoDB: Submit a detailed bug report to http://bugs.mysql.com. > InnoDB: If you get repeated assertion failures or crashes, even > InnoDB: immediately after the mysqld startup, there may be > InnoDB: corruption in the InnoDB tablespace. Please refer to > InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html > InnoDB: about forcing recovery. > 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=16777216 > read_buffer_size=131072 > max_used_connections=2 > max_connections=100 > threads_connected=1 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + > sort_buffer_size)*max_connections = 80383 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > thd=0x89441a8 > 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=0x42fcb1ac, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x815f0cf > 0xe420 > 0x82e71d5 > 0x82e71d5 > 0x82db68f > 0x830479f > 0x8304cc8 > 0x82be800 > 0x82d14a6 > 0x82ccafb > 0x82cd865 > 0x826232b > 0x827915a > 0x81fe924 > 0x81ef33c > 0x820aead > 0x820b19d > 0x8201554 > 0x8202739 > 0x81796cb > 0x817c1b4 > 0x817de5d > 0x817f137 > 0x401619ed > 0x403519ca > 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,
RE: Install fails
Hi Bernhard, Check the binary to see the path name it is requiring. ie. ldd /usr/local/libexec/mysqld, I'm running an earlier version (4.0.20) and not running max so I probably don't require the libCrun library, it is in my /usr/lib as well, but the output is : bash-2.05$ ldd /usr/local/libexec/mysqld librt.so.1 =>/usr/lib/librt.so.1 libdl.so.1 =>/usr/lib/libdl.so.1 libpthread.so.1 => /usr/lib/libpthread.so.1 libthread.so.1 =>/usr/lib/libthread.so.1 libz.so => /usr/lib/libz.so libcrypt_i.so.1 => /usr/lib/libcrypt_i.so.1 libgen.so.1 => /usr/lib/libgen.so.1 libsocket.so.1 =>/usr/lib/libsocket.so.1 libnsl.so.1 => /usr/lib/libnsl.so.1 libstdc++.so.6 =>/usr/local/lib/libstdc++.so.6 libm.so.1 => /usr/lib/libm.so.1 libgcc_s.so.1 => /usr/local/lib/libgcc_s.so.1 libc.so.1 => /usr/lib/libc.so.1 libaio.so.1 => /usr/lib/libaio.so.1 libmd5.so.1 => /usr/lib/libmd5.so.1 libmp.so.2 =>/usr/lib/libmp.so.2 bash-2.05$ uname -a SunOS porkchop 5.9 Generic_112234-12 i86pc i386 i86pc It may be the version that has been built is looking in the wrong place. This will tell you straight away and you may have to set the linkage run paths using the crle command. Run crle and see if the path (for libCrun.so.1) is there. If not, then add it. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Bernhard v. Fromberg [mailto:[EMAIL PROTECTED] Sent: Friday, 31 December 2004 12:37 AM To: mysql@lists.mysql.com Subject: Install fails Hello, I tried to install mysql-max-4.1.8-pc-solaris2.9-i386 on v20z with Solaris 9 04/04. I get the following error message: ld.so.1: ./bin/mysqld: fatal: libCrun.so.1: version `SUNW_1.4' not found (required by file ./bin/mysqld) ld.so.1: ./bin/mysqld: fatal: libCrun.so.1: open failed: No such file or directory The strange thing is. libCrun.so.1 is in /usr/lib. I have no idea what can be wrong: Here is the output of what /usr/lib/libCrun.so.1 /usr/lib/libCrun.so.1: release.cc 1.2 01/08/04 SMI crti.s 1.16 01/11/29 SMI CCrti.s 1.7 01/11/29 SMI thread.h signal.h feature_tests.h isa_defs.h unistd.h siginfo.h types.h machtypes.h int_types.h select.h time.h time.h time_iso_SUNWCC.h time_impl.h machsig.h faultcode.h synch.h machlock.h synch.h stddef.h stddef_iso_SUNWCC.h stdlib.h stdlib_iso_SUNWCC.h nl_types.h stdio.h stdio_iso_SUNWCC.h va_list.h stdio_tag.h stdio_impl.h string.h string_iso_SUNWCC.h assert.h unistd.h limits.h int_limits.h signal.h signal_iso_SUNWCC.h procset.h RELEASE VERSION Sun C++ 5.5 Patch 111713-05 2003/12/19 CCrtn.s 1.6 01/11/06 SMI crtn.s 1.10 01/08/07 SMI TIA and a happy new year. Bernhard Fromberg -- Mit freundlichen Grüssen, Bernhard Fromberg -- 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: Multiple MySQLs on Mandrake 9.x?
Rhino wrote: Our copy of MySQL (4.0.15'-Max') is on a Linux Mandrake 9.x server. We installed it from an RPM (actually an MDK, which is functionally the same thing) and are running MySQL as a service. Would it be possible to run a second copy of MySQL, ideally the latest 4.1.x, on our server in parallel with the 4.0.15 instance so that I could get a look at 4.1.x to see if it can do what I want it to do? If it is possible, how do I set the two different versions up so that both could run simultaneously and independently? Also, if it is possible to run two instances concurrently, what is the safest way of migrating my databases to 4.1.x when I am satisfied that I want to do so? Or would I be better to simply upgrade my 4.0.15 to the latest available version of 4.1.x? Rhino: It would be easier to upgrade to 4.1, although it would be possible to run two instances side by side. Just install 4.1 under a different root (e.g /opt/mysql-4.1), and make sure to fix up mysqld_safe from 4.1 to read the correct my.cnf 4.1 should be a fairly safe bet at this point if you mostly use the features that are from 4.0. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install fails
Bernhard v. Fromberg wrote: Hello, I tried to install mysql-max-4.1.8-pc-solaris2.9-i386 on v20z with Solaris 9 04/04. I get the following error message: ld.so.1: ./bin/mysqld: fatal: libCrun.so.1: version `SUNW_1.4' not found (required by file ./bin/mysqld) ld.so.1: ./bin/mysqld: fatal: libCrun.so.1: open failed: No such file or directory Bernhard: Disclaimer - I am not a Solaris expert, so my solution might not be the best. But if I were in your shoes, I would first try to build from source on that system. The problem is that the binary you are trying to use is linked against incompatible libraries. Another option is dig deep into Solaris and try to figure out the SUNW_1.4 mystery. Unfortunately, in the end the answer is still very likely to be that you just need to build your own binary. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Complicated SELECT query
shaun thornburgh wrote: Hi, The following query returns the amount of hours a user has been booked for on a particular day. SELECT (SUM(((DATE_FORMAT(B.Booking_End_Date, "%k") * 60 ) + DATE_FORMAT(B.Booking_End_Date, "%i")) - ((DATE_FORMAT(B.Booking_Start_Date, "%k") * 60 ) + DATE_FORMAT(B.Booking_Start_Date, "%i"))) / 60 ) AS Booked_Hours FROM Bookings B, Projects P WHERE B.User_ID = 610 AND B.Booking_Type = "Booking" AND P.Project_ID = 2 AND B.Project_ID = P.Project_ID AND NOT ( "2005-01-10" < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR "2005-01-10" > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Bookings are related to table Projects by Project_ID and to table Users by User_ID. I need to update this query to cater for another type of booking. The Booking_Type = "Task", and the booking is related to Table Tasks by Task_ID. Each Project will have many Tasks and this is how the booking indirectly relates to a project. Therefore my question is how can i check how many hours a user has been booked for on a particular date relating to a particular project whatever the type of the booking is? If I am checking for a 'Booking' I need to check that B.Project_ID = P.Project_ID and if I am checking for a 'Task' I need to check that AND B.Task_ID = T.Task_ID AND T.Project_ID = P.Project_ID. Shaun: I must admit I spent only 5 minutes studying your problem, so it is very likely that I might have overlooked something. However, at this point it appears to me that you should just use two separate queries and a temporary table (or just post-process in the application) to get the results you need. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQLD and permissions
Russ wrote: I just set up mysql and found it running as root. I want to change it to run as mysql. I know I need an entry under [mysqld] in my.cnf. I also know the db directories and files need peermission changes but I'm confused on that. I use SuSE 9.1 and the datadir is: /var/lib/mysql> ls -l total 13 drwxrwxr-x 2 root mysql 528 2004-12-28 09:49 mysql -rw-rw 1 mysql root 725 2004-12-29 08:36 mysqld.log -rw-r--r-- 1 root root 340 2004-12-28 15:45 mysqld.log-20041228.gz -rw-rw 1 mysql daemon 5 2004-12-29 08:36 mysqld.pid srwxrwxrwx 1 mysql daemon 0 2004-12-29 08:36 mysql.sock drwxrwxr-x 2 root mysql 48 2004-12-28 09:49 test Russ: chown -R mysql.mysql /var/lib/mysql add user=mysql in /etc/my.cnf that should be enough, if for odd some reason it is not, read the error log, it will tell you what else needs to be fixed. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird server timeout problem
Dale Blount wrote: Hello, I've been fighting this problem for a while, and now it's time to ask the experts. Please also CC me on this, I'm only on the list as a digest. Dale: Enable the log-long-format and log-slow-queries on your server, and see if you have some hogs that lock your table for a long time once in a while. This would explain the results you are observing. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to speed up ANALYZE TABLE?
Kevin A. Burton wrote: The ANALYZE TABLE entry in the manual doesn't say anything about performance optimization. How do I get optimum performance out of ANALYZE TABLE? Can I just set the same variables as I would with REPAIR TABLE? key_buffer_size sort_buffer_size myisam_sort_buffer_size Also... Does A.T. always need to read ALL rows from the table? I assume so. Also... why does it require *any* type of lock? For large tables the chance that data would be modified during the ANALYZE to cause the results to be wildly innacurate are rare. It seems possible to just let ANALYZE run without any locks and just let the data be off by a few K rows. Which seems MUCH better than NEVER running them. Kevin: ANALYZE table does not do any sorting, so the setting of myisam_sort_buffer_size should not affect its performance. However, since it has to traverse the B-trees of the keys, a large key_buffer_size should be helpful (although it depends on how well the OS caches the data). The lock is needed not only to ensure the consistency of the results, but also to keep the server from crashing. If some other thread is in the middle of modifying the key structure, some pointers in the data structures could be invalid, and cause the ANALYZE thread to crash (and take the whole server down with it). It should be possible to modify the server to ANALYZE a key at a time, or even a key range at a time, but I do not anticipate it happening in the near future. If the optimizer is making bad choices because of incorrect key statistics, you can instruct it to use the correct key with FORCE INDEX -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
Spenser, the bug report was a direct hit in the sense that it spoke about the problem I am having, but it was actually wrong and the suggested FLUSH TABLES workaround did not work. On my servers (4.0.13-nt running on Windows 2000 Pro) FLUSH TABLES had no effect at all on the Update_time. I quit trying to use Update_time to track replication status. Now I do the following: SHOW MASTER STATUS on the master and record the binlog file name and position. SHOW SLAVE STATUS on the slave and record the Master_log_file and Exec_master_log_position. If these match, then I assume replication is up to date. Is this an okay assumption? BTW, I am aware that starting with 4.1.1 there is a "Seconds_behind_master" field that could be helpful, but our medical application currently only supports up to MySQL 4.0.18. -- Eric Robinson -Original Message- From: Spenser [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 29, 2004 6:23 PM To: Robinson, Eric Cc: Mikael Fridh; mysql@lists.mysql.com Subject: RE: SHOW TABLE STATUS: Update_Time Is Wrong? Eric, I'm glad that last answer worked, but I'm wondering what exactly you did to resolve the problem? I see the bug report and work around. But what specifically did you do, what did you type to fix it? By the way, what operating system are you using for your servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BIG InnoDB problems!
I've been struggling with this problem for the last few days. I've enlisted the help of some colleagues on the NYLUG (NY Linux User's Group) list but finally we figured this is the best place to look for some help. We have a server running SLES 9.0 (SuSE Linux Enterprise Server 9.0) and: mysqladmin Ver 8.41 Distrib 4.1.8a, for pc-linux-gnu on i686 There are 5 MySQL databases on the server. The smallest has 5 tables, the largest 14 tables. All the tables in all the databases are myISAM tables. There is ONE database on the server that we are trying to create/work with that is all InnoDB tables. We are having serious problems with these tables. There are indications in the error logfile regarding what to do to try and discover the root of these problems and fix them. I will begin pursuing those options shortly after posting this but as: 1) We're under a deadline with the application in question that requires the InnoDB tables and 2) Although I'm the most qualified person, from a technical standpoint, at my institution to try and get this fixed, that's not saying much as I'm not THAT deeply technical. I thought I'd risk posting some of the logfile here to see what the experts have to say. Please accept my apologies for just coming here and dumping this on the list's lap. I will try to figure it out myself but if anyone can help guide me towards a solution in the meantime I'd be much obliged. Many thanks in advance. Joshua Here is the output of 'tail -100' on the error logfile: --snip-- InnoDB: log sequence number 0 241346488. InnoDB: Doing recovery: scanned up to log sequence number 0 241346521 InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.52 041230 16:43:20 InnoDB: Flushing modified pages from the buffer pool... 041230 16:43:20 InnoDB: Started; log sequence number 0 241346521 InnoDB: !!! innodb_force_recovery is set to 5 !!! 041230 16:43:20 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 041230 16:43:20 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/local/libexec/mysqld: ready for connections. Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB: Error: trying to access page number 940269659 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 041230 16:46:01InnoDB: Assertion failure in thread 1123867568 in file fil0fil.c line 3729 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. 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=16777216 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x89441a8 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=0x42fcb1ac, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x815f0cf 0xe420 0x82e71d5 0x82e71d5 0x82db68f 0x830479f 0x8304cc8 0x82be800 0x82d14a6 0x82ccafb 0x82cd865 0x826232b 0x827915a 0x81fe924 0x81ef33c 0x820aead 0x820b19d 0x8201554 0x8202739 0x81796cb 0x817c1b4 0x817de5d 0x817f137 0x401619ed 0x403519ca 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 0x8951778 = DROP DATABASE `josh_Test` thd->thread_id=5 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. Number of processes running now: 0 041230 16:46:01 mysqld restarted 041230 16:46:01 InnoDB: Database was not shut
RE: Multi table update OT
[snip] ...tons o' stuff... [/snip] BTW...Happy New Year! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] > Anyhow, it has been a good discussion. No bugs were uprooted and > everyone's understanding may have risen a notch. After a couple of > decades using SQL I still find interesting topics to discuss. Yes, I too have enjoyed this conversation. Thank you for thinking out loud with me on such an important topic. I have one last pitch to make, though, so I don't think you are quite off the hook yet. ;-) I went looking and found a nearly finished copy of the SQL-2003 spec (but not the official one) at http://www.wiscorp.com/sql/sql_2003_standard.zip (subfile: 5WD-02-Foundation-2003-09.pdf). I can't imagine the final draft changing too much from what I quote below. [/snip] Most manufacturers are using ANSI SQL 99 IIRC (ymmv) which is available from ANSI (http://www.ansi.org) for a small fee. It generally takes a few years for standards to become implemented by mfgs. I think that you may be confusing search conditionals and equation operations too... WHERE foo <= bar is a search condition IF(t1.foo=t2.bar, 2, 1) is an equation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 03:50:24 PM: > [snip] > ... a bunch of stuff > [/snip] > > It alomost sounds as if you're coming from a M$ campwhat you > describe are VIEWS. In a way it is, an internally generated view of the source/destination tables. > > Anyhow, the ANSI SQL standards specify some of the behaviors we have > been talking about. Many DB manufacturers use the standards and > implement other "features" like you describe that fall outside of the > standards. Can you send me a link to those, I would like to read them so that I can stay up to date. I was able to find a version of SQL-2003 (see below), which one are you thinking of? > > I will repeat that with regards to the actions that you describe > concerning variables that you would be looking for something called a > CONSTANT. Variables are just that...variable. > And they will change their value to match the last update applied to it. But they don't change their values (for calculation purposes) until the engine finishes the row. > Anyhow, it has been a good discussion. No bugs were uprooted and > everyone's understanding may have risen a notch. After a couple of > decades using SQL I still find interesting topics to discuss. Yes, I too have enjoyed this conversation. Thank you for thinking out loud with me on such an important topic. I have one last pitch to make, though, so I don't think you are quite off the hook yet. ;-) I went looking and found a nearly finished copy of the SQL-2003 spec (but not the official one) at http://www.wiscorp.com/sql/sql_2003_standard.zip (subfile: 5WD-02-Foundation-2003-09.pdf). I can't imagine the final draft changing too much from what I quote below. (book page: 849, PDF page 873) * begin excerpts *** ISO/IEC 9075-2:2003 (E) 14.11 5) Case: a) If contains ONLY, then Case: i) If a is not specified, then all rows of T for which there is no subrow in a proper subtable of T are the subject rows. ii) If a is specified, then it is applied to each row of T with the exposed s or s of the bound to that row, and the subject rows are those rows for which the result of the is True and for which there is no subrow in a proper subtable of T. The is effectively evaluated for each row of T before updating any row of T. Each in the is effectively executed for each row of T and the results used in the application of the to the given row of T. If any executed contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T. b) Otherwise, Case: i) If a is not specified, then all rows of T are the subject rows. ii) If a is specified, then it is applied to each row of T with the exposed of the bound to that row, and the subject rows are those rows for which the result of the is True. The is effectively evaluated for each row of T before any row of T is updated. Each in the is effectively executed for each row of T and the results used in the application of the to the given row of T. If any executed contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T. NOTE 389 â outer reference is defined in Subclause 6.7, ââ. 6) If T is a base table, then each subject row is also an object row; otherwise, an object row is any row of a leaf generally underlying table of T from which a subject row is derived. 7) Equivalent s shall not appear more than once in a . 9) The of each is effectively evaluated for each row of T before any row of T is updated. 10) For each subject row, a candidate new row is constructed by copying the subject row and updating it as specified by each by applying the General Rules of Subclause 14.12, ââ. ** end excerpts *** If I interpret the above excerpted section and the other (referenced) sections appropriately (which I may not be, after such a quick read) it seems as though the SQL engine is supposed to determine the (which may be composed of multiple tables, views, and/or subqueries) then apply changes to each row of the by starting from a copy of the subject row and transforming it according to the then replacing that row in the with the results of the transformations. To be even more succinct: Start from a snapshot of all initial values and construct a new row based on values from the snapshot. (much as each generation of a "cellular automata"-baed simulation is computed). By my interpretation of these rules, I believe that MySQL fails to follow specification as Kai's sample data demonstrates. The failure is because changed data is immediately available as source data even though all of the row's transformations are not complete. The transformation to be applied to any column is not insulated from the results of the transformations applied to any of columns listed before it in the . However, I
Encodings
How should I handle encodings and output to web pages? I'm inserting some POST variables from a PHP script directly into a MySQL database. (I have magic quotes turned on, so quotes already taken care of.) There's the possibility that there could be some UTF-8 bytes in the POST variable strings. (This PHP script is called by a desktop app, so I can guarantee the encoding is UTF.) What should I do when retrieving the data from the database and outputting it to a PHP page? For example, how do I convert é to é (or whatever is appropriate)? Adam Ernst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] ... a bunch of stuff [/snip] It alomost sounds as if you're coming from a M$ campwhat you describe are VIEWS. Anyhow, the ANSI SQL standards specify some of the behaviors we have been talking about. Many DB manufacturers use the standards and implement other "features" like you describe that fall outside of the standards. I will repeat that with regards to the actions that you describe concerning variables that you would be looking for something called a CONSTANT. Variables are just that...variable. Anyhow, it has been a good discussion. No bugs were uprooted and everyone's understanding may have risen a notch. After a couple of decades using SQL I still find interesting topics to discuss. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with SELECT
- Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "mysql" Sent: Thursday, December 30, 2004 9:03 AM Subject: Need help with SELECT > I am trying to think of SQL that will let me show a one to many relationship > as a single row in a result set. They say a picture is worth a thousand > words so let me draw that picture. > > Event_IDEvent_Nameetc. > 1 Concert > 2 Art Exhibit > 3 Spelling Bee > [Primary key: Event_ID] > > > Event_IDFile_NumberFile > 1 1promoter_logo.jpg > 1 2venue_logo.jpg > 1 3performer_graphic.jpg > 2 1artist_graphic.jpg > [Primary Key: Event_ID, File_Number] > > > Desired Result: > Event_IDEvent_NameFileFile > File > 1 Concert promoter_logo.jpg venue_logo.jpg > performer_graphic.jpg > 2 Art Exhibitartist_graphic.jpg > 3 Spelling Bee > > > There is one record in the Events table for each event that an organization > is promoting. For each event, there could be 0 to 'n' files that give more > information about the event, such as pictures, audio clips, or whatever. I > want to display all the information about the event, including all of the > file names for that event, on a single result set row for each event, > regardless of how many files exist for that event. [At the moment, there is > a limit of 3 files per event but that could increase.] Here are two ways, but both are kind of hoaky. They both assume you know the number of files you want to display. Method 1 - with subqueries SELECT DISTINCT EventName, (SELECT f1.FileName from EventFiles as f1 where f1.EventID = e.EventID and f1.FileID = 1), (SELECT f2.FileName as File2 from EventFiles as f2 where f2.EventID = e.EventID and f2.FileID =2), (SELECT f3.FileName as File3 from EventFiles as f3 where f3.EventID = e.EventID and f3.fileID =3) FROM Events e Method 2 - without subqueries CREATE TEMPORARY TABLE FileOrder( EventID tinyint, EventName char(20), File1 varchar(35), File2 varchar(35), File3 varchar(35)); INSERT INTO FileOrder (select Events.EventID,EventName, FileName,'None','None' FROM EVENTS INNER JOIN EventFiles on EventFiles.EventID = Events.EventID Where FileID = 1) UPDATE FileOrder SET File2 = (SELECT DISTINCT FileName FROM EventFiles,Events WHERE EventFiles.FileID = 2 AND EventFiles.EventID = FileOrder.EventID); UPDATE FileOrder SET File3 = (SELECT DISTINCT FileName FROM EventFiles,Events WHERE EventFiles.FileID = 3 AND EventFiles.EventID = FileOrder.EventID); This approach won't give you a row for Event 3 since it doesn't exist in EventFiles. I'm sure this can be solved with a little work. Sorry I didn't you the same column names as you did, but I was in a hurry. A report writer or even ACCESS makes these cross-tab reports trivial. Regards Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
follow-up: export from older version
Don Stefani wrote: Hello, I am trying to import a table structure from ver 3.23.52 to ver 4.1.7-max and I am getting an error: --- error - |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wish_list_id` int(6) NOT NULL auto_increment, `Client_Name` varchar(255) defau' at line 2 - end I am using phpMyAdmin to do the export, and I have tried using it and mysql> source to run the file but I get the same error from both. You help would be appreciated. Thanks, - dstefani | Here is part of the sql file where is seems to break: [...] CREATE TABLE `wish_list` ( `wish_list_id` int(6) NOT NULL auto_increment, `Client_Name` varchar(255) default NULL, `date` date NOT NULL default '-00-00', `Full_Name` varchar(200) default NULL, `Email` varchar(255) default NULL, [...] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
01:32:20 PM:
> [snip]
> I understand that is the behavior of the assignment operator. That's why
> I used it. ;-) I expected the results to be 19 and 6 not 19 and 20.
> This is just another example of the serialization of evaluation that
> occurs while processing a single row of data. I have the opinion that
> while processing that row, the variable @testvalue should keep its
> original value and only be updated when moving to the next
> row(statement).
> [/snip]
>
> This has nothing to do with serialization, you have reassigned the
> variable value, and like any other programming language the new value is
> valid until it is reset by something else, so any subsequent operation
> will use that value. I believ that what you would like here is a
> CONSTANT
>
> [snip]
> If evaluation is serialized in a SELECT statement based on the order in
> which they are listed, then why aren't they evaluated in that order
> during the processing of a SET clause? Why does the query engine seem to
> revert to processing changes in "table column order" and not maintain
> the "statement sequence order" as specified by the user? That could
> imply that I need to change the design of my tables so that my update
> statements will execute in the correct order. If only the changes had
> happened in the sequence specified in Kai's original SET statement...
> (sigh) 8-D
> [/snip]
>
> It is because you are defining the order of the update in the UPDATE
> statement itself;
>
> UPDATE t2, t1, t0
>
> You have now specified the order of the update regardless of the actual
> updates that occur in the SET statements. SETs do not define order, even
> in single table/multiple column updates. If I do
I don't visualize it that way. I see the UPDATE ... JOIN ... portion of
the statement as defining an internally-constructed virtual table that
consists of all of the columns of each table participating in this
particular statement. This table is "populated with records" (mapped to
the actual data records) according to the inclusiveness (inner or outer)
and direction (left or right) of the JOIN clause and according to all of
the conditions established in the ON clause(s). The effect is, there is a
virtual table that becomes the target/source of the UPDATE or SELECT
statement being executed.
Now, each row of the virtual table must also be evaluated according to the
conditions specified in the WHERE clause (optimizations do exist that
shortcut out many row-by-row comparisons). For those rows that made it to
the table (from the JOINS) and pass the WHERE conditions, the column
transformations (formulas, simple reads, or assignments) specified in the
SET/SELECT clause are applied and those changes are either cached (until
the transaction commits or gets rolled back) or are committed to disk on a
row-by-row basis until we either run out of rows in the virtual table or
encounter an exception.
I think that where we truly disagree is about the order in which the
column transformations should be made. I say that each transformation
performed during the processing of a row should be based on the values
present when the processing of that row starts and performed in the order
specified in the SET/SELECT clause. If I understand you correctly, you are
saying that the transformations must be performed in the column order of
the virtual table and not in the sequence specified in the statement being
executed.
I also disagree with you that the column order of the virtual table should
have any effect on the final results. To use another variable-based
example (this is based on the principle that the value of an assignment
operation is the value that was assigned):
SET @Basevalue = 10;
SELECT @Basevalue:= @Basevalue + 10,@Basevalue:= @Basevalue +
10,@Basevalue:= @Basevalue + 10;
I expect the results {20,20,20} not {20,30,40} because for each formula,
@Basevalue (originally, on entry) is 10 not the results of the formula
that happened to be listed before it in the statement. If I repeated the
SELECT statement I would get {30,30,30} because on entry to that statement
@Basevalue had the initial value of 20. On exit, @Basevalue would be 30.
> SELECT t2.foo, t1.bar
>
> my results come out in the order specified in the SELECT statement, not
> the order in the FROM or subsequent condition statements...so the
> behavior really is no different between SELECT and UPDATE.
>
>
I agree, both SELECT and UPDATE (mis-)behave the same way.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: installing mysql / error
[snip] When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) [/snip] In that case, you may have a password set for root. To reset the password, shutdown the mysql server. Then start it from a command line using: [PATH TO MYSQL BINS]/mysqld-nt --skip-grant-tables Then open another command prompt and run 'mysql -u root' and run the following query: UPDATE mysql.user SET Password='' WHERE User='root' AND Host='localhost'; Then shutdown the server, and restart it normally. You should then be able to connect with 'mysql -u root'. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa office 712.328.4808 mobile 402.677.1592 -Original Message- From: David Katz [mailto:[EMAIL PROTECTED] Sent: Thursday, December 30, 2004 1:25 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: installing mysql / error When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) - Original Message - From: "Tom Crimmins" <[EMAIL PROTECTED]> To: "David Katz" <[EMAIL PROTECTED]> Cc: Sent: Thursday, December 30, 2004 1:57 PM Subject: RE: installing mysql / error > [snip] > We loaded a new server with Windows XP professional, and mysql server 4.1. > We can't get Mysql to run. After installing mysql and trying to execute it > we get the following message: > > ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using > password: NO) > [/snip] > > Ignore my previous post, I misunderstood. > > ODBC is the default account on a windows server. You need to use run 'mysql > -u root' from a command-line. You should then be able to grant privs to > [EMAIL PROTECTED] > > --- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
export from older version
Hello, I am trying to import a table structure from ver 3.23.52 to ver 4.1.7-max and I am getting an error: --- error - |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wish_list_id` int(6) NOT NULL auto_increment, `Client_Name` varchar(255) defau' at line 2 - end I am using phpMyAdmin to do the export, and I have tried using it and mysql> source to run the file but I get the same error from both. You help would be appreciated. Thanks, - dstefani | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installing mysql / error
When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) - Original Message - From: "Tom Crimmins" <[EMAIL PROTECTED]> To: "David Katz" <[EMAIL PROTECTED]> Cc: Sent: Thursday, December 30, 2004 1:57 PM Subject: RE: installing mysql / error > [snip] > We loaded a new server with Windows XP professional, and mysql server 4.1. > We can't get Mysql to run. After installing mysql and trying to execute it > we get the following message: > > ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using > password: NO) > [/snip] > > Ignore my previous post, I misunderstood. > > ODBC is the default account on a windows server. You need to use run 'mysql > -u root' from a command-line. You should then be able to grant privs to > [EMAIL PROTECTED] > > --- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa > > > > -- > 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: Fw: installing mysql / error
"David Katz" <[EMAIL PROTECTED]> wrote on 12/30/2004 01:32:41 PM: > I'm going to the dos window on the server and typing "mysql" to kick it off. > That's when I get the error. > OK, "mysql" is the client that comes with the server. What you attempted to do was to login as "nobody" and you said you weren't going to provide a password. What you should have typed is mysql -u root so that you attempt to connect as the mysql user "root". That is one of the two accounts set up by default when you install a new MySQL server. I think you have accomplished all of the setup steps down to here: http://dev.mysql.com/doc/mysql/en/Post-installation.html I especially think that you are ready for step 2.9.3 - Securing the Initial MySQL Accounts http://dev.mysql.com/doc/mysql/en/Default_privileges.html Try it out and let me know if you need more information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine > > - Original Message - > From: <[EMAIL PROTECTED]> > To: "David Katz" <[EMAIL PROTECTED]> > Cc: > Sent: Thursday, December 30, 2004 1:13 PM > Subject: Re: Fw: installing mysql / error > > > > "David Katz" <[EMAIL PROTECTED]> wrote on 12/30/2004 12:34:31 PM: > > > > > > > > > > > We loaded a new server with Windows XP professional, and mysql > > > server 4.1. We can't get Mysql to run. After installing mysql and > > > trying to execute it we get the following message: > > > > > > ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' > > > (using password: NO) > > > > > > Any ideas on what we can try and do? > > > > > > Thanks > > > David. > > > > Your server IS running or you wouldn't have that error, you would have > > seen something else. You left out a critical detail from your original > > post: > > > > What are you doing when you get that error? > > > > If you are attempting a connection through an ODBC driver, you need to be > > aware that even the most up-to-date driver acts like a pre-4.1 client. > > That means you may need to follow one or more of the suggestions > > documented here: > > > > http://dev.mysql.com/doc/mysql/en/Problems.html > > especially read A.2.3 : > > http://dev.mysql.com/doc/mysql/en/Old_client.html > > > > Let me know if I am on the right track, OK? > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
RE: installing mysql / error
[snip] We loaded a new server with Windows XP professional, and mysql server 4.1. We can't get Mysql to run. After installing mysql and trying to execute it we get the following message: ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using password: NO) [/snip] Ignore my previous post, I misunderstood. ODBC is the default account on a windows server. You need to use run 'mysql -u root' from a command-line. You should then be able to grant privs to [EMAIL PROTECTED] --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: installing mysql / error
[snip] We loaded a new server with Windows XP professional, and mysql server 4.1. We can't get Mysql to run. After installing mysql and trying to execute it we get the following message: ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using password: NO) [/snip] Try starting mysqld-nt with the --skip-grant-tables option. Then attempt to login from the command line. If this works, the privileges for the ODBC account are wrong or there is a password set. You can fix the privileges or reset the password once you are logged in. Then you can restart the server normally. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] I understand that is the behavior of the assignment operator. That's why I used it. ;-) I expected the results to be 19 and 6 not 19 and 20. This is just another example of the serialization of evaluation that occurs while processing a single row of data. I have the opinion that while processing that row, the variable @testvalue should keep its original value and only be updated when moving to the next row(statement). [/snip] This has nothing to do with serialization, you have reassigned the variable value, and like any other programming language the new value is valid until it is reset by something else, so any subsequent operation will use that value. I believ that what you would like here is a CONSTANT [snip] If evaluation is serialized in a SELECT statement based on the order in which they are listed, then why aren't they evaluated in that order during the processing of a SET clause? Why does the query engine seem to revert to processing changes in "table column order" and not maintain the "statement sequence order" as specified by the user? That could imply that I need to change the design of my tables so that my update statements will execute in the correct order. If only the changes had happened in the sequence specified in Kai's original SET statement... (sigh) 8-D [/snip] It is because you are defining the order of the update in the UPDATE statement itself; UPDATE t2, t1, t0 You have now specified the order of the update regardless of the actual updates that occur in the SET statements. SETs do not define order, even in single table/multiple column updates. If I do SELECT t2.foo, t1.bar my results come out in the order specified in the SELECT statement, not the order in the FROM or subsequent condition statements...so the behavior really is no different between SELECT and UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: installing mysql / error
I'm going to the dos window on the server and typing "mysql" to kick it off. That's when I get the error. - Original Message - From: <[EMAIL PROTECTED]> To: "David Katz" <[EMAIL PROTECTED]> Cc: Sent: Thursday, December 30, 2004 1:13 PM Subject: Re: Fw: installing mysql / error > "David Katz" <[EMAIL PROTECTED]> wrote on 12/30/2004 12:34:31 PM: > > > > > > > We loaded a new server with Windows XP professional, and mysql > > server 4.1. We can't get Mysql to run. After installing mysql and > > trying to execute it we get the following message: > > > > ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' > > (using password: NO) > > > > Any ideas on what we can try and do? > > > > Thanks > > David. > > Your server IS running or you wouldn't have that error, you would have > seen something else. You left out a critical detail from your original > post: > > What are you doing when you get that error? > > If you are attempting a connection through an ODBC driver, you need to be > aware that even the most up-to-date driver acts like a pre-4.1 client. > That means you may need to follow one or more of the suggestions > documented here: > > http://dev.mysql.com/doc/mysql/en/Problems.html > especially read A.2.3 : > http://dev.mysql.com/doc/mysql/en/Old_client.html > > Let me know if I am on the right track, OK? > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] So the question is not, if a multi table update should be implemented serialized. The answer is clear: yes it should. The question is, on which data the subsequent updates should work. [/snip] It should work on the data specified in the update query. Each set statement is isolated and the join applies to the set statement at hand. update t2, t1, t0 set t2.value=IF(t1.value='foo', 1, 0) <--updates based on these conditions && join , t1.value='bar' <--updates based on these conditions && join , t0.value=IF(t1.value='bar', 5, 2) <--updates based on these conditions && join WHERE t2.id = t1.id AND t1.id = t0.id <-- all updates based on these conditions Now, there was a bug that pointed out earlier concerning joins in multi-table updates, but this has been fixed in version 4.1.x ^ I may not be understanding you still, my apologies if not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: installing mysql / error
"David Katz" <[EMAIL PROTECTED]> wrote on 12/30/2004 12:34:31 PM: > > > We loaded a new server with Windows XP professional, and mysql > server 4.1. We can't get Mysql to run. After installing mysql and > trying to execute it we get the following message: > > ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' > (using password: NO) > > Any ideas on what we can try and do? > > Thanks > David. Your server IS running or you wouldn't have that error, you would have seen something else. You left out a critical detail from your original post: What are you doing when you get that error? If you are attempting a connection through an ODBC driver, you need to be aware that even the most up-to-date driver acts like a pre-4.1 client. That means you may need to follow one or more of the suggestions documented here: http://dev.mysql.com/doc/mysql/en/Problems.html especially read A.2.3 : http://dev.mysql.com/doc/mysql/en/Old_client.html Let me know if I am on the right track, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 11:43:43 AM: > [snip] > I hope I am not so naive that Kai and I are the only two who sees a > problem with this behavior. Some of you may think this is a "feature" > but I do not like it because that means that calculational errors will > occur simply by inverting column positions (putting one before the > other) within a statement. If anyone has links to any pages on database > theory that explains why it is proper to serialize the calculations > WITHIN a single SQL statement, I need to read them because I have been > operating under some mistaken beliefs for a long time. > > This is a real "house of cards" and I am a bit uncomfortable with the > situation. I am also surprised this hasn't come up more often. I will be > forwarding this to the internals list to get more eyes on the issue. > Hopefully one of them can explain why this is "correct" behavior and I > can just settle down and just be more deliberate in how I construct my > statements. > [/snip] > > I am curious to see what the internals list says on this as well. A > reading of SQL 92 and 99 support serialization behavior because "atomic" > behavior in this type of operation is likely ambiguous at best. On > several other implementations (other DBs) of multi-table updates the > behavior is exactly the same as this. There was a bug, > http://bugs.mysql.com/bug.php?id=1038 , that had something to do with th > join operations, but the bug has been fixed. > > Careful and deliberate construction of SQL statements should always > occur and be tested thouroughly prior to any implementation in a > production environment. And make sure to EXPLAIN each and every query > when testing. > > As to your second concern, variables, let us explore why your query > behaves as it does. > > >SET @testvalue=5; > >Select @testvalue:=19, @testvalue+1 as bump; > >++--+ > >| @testvalue:=19 | bump | > >++--+ > >| 19 | 20 | > >++--+ > > > > From http://dev.mysql.com/doc/mysql/en/Variables.html > > "You can also assign a value to a user variable in statements other than > SET. In this case, the assignment operator must be := and not = because > = is treated as a comparison operator in non-SET statements:" > > So you set @testvar to be 5, then when you SELECTed it you performed a > reassignment (@testvar:=19) and then bumped it. This is expected > behavior, so your reversal gives you the expected behavior because you > performed the re-assignment after you bumped the original value. I understand that is the behavior of the assignment operator. That's why I used it. ;-) I expected the results to be 19 and 6 not 19 and 20. This is just another example of the serialization of evaluation that occurs while processing a single row of data. I have the opinion that while processing that row, the variable @testvalue should keep its original value and only be updated when moving to the next row(statement). If evaluation is serialized in a SELECT statement based on the order in which they are listed, then why aren't they evaluated in that order during the processing of a SET clause? Why does the query engine seem to revert to processing changes in "table column order" and not maintain the "statement sequence order" as specified by the user? That could imply that I need to change the design of my tables so that my update statements will execute in the correct order. If only the changes had happened in the sequence specified in Kai's original SET statement... (sigh) 8-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Master will insist on running as a SLAVE if data/master.info exists.
Kevin A. Burton wrote: This is a bug. Feature. I have all the slave configuration in /etc/my.cnf commented out. However the machine was ONCE a slave and so data/master.info exists. > I commented out all the /etc/my.cnf settings WRT master-* yet when I restart the box it STILL insists on starting the slave via master.info In fact, you don't need those configuration statements in the first place. You could just as well initiated the slave replication by issuing CHANGE MASTER TO... If these settings aren't enabled the slave shouldn't be started. Slave is always "started" unless my.cnf says "skip-slave-start". Anyway, WITH skip-slave-start you will still have the slave information (binlog positions etc.) initiated but it will just not start replicating. The information in master.info overrides anything in my.cnf. -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Jay Blanchard schrieb: [snip] I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement, I need to read them because I have been operating under some mistaken beliefs for a long time. This is a real "house of cards" and I am a bit uncomfortable with the situation. I am also surprised this hasn't come up more often. I will be forwarding this to the internals list to get more eyes on the issue. Hopefully one of them can explain why this is "correct" behavior and I can just settle down and just be more deliberate in how I construct my statements. [/snip] I am curious to see what the internals list says on this as well. A reading of SQL 92 and 99 support serialization behavior because "atomic" behavior in this type of operation is likely ambiguous at best. On several other implementations (other DBs) of multi-table updates the behavior is exactly the same as this. I don't think it is clear, what Shawn's and my point is. It is clear, that a multi table update will be executed in a serialized manner. I don't want to think of the difficulties in implementing a true atomic multi table update. But it is not clear, on which data the subsequent updates work. Currently the first update uses the initial data. The second uses the initial data and the updated data from the first table. The third update sees the updated data from the first two tables and the initial data for the remaining, etc... Here comes the "house of cards" into play: - What if an upgraded optimizer decides to swap table 4 and table 6 in an update line. A query on the same data could perform differently. Or on the other side an optimizer will never be allowed to change any order in the update and every join is automatically "straight". - What about the equivalence in a SELECT between FROM table1 LEFT JOIN table2 and FROM table2 RIGHT JOIN table1 Doesn't this apply to updates, because in the first case the update order is table1 followed by table2 and vice versa in the second case. So the question is not, if a multi table update should be implemented serialized. The answer is clear: yes it should. The question is, on which data the subsequent updates should work. In my opinion every subsequent update should work on the initial data. - An optimizer can perform the updates in any order without changing their result. - From the clients point of view the update acts atomically. There is initial data, an update and then changed data. There is no intermediate state during the update that the client can get / must be aware of. - The equivalence between severel joins remains. Greetings Kai -- Kai Ruhnau Software t a r g e t systemelectronic gmbh kölner str. 99 42651 Solingen germany Tel: +49 (0)212 22 20 9 - 710 Fax: +49 (0)212 20 10 45 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: installing mysql / error
We loaded a new server with Windows XP professional, and mysql server 4.1. We can't get Mysql to run. After installing mysql and trying to execute it we get the following message: ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using password: NO) Any ideas on what we can try and do? Thanks David.
RE: Multi table update
[snip] I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement, I need to read them because I have been operating under some mistaken beliefs for a long time. This is a real "house of cards" and I am a bit uncomfortable with the situation. I am also surprised this hasn't come up more often. I will be forwarding this to the internals list to get more eyes on the issue. Hopefully one of them can explain why this is "correct" behavior and I can just settle down and just be more deliberate in how I construct my statements. [/snip] I am curious to see what the internals list says on this as well. A reading of SQL 92 and 99 support serialization behavior because "atomic" behavior in this type of operation is likely ambiguous at best. On several other implementations (other DBs) of multi-table updates the behavior is exactly the same as this. There was a bug, http://bugs.mysql.com/bug.php?id=1038 , that had something to do with th join operations, but the bug has been fixed. Careful and deliberate construction of SQL statements should always occur and be tested thouroughly prior to any implementation in a production environment. And make sure to EXPLAIN each and every query when testing. As to your second concern, variables, let us explore why your query behaves as it does. >SET @testvalue=5; >Select @testvalue:=19, @testvalue+1 as bump; >++--+ >| @testvalue:=19 | bump | >++--+ >| 19 | 20 | >++--+ > >From http://dev.mysql.com/doc/mysql/en/Variables.html "You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements:" So you set @testvar to be 5, then when you SELECTed it you performed a reassignment (@testvar:=19) and then bumped it. This is expected behavior, so your reversal gives you the expected behavior because you performed the re-assignment after you bumped the original value. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql memory,users monitoring
First of all, thanks for the mailing list for giving details about myisamchk.we used them but we got some errors which I have reported in 'myisamchk errors' mail to mysql. At present we are working in mysql 3.23.32 ,windows xp os. Are there any system tables that store mysql related info.(like memory related issues etc). Are there any other ways to find memory used and free memory available by data tables,log files,temporary tables.We want the information so that we can use it in programs.Information available through commands could not be used in programming(For ex: myisamchk works with single table.we need to monitor entire datadirectory so we need write programs.) Thanking you, yours sincerely, sirisha. Yahoo! India Matrimony: Find your life partneronline.
Re: Multi table update
[EMAIL PROTECTED] schrieb: I snipped the discussion down to the SQL-statements with some explanational text. I hope it does not break reading. [snip] UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) fails to update table2.value because by the time it gets to that column (as part of the internal JOIN tableset), table1.condition is already 8 (even though that change was listed AFTER the conditional IF() in the SET clause) This worked: UPDATE table1 LEFT JOIN table2 ON table2.ID_table1=table1.ID AND table1.condition=7 SET table2.value= table2.value-1, table1.condition=8 WHERE table1.ID IN (1,2); and so did: UPDATE table2 INNER JOIN table1 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) I think that's improper behavior. I agree with Kai, the changes should not occur in serial but in parallel based on the initial values of the JOINed tables. I also disagree with this behavior: SET @testvalue=5; Select @testvalue:=19, @testvalue+1 as bump; ++--+ | @testvalue:=19 | bump | ++--+ | 19 | 20 | ++--+ [snip] SET @testvalue=5; Select @testvalue+1 as bump, @testvalue:=19; +--++ | bump | @testvalue:=19 | +--++ |6 | 19 |(not the same results, but I only changed the column order) +--++ [snip] I have also long thought that: UPDATE table1 LEFT JOIN table2 ... would be OPERATIONALLY equivalent to: UPDATE table2 RIGHT JOIN table1 ... However, with this calculation serialization behavior, I fear it won't be. [snip] Just in case, I filled bug report number #7590. Feel free to add your comment. Greetings Kai -- Kai Ruhnau Software t a r g e t systemelectronic gmbh kölner str. 99 42651 Solingen germany Tel: +49 (0)212 22 20 9 - 710 Fax: +49 (0)212 20 10 45 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple MySQLs on Mandrake 9.x?
Our copy of MySQL (4.0.15'-Max') is on a Linux Mandrake 9.x server. We installed it from an RPM (actually an MDK, which is functionally the same thing) and are running MySQL as a service. Would it be possible to run a second copy of MySQL, ideally the latest 4.1.x, on our server in parallel with the 4.0.15 instance so that I could get a look at 4.1.x to see if it can do what I want it to do? If it is possible, how do I set the two different versions up so that both could run simultaneously and independently? Also, if it is possible to run two instances concurrently, what is the safest way of migrating my databases to 4.1.x when I am satisfied that I want to do so? Or would I be better to simply upgrade my 4.0.15 to the latest available version of 4.1.x? Rhino --- rhino1 AT sympatico DOT ca "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies." - C.A.R. Hoare -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 07:59:35 AM: > [snip] > "atomically" from my applications point of view means: > I have a snapshot of my data (due to InnoDB this does not change > hiddenly during > my session). I send a query to the database. I get the result / changed > data. > And everything between sending and the receiving the result I expect to > be > handled at once. I know MySQL cannot handle handle the query as one big > block > behind the doors, but I wonder why I / my application has to know about > these > internals in this case. > > > UPDATE statements > > work with the JOIN order and update each table in succession. > Dependent > > on the number of rows affected you may see both tables locked > > (transactionally) but note a delay in the update of table1. > > My application does not see this and that's my point. > In case of this query I have to know, that there is more than the data > before > the query and the data after the query. There is some intermediate state > that I > must be aware of. > [/snip] > > Since I am not aware of your application language I can only speak to > the C style languages, including scripting languages like PHP. Any of > these will see the query as the sum of its parts, not as individual > actions. If you need to see those things you will have to write seperate > queries for each event you wish to see. > Jay, I think my problem with the behavior you found documentation for (not in the MySQL docs, but in Paul's book?) is that it documents that an UPDATE doesn't work on the ENTIRE JOIN as a whole but on each table individually. Here is a repost of Kai's test case: CREATE TABLE table1 ( ID int(10) unsigned NOT NULL default '0', condition int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table1 VALUES (1,7); INSERT INTO table1 VALUES (2,8); CREATE TABLE table2 ( ID int(10) unsigned NOT NULL default '0', ID_table1 int(10) unsigned NOT NULL default '0', value int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table2 VALUES (1,1,5); INSERT INTO table2 VALUES (2,2,2); If I do: SELECT * from table1 INNER JOIN table2 on table1.ID= table2.ID_table1; I get: ++---++---+---+ | ID | condition | ID | ID_table1 | value | ++---++---+---+ | 1 | 7 | 1 | 1 | 5 | | 2 | 8 | 2 | 2 | 2 | ++---++---+---+ Which is what I expect. However, when you do an UPDATE on that same JOIN, the engine breaks the JOIN back into it's individual pieces then serializes the changes between those pieces. UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) fails to update table2.value because by the time it gets to that column (as part of the internal JOIN tableset), table1.condition is already 8 (even though that change was listed AFTER the conditional IF() in the SET clause) This worked: UPDATE table1 LEFT JOIN table2 ON table2.ID_table1=table1.ID AND table1.condition=7 SET table2.value= table2.value-1, table1.condition=8 WHERE table1.ID IN (1,2); and so did: UPDATE table2 INNER JOIN table1 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) I think that's improper behavior. I agree with Kai, the changes should not occur in serial but in parallel based on the initial values of the JOINed tables. I also disagree with this behavior: SET @testvalue=5; Select @testvalue:=19, @testvalue+1 as bump; ++--+ | @testvalue:=19 | bump | ++--+ | 19 | 20 | ++--+ On entry to the SELECT statement, @testvalue has a value of 5, on exit it will have the value of 19. The value of "bump" SHOULD be 6 because that is one more than the value that @testvalue had at the beginning of the statement/transaction but it isn't. This means that calculations are serialized in the order they are listed in the select statement. That means that both the SELECT and UPDATE statements are non-deterministic when it comes to column order. SET @testvalue=5; Select @testvalue+1 as bump, @testvalue:=19; +--++ | bump | @testvalue:=19 | +--++ |6 | 19 |(not the same results, but I only changed the column order) +--++ I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement
Install fails
Hello, I tried to install mysql-max-4.1.8-pc-solaris2.9-i386 on v20z with Solaris 9 04/04. I get the following error message: ld.so.1: ./bin/mysqld: fatal: libCrun.so.1: version `SUNW_1.4' not found (required by file ./bin/mysqld) ld.so.1: ./bin/mysqld: fatal: libCrun.so.1: open failed: No such file or directory The strange thing is. libCrun.so.1 is in /usr/lib. I have no idea what can be wrong: Here is the output of what /usr/lib/libCrun.so.1 /usr/lib/libCrun.so.1: release.cc 1.2 01/08/04 SMI crti.s 1.16 01/11/29 SMI CCrti.s 1.7 01/11/29 SMI thread.h signal.h feature_tests.h isa_defs.h unistd.h siginfo.h types.h machtypes.h int_types.h select.h time.h time.h time_iso_SUNWCC.h time_impl.h machsig.h faultcode.h synch.h machlock.h synch.h stddef.h stddef_iso_SUNWCC.h stdlib.h stdlib_iso_SUNWCC.h nl_types.h stdio.h stdio_iso_SUNWCC.h va_list.h stdio_tag.h stdio_impl.h string.h string_iso_SUNWCC.h assert.h unistd.h limits.h int_limits.h signal.h signal_iso_SUNWCC.h procset.h RELEASE VERSION Sun C++ 5.5 Patch 111713-05 2003/12/19 CCrtn.s 1.6 01/11/06 SMI crtn.s 1.10 01/08/07 SMI TIA and a happy new year. Bernhard Fromberg -- Mit freundlichen Grüssen, Bernhard Fromberg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with SELECT
> There is one record in the Events table for each event that an organization > is promoting. For each event, there could be 0 to 'n' files that give more > information about the event, such as pictures, audio clips, or whatever. I > want to display all the information about the event, including all of the > file names for that event, on a single result set row for each event, > regardless of how many files exist for that event. [At the moment, there is > a limit of 3 files per event but that could increase.] > > Can anyone suggest a query that would do what I need to do? I'm not sure if there is a way to return multiple File columns but you could use the GROUP_CONCAT function (added in MySQL 4.1) to obtain the files associated with the event and parse the resulting list: SELECT Event_ID, Event_Name (SELECT GROUP_CONCAT(File Separator '|') FROM Files WHERE Events.Event_ID = Files.Event_ID) AS Files FROM Events -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with ibdata1
[snip] When I upload some data into my mysql database, the file ibdata1 is growing. Usually I export contents of files into database. That's our requirement. When I remove the same data from tables, there is no change in ibdata size. I.e. it is not getting reduced with the deletions. But if I export again the same file its not growing. When I created the tables again the size remains same. How do I handle/manage/configure the file ibdata, as the size of it should grow/reduce with my operation. [/snip] This is the expected behavior of the InnoDB table space. The table space is a fixed size unless you have autoextend set, in which case it will grow when space is required. It will not shrink. So when you remove data from a table, the size of the table space will not shrink, but when you re-insert that same data it will not grow either because there is already enough space. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with SELECT
I am trying to think of SQL that will let me show a one to many relationship as a single row in a result set. They say a picture is worth a thousand words so let me draw that picture. Event_IDEvent_Nameetc. 1 Concert 2 Art Exhibit 3 Spelling Bee [Primary key: Event_ID] Event_IDFile_NumberFile 1 1promoter_logo.jpg 1 2venue_logo.jpg 1 3performer_graphic.jpg 2 1artist_graphic.jpg [Primary Key: Event_ID, File_Number] Desired Result: Event_IDEvent_NameFileFile File 1 Concert promoter_logo.jpg venue_logo.jpg performer_graphic.jpg 2 Art Exhibitartist_graphic.jpg 3 Spelling Bee There is one record in the Events table for each event that an organization is promoting. For each event, there could be 0 to 'n' files that give more information about the event, such as pictures, audio clips, or whatever. I want to display all the information about the event, including all of the file names for that event, on a single result set row for each event, regardless of how many files exist for that event. [At the moment, there is a limit of 3 files per event but that could increase.] Can anyone suggest a query that would do what I need to do? Rhino --- rhino1 AT sympatico DOT ca "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies." - C.A.R. Hoare -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is there a utility like mysqlbinlog but instead processes the query log?
is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] "atomically" from my applications point of view means: I have a snapshot of my data (due to InnoDB this does not change hiddenly during my session). I send a query to the database. I get the result / changed data. And everything between sending and the receiving the result I expect to be handled at once. I know MySQL cannot handle handle the query as one big block behind the doors, but I wonder why I / my application has to know about these internals in this case. > UPDATE statements > work with the JOIN order and update each table in succession. Dependent > on the number of rows affected you may see both tables locked > (transactionally) but note a delay in the update of table1. My application does not see this and that's my point. In case of this query I have to know, that there is more than the data before the query and the data after the query. There is some intermediate state that I must be aware of. [/snip] Since I am not aware of your application language I can only speak to the C style languages, including scripting languages like PHP. Any of these will see the query as the sum of its parts, not as individual actions. If you need to see those things you will have to write seperate queries for each event you wish to see. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing binary data
Hi, I'd like to insert binary data from a series of image files into a blob column using a sql statement. I have working examples from jsps / java but I'd like to write a sql script to do the same. I've had a look at the load data infile commands but they only seem to relate to text data. more than happy to read the docs, just need a pointer to which docs I should be reading :) thanks Nathan -- Nathan Coast Managing Director Codeczar Ltd mob : (852) 9049 5581 tel : (852) 2834 8733 fax : (852) 2834 8755 web : http://www.codeczar.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
On Thu December 30 2004 10:02 pm, [EMAIL PROTECTED] wrote: > To confirm that you would like > > [EMAIL PROTECTED] > > added to the mysql mailing list, please click on > the following link: > > http://lists.mysql.com/s/mysql/41d3ee5569b3c2f7/mabiddulph=netxp.com.au > > This confirmation serves two purposes. First, it verifies that we are > able to get mail through to you. Second, it protects you in case > someone forges a subscription request in your name. > > > --- Administrative commands for the mysql list --- > > I can handle administrative requests automatically. Please > do not send them to the list address! Instead, send > your message to the correct command address: > > For help and a description of available commands, send a message to: ><[EMAIL PROTECTED]> > > To subscribe to the list, send a message to: ><[EMAIL PROTECTED]> > > To remove your address from the list, just send a message to > the address in the ``List-Unsubscribe'' header of any list > message. If you haven't changed addresses since subscribing, > you can also send a message to: ><[EMAIL PROTECTED]> > > or for the digest to: ><[EMAIL PROTECTED]> > > For addition or removal of addresses, I'll send a confirmation > message to that address. When you receive it, simply reply to it > to complete the transaction. > > If you need to get in touch with the human owner of this list, > please send a message to: > > <[EMAIL PROTECTED]> > > Please include a FORWARDED list message with ALL HEADERS intact > to make it easier to help you. > > --- Enclosed is a copy of the request I received. > > Received: (qmail 22879 invoked by uid 48); 30 Dec 2004 12:02:29 - > Date: 30 Dec 2004 12:02:29 - > Message-ID: <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Unsubscribe request > From: <[EMAIL PROTECTED]> > > This message was generated because of a request from 220.240.131.109. pgpQZ6NlPj0iZ.pgp Description: PGP signature
Re: Fixing "the worst InnoDB corruption bug in 3 years" - when
sorry -- -- -- -- -- Sayusi Ando -- -- -- -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
