After backup/restore view turns to table
Hi All ! I did mysqldump mydb -uroot -ppass --databases --add-locks --allow-keywords --create-options --extended-insert --routines -r file.sql and after that in MySQL Command Line Client source file.sql After that my view was converted to an empty table. What should I do to backup and restore my view correctly ? Regards, Dundo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup: Copy MYSQL Directory or use MysqlDump
On 1/23/07, Alex Arul [EMAIL PROTECTED] wrote: Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Alex is right. Depending on how you'll want to be able to restore, and how big your database is you may use different methods for backup. I usually use mysqldump along with bzip2 for maximum compression, but I have used tar in the past. cp -av retains permissions, making it easy for restore, but its not a good option, as you can't compress data nor restore pieces of information in an easy way. You'll have to test and choose from multiple options in this matter. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup: Copy MYSQL Directory or use MysqlDump
I used mysqlhotcopy and all is fine. Daniel da Veiga wrote: On 1/23/07, Alex Arul [EMAIL PROTECTED] wrote: Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Alex is right. Depending on how you'll want to be able to restore, and how big your database is you may use different methods for backup. I usually use mysqldump along with bzip2 for maximum compression, but I have used tar in the past. cp -av retains permissions, making it easy for restore, but its not a good option, as you can't compress data nor restore pieces of information in an easy way. You'll have to test and choose from multiple options in this matter. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8522321 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: Ms access front end and mysql back end
If the connection itself passes a test, but MS Access hangs, I don't know what to suggest. I'm not an expert, it just worked when I tried it. You use File... Get external data... Link tables, right? Is that where it hangs? Does it list the tables in your MySQL data base, or doesn't it even get that far? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Langson B.-Computer Specialist [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 23, 2007 4:27 AM To: Jerry Schwartz Subject: RE: Ms access front end and mysql back end My database is in MySQL on the server and I have 5 machines connected to the server. The front end is MS access. This is where I have forms, queries, reports and macros. The front end is installed on all the five work workstations. The server runs Windows Advanced 2000 server and the workstations run Windows XP. The problem is, I cannot connect to the server using any of the workstations. When I try to connect using myodbc, the system the system is able to connect to the server. But when I open MS access and then try to link to the server, MS access hangs. What is the solution? I would really appreciate If you could assist. Is there any other way I can connect to the database on the server. MS Access has limitation in terms of number of records. I have been having problems with constant hanging, working in MS Access (Both Front and Back end). That is the reason why I opted to convert MS Access Tables into Mysql. Can you please advise if I have to abandon MS access completely and go to another front end. __ __ Food fight? Enjoy some healthy debate in the Yahoo! Answers Food Drink QA. http://answers.yahoo.com/dir/?link=listsid=396545367 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New DBManager Released
Hi, DBTools Software is proud to announce the availability of the new DBManager Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2. For a complete list of changes please check our website at http://www.dbtools.com.br/EN. The new versions are available in our downloads center at http://www.dbtools.com.br/EN/downloads. What is DBTools Manager? DBTools manager is an application for database managementm supporting MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC Datasources. This is a simple list of its features: - Full object management - Query Builder with planning, debugging capabilities - Diagram Designer - Report and Form Builders - Lots of Wizards to import and export data/structure to/from a variety of sources - Database Documenter, Comparer and Migration Wizards - View, Procedure and Function Builder - English and Portuguese BR language resources available - and much more Available in two editions: Enteprise and Standard. The latest can be used for free for personal use. For a complete list of its features check http://www.dbtools.com.br/EN/dbmanagerpro/features.php. Best Regards, DBTools Software http://www.dbtools.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speeding up a join COUNT
I'm performance testing my 'users' table. It currently has roughly 1M user records. The 'geo_entities' table has ~ 250 records. Here's my query. SELECT users.entity_id, geo_entities.entity_name, geo_entities.short_code, COUNT( users.entity_id ) FROM users, geo_entities WHERE users.user_type = 'user' AND users.entity_id = geo_entities.id GROUP BY entity_id LIMIT 0 , 30 It took 51 seconds to execute. Both tables only have an index on their unique record id. Is there a way to speed up this up? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New DBManager Released
The free version is crippled. You don't get all the capabilities they say. COS [EMAIL PROTECTED] 1/23/07 8:16 AM Hi, DBTools Software is proud to announce the availability of the new DBManager Professional Enterprise Edition 3.4.2 and DBManager Standard Edition 3.2.2. For a complete list of changes please check our website at http://www.dbtools.com.br/EN. The new versions are available in our downloads center at http://www.dbtools.com.br/EN/downloads. What is DBTools Manager? DBTools manager is an application for database managementm supporting MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF Tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC Datasources. This is a simple list of its features: - Full object management - Query Builder with planning, debugging capabilities - Diagram Designer - Report and Form Builders - Lots of Wizards to import and export data/structure to/from a variety of sources - Database Documenter, Comparer and Migration Wizards - View, Procedure and Function Builder - English and Portuguese BR language resources available - and much more Available in two editions: Enteprise and Standard. The latest can be used for free for personal use. For a complete list of its features check http://www.dbtools.com.br/EN/dbmanagerpro/features.php. Best Regards, DBTools Software http://www.dbtools.com.br ( http://www.dbtools.com.br/ ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to put data on a different computer from mysql
I've been trying to put the data onto our data drive, and the mysql application on the main server. I've edited the my.ini file correctly. I can get it to work on different drives where the data is on the same computer as mysql. I can't get it to work where the data is on a different computer. Below is a copy of the my.ini. I've also tried it where my datadir=S:/mydata/data where I put the virtual drive letter in. No go. the service will not start. I'm using version 4.0.18-nt. I've also tried it with version 4.1.21-win32. Same result. Works on a different drive but both mysql and data must be on the same computer. Any thoughts? #This File was made using the WinMySQLAdmin 1.4 Tool #10/17/2005 10:37:37 AM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=C:/mysql #bind-address=127.0.0.1 datadir=//SOFTWOODSERVER/SharedDocs/mydata/data #language=C:/mysql/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M #set-variable=max_allowed_packet=3M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-nt.exe user=PTurk password=password -- Kelly Solakofski Softwood Technology Customer Service Manager 416-504-8724 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to put data on a different computer from mysql
On 1/23/07, Kelly Solakofski [EMAIL PROTECTED] wrote: I've been trying to put the data onto our data drive, and the mysql application on the main server. I've edited the my.ini file correctly. I can get it to work on different drives where the data is on the same computer as mysql. I can't get it to work where the data is on a different computer. AFAIK, Mysql refuses a remote datadir by design. You CAN use tricks to bypass this at Windows, and you surely won't have this problems with Linux if you configure something like NFS. You may Google for win solutions, but its just not advisable on a real system with some valuable data. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to put data on a different computer from mysql
[EMAIL PROTECTED] (Kelly Solakofski) wrote in news:[EMAIL PROTECTED]: I've been trying to put the data onto our data drive, and the mysql application on the main server. I've edited the my.ini file correctly. I can get it to work on different drives where the data is on the same computer as mysql. I can't get it to work where the data is on a different computer. Did you try the method outlined here: http://dev.mysql.com/doc/refman/4.1/en/windows-symbolic-links.html -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alias a function result?
I know that you can alias a function result as easily as anything else, but I've run into a problem with an application I inherited. For reasons too murky to go into, I would like to refer to the result of a group function as the name of the argument of the function. Here's an example of what I mean: SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY book_author WHERE book_title LIKE something; This actually seems to work, but it makes me ill to look at it. Is this legal, or have I found a loophole that might be closed in the future? I'm using server version 4.1.21-standard. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341
Re: Alias a function result?
Jerry Schwartz wrote: I know that you can alias a function result as easily as anything else, but I've run into a problem with an application I inherited. For reasons too murky to go into, I would like to refer to the result of a group function as the name of the argument of the function. Here's an example of what I mean: SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY book_author WHERE book_title LIKE something; This actually seems to work, but it makes me ill to look at it. Is this legal, or have I found a loophole that might be closed in the future? Perfectly legal, will not get changed. :) Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]