Rollback is not take effect on MySQL 5.0.18
Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql> set autocommit=0; mysql> savepoint abc; mysql> insert something mysql> rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Thanks you and best regards, -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best method to backup this mysql data
Hi I have a mySQL database running on linux. Can anyone recommend the best method to backup this data from a Windows XP machine on a scheduled basis ? My database server version at the moment is 3.23. Thanks Neil Neil Tompkins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best method to backup this mysql data
Neil Tompkins wrote: I have a mySQL database running on linux. Can anyone recommend the best method to backup this data from a Windows XP machine on a scheduled basis ? My database server version at the moment is 3.23. Generally information about backups you could find at http://dev.mysql.com/doc/refman/5.0/en/backup.html You could make bash script of Linux computer to create backups and the easiest way is to put that script as cron jobs if you have access to the cron. You can also shedule Windows BAT script which use FTP to get this data from that Linux computer. Otherwise - I mean if you don't have FTP access to the files or cron jobs - it is more difficult IMHO. -- Mladen Adamovic http://home.blic.net/adamm http://www.shortopedia.com http://www.froola.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
בעניין: RE: FOREIGN KEYS
Thanks! Nanu >>> "Ing. Edwin Cruz" <[EMAIL PROTECTED]> 10/03/2006 22:06:27 >>> Ive found this on internet: "If you re-create a table which was dropped, it has to have a definition which conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated above. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string." Use show create table statement to see table definition (on both)... Regards! -Mensaje original- De: Nanu Kalmanovitz [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 10 de Marzo de 2006 01:35 p.m. Para: mysql@lists.mysql.com Asunto: FOREIGN KEYS Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. I just finished create a new DB called TIULIM (InnoDB) with 3 tables (Sites, Tracks & Pathes). Now, using MySQL Query Browser ver. 1.1.15, I'm trying to build FOREIGN KEYS. The above tool is generating the following query: ALTER TABLE `tiulim`.`pathes` ADD CONSTRAINT `FK_pathes_1` FOREIGN KEY `FK_pathes_1` (`Site_ID`) REFERENCES `sites` (`Site_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `FK_pathes_2` FOREIGN KEY `FK_pathes_2` (`Track_ID`) REFERENCES `tracks` (`Track_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; After executing the query, it display the error message: MySQL Error Number 1005 Can't create table './tiulim/#sql-84_169.frm' (errno: 150) What is the problem? How to solve it? TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: best method to backup this mysql data
At the moment I'm using mysql version 3.23. Can you recommend any free software that can automate the backup process From: Mladen Adamovic <[EMAIL PROTECTED]> To: Neil Tompkins <[EMAIL PROTECTED]>, mysql@lists.mysql.com Subject: Re: best method to backup this mysql data Date: Sat, 11 Mar 2006 12:11:32 +0100 Neil Tompkins wrote: I have a mySQL database running on linux. Can anyone recommend the best method to backup this data from a Windows XP machine on a scheduled basis ? My database server version at the moment is 3.23. Generally information about backups you could find at http://dev.mysql.com/doc/refman/5.0/en/backup.html You could make bash script of Linux computer to create backups and the easiest way is to put that script as cron jobs if you have access to the cron. You can also shedule Windows BAT script which use FTP to get this data from that Linux computer. Otherwise - I mean if you don't have FTP access to the files or cron jobs - it is more difficult IMHO. -- Mladen Adamovic http://home.blic.net/adamm http://www.shortopedia.com http://www.froola.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~~Info needed~~
Hi Green & Eugene, Thanks a lot. On Fri, 2006-03-10 at 09:57 -0500, [EMAIL PROTECTED] wrote: > Косов Евгений <[EMAIL PROTECTED]> wrote on 03/10/2006 07:53:37 AM: > > > Hm... It seems to me In MySQL there's no such thing as "database owner". > > > Or am I missing something? > > > > Mohammed Abdul Azeem пишет: > > > Hi, > > > > > > How to check for the database owner for a particular database ? The > way > > > "show procedure status" command lists the "definer" column, Is there a > > > command that shows the database owner. Similarly, what is the command > to > > > see the owner of a table in a database ? > > > > > > Thanks in advance, > > > Abdul. > > > > > > > > > This email has been Scanned for Viruses! > > > www.newbreak.com > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > Eugene is right. Objects within a MySQL database are not "owned" by any > one account. They are essentially all "global" objects organized into > specific databases. > > Here's the reason behind the column your found. In the CREATE STORED > PROCEDURE statement there is a clause: > > SQL SECURITY { DEFINER | INVOKER } > > That clause tells the database under whose credentials should the > statements of the SPROC be executed. It determines if the SPROC will > execute under the credentials of the user who created it or the user who > is invoking it. DEFINER is the default value if none is specified. > > More details here: > http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reusing connections.
I am using php as my programming language. Let me clarify my post. When I say 'doing a connect', I am talking about the mysql_connect statement not some tcp/ip thing done before the mysql server. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 2:24 PM To: [EMAIL PROTECTED] Cc: Mysql Subject: Re: Reusing connections. "fbsd_user" <[EMAIL PROTECTED]> wrote on 03/10/2006 02:18:49 PM: > In my reading of mysql tutorials I see many examples of mysql access > with out first doing a connect. Comments say something about reusing > open connections for faster processing and less resources usage > overhead. What are they talking about & what code do I need to make > this happen? > You first need to tell us which of several dozen languages you are programming with. We can't read your mind. ;-) 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]
Strange problem: Increasing Memory / HEAP Table
Hallo, is there a way of maintaining Memory tables or rebuilding indexes? I have a "big" memory table with about 300k rows, 12 Attributes, 7 BTREE-Indexes and a PRIMARY KEY (Hash index). Now i'm doing many many Updates (and a little number of inserts) on this table that also do effect indexed attributes. (With many updates I mean about 2000 per second...thats why I have chosen Memory storage engine) The strange thing is that my table grows above linear to the happening inserts, so average row size (=total memory used by table divided by number of rows) increases too. When I now copy this table to another database or another table name by "CREATE TABLE new ..." and then "INSERT INTO new SELECT * FROM old" memory used by new table can be about 50% (depends on number of actions done on the old table before) of memory used by old table although Structure and Data are equal. Could that be a bug? Table size especially of Memory tables should not increase by updates i think... greets! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: key_buffer_size and memory used by mysqld
KJ> This would clear your doubt about which memory is shared by all KJ> threads and which memory is used by individual threads .. KJ> min_memory_needed_by_mysql = (global_buffers + KJ> ((thread_buffers )* max_connections))) OK, so when I see in `ps aux` that every thread uses 3.4% (twenty MB or so in my case) of memory it is not indeed true and actually the usage for every thread is less than `ps` shows as I understand it? KJ> Also its very easy to set key_buffer_size , just add up the KJ> total size of your indexes in the mysql data dir ( *.MYI files) and KJ> set the key buffer size approx to that, so that all your keys are in KJ> memory also you can check your key efficiency using mytop to make KJ> any changes... I don't think it's always a good idea, because indexes for big (really big) tables may be huge, and also not all indexes are used with the same frequency... By the way, how mysql caches indexes for a single table - separately (i.e. it loads only those indexes which are used) or altogether (i.e. all indexes for a table even if, say, only one index of several is in use)? If table has, say, 3 indexes, and there comes a query which uses one of these indexes, will other 2 indexes be read and cached right now for possible use in future? May key be buffered if it exists in table but is never used by queries (bad but possible example)? KJ> Kishore Jalleda Denis Solovyov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_commect or die
mysql_connect("localhost","root") or die("Unable to connect to MySQL server"); $err mysql_connect("localhost","root"); if ($err != 0) { Under what conditions will the die clause be taken. What is in $err if there is a error? Are these 2 statements basically doing the same thing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql_commect or die
mysql_connect("localhost","root") or die("Unable to connect to MySQL server"); $err = mysql_connect("localhost","root"); if ($err != 0) { Mysql_connect returns a link to a identifier of the conection, also returns 0 when the conection could not be estabished, When you do: $err = mysql_connect("localhost","root"); if ($err != 0) { echo "Error: Unable to connect to MySQL server ".mysql_error(); } else{ [code] } Is the same that this: $link = mysql_connect("localhost","root") or die("Unable to connect to MySQL server ".mysql_error()); [code] The caluse "or die()" after mysql_connect only will be launched if there is an error, if not, the script continues its execution. The $err variable has 0 if there is an error on that conditions. And yes! Basically both are doing the same thing but if the conextion is succesfully stablised it will have a link identifier of the conection. Regards! Edwin. -Mensaje original- De: fbsd_user [mailto:[EMAIL PROTECTED] Enviado el: Sábado, 11 de Marzo de 2006 10:56 a.m. Para: Mysql Asunto: mysql_commect or die mysql_connect("localhost","root") or die("Unable to connect to MySQL server"); $err mysql_connect("localhost","root"); if ($err != 0) { Under what conditions will the die clause be taken. What is in $err if there is a error? Are these 2 statements basically doing the same thing? -- 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]
Query Optimization Question
In a previous database engine I was using an IN was more optimal than a <>. So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type <> 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_query gives Resource id #3 error
$sql = "SELECT logon_id FROM members WHERE logon_id = '$logonid' AND logon_pw = '$logonpw'"; $result = mysql_query($sql) or die('Query failed. ' . mysql_error()); print "$result"; shows Resource id #3 Where can I find meaning for what this means? And why does mysql_error() not contain the description of this error? And why was the 'or die' condition not taken? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_commect or die
fbsd_user wrote: mysql_connect("localhost","root") or die("Unable to connect to MySQL server"); $err mysql_connect("localhost","root"); if ($err != 0) { Under what conditions will the die clause be taken. What is in $err if there is a error? Are these 2 statements basically doing the same thing? mysql_connect() returns a PHP connection resource on success, FALSE on failure; or ... executes if the func returns FALSE. To see what the error is, try ... or exit( mysql_error() ). Best practice is to build your own generic error handler around this functionality. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Optimization Question
Robert DiFalco wrote: In a previous database engine I was using an IN was more optimal than a <>. So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type <> 0; IN should be faster implemented with both hash tables and BTREE's so nowadays it should also be faster than <> as all MySQL implementation AFAIK use those well known data structures for indexes. I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. -- Mladen Adamovic http://home.blic.net/adamm http://www.shortopedia.com http://www.froola.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: best method to backup this mysql data
I use Navicat on my dev machine, which is an XP as well. Navicat backups my live server daily on my dev machine, as well as my local DBs, in case I screw up. -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED] Sent: Saturday, March 11, 2006 3:49 AM To: mysql@lists.mysql.com Subject: best method to backup this mysql data Hi I have a mySQL database running on linux. Can anyone recommend the best method to backup this data from a Windows XP machine on a scheduled basis ? My database server version at the moment is 3.23. Thanks Neil Neil Tompkins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_query gives Resource id #3 error
On Sat, 2006-03-11 at 12:53 -0500, fbsd_user wrote: > $sql = "SELECT logon_id > FROM members > WHERE logon_id = '$logonid' AND logon_pw = > '$logonpw'"; > > $result = mysql_query($sql) or die('Query failed. ' . > mysql_error()); > > print "$result"; shows Resource id #3 > > Where can I find meaning for what this means? > > And why does mysql_error() not contain the description of this > error? > > And why was the 'or die' condition not taken? Try print($result[0]) or print($result['logon_id']); $result is a handle to the result set, not something you can print. It's the same thing as if you tried to print out the return value of mysql_connect, which should return a resource id. There wasn't an error with the query, so the or die shouldn't execute, and mysql_error should return null. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
RE: mysql_query gives Resource id #3 error
Hi, This is more a question of how to use php, so perhaps this page may be of more use http://us2.php.net/manual/en/function.mysql-query.php It gives a good explanation there of how the SELECT statement will return the resource number on success, if you wish to access the data returned, you need to use one of several other functions eg. mysql_fetch_array etc. In other words, your query worked fine, you just haven't accessed the data returned yet. http://www.php.net is a valuable resource on how to use this fine language. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Pat Adams [mailto:[EMAIL PROTECTED] Sent: Sunday, 12 March 2006 4:45 PM To: Mysql Subject: Re: mysql_query gives Resource id #3 error On Sat, 2006-03-11 at 12:53 -0500, fbsd_user wrote: > $sql = "SELECT logon_id > FROM members > WHERE logon_id = '$logonid' AND logon_pw = > '$logonpw'"; > > $result = mysql_query($sql) or die('Query failed. ' . > mysql_error()); > > print "$result"; shows Resource id #3 > > Where can I find meaning for what this means? > > And why does mysql_error() not contain the description of this > error? > > And why was the 'or die' condition not taken? Try print($result[0]) or print($result['logon_id']); $result is a handle to the result set, not something you can print. It's the same thing as if you tried to print out the return value of mysql_connect, which should return a resource id. There wasn't an error with the query, so the or die shouldn't execute, and mysql_error should return null. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]