Re: mysqldump vs phpmyadmin dump
Wang Zi Feng wrote: Hi everyone, Here is a rookie question. The problem what I found is that mysqldump and phpmyadmin generate different size of backup file against same database. I try to dump same database with the 2 different methods, the original database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only 941kb file. mysqldump has a number of different settings, and the file size will vary according to which you use. For example, using extended insert syntax will significantly increase the size of the output, and that could easily account for the difference between your two files. What's probably happening is that the settings you're using when running mysqldump from the command line are different to those used by phpMyAdmin, so you end up with differently formatted files. I know there must be some difference between the two export method, but after I import the 941kb file which mysqldump created into a new database, it just works fine. So I'm not sure if I can use mysqldump as the best option to do mysql backup, can someone can help me to figure out why phpmyadmin would generate twice big file? And I see some post that address it is not recommend to import mysqldump file by using phpmyadmin, because it will cause problem. Importing any large file via phpMyAdmin is likely to have problems, as you'll find yourself limited by the maximum upload file size of the web server where phpMyAdmin is running (that's typically 2Mb for PHP on Apache, although the administrators can change that). But the source of the file is irrelevant; so long as it's within the file upload limit then it doesn't matter whether it was exported by phpMyAdmin itself or created using mysqldump from the command line. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump vs phpmyadmin dump
hi there, thanks for your reply. the mysql server is running on windows xp sp3, every time I use root user to log in. the command line i use for mysqldump is mysqldump --user=root --password=pass testtest.sql phpmyadmin is with following checked Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT Add IF NOT EXISTS Add AUTO_INCREMENT value Enclose table and field names with backquotes Complete inserts Extended inserts I read about the manual saying that mysqldump is default enabled with -opt, which is --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. So I wonder what is the most secure way to backup mysql database to keep data consistency? Thanks and best regards Wang 2009/11/20 Mark Goodge m...@good-stuff.co.uk Wang Zi Feng wrote: Hi everyone, Here is a rookie question. The problem what I found is that mysqldump and phpmyadmin generate different size of backup file against same database. I try to dump same database with the 2 different methods, the original database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only 941kb file. mysqldump has a number of different settings, and the file size will vary according to which you use. For example, using extended insert syntax will significantly increase the size of the output, and that could easily account for the difference between your two files. What's probably happening is that the settings you're using when running mysqldump from the command line are different to those used by phpMyAdmin, so you end up with differently formatted files. I know there must be some difference between the two export method, but after I import the 941kb file which mysqldump created into a new database, it just works fine. So I'm not sure if I can use mysqldump as the best option to do mysql backup, can someone can help me to figure out why phpmyadmin would generate twice big file? And I see some post that address it is not recommend to import mysqldump file by using phpmyadmin, because it will cause problem. Importing any large file via phpMyAdmin is likely to have problems, as you'll find yourself limited by the maximum upload file size of the web server where phpMyAdmin is running (that's typically 2Mb for PHP on Apache, although the administrators can change that). But the source of the file is irrelevant; so long as it's within the file upload limit then it doesn't matter whether it was exported by phpMyAdmin itself or created using mysqldump from the command line. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=frank.zif...@gmail.com
Re: Fwd: ODBC MySQL Password as plain text
I wondered if anyone else had any thoughts on this issue ? Cheers Neil On Thu, Nov 19, 2009 at 12:40 PM, Jay Ess li...@netrogenic.com wrote: Tompkins Neil wrote: Following my previous email. I've now configured my database connection using a ODBC DNSLESS SSL connection. However the problem still remains, the password is stored in the ASP file in plain text. Does anyone have any recommendations on how to overcome this issue ? Secure the access to the ASP-source file. You *could* encrypt it but then you have to store the key for it somewhere the ASP can access and . Catch 22. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Foreign Keys Problem
On Thu, Nov 19, 2009 at 9:34 PM, Ye Yuan yuan4...@gmail.com wrote: Hi Victor, It looks to me the foreign key syntax is wrong. Can you create the Relationship table on your database by using below ddl? create table if not exists Relationship (ID integer auto_increment primary key, Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID), check (Parent Child) ); Yes, I can create it. Thank you. V
Re: Fwd: ODBC MySQL Password as plain text
Don Thanks for your response. The issue I have is that the password for our database is stored either in the ODBC registry or within our ASP page as plain text. One option I have is to encrypt the password in the database connnection string and have a function with a key in a external file that can unlock it. The problem remains though - that if the web server is hacked, the hacker can still navigation there way to the file containing the key to unlock the password. Does that make any sense ? Cheers Neil On Fri, Nov 20, 2009 at 3:18 PM, Don Cohen don-mysq...@isis.cs3-inc.comwrote: Tompkins Neil writes: I wondered if anyone else had any thoughts on this issue ? Following my previous email. I've now configured my database connection using a ODBC DNSLESS SSL connection. However the problem still remains, I'm not sure what dnsless means (that you allow only certain ip addresses?) or what difference it makes. the password is stored in the ASP file in plain text. Does anyone have any recommendations on how to overcome this issue ? Is the issue that you're worried that your web server will serve the contents of the asp file? And I gather that you want the file to access the DB without the user having to supply a password. I'll just guess that asp files are similar to php files. One thing you could do is have that file read the password from another file that is in some place where the web server does not look. Secure the access to the ASP-source file. You *could* encrypt it but then you have to store the key for it somewhere the ASP can access and . Catch 22.
Strange problem with mysqldump / automysqlbackup (ERROR 1300)
I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene
MySQL Community Server 5.0.88 has been released
Dear MySQL users, MySQL Community Server 5.0.88, a new version of the popular Open Source Database Management System, has been released. This and future releases in the MySQL Community Server 5.0 series share version numbers with their MySQL Enterprise Server counterparts. The release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing This section documents all changes and bugfixes that have been applied since the last MySQL Community Server release (5.0.87). http://dev.mysql.com/doc/refman/5.0/en/news-5-0-88.html If you would like to receive more fine-grained and personalized update alerts about fixes that are relevant to the version and features you use, please consider subscribing to MySQL Enterprise (a commercial MySQL offering). For more details please see http://www.mysql.com/products/enterprise/advisors.html. Enjoy! -- C.1.1. Changes in MySQL 5.0.88 Bugs fixed: * Security Fix: MySQL clients linked against OpenSSL did not check server certificates presented by a server linked against yaSSL. (Bug#47320: http://bugs.mysql.com/47320) * MySQL Cluster: When a data node had written its GCI marker to the first page of a megabyte, and that node was later killed during restart after having processed that page (marker) but before completing a LCP, the data node could fail with filesystem errors. (Bug#44952: http://bugs.mysql.com/44952) See also Bug#42564: http://bugs.mysql.com/42564, Bug#44291: http://bugs.mysql.com/44291. * Replication: When a session was closed on the master, temporary tables belonging to that session were logged with the wrong database names when either of the following conditions was true: 1. The length of the name of the database to which the temporary table belonged was greater than the length of the current database name. 2. The current database was not set. (Bug#48216: http://bugs.mysql.com/48216) See also Bug#46861: http://bugs.mysql.com/46861, Bug#48297: http://bugs.mysql.com/48297. * Error handling was missing for SELECT statements containing subqueries in the WHERE clause and that assigned a SELECT result to a user variable. The server could crash as a result. (Bug#48291: http://bugs.mysql.com/48291) * An assertion could fail if the optimizer used a SPATIAL index. (Bug#48258: http://bugs.mysql.com/48258, Bug#47019: http://bugs.mysql.com/47019) * mysys/mf_keycache.c requires threading, but no test was made for thread support. (Bug#47923: http://bugs.mysql.com/47923) * If the first argument to GeomFromWKB() function was a geometry value, the function just returned its value. However, it failed to preserve the argument's null_value flag, which caused an unexpected NULL value to be returned to the caller, resulting in a server crash. (Bug#47780: http://bugs.mysql.com/47780) * The GPL and commercial license headers had different sizes, so that error log, backtrace, core dump, and cluster trace file line numbers could be off by one if they were not checked against the version of the source used for the build. (For example, checking a GPL build backtrace against commercial sources.) (Bug#46216: http://bugs.mysql.com/46216) * During the build of the Red Hat IA64 MySQL server RPM, the system library link order was incorrect. This made the resulting Red Hat IA64 RPM depend on libc.so.6.1(GLIBC_PRIVATE)(64bit), thus preventing installation of the package. (Bug#45706: http://bugs.mysql.com/45706) * Failure to treat BIT values as unsigned could lead to unpredictable results. (Bug#42803: http://bugs.mysql.com/42803) Thanks, MySQL RE Team Hery Ramilison, Karen Langford, MySQL Release Engineers Database Group, Sun Microsystem Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Have you tried dumping that table manually using mysqldump on the command line to confirm it's not an issue with automysqlbackup? Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Friday, November 20, 2009 8:31 AM To: mysql Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Yes, still exhibits this problem -- although at a different line in the file. (Seems random.) I should also mention, the backup is running across a local network. The machine the starts automysqlbackup (and internally, mysqldump) connects over TCP/IP to the database server. I am going to try running mysqldump on the database server itself, but it's not something I can do during normal hours -- plus at 10GB, it's a time-consuming thing to test. (Would it matter? I guess I need to find out.) In any case, it seems clear that during the dump, mysqldump is periodically adding a character to the dump file. On 2009-11-20, at 12:41 PM, Gavin Towey wrote: Have you tried dumping that table manually using mysqldump on the command line to confirm it's not an issue with automysqlbackup? Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Friday, November 20, 2009 8:31 AM To: mysql Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
