Re: Excluding MySQL database tables from mysqldump
2014/04/07 08:02 -0800, Tim Johnson 2)mysqldump forces all database names to lower case in the CREATE DATABASE statement. I know, one shouldn't use upper case in database names, but :) tell that to my clients. Why not? That is not mentioned in the section devoted to mapping such names to the file-system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Excluding MySQL database tables from mysqldump
* h...@tbbs.net h...@tbbs.net [140407 23:09]: 2014/04/07 08:02 -0800, Tim Johnson 2)mysqldump forces all database names to lower case in the CREATE DATABASE statement. I know, one shouldn't use upper case in database names, but :) tell that to my clients. Why not? That is not mentioned in the section devoted to mapping such names to the file-system. I found 'official' documentation here regarding Mac OS X: https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html I had also found some reference to this having been a side effect of migrating files from older macs (of which I am not familiar) filesystems. I don't find any reference in the mysqldump documentation at https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html to any mechanism for overriding this. The incompatibility kicks in when trying to restore the databases on linux - and I presume FreeBSD, sun OS and other posix systems would show the same problem. Live and learn ... -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.com, http://www.tj49.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Excluding MySQL database tables from mysqldump
Hello Tim, On 4/4/2014 10:27 PM, Tim Johnson wrote: * Tim Johnson t...@akwebsoft.com [140404 17:46]: Currently I'm running mysql on a Mac OSX partition. I have installed an ubuntu dual-booted partition and put mysql on it. I have already set up a mysql user on the ubuntu OS. In the past I have used mysqldump with just the --all-databases option to transfer data across different linux partitions. I'm wondering if I should explicitly exclude some of the tables from the mysql database. If so, which? perhaps mysql.user? thoughts? Opinions? thanks I should add the following: 1)the only user added to the new partition is the same as the primary non-root user on the Mac partition. Same credentials 2)this is a workstation - it is closed to the outside world. FYI: ... There are several ways to select which data you want in the backup. You can backup per-table, per-database, object type per database (routines, triggers), or global objects (events). What level of detail you want to copy from your old instance into your new instance is completely up to you. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Excluding MySQL database tables from mysqldump
* shawn l.green shawn.l.gr...@oracle.com [140407 07:05]: Hello Tim, On 4/4/2014 10:27 PM, Tim Johnson wrote: * Tim Johnson t...@akwebsoft.com [140404 17:46]: Currently I'm running mysql on a Mac OSX partition. I have installed an ubuntu dual-booted partition and put mysql on it. I have already set up a mysql user on the ubuntu OS. In the past I have used mysqldump with just the --all-databases option to transfer data across different linux partitions. I'm wondering if I should explicitly exclude some of the tables from the mysql database. If so, which? perhaps mysql.user? thoughts? Opinions? thanks I should add the following: 1)the only user added to the new partition is the same as the primary non-root user on the Mac partition. Same credentials 2)this is a workstation - it is closed to the outside world. FYI: ... There are several ways to select which data you want in the backup. You can backup per-table, per-database, object type per database (routines, triggers), or global objects (events). What level of detail you want to copy from your old instance into your new instance is completely up to you. I've run into other problems, such as a 1)running mysqldump exactly as I would have in linux and not getting all databases. Dunno why, but keep on reading. 2)mysqldump forces all database names to lower case in the CREATE DATABASE statement. I know, one shouldn't use upper case in database names, but :) tell that to my clients. It turns out '2)' is a known problem in Mac, but I just didn't know it... My workaround was to write a python app that uses the MySQLdb module to get the name of all databases, iterate through the list and selectively operate on them, and ensure that proper case is used in the CREATE DATABASE command.. So I'm good here, I think. Thanks much for the reply. -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.com, http://www.tj49.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Excluding MySQL database tables from mysqldump
Currently I'm running mysql on a Mac OSX partition. I have installed an ubuntu dual-booted partition and put mysql on it. I have already set up a mysql user on the ubuntu OS. In the past I have used mysqldump with just the --all-databases option to transfer data across different linux partitions. I'm wondering if I should explicitly exclude some of the tables from the mysql database. If so, which? perhaps mysql.user? thoughts? Opinions? thanks -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.com, http://www.tj49.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Excluding MySQL database tables from mysqldump
* Tim Johnson t...@akwebsoft.com [140404 17:46]: Currently I'm running mysql on a Mac OSX partition. I have installed an ubuntu dual-booted partition and put mysql on it. I have already set up a mysql user on the ubuntu OS. In the past I have used mysqldump with just the --all-databases option to transfer data across different linux partitions. I'm wondering if I should explicitly exclude some of the tables from the mysql database. If so, which? perhaps mysql.user? thoughts? Opinions? thanks I should add the following: 1)the only user added to the new partition is the same as the primary non-root user on the Mac partition. Same credentials 2)this is a workstation - it is closed to the outside world. FYI: ... -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.com, http://www.tj49.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Cannot connect to MySql Database
Hi, I am trying to connect to a MySql database and I keep getting the following: Cannot find (ping) database host Joseph-Kosinskis-MacBook on the network Failed to connect to Unix socket '/var/run/lirc/lircd' No such file or directory (2) The database is located on a MacBook running OS X 10.8. The MySql Server is running. Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Cannot connect to MySql Database
On 4/9/13 1:54 PM, Joe Kosinski joj...@gmail.com wrote: Hi, I am trying to connect to a MySql database and I keep getting the following: Cannot find (ping) database host Joseph-Kosinskis-MacBook on the network Failed to connect to Unix socket '/var/run/lirc/lircd' No such file or directory (2) The database is located on a MacBook running OS X 10.8. The MySql Server is running. Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The hostname can't be found. Try localhost instead of Joseph-Kosinskis-MacBook. As for the next error, that's not a database error, that's a MythTV error. lircd is the daemon for remotes (Linux Infrared Remote Control Daemon). Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
a MySQL database that lives in Google's cloud?
This might interest some that Google Cloud SQL is actually a MySQL database that lives in Google's cloud Source: https://developers.google.com/cloud-sql/docs/introduction
MySQL Database Server 5.1.59 has been released
Dear MySQL users, MySQL Server 5.1.59, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.59 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.59 on new servers or upgrading to MySQL 5.1.59 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ 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 For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-59.html Enjoy! === D.1.2. Changes in MySQL 5.1.59 (15 September, 2011) Functionality Added or Changed * If the --with-ndbcluster option is given to the configure script, it now produces a warning that the version of MySQL Cluster included in 5.1 is no longer maintained. (The separate MySQL Cluster distribution should be used instead.) (Bug #49093, Bug #11757091) Bugs Fixed * InnoDB Storage Engine: The random read-ahead (http://dev.mysql.com/doc/innodb-plugin/1.0/en/glossary.html#g los_read_ahead) feature that was removed from the InnoDB Plugin is now available again. Because it is only helpful for certain workloads, it is turned off by default. To turn it on, enable the innodb_random_read_ahead configuration option. Because this feature can improve performance in some cases and reduce performance in others, before relying on this setting, benchmark both with and without the setting enabled. (Bug #12356373) * Partitioning: Auto-increment columns of partitioned tables were checked even when they were not being written to. In debug builds, this could lead to a server crash. (Bug #11765667, Bug #58655) * The option-parsing code for empty strings leaked memory. (Bug #12589928) * Replication: Processing of corrupted table map events could cause the server to crash. This was especially likely if the events mapped different tables to the same identifier, such as could happen due to Bug#56226. Now, before applying a table map event, the server checks whether the table has already been mapped with different settings, and if so, an error is raised and the slave SQL thread stops. If it has been mapped with the same settings, or if the table is set to be ignored by filtering rules, there is no change in behavior: the event is skipped and IDs are not checked. (Bug #44360, Bug #11753004) See also Bug #11763509. * ALTER TABLE {MODIFY|CHANGE} ... FIRST did nothing except rename columns if the old and new versions of the table had exactly the same structure with respect to column data types. As a result, the mapping of column name to column data was incorrect. The same thing happened for ALTER TABLE DROP COLUMN ... ADD COLUMN statements intended to produce a new version of the table with exactly the same structure as the old version. (Bug #61493, Bug #12652385) * For a lower_case_table_names value of 1 or 2 and a database having a mixed-case name, calling a stored function using a fully qualified name including the database name failed. (Bug #60347, Bug #11840395) * Previously, Performance Schema table columns that held byte counts were BIGINT UNSIGNED. These were changed to BIGINT (signed). This makes it easier to perform calculations that compute differences between columns. (Bug #59631, Bug #11766504) * For MyISAM tables, attempts to insert incorrect data into an indexed GEOMETRY column could result in table corruption. (Bug #57323, Bug #11764487) * A race condition between loading a stored routine using the name qualified by the database name and dropping that database resulted in a spurious error message: The table mysql.proc is missing, corrupt, or contains bad data (Bug #47870, Bug #11756013) * Upgrades using an RPM package recreated the test database, which is undesirable when the DBA had removed it. (Bug #45415,
MySQL Database Server 5.1.59 has been released
Dear MySQL users, MySQL Server 5.1.59, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.59 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.59 on new servers or upgrading to MySQL 5.1.59 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ 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 For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-59.html Enjoy! === D.1.2. Changes in MySQL 5.1.59 (15 September, 2011) Functionality Added or Changed * If the --with-ndbcluster option is given to the configure script, it now produces a warning that the version of MySQL Cluster included in 5.1 is no longer maintained. (The separate MySQL Cluster distribution should be used instead.) (Bug #49093, Bug #11757091) Bugs Fixed * InnoDB Storage Engine: The random read-ahead (http://dev.mysql.com/doc/innodb-plugin/1.0/en/glossary.html#g los_read_ahead) feature that was removed from the InnoDB Plugin is now available again. Because it is only helpful for certain workloads, it is turned off by default. To turn it on, enable the innodb_random_read_ahead configuration option. Because this feature can improve performance in some cases and reduce performance in others, before relying on this setting, benchmark both with and without the setting enabled. (Bug #12356373) * Partitioning: Auto-increment columns of partitioned tables were checked even when they were not being written to. In debug builds, this could lead to a server crash. (Bug #11765667, Bug #58655) * The option-parsing code for empty strings leaked memory. (Bug #12589928) * Replication: Processing of corrupted table map events could cause the server to crash. This was especially likely if the events mapped different tables to the same identifier, such as could happen due to Bug#56226. Now, before applying a table map event, the server checks whether the table has already been mapped with different settings, and if so, an error is raised and the slave SQL thread stops. If it has been mapped with the same settings, or if the table is set to be ignored by filtering rules, there is no change in behavior: the event is skipped and IDs are not checked. (Bug #44360, Bug #11753004) See also Bug #11763509. * ALTER TABLE {MODIFY|CHANGE} ... FIRST did nothing except rename columns if the old and new versions of the table had exactly the same structure with respect to column data types. As a result, the mapping of column name to column data was incorrect. The same thing happened for ALTER TABLE DROP COLUMN ... ADD COLUMN statements intended to produce a new version of the table with exactly the same structure as the old version. (Bug #61493, Bug #12652385) * For a lower_case_table_names value of 1 or 2 and a database having a mixed-case name, calling a stored function using a fully qualified name including the database name failed. (Bug #60347, Bug #11840395) * Previously, Performance Schema table columns that held byte counts were BIGINT UNSIGNED. These were changed to BIGINT (signed). This makes it easier to perform calculations that compute differences between columns. (Bug #59631, Bug #11766504) * For MyISAM tables, attempts to insert incorrect data into an indexed GEOMETRY column could result in table corruption. (Bug #57323, Bug #11764487) * A race condition between loading a stored routine using the name qualified by the database name and dropping that database resulted in a spurious error message: The table mysql.proc is missing, corrupt, or contains bad data (Bug #47870, Bug #11756013) * Upgrades using an RPM package recreated the test database, which is undesirable when the DBA had removed it. (Bug #45415,
MySQL Database Server 5.1.59 has been released
Dear MySQL users, MySQL Server 5.1.59, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.59 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.59 on new servers or upgrading to MySQL 5.1.59 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ 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 For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-59.html Enjoy! === D.1.2. Changes in MySQL 5.1.59 (15 September, 2011) Functionality Added or Changed * If the --with-ndbcluster option is given to the configure script, it now produces a warning that the version of MySQL Cluster included in 5.1 is no longer maintained. (The separate MySQL Cluster distribution should be used instead.) (Bug #49093, Bug #11757091) Bugs Fixed * InnoDB Storage Engine: The random read-ahead (http://dev.mysql.com/doc/innodb-plugin/1.0/en/glossary.html#g los_read_ahead) feature that was removed from the InnoDB Plugin is now available again. Because it is only helpful for certain workloads, it is turned off by default. To turn it on, enable the innodb_random_read_ahead configuration option. Because this feature can improve performance in some cases and reduce performance in others, before relying on this setting, benchmark both with and without the setting enabled. (Bug #12356373) * Partitioning: Auto-increment columns of partitioned tables were checked even when they were not being written to. In debug builds, this could lead to a server crash. (Bug #11765667, Bug #58655) * The option-parsing code for empty strings leaked memory. (Bug #12589928) * Replication: Processing of corrupted table map events could cause the server to crash. This was especially likely if the events mapped different tables to the same identifier, such as could happen due to Bug#56226. Now, before applying a table map event, the server checks whether the table has already been mapped with different settings, and if so, an error is raised and the slave SQL thread stops. If it has been mapped with the same settings, or if the table is set to be ignored by filtering rules, there is no change in behavior: the event is skipped and IDs are not checked. (Bug #44360, Bug #11753004) See also Bug #11763509. * ALTER TABLE {MODIFY|CHANGE} ... FIRST did nothing except rename columns if the old and new versions of the table had exactly the same structure with respect to column data types. As a result, the mapping of column name to column data was incorrect. The same thing happened for ALTER TABLE DROP COLUMN ... ADD COLUMN statements intended to produce a new version of the table with exactly the same structure as the old version. (Bug #61493, Bug #12652385) * For a lower_case_table_names value of 1 or 2 and a database having a mixed-case name, calling a stored function using a fully qualified name including the database name failed. (Bug #60347, Bug #11840395) * Previously, Performance Schema table columns that held byte counts were BIGINT UNSIGNED. These were changed to BIGINT (signed). This makes it easier to perform calculations that compute differences between columns. (Bug #59631, Bug #11766504) * For MyISAM tables, attempts to insert incorrect data into an indexed GEOMETRY column could result in table corruption. (Bug #57323, Bug #11764487) * A race condition between loading a stored routine using the name qualified by the database name and dropping that database resulted in a spurious error message: The table mysql.proc is missing, corrupt, or contains bad data (Bug #47870, Bug #11756013) * Upgrades using an RPM package recreated the test database, which is undesirable when the DBA had
MySQL Database Server 5.1.58 has been released
Dear MySQL users, MySQL Server 5.1.58, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.58 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.58 on new servers or upgrading to MySQL 5.1.58 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ 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 For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-58.html Enjoy! === Changes in MySQL 5.1.58 (July 5th, 2011) Bugs fixed: * InnoDB Storage Engine: If the server crashed while an XA transaction was prepared but not yet committed, the transaction could remain in the system after restart, and cause a subsequent shutdown to hang. (Bug #11766513, Bug #59641) * Partitioning: When executing a row-ordered retrieval index merge, the partitioning handler used memory from that allocated for the table, rather than allocated to the query, causing table object memory not to be freed until the table was closed. (Bug #11766249, Bug #59316) * Replication: When mysqlbinlog was invoked using --base64-output=decode-row and --start-position=pos, (where pos is a point in the binary log past the format description log event), a spurious error of the type shown here was generated: malformed binlog: it does not contain any Format_description_log_event... However, since there is nothing unsafe about not printing the format description log event, the error has been removed for this case. (Bug #12354268) * Replication: Typographical errors appeared in the text of several replication error messages. (The word position was misspelled as postion.) (Bug #11762616, Bug #55229) * After the fix for Bug#11889186, MAKEDATE() arguments with a year part greater than raised an assertion. (Bug #12403504) * An assertion could be raised due to a missing NULL value check in Item_func_round::fix_length_and_dec(). (Bug #12392636) * In debug builds on Solaris, an assertion was raised if a reverse IP lookup with gethostbyaddr_r() failed. (Bug #12377872) * MySQL did not build if configured with both --with-debug and --with-libedit. (Bug #12329909) * A problem introduced in 5.1.57 caused very old (MySQL 4.0) clients to be unable to connect to the server. (Bug #61222, Bug #12563279) * Using CREATE EVENT IF NOT EXISTS for an event that already existed and was enabled caused multiple instances of the event to run. (Bug #61005, Bug #12546938) * The incorrect max_length value for YEAR values could be used in temporary result tables for UNION, leading to incorrect results. (Bug #59343, Bug #11766270) * In Item_func_in::fix_length_and_dec(), a Valgrind warning for uninitialized values was corrected. (Bug #59270, Bug #11766212) * In ROUND() calculations, a Valgrind warning for uninitialized memory was corrected. (Bug #58937, Bug #11765923) * Valgrind warnings caused by comparing index values to an uninitialized field were corrected. (Bug #58705, Bug #11765713) * LOAD DATA INFILE errors could leak I/O cache memory. (Bug #58072, Bug #11765141) * For LOAD DATA INFILE, multibyte character sequences could be pushed onto a stack too small to accommodate them. (Bug #58069, Bug #11765139) * An embedded client would abort rather than issue an error message if it issued a TEE command (\T file_name) and the directory containing the file did not exist. This occurred because the wrong error handler was called. (Bug #57491, Bug #11764633) * In debug builds, Field_new_decimal::store_value() was subject to buffer overflows. (Bug #55436, Bug #11762799) * On Linux, the mysql client built using the bundled libedit did not read ~/.editrc. (Bug #49967, Bug #11757855) * The optimizer sometimes incorrectly processed
MySQL Database Server 5.1.56 has been released
Dear MySQL users, MySQL Server 5.1.56, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.56 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.56 on new servers or upgrading to MySQL 5.1.56 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ 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 For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-56.html Changes in MySQL 5.1.56 (01 March 2011): Functionality added or changed: * mysqldump --xml now displays comments from column definitions. (Bug #13618) Bugs fixed: * InnoDB Storage Engine: InnoDB returned values for rows examined in the query plan that were higher than expected. NULL values were treated in an inconsistent way. The inaccurate statistics could trigger false positives in combination with the MAX_JOIN_SIZE setting, because the queries did not really examine as many rows as reported. (Bug #30423) * Partitioning: Trying to use the same column more than once in the partitioning key when partitioning a table by KEY caused mysqld to crash. Such duplication of key columns is now expressly disallowed, and fails with an appropriate error. (Bug #53354, Bug #57924) * Replication: When using the statement-based logging format, INSERT ON DUPLICATE KEY UPDATE and INSERT IGNORE statements affecting transactional tables that did not fail were not written to the binary log if they did not insert any rows. (With statement-based logging, all successful statements should be logged, whether they do or do not cause any rows to be changed.) (Bug #59338) * Replication: Formerly, STOP SLAVE stopped the slave I/O thread first and then stopped the slave SQL thread; thus, it was possible for the I/O thread to stop after replicating only part of a transaction which the SQL thread was executing, in wich case---if the transaction could not be rolled back safely---the SQL thread could hang. Now, STOP SLAVE stops the slave SQL thread first and then stops the I/O thread; this guarantees that the I/O thread can fetch any remaining events in the transaction that the SQL thread is executing, so that the SQL thread can finish the transaction if it cannot be rolled back safely. (Bug #58546) * A query of the following form returned an incorrect result, where the values for col_name in the result set were entirely replaced with NULL values: SELECT DISTINCT col_name ... ORDER BY col_name DESC; (Bug #59308, Bug #11766241) * DELETE or UPDATE statements could fail if they used DATE or DATETIME values with a year, month, or day part of zero. (Bug #59173) * The ESCAPE clause for the LIKE operator allows only expressions that evaluate to a constant at execution time, but aggregrate functions were not being rejected. (Bug #59149) * Memory leaks detected by Valgrind, some of which could cause incorrect query results, were corrected. (Bug #59110, Bug #11766075) * mysqlslap failed to check for a NULL return from mysql_store_result() and crashed trying to process the result set. (Bug #59109) * In debug builds, SUBSTRING_INDEX(FORMAT(...), FORMAT(...)) could cause a server crash. (Bug #58371) * When mysqldadmin was run with the --sleep and --count options, it went into an infinite loop executing the specified command. (Bug #58221) * Some string manipulating SQL functions use a shared string object intended to contain an immutable empty string. This object was used by the SQL function SUBSTRING_INDEX() to return an empty string when one argument was of the wrong datatype. If the string object was then modified by the SQL function INSERT(), undefined behavior ensued. (Bug #58165, Bug #11765225) * Parsing nested regular expressions could lead to recursion resulting in a stack overflow
Upgrading of mysql database
Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel
Re: Upgrading of mysql database
That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Upgrading of mysql database
Replace the software - if you're using packaged versions, they should take care of most anything. If not, there's mysql-upgrade or some script. See the online docs for specifics. On Mon, Nov 22, 2010 at 3:56 PM, Machiel Richards machi...@rdc.co.zawrote: How would I do an inplace upgrade? -Original Message- *From*: Johan De Meersman vegiv...@tuxera.bejohan%20de%20meersman%20%3cvegiv...@tuxera.be%3e *To*: Machiel Richards machi...@rdc.co.zamachiel%20richards%20%3cmachi...@rdc.co.za%3e *Cc*: mysql mailing list mysql@lists.mysql.commysql%20mailing%20list%20%3cmy...@lists.mysql.com%3e *Subject*: Re: Upgrading of mysql database *Date*: Mon, 22 Nov 2010 15:25:44 +0100 That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Upgrading of mysql database
How would I do an inplace upgrade? -Original Message- From: Johan De Meersman vegiv...@tuxera.be To: Machiel Richards machi...@rdc.co.za Cc: mysql mailing list mysql@lists.mysql.com Subject: Re: Upgrading of mysql database Date: Mon, 22 Nov 2010 15:25:44 +0100 That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Recover accidentally deleted MySQL database files
Dear sirs, Accidentally files in a database have been deleted (/ var/lib/mysql /database) when entering the mysql console shows that the database is created but does not show any table, there is some method to recever the information in this database? Centos 4.6 mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3 Thanks Jose Luis
Re: Recover accidentally deleted MySQL database files
At 10:42 AM 5/21/2010, you wrote: Dear sirs, Accidentally files in a database have been deleted (/ var/lib/mysql /database) when entering the mysql console shows that the database is created but does not show any table, there is some method to recever the information in this database? You need to restore your database data directory from backup. You could try to undelete the files in the data directory but that may not recover all of the data since the tables may have been open when the files were erased. Before restoring the files from your backup, make sure you either flush the tables or shutdown the mysql server. Mike Centos 4.6 mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3 Thanks Jose Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Recover accidentally deleted MySQL database files
thanks for your answer, it seems these files were deleted when running fsck. In the /Lost+found directory files are referenced to Mysql for example: #114470: MySQL table definition file Version 9 #114471: MySQL MISAM compressed data file Version 1 #114472: DBase 3 data file (256 records) #114473: MySQL table definition file Version 9 #114474: MySQL MISAM compressed data file Version 1 #114475: DBase 3 data file (258 records) #114476: MySQL table definition file Version 9 #114477: MySQL MISAM compressed data file Version 1 #114478: DBase 3 data file (33489404 records) #114479: MySQL table definition file Version 9 #114480: MySQL MISAM compressed data file Version 1 #114482: MySQL table definition file Version 9 #114483: MySQL MISAM compressed data file Version 1 #114484: DBase 3 data file (256 records) #114485: MySQL table definition file Version 9 #114486: MySQL MISAM compressed data file Version 1 #114487: DBase 3 data file (8960 records) #114488: MySQL table definition file Version 9 #114489: MySQL MISAM compressed data file Version 1 #114490: DBase 3 data file (256 records) There any way to recover these files in its original location? Thanks Jose Luis -Mensaje original- De: mos [mailto:mo...@fastmail.fm] Enviado el: Viernes, 21 de Mayo de 2010 11:24 a.m. Para: mysql@lists.mysql.com Asunto: Re: Recover accidentally deleted MySQL database files At 10:42 AM 5/21/2010, you wrote: Dear sirs, Accidentally files in a database have been deleted (/ var/lib/mysql /database) when entering the mysql console shows that the database is created but does not show any table, there is some method to recever the information in this database? You need to restore your database data directory from backup. You could try to undelete the files in the data directory but that may not recover all of the data since the tables may have been open when the files were erased. Before restoring the files from your backup, make sure you either flush the tables or shutdown the mysql server. Mike Centos 4.6 mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3 Thanks Jose Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jma...@isp.qnet.com.pe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recover accidentally deleted MySQL database files
And this is why you have backups :-) You have little guarantee that the recovered blocks form complete files, but you can try to move them into the directory (with the correct mdi, myd and frm extensions and named consistently, of course) and try to figure out what contains which data from the contents of the tables. The first challenge, however, is going to be matching the correct .frm file to the correct .myd and .mdi. I guess you could skip the .mdi files, as they're just indices, that'll make the job slightly easier at least. Keep the originally recovered files, too, just in case MySQL tries to write to some of them and corrupts them. I hope you enjoy puzzles... On Fri, May 21, 2010 at 6:48 PM, Jose Luis Marin Perez jma...@isp.qnet.com.pe wrote: thanks for your answer, it seems these files were deleted when running fsck. In the /Lost+found directory files are referenced to Mysql for example: #114470: MySQL table definition file Version 9 #114471: MySQL MISAM compressed data file Version 1 #114472: DBase 3 data file (256 records) #114473: MySQL table definition file Version 9 #114474: MySQL MISAM compressed data file Version 1 #114475: DBase 3 data file (258 records) #114476: MySQL table definition file Version 9 #114477: MySQL MISAM compressed data file Version 1 #114478: DBase 3 data file (33489404 records) #114479: MySQL table definition file Version 9 #114480: MySQL MISAM compressed data file Version 1 #114482: MySQL table definition file Version 9 #114483: MySQL MISAM compressed data file Version 1 #114484: DBase 3 data file (256 records) #114485: MySQL table definition file Version 9 #114486: MySQL MISAM compressed data file Version 1 #114487: DBase 3 data file (8960 records) #114488: MySQL table definition file Version 9 #114489: MySQL MISAM compressed data file Version 1 #114490: DBase 3 data file (256 records) There any way to recover these files in its original location? Thanks Jose Luis -Mensaje original- De: mos [mailto:mo...@fastmail.fm] Enviado el: Viernes, 21 de Mayo de 2010 11:24 a.m. Para: mysql@lists.mysql.com Asunto: Re: Recover accidentally deleted MySQL database files At 10:42 AM 5/21/2010, you wrote: Dear sirs, Accidentally files in a database have been deleted (/ var/lib/mysql /database) when entering the mysql console shows that the database is created but does not show any table, there is some method to recever the information in this database? You need to restore your database data directory from backup. You could try to undelete the files in the data directory but that may not recover all of the data since the tables may have been open when the files were erased. Before restoring the files from your backup, make sure you either flush the tables or shutdown the mysql server. Mike Centos 4.6 mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3 Thanks Jose Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jma...@isp.qnet.com.pe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Rename mysql database
Hi All,I want to rename my database. but as per the mysql documentation it says, RENAME DATABASE command has been removed from mysql 5.1.Can anyone help me with this, that is there any other way I can rename it. Thanks in advance.--Regards, Manasi Save
Re: Rename mysql database
You can try this one. http://preetul.wordpress.com/2009/07/27/rename-database-in-mysql/ Note: I have not tested. On Fri, Jan 8, 2010 at 5:04 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I want to rename my database. but as per the mysql documentation it says, RENAME DATABASE command has been removed from mysql 5.1. Can anyone help me with this, that is there any other way I can rename it. Thanks in advance. -- Regards, Manasi Save -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: question regarding mysql database location
Is mysql the owner of the directories? - Original Message From: Manasi Save manasi.s...@artificialmachines.com To: Johan De Meersman vegiv...@tuxera.be Cc: Waynn Lue waynn...@gmail.com; mysql mysql@lists.mysql.com Sent: Wed, November 25, 2009 8:12:25 PM Subject: Re: question regarding mysql database location Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ntn...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Hi Johan, It worked perfectly. Thank you so much for this explanation. I am really greatful. -- Best Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 3:42 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Make sure the directory the symlink points to has the same owner, group and permissions as your other databases. The permissions of the symlink itself are irrelevant (at least, on a Linux system. YMMV for other *nixen). *mytest1:~# cd /var/lib/mysql mytest1:/var/lib/mysql# ls -lh* total 117M -rw-r--r-- 1 root root 0 2009-11-19 12:08 debian-5.0.flag -rw-rw 1 mysql mysql 106M 2009-11-25 11:53 ibdata1 -rw-rw 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile0 -rw-rw 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile1 drwxr-xr-x 2 mysql root 4.0K 2009-11-24 10:27 mysql -rw--- 1 root root 7 2009-11-19 12:08 mysql_upgrade_info drwx-- 2 mysql mysql 4.0K 2009-11-24 10:43 tmp *mytest1:/var/lib/mysql# mysql* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 690 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *mysql show databases;* ++ | Database | ++ | information_schema | | mysql | | tmp| ++ 10 rows in set (0.13 sec) mysql Bye *mytest1:/var/lib/mysql# mkdir /tmp/remotedatabase mytest1:/var/lib/mysql# chown mysql:mysql /tmp/remotedatabase mytest1:/var/lib/mysql# chmod u+rwX /tmp/remotedatabase mytest1:/var/lib/mysql# ln -s /tmp/remotedatabase ./ mytest1:/var/lib/mysql# mysql* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 691 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *mysql show databases;* ++ | Database | ++ | information_schema | | mysql | | remotedatabase | | tmp| ++ 11 rows in set (0.01 sec) *mysql use remotedatabase;* Database changed *mysql create table a (a int);* Query OK, 0 rows affected (0.04 sec) *mysql show tables;* +--+ | Tables_in_remotedatabase | +--+ | a| +--+ 1 row in set (0.01 sec) mysql Bye mytest1:/var/lib/mysql# Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Hi Nitin, Yes for the actually directories created mysql is the owner. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. Is mysql the owner of the directories? - Original Message From: Manasi Save manasi.s...@artificialmachines.com To: Johan De Meersman vegiv...@tuxera.be Cc: Waynn Lue waynn...@gmail.com; mysql mysql@lists.mysql.com Sent: Wed, November 25, 2009 8:12:25 PM Subject: Re: question regarding mysql database location Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ntn...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
I fixed this by using symlinks for the directories for the underlying databases. The limit for files is significantly higher than directories. Waynn On 11/24/09, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=waynn...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. Regards, Manasi Save Artificial Machines Pvt Ltd. I fixed this by using symlinks for the directories for the underlying databases. The limit for files is significantly higher than directories. Waynn On 11/24/09, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=waynn...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. So assuming /var/lib/mysql/data/ is your mysql data directory, you could create a new directory called /var/lib/mysql/data/data1, then move all the directories from /var/lib/mysql/data/* into data1. Then you could create a symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/dir name. When mysql tries to load the data directory, it follows the symlink to the underlying directory (in /var/lib/mysql/data/data1).
Re: question regarding mysql database location
Well Waynn, In this case I need to move all the existing databases to new location right. Which I don't want to do. Is it possible that I create sym link between two and use both. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. So assuming /var/lib/mysql/data/ is your mysql data directory, you could create a new directory called /var/lib/mysql/data/data1, then move all the directories from /var/lib/mysql/data/* into data1. Then you could create a symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/dir name. When mysql tries to load the data directory, it follows the symlink to the underlying directory (in /var/lib/mysql/data/data1). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
You don't need to move any databases. Look at this structure: /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) /disk2/mysql/db4 (directory) /db5 (directory) /db6 (directory) If your mysql data directory is set to /data/disk1/mysql, the server will pick up the symlinks there and use them as if they were just ordinary directories. On Wed, Nov 25, 2009 at 10:48 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Well Waynn, In this case I need to move all the existing databases to new location right. Which I don't want to do. Is it possible that I create sym link between two and use both. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. So assuming /var/lib/mysql/data/ is your mysql data directory, you could create a new directory called /var/lib/mysql/data/data1, then move all the directories from /var/lib/mysql/data/* into data1. Then you could create a symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/dir name. When mysql tries to load the data directory, it follows the symlink to the underlying directory (in /var/lib/mysql/data/data1). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: question regarding mysql database location
Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). Also is it somthing that in disk1/mysql it will not create physical folder of it. /disk2/mysql/db4 (directory) /db5 (directory) /db6 (directory) -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. You don't need to move any databases. Look at this structure: /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) /disk2/mysql/db4 (directory) /db5 (directory) /db6 (directory) If your mysql data directory is set to /data/disk1/mysql, the server will pick up the symlinks there and use them as if they were just ordinary directories. On Wed, Nov 25, 2009 at 10:48 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Well Waynn, In this case I need to move all the existing databases to new location right. Which I don't want to do. Is it possible that I create sym link between two and use both. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. So assuming /var/lib/mysql/data/ is your mysql data directory, you could create a new directory called /var/lib/mysql/data/data1, then move all the directories from /var/lib/mysql/data/* into data1. Then you could create a symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/dir name. When mysql tries to load the data directory, it follows the symlink to the underlying directory (in /var/lib/mysql/data/data1). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Hi Manasi, At a time mysql can point to one data directory. For your task you can have n number of mysql installation with different data directory. After that you can use federated storage engine to perform your task. Thanks, Krishna Ch. Prajapati On Wed, Nov 25, 2009 at 12:19 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: question regarding mysql database location
On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further.
Re: question regarding mysql database location
On Wed, Nov 25, 2009 at 12:05 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: At a time mysql can point to one data directory. For your task you can have n number of mysql installation with different data directory. After that you can use federated storage engine to perform your task. The federated engine ignores indexes on the remote database, though. Read up on the documentation before jumping in to this :-)
Re: question regarding mysql database location
Thanks Johan, It was really a great help. I'll try to implement it. I dont want to opt for multiple mysql instances option as thats not feasible. I'll get back to you all if it works fine. Thanks again. -- Best Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
On Wed, Nov 25, 2009 at 3:42 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Make sure the directory the symlink points to has the same owner, group and permissions as your other databases. The permissions of the symlink itself are irrelevant (at least, on a Linux system. YMMV for other *nixen). *mytest1:~# cd /var/lib/mysql mytest1:/var/lib/mysql# ls -lh* total 117M -rw-r--r-- 1 root root 0 2009-11-19 12:08 debian-5.0.flag -rw-rw 1 mysql mysql 106M 2009-11-25 11:53 ibdata1 -rw-rw 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile0 -rw-rw 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile1 drwxr-xr-x 2 mysql root 4.0K 2009-11-24 10:27 mysql -rw--- 1 root root 7 2009-11-19 12:08 mysql_upgrade_info drwx-- 2 mysql mysql 4.0K 2009-11-24 10:43 tmp *mytest1:/var/lib/mysql# mysql* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 690 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *mysql show databases;* ++ | Database | ++ | information_schema | | mysql | | tmp| ++ 10 rows in set (0.13 sec) mysql Bye *mytest1:/var/lib/mysql# mkdir /tmp/remotedatabase mytest1:/var/lib/mysql# chown mysql:mysql /tmp/remotedatabase mytest1:/var/lib/mysql# chmod u+rwX /tmp/remotedatabase mytest1:/var/lib/mysql# ln -s /tmp/remotedatabase ./ mytest1:/var/lib/mysql# mysql* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 691 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *mysql show databases;* ++ | Database | ++ | information_schema | | mysql | | remotedatabase | | tmp| ++ 11 rows in set (0.01 sec) *mysql use remotedatabase;* Database changed *mysql create table a (a int);* Query OK, 0 rows affected (0.04 sec) *mysql show tables;* +--+ | Tables_in_remotedatabase | +--+ | a| +--+ 1 row in set (0.01 sec) mysql Bye mytest1:/var/lib/mysql# Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further.
question regarding mysql database location
Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Also I forgot to mention that I have gone through the innodb option of innodb_data_file_path but I can just specify it as : innodb_data_file_path=ibdata1:2048M:autoextend:max:1024M;ibdata1:2048M:autoextend:max:1024M; But not as : innodb_data_file_path=/var/lib/mysql/data/ibdata1:2048M:autoextend:max:1024M;/var/lib/mysql/data1/ibdata1:2048M:autoextend:max:1024M; Is there any wayout for this? Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to allocate memory for mysql database
hi All how we can allocate memory for mysql database. since we have different storage. each having its own buffer for running the query. in oracle we have some calculation for allocating sga size,like this any method is exist for mysql. i am seeing mysql is using default 8m for all (myisam and innodb). how to increase the memory size for innodb and myisam innodb_buffer_pool_size -- 8388608 innodb_additional_mem_pool_size -- 1048576 in oracle,we have dynamic memory and static memory. we can update dynamic memory in online but for static we need to shutdown and startup the db. , what about for mysql. Architecture wise both database has huge different but i need some idea to upgrade my mysql server please assist me.. thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
Porting MySQL database across international sites
Hello everybody, the MySQL application I'm building needs to be ported from my current site to the hosting site - which is based on another nation. The problem I'm having is that the login doesn't work as the password doesn't match in the charset of the new hosting site. To address this problem, I have made some changes to the server configuration: - set default charset as utf8 (originally it was latin1) Plus, I've made some correction to the J2EE application: - forcing the JVM to start with the option -Dfile.encoding=UTF-8 But still, the passwords doesn't match when trying to log in. Any suggestion or hint about what I should look into? To me, it appears that all the settings have been properly changes, but there is something missing. Thanks! Dan
Extract strings from corrupted MySQL database
Hi, I'm trying to extract texts from MySQL database (phpbb). Please, could anybody explain which format is used to store texts? I mean binary structure. I know the sources are open, but I don't like to go throught the sources to find answer for such common question. And, you know, Google did not help me. Thanks very much, Oto 'tapik' Buchta PS: The database uses utf-8 encoding with utf8_czech_ci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master - Slave MySQL Database Server
hi, Is there a step by step guide to set up Master - Slave MySQL Database Server Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master - Slave MySQL Database Server
On Wed, Apr 2, 2008 at 1:09 PM, Kaushal Shriyan [EMAIL PROTECTED] wrote: hi, Is there a step by step guide to set up Master - Slave MySQL Database Server Thanks and Regards Kaushal Hi, what are the different test cases used for this set up Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Database Dimensioning
Hi Iam very new user of MySQL. Can anyone help me in guiding me that how can I dimension and design the MySQL Database Server. If some one can provide the guidelines and the critical parameters that are required to dimension the MySQL Server. Iam looking for scaling the database for at least 5 years How can I do that Regards Vivek Aggarwal
Re: MySQL database synchronization
Hi, If you have 256Kbps should not be a problem as long as it used most for mysql, if there are too may other user accessing net then it would be too slow. When you say, you want to access from both the sites, does it mean there would be read and write from both sites or just read from from one site and write from other sites. If its just read from one site and read and write from other site then, you can setup a master where read and write happen and slave where only read happens. Also, the slave can be used as a backup. There are many third party software available where you can manually apply bin-logs from one site to other, but this is little complicated and need precise scripting taking care of all the bin-log position. regards anandkl On Jan 25, 2008 4:17 AM, C K [EMAIL PROTECTED] wrote: I have a problem related with 2 mysql database synchronization. We are using a 256kbps internet at our mfg. site and a 2mbps internet connection at our HO. We are using MySQL5.0.45 for our ERP application. We want to work from both locations at a time through ERP software. For this we are trying to synchronize both servers are site and at HO. what will be the best solution for this? 1. Replication, (is it possible over 256kbps connection?) 2. Manual synchronization (using Navicat/SQLyog like software) 3. Using Binlogs (applying binlog to the another db) 4. any other We need urgent help regarding this. Thanks in advance and regards CPK -- Keep your Environment clean and green.
MySQL database synchronization
I have a problem related with 2 mysql database synchronization. We are using a 256kbps internet at our mfg. site and a 2mbps internet connection at our HO. We are using MySQL5.0.45 for our ERP application. We want to work from both locations at a time through ERP software. For this we are trying to synchronize both servers are site and at HO. what will be the best solution for this? 1. Replication, (is it possible over 256kbps connection?) 2. Manual synchronization (using Navicat/SQLyog like software) 3. Using Binlogs (applying binlog to the another db) 4. any other We need urgent help regarding this. Thanks in advance and regards CPK -- Keep your Environment clean and green.
Re: preferred way to backup a 20GB MySQL database
What about the MySQL Administration backup routine, which can be scheduled for a convenient time? No-one has mentioned this one. We use it but perhaps there is something I should know...? Thanks Jonathan Trahair - Original Message - From: B. Keith Murphy To: Dan Buettner Cc: David Campbell ; MySql Sent: Wednesday, November 28, 2007 2:55 AM Subject: Re: preferred way to backup a 20GB MySQL database I would echo what Dan says. In addition, from the slave server, you might look at running the new mysql-parallel-dump tool that Baron Schwartz has developed. It essentially does a dump with a thread running (by default) for each CPU core you have. A dual core box will run two threads and dump roughly twice as fast as a normal mysqldump. In addition, it compresses the output making it much more compact. He has renamed the toolkit to Maatkit and it is available at http://maatkit.sourceforge.net/. Also, you might look into using an LVM snapshot to run the copy from. That way it doesn't interfere with your operations as much. I do that for some of our production slave servers myself. Keith Dan Buettner wrote: I'd strongly recommend setting up replication, and then taking your backups from the replica. mysqlhotcopy works great, I used it for years myself, but it does require freezing your database while the copy happens. And no matter how you do it, copying 20 GB takes a little bit of time. Dan On Nov 27, 2007 4:35 PM, David Campbell [EMAIL PROTECTED] wrote: Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: preferred way to backup a 20GB MySQL database
The MySQL Administration backup routine is just a front end for mysqldump On Nov 28, 2007 12:51 AM, J Trahair [EMAIL PROTECTED] wrote: What about the MySQL Administration backup routine, which can be scheduled for a convenient time? No-one has mentioned this one. We use it but perhaps there is something I should know...? Thanks Jonathan Trahair - Original Message - From: B. Keith Murphy To: Dan Buettner Cc: David Campbell ; MySql Sent: Wednesday, November 28, 2007 2:55 AM Subject: Re: preferred way to backup a 20GB MySQL database I would echo what Dan says. In addition, from the slave server, you might look at running the new mysql-parallel-dump tool that Baron Schwartz has developed. It essentially does a dump with a thread running (by default) for each CPU core you have. A dual core box will run two threads and dump roughly twice as fast as a normal mysqldump. In addition, it compresses the output making it much more compact. He has renamed the toolkit to Maatkit and it is available at http://maatkit.sourceforge.net/. Also, you might look into using an LVM snapshot to run the copy from. That way it doesn't interfere with your operations as much. I do that for some of our production slave servers myself. Keith Dan Buettner wrote: I'd strongly recommend setting up replication, and then taking your backups from the replica. mysqlhotcopy works great, I used it for years myself, but it does require freezing your database while the copy happens. And no matter how you do it, copying 20 GB takes a little bit of time. Dan On Nov 27, 2007 4:35 PM, David Campbell [EMAIL PROTECTED] wrote: Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Grant Limberg [EMAIL PROTECTED]
preferred way to backup a 20GB MySQL database
Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Thanks, Andras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: preferred way to backup a 20GB MySQL database
Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: preferred way to backup a 20GB MySQL database
I'd strongly recommend setting up replication, and then taking your backups from the replica. mysqlhotcopy works great, I used it for years myself, but it does require freezing your database while the copy happens. And no matter how you do it, copying 20 GB takes a little bit of time. Dan On Nov 27, 2007 4:35 PM, David Campbell [EMAIL PROTECTED] wrote: Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: preferred way to backup a 20GB MySQL database
I would echo what Dan says. In addition, from the slave server, you might look at running the new mysql-parallel-dump tool that Baron Schwartz has developed. It essentially does a dump with a thread running (by default) for each CPU core you have. A dual core box will run two threads and dump roughly twice as fast as a normal mysqldump. In addition, it compresses the output making it much more compact. He has renamed the toolkit to Maatkit and it is available at http://maatkit.sourceforge.net/. Also, you might look into using an LVM snapshot to run the copy from. That way it doesn't interfere with your operations as much. I do that for some of our production slave servers myself. Keith Dan Buettner wrote: I'd strongly recommend setting up replication, and then taking your backups from the replica. mysqlhotcopy works great, I used it for years myself, but it does require freezing your database while the copy happens. And no matter how you do it, copying 20 GB takes a little bit of time. Dan On Nov 27, 2007 4:35 PM, David Campbell [EMAIL PROTECTED] wrote: Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL DATABASE SERVER
Hi All, The production server on which mysql database was running, get shutdown on one day. Then we have to manually start it. The server is hpdl585. What can be the reason of shutdown. What has gone wrong with the server. Is there is any to find the reason for shutdown. Thanks, Krishna
Re: MYSQL DATABASE SERVER
Krishna Chandra Prajapati schrieb: Hi All, The production server on which mysql database was running, get shutdown on one day. Then we have to manually start it. The server is hpdl585. What can be the reason of shutdown. What has gone wrong with the server. Is there is any to find the reason for shutdown. i think this is the wrong list to ask why the (hardware) server with your (software) MySQL server went down. try an OS related mailing list instead but anyway: check your logfiles -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to restore mysql database after Crash
Hey, Environment: Windows 2003 server Mysql 5.0 server Problem: Our server crashed and the c drive was formatted. Unfortunately we did not have a backup of the mysql databases which we were using. We can hopefully retrieve the file structure through a file system recovery utility. What would the steps in order to restore the databases again ?
Re: how to restore mysql database after Crash
Recover your files first and then you can see where you can go. I suppose you now have backup plans in place now? Keith David Winslow wrote: Hey, Environment: Windows 2003 server Mysql 5.0 server Problem: Our server crashed and the c drive was formatted. Unfortunately we did not have a backup of the mysql databases which we were using. We can hopefully retrieve the file structure through a file system recovery utility. What would the steps in order to restore the databases again ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Database Administrator/ Data Architect - LONDON
hi everyone, i am looking to find a MySQL guru to come work full time at a passionate mobile technology/ communications company in the heart of London. This truly is the place to be! They have global footprint - everyone in the company are technically competent and extremely passionate about what the do. Their product has launched recently in the US (in addition to initially launched in the UK, it has launched in Germany and other European countries). More territories are planed for late 07 and 08. We need to find a DBA to take control of the MySQL RDBMS and the data held therein. The role will actually combine elements of Data Architect too. The role is going to involve liaising with the application developers and the network operations team. The dress style of the company is informal and they also have a Nintendo Wii in the office that anybody can play on at any time of the day(work load permitting of course). Feel free to contact me using jamesbenjamin AT linuxmail DOT org thanks James = Measure Network Availability Security Carrier, MSO and Vendor Triple Play product testing reduces downtime. http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=d446202f87d73543d468e6891d79072d -- Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
-Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... Now, I went through on a few of them and added \n to the end of the line, then tried to load the file again with the LINES TERMINATED BY \n and it looks like it went just fine for the ones I did that too... So now I need to add that to the rest... I could have sworn I had done this before and it didn't work though On Aug 9, 2007, at 11:22 AM, Jerry Schwartz wrote: Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
Doesn't that (the trailing comma) depend upon whether or not you want the default value for the (missing) field, as opposed to or 0 used for empty fields? Either way, you are right - you should be able to import the data. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 11:29 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import file into MySQL Database..
Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
RE: Import file into MySQL Database..
If you can import your excel doc into MS Access I'd suggest: http://www.mysql.com/products/tools/migration-toolkit/ I just started using and love it - easy intutitive GUI tool for importing data into nySQL databases. Good luck. -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 1:15 PM To: mysql@lists.mysql.com Subject: Import file into MySQL Database.. Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
A... the one thing I can't do... I don't have Access to well... Access... :) Did some more testing, made a new table and matched the field names, now it will load it without any errors, it's just only importing the first row... Not the rest of the 934 records... On Aug 8, 2007, at 1:20 PM, Stephen Sunderlin wrote: If you can import your excel doc into MS Access I'd suggest: http://www.mysql.com/products/tools/migration-toolkit/ I just started using and love it - easy intutitive GUI tool for importing data into nySQL databases. Good luck. -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 1:15 PM To: mysql@lists.mysql.com Subject: Import file into MySQL Database.. Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Hi. On Wednesday 08 August 2007 18:39, Jason Pruim wrote: Did some more testing, made a new table and matched the field names, now it will load it without any errors, it's just only importing the first row... Not the rest of the 934 records... You are using ENCLOSED BY '' in your SQL, which I have had cause hassle with excel generated csv files. Basically, excel will only wrap field values in double quotes if the value contains the field delimiter. This caught me out, although I seem to remember I did get an error. Try removing the ENCLOSED BY anyway, it's optional. Another thing to watch for is column headers in your input file that may be triggering column constraints. You can get past this by using the IGNORE n LINES syntax in your command. I usually specify the table column names too - I have happily loaded up csv files from excel with something like the following: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE MyTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (column1,column2,column_etc); Hope this helps. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
First line of my .csv file is: First,Last,Add1,Add2,City,State,Zip,Date,Xcode,Reason DESCRIBE is: mysql describe test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | First | varchar(20) | YES | | NULL| | | Last | varchar(20) | YES | | NULL| | | Add1 | varchar(50) | YES | | NULL| | | Add2 | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(10) | YES | | NULL| | | Zip| varchar(20) | YES | | NULL| | | XCode | varchar(20) | YES | | NULL| | | Reason | varchar(50) | YES | | NULL| | | Date | varchar(20) | YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.09 sec) I've also tried adding the filed names at the end of my load data command but that didn't help... As it sits right now this is the command I'm attempting to use: mysql LOAD DATA LOCAL INFILE '/volumes/raider/aml.master. 8.6.07.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; Which displays this: Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 if I remove the IGNORE 1 LINES; from the end then I get this added into the table: A. DREW | Last | Add1 | Add2 | City | State | Zip | Date | Xcode | Reason Which is a combination of the first address and the column names. On Aug 8, 2007, at 3:34 PM, Gary Josack wrote: Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
I believe that excel files generally terminate lines with '\r\n' and if you use terminated by '\n' it will cause this behavior. so try it with lines terminated by '\r\n' or get TextPad or a similar editor that can save the file as unix platform that uses the same line terminators that MySQL expects by default and leave out the optional terminated by cj On 8/8/07, Jason Pruim [EMAIL PROTECTED] wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Jason Pruim wrote: First line of my .csv file is: First,Last,Add1,Add2,City,State,Zip,Date,Xcode,Reason DESCRIBE is: mysql describe test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | First | varchar(20) | YES | | NULL| | | Last | varchar(20) | YES | | NULL| | | Add1 | varchar(50) | YES | | NULL| | | Add2 | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(10) | YES | | NULL| | | Zip| varchar(20) | YES | | NULL| | | XCode | varchar(20) | YES | | NULL| | | Reason | varchar(50) | YES | | NULL| | | Date | varchar(20) | YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.09 sec) I've also tried adding the filed names at the end of my load data command but that didn't help... As it sits right now this is the command I'm attempting to use: mysql LOAD DATA LOCAL INFILE '/volumes/raider/aml.master.8.6.07.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; Which displays this: Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 if I remove the IGNORE 1 LINES; from the end then I get this added into the table: A. DREW | Last | Add1 | Add2 | City | State | Zip | Date | Xcode | Reason Which is a combination of the first address and the column names. On Aug 8, 2007, at 3:34 PM, Gary Josack wrote: Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database synchronizing from 2 locations
Hello, As per your suggestions I tried to get some correct solution for the problem, but there is a big problem for replication and it is network connection. Though Internet is available to the Mfg. Site, it is not having good speed and continuous. So that replication may not be a good choice. As we are using Auto-increment fields for each table and it is Primary Key and also physical records are already marked with this PK. Is there any other solution for this? Please give the details. Thanks CPK -- Keep your Environment clean and green.
Re: MySQL database synchronizing from 2 locations
Sorry maybe this is completly out of topic, but why do you need it to synchronise in two locations, can't you just get both servers to connect to the same database? And then if you need you can set a cron to backup your database hourly or daily to the other server using mysql dump and scp. I don't understand the need to always have the same in two locations as this uses up twice the amount of ressources. C K a écrit : Hello, As per your suggestions I tried to get some correct solution for the problem, but there is a big problem for replication and it is network connection. Though Internet is available to the Mfg. Site, it is not having good speed and continuous. So that replication may not be a good choice. As we are using Auto-increment fields for each table and it is Primary Key and also physical records are already marked with this PK. Is there any other solution for this? Please give the details. Thanks CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database synchronizing from 2 locations
Richard- If you have 2 or more servers which you want to dedicate to MySQL you may want to look into MySQL Clustering To focus on sync'ing I would read about the the 'syncronisation replication' vs 'asynchronous replication' that the Participating Nodes employ http://www.mysql.com/news-and-events/newsletter/2003-02/a000125.html HTH/ Martin-- This email message and any files transmitted with it contain confidential information intended only for the person(s) to whom this email message is addressed. If you have received this email message in error, please notify the sender immediately by telephone or email and destroy the original message without making a copy. Thank you. - Original Message - From: Richard [EMAIL PROTECTED] To: C K [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Sunday, August 05, 2007 4:01 AM Subject: Re: MySQL database synchronizing from 2 locations Sorry maybe this is completly out of topic, but why do you need it to synchronise in two locations, can't you just get both servers to connect to the same database? And then if you need you can set a cron to backup your database hourly or daily to the other server using mysql dump and scp. I don't understand the need to always have the same in two locations as this uses up twice the amount of ressources. C K a écrit : Hello, As per your suggestions I tried to get some correct solution for the problem, but there is a big problem for replication and it is network connection. Though Internet is available to the Mfg. Site, it is not having good speed and continuous. So that replication may not be a good choice. As we are using Auto-increment fields for each table and it is Primary Key and also physical records are already marked with this PK. Is there any other solution for this? Please give the details. Thanks CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL database synchronizing from 2 locations
Hello, My client has a mfg. unit at 65 Km from a city in India. He wants to connect to his corporate office in the city. Both offices will use same data and same ERP system. He is using Win 2K3 server and MySQL 5.0.17. Is it possible to make them synchronized at a particular or regular intervals? How? Please give details. Options I think - Replication (is it possible for Windows?) Cluster (Is it possible?) Manual Sync by using Navicat or any other tool (other tools please) Please help. Prior Thanks, CPK -- Keep your Environment clean and green.
RE: MySQL database synchronizing from 2 locations
Replication works with Windows (we do it extensively here at work). And it's definitely one option. But if there are any problems, then without some monitoring mechanism, you'll not be alerted if replication chokes (all that will happen is that updates to the slave will seemingly just stop). You can implement any of a bunch of alerting and self-repair mechanisms. Other methods of transferring the data are similarly challenging. You can do an automated MySQLDUMP on a periodic basis with with a --master-data option (in case you do perform the replication) just to be sure OR just do MySQLDumps each night (depending on the volume). It all depends on the degree and need for whatever level of synchronicity. Tim... -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 2:08 PM To: mysql@lists.mysql.com Subject: MySQL database synchronizing from 2 locations Hello, My client has a mfg. unit at 65 Km from a city in India. He wants to connect to his corporate office in the city. Both offices will use same data and same ERP system. He is using Win 2K3 server and MySQL 5.0.17. Is it possible to make them synchronized at a particular or regular intervals? How? Please give details. Options I think - Replication (is it possible for Windows?) Cluster (Is it possible?) Manual Sync by using Navicat or any other tool (other tools please) Please help. Prior Thanks, CPK -- Keep your Environment clean and green. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL database synchronizing from 2 locations
Hi, Set up two-way replication between Corporate and Manufacturing. As long as a live network connection is available between the two sites, replication will carry on. Make sure that all your hardware is able to handle the peak I/O loads, in order to keep replication humming along seamlessly. If the connectivity between the two servers is lost, replication will gracefully auto-recover. You will need to set up some heartbeat script to monitor the connectivity, and SMS/email you if it is lost, allowing you to check on it. Sincerely, Raj Mehrotra -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 2:08 PM To: mysql@lists.mysql.com Subject: MySQL database synchronizing from 2 locations Hello, My client has a mfg. unit at 65 Km from a city in India. He wants to connect to his corporate office in the city. Both offices will use same data and same ERP system. He is using Win 2K3 server and MySQL 5.0.17. Is it possible to make them synchronized at a particular or regular intervals? How? Please give details. Options I think - Replication (is it possible for Windows?) Cluster (Is it possible?) Manual Sync by using Navicat or any other tool (other tools please) Please help. Prior Thanks, CPK -- Keep your Environment clean and green. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To store Myanmar unicode characters in MySQL database
Hello everybody, I want to store Myanmar unicode data in MySQL database. Then, I want to make data manipulation with it. Firstly, i tried to input Myanmar characters in MySQL's GUI tool. i chose Myanmar keyboard and installed Myanmar font in tool. But, i couldn't type Myanmar characters. It shows only English characters. how can i do it? can i make data manipulation for Myanmar characters in MySQL? Have a nice time!!! violet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database move
Hi Ace, If you cant affort downtime and if you are using innodb try removing auto extend on the current data file and create a datafile in a different partition and put autoextend on the same. If you are using MyISAM , you can move few tables to different disk use symlinks. -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu On 7/9/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Ace, Can't you zip or move old bin-logs to a different disk and release some free space on the current drive. regards anandkl On 7/9/07, Ace [EMAIL PROTECTED] wrote: Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan
MySQL database move
Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan
Re: MySQL database move
backup and restore would be what i would do. using either mysql administrator or mysqldump. On 09/07/2007, at 3:45 PM, Ace wrote: Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan Regards, Hartleigh Burton Resident Geek MRA Entertainment Pty Ltd 5 Dividend St | Mansfield | QLD 4122 | Australia Phone: (07) 3457 5041 Fax: (07) 3349 8806 Mobile: 0421 646 978 www.mraentertainment.com Internal Virus Database was built: Never Checked by MAC OSX... we don't get viruses!
Re: MySQL database move
Hi Ace, Can't you zip or move old bin-logs to a different disk and release some free space on the current drive. regards anandkl On 7/9/07, Ace [EMAIL PROTECTED] wrote: Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan
Re: MySQL database move
Will try with dump and moving logs. Can I just move my datadir=/usr/local/mysql/data to some other location and change it in my.cnf? Will there be any complications to this? Thanks, Rajan On 7/8/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Ace, Can't you zip or move old bin-logs to a different disk and release some free space on the current drive. regards anandkl On 7/9/07, Ace [EMAIL PROTECTED] wrote: Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan
multilanguage web application with mysql database?
I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, enter the Chinese name in gb2312, and then switch the session language to big5, enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together (where jp is any kind of japaness language encoding). My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks
RE: multilanguage web application with mysql database?
Are all of your fields using the UTF8 character set? I think that's necessary. We use UTF8 and have stored Chinese characters successfully using UTF8 with a PHP web application. (At least, they look right to me - I don't know Chinese at all.) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: YL [mailto:[EMAIL PROTECTED] Sent: Friday, May 04, 2007 10:19 AM To: mysql@lists.mysql.com Subject: multilanguage web application with mysql database? I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, enter the Chinese name in gb2312, and then switch the session language to big5, enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together (where jp is any kind of japaness language encoding). My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multilanguage web application with mysql database?
I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. All the strings/fields etc. need to be in the *same* encoding - UTF-8 Only UTF-8 can handle all languages. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, gb2312 is an encoding - not a language. enter the Chinese name in gb2312, and then switch the session language to big5, big5 is an encoding - not a language enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together (where jp is any kind of japaness language encoding). My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks Everything needs to be in UTF-8. - the database - application logic - and all web interfaces. Forget about big5, gb2312, shift_jis etc! Also, web forms submit content in the encoding of the web page. So make sure your http content-type header is set to UTF-8 for all web pages. I'm not sure what technology you're using - php/java etc? By default, most web servers/applications serve content as ISO-8859-1encoding - which only works for western European languages. - unless you specifically tell it otherwise. In a JSP for example, you need to put this directive at the top of a page: %@ page contentType=text/html;charset=UTF-8 pageEncoding=UTF-8 % This article is useful - http://java.sun.com/developer/technicalArticles/Intl/HTTPCharset/index.html even if you're not using Java technology. ~mm
Speed of queries in a MySQL database
Hi Everyone. I have just upgraded a Visual Basic 6 project which used an Access database as a data back end, using DAO and SQL strings. The Access database was exceedingly slow, and prone to glitches. I have changed the VB code to ADO, and set up a MySQL database in the hope that the MySQL database would be better suited to the large size of some tables, and would therefore return queries more quickly. It is now populated with the data from the old Access tables. So far, the MySQL speed has been very slow. I'm talking about 30 seconds to return the 2 records found by SELECT * FROM OrderItems WHERE CustomerCode = 'ABE001' AND InvoiceNo = 0 ORDER BY OrderNumber, ProductCode, Colour, RecNo from the OrderItems table - 309,000 rows (ie. records). And this is in the MySQL Query Browser, not the VB code. Database information: all 23 tables use the InnoDB, 2 tables have more than 276,000 records, most have a lot less. Uses ODBC. My questions are: 1. Have I chosen the right database? What databases do people use which return data from huge tables in micro-seconds (well, alright, seconds, then!)? 2. Should I set up the database in a different way, if so, how? 3. Is this the right list for such questions? Thanks in advance. Jonathan Trahair
Re: Speed of queries in a MySQL database
Jonathan Trahair wrote: Hi Everyone. I have just upgraded a Visual Basic 6 project which used an Access database as a data back end, using DAO and SQL strings. The Access database was exceedingly slow, and prone to glitches. I have changed the VB code to ADO, and set up a MySQL database in the hope that the MySQL database would be better suited to the large size of some tables, and would therefore return queries more quickly. It is now populated with the data from the old Access tables. So far, the MySQL speed has been very slow. I'm talking about 30 seconds to return the 2 records found by SELECT * FROM OrderItems WHERE CustomerCode = 'ABE001' AND InvoiceNo = 0 ORDER BY OrderNumber, ProductCode, Colour, RecNo from the OrderItems table - 309,000 rows (ie. records). And this is in the MySQL Query Browser, not the VB code. Database information: all 23 tables use the InnoDB, 2 tables have more than 276,000 records, most have a lot less. Uses ODBC. My questions are: 1. Have I chosen the right database? What databases do people use which return data from huge tables in micro-seconds (well, alright, seconds, then!)? 2. Should I set up the database in a different way, if so, how? 3. Is this the right list for such questions? Thanks in advance. Jonathan Trahair Have you thought about putting in an index on CustomerCode? And have you run EXPLAIN on your select statement? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of queries in a MySQL database
At 09:21 AM 3/4/2007, Jonathan Trahair wrote: Hi Everyone. I have just upgraded a Visual Basic 6 project which used an Access database as a data back end, using DAO and SQL strings. The Access database was exceedingly slow, and prone to glitches. I have changed the VB code to ADO, and set up a MySQL database in the hope that the MySQL database would be better suited to the large size of some tables, and would therefore return queries more quickly. It is now populated with the data from the old Access tables. So far, the MySQL speed has been very slow. I'm talking about 30 seconds to return the 2 records found by SELECT * FROM OrderItems WHERE CustomerCode = 'ABE001' AND InvoiceNo = 0 ORDER BY OrderNumber, ProductCode, Colour, RecNo from the OrderItems table - 309,000 rows (ie. records). And this is in the MySQL Query Browser, not the VB code. Database information: all 23 tables use the InnoDB, 2 tables have more than 276,000 records, most have a lot less. Uses ODBC. My questions are: 1. Have I chosen the right database? What databases do people use which return data from huge tables in micro-seconds (well, alright, seconds, then!)? 2. Should I set up the database in a different way, if so, how? 3. Is this the right list for such questions? Thanks in advance. Jonathan Trahair Jonathan, If you are going to use CustomerCode and InvoiceNo to retrieve the rows, then create a compound index with CustomerCode and InvoiceNo in that one index. Why did you choose InnoDb instead of MyISAM? Do you need RI or are there a lot of people updating the table at the same time? If not, MyISAM might be a better choice because it's faster. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mmultiple languages in the MySQL database
We have multiple Western languages in our UTF-8 data base, without a problem. Right now I'm working on adding Chinese data, but my major problem is reading back what's in there to find out if it went in correctly :( Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: krishna [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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: Mmultiple languages in the MySQL database
On 9/28/06, Jerry Schwartz [EMAIL PROTECTED] wrote: We have multiple Western languages in our UTF-8 data base, without a problem. Right now I'm working on adding Chinese data, but my major problem is reading back what's in there to find out if it went in correctly :( One thing I've done in the past to verify this exact issue, is to simply run something like: # mysql -e select fields from table output.txt - making sure that the the output contains some non-ASCII characters - preferably non-Latin too. Then open output.txt in a browser and set the character encoding to UTF-8. [View-Character Encoding-UTF-8 in Firefox]. If the content renders properly [provided you've got the right fonts obviously], then you can be sure that the content went in properly. ~mm Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- Wrom: DULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTLBXFGGMEPYO Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mmultiple languages in the MySQL database
That's a good suggestion. As it happens, I have a web-based application that should display the data, but it doesn't. Without going into detail, all of the Chinese data is in one account and nothing shows in that account in the web application even though I can see the records with the MySQL client. Now I'm chasing that hare. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Michael Monaghan [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 9:58 AM To: Jerry Schwartz Cc: krishna; mysql@lists.mysql.com Subject: Re: Mmultiple languages in the MySQL database On 9/28/06, Jerry Schwartz [EMAIL PROTECTED] wrote: We have multiple Western languages in our UTF-8 data base, without a problem. Right now I'm working on adding Chinese data, but my major problem is reading back what's in there to find out if it went in correctly :( One thing I've done in the past to verify this exact issue, is to simply run something like: # mysql -e select fields from table output.txt - making sure that the the output contains some non-ASCII characters - preferably non-Latin too. Then open output.txt in a browser and set the character encoding to UTF-8. [View-Character Encoding-UTF-8 in Firefox]. If the content renders properly [provided you've got the right fonts obviously], then you can be sure that the content went in properly. ~mm Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- Wrom: DULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTLBXFGGMEPYO Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mmultiple languages in the MySQL database
One thing I've done in the past to verify this exact issue, is to simply run something like: # mysql -e select fields from table output.txt - making sure that the the output contains some non-ASCII characters - preferably non-Latin too. Then open output.txt in a browser and set the character encoding to UTF-8. [View-Character Encoding-UTF-8 in Firefox]. If the content renders properly [provided you've got the right fonts obviously], then you can be sure that the content went in properly. Or alternatively there's a nice Solaris 10 utility - auto_ef [auto encoding-finder], to which you pass a file as a parameter and it will tell you the encoding. http://docs.sun.com/app/docs/doc/819-2246/6n4i34qee?a=view#DESCRIPTION ~mm ~mm Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- Wrom: DULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTLBXFGGMEPYO Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mmultiple languages in the MySQL database
How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-database-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access mySQL database across Internet
The connection time to my mySQL database is OK from the ASP page. Are there any ways to speed this up though ? From: [EMAIL PROTECTED] To: My.SQL mysql@lists.mysql.com@ashcomp.net Subject: RE: Access mySQL database across Internet Date: Fri, 11 Aug 2006 12:20:19 -0400 On Fri, 11 Aug 2006 15:02:12 +, Neil Tompkins wrote At the moment our mysql server hosted by an ISP, is updated every second by a program running on our local Internet connection from our own office network. We have seen no performance issues from our websites hosted by the same ISP running our mySQL server. That's fairly impressive, but I hope you mean that you only *consider* updating once per second, if actually necessary. It would be an awful lot of wasteful traffic if you actually had the two machines talking every seconed to transmit nothing. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: Access mySQL database across Internet
You could look into establishing persistent connections. Know it's possible with mod_perl and I believe PHP. Not sure about ASP. Basically, this leaves a connection always open, eliminating the overhead of resolving host, connecting, authenticating, switching to the proper database. Not really a lot of time saved but for a high-volume site it can make a difference; also may help if you are experiencing network latency. Is establishing a connection what you feel is slow? Or the transfer of data between your servers is what's slow? Dan On 8/15/06, Neil Tompkins [EMAIL PROTECTED] wrote: The connection time to my mySQL database is OK from the ASP page. Are there any ways to speed this up though ? From: [EMAIL PROTECTED] To: My.SQL mysql@lists.mysql.com@ashcomp.net Subject: RE: Access mySQL database across Internet Date: Fri, 11 Aug 2006 12:20:19 -0400 On Fri, 11 Aug 2006 15:02:12 +, Neil Tompkins wrote At the moment our mysql server hosted by an ISP, is updated every second by a program running on our local Internet connection from our own office network. We have seen no performance issues from our websites hosted by the same ISP running our mySQL server. That's fairly impressive, but I hope you mean that you only *consider* updating once per second, if actually necessary. It would be an awful lot of wasteful traffic if you actually had the two machines talking every seconed to transmit nothing. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access mySQL database across Internet
The problem appears to be retrieving the data which is slow. Date: Tue, 15 Aug 2006 11:32:23 -0500 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Access mySQL database across Internet CC: mysql@lists.mysql.com You could look into establishing persistent connections. Know it's possible with mod_perl and I believe PHP. Not sure about ASP. Basically, this leaves a connection always open, eliminating the overhead of resolving host, connecting, authenticating, switching to the proper database. Not really a lot of time saved but for a high-volume site it can make a difference; also may help if you are experiencing network latency. Is establishing a connection what you feel is slow? Or the transfer of data between your servers is what's slow? Dan On 8/15/06, Neil Tompkins [EMAIL PROTECTED] wrote: The connection time to my mySQL database is OK from the ASP page. Are there any ways to speed this up though ?From: [EMAIL PROTECTED] To: My.SQL mysql@lists.mysql.com@ashcomp.net Subject: RE: Access mySQL database across Internet Date: Fri, 11 Aug 2006 12:20:19 -0400 On Fri, 11 Aug 2006 15:02:12 +, Neil Tompkins wrote At the moment our mysql server hosted by an ISP, is updated every second by a program running on our local Internet connection from our own office network. We have seen no performance issues from our websites hosted by the same ISP running our mySQL server. That's fairly impressive, but I hope you mean that you only *consider* updating once per second, if actually necessary. It would be an awful lot of wasteful traffic if you actually had the two machines talking every seconed to transmit nothing. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d