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.
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]
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]
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]
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
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
RE: Mysql database capacity
Hi Vishwas These and many other answers can be found here : http://dev.mysql.com/doc/refman/5.0/en/what-is.html There is no practical limitation on the row size apart from the number of fields x the size of these fields. eg. 1000 fields of varchar(255) or larger will take up that space. I don't know your table description so can't say. I have referenced the version 5.0 manual as I'm not sure of your version. How long to search a record in 1 billion rows? That is very much dependent on how you are searching, which index you are using etc. It could take as little as .01 of a second if you use the primary key to access the record directly. For the number of records, it is dependent on the size of the record. Unfortunately there is very little information in your email to be able to make a judgement. I would suggest you see the limitations in the url above and divide your record size into that. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, 9 January 2006 8:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql database capacity
I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql database capacity
Hi Sujay, Can you please post details like the my.cnf configs, how is the load on system like cpu, memory, disk usage etc. Thanx in advance --Alex On Mon, 09 Jan 2006 15:16:39 +0530, Sujay Koduri [EMAIL PROTECTED] wrote: I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql database capacity
Sujay , Tables : 1 (only 1) ::: Innodb or Myisam ? I see lots of updates happening on that table so , how frequent do you defragment the table . -- Thanks Praj On Mon, 9 Jan 2006 01:46:39 -0800 Sujay Koduri [EMAIL PROTECTED] wrote: I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Praj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL database design documentation
Hello, Two admin tools to check out if you haven't already... PHP, you can try PHPMyAdmin - http://www.phpmyadmin.net/home_page/index.php Non-PHP, try MySQL's GPL MySQL Administrator - http://dev.mysql.com/downloads/administrator/index.html However, they too may not be suitable for remote admin depending on your setup and security needs. Thanks, Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA Phone: (713) 636-9239 -Original Message- From: Maurice van Peursem [mailto:[EMAIL PROTECTED] Sent: Thursday, January 05, 2006 3:37 PM To: mysql@lists.mysql.com Subject: MySQL database design documentation Hi, I'm sure this is a stupid question, but I haven't been able to find it myself. Surely there must be a free PHP utility to web-administrate a MySQL database? I use CocoaMySQL (http://cocoamysql.sourceforge.net/) on my own Mac, but it isn't suitable for online databases. Can anyone lead me in the right direction? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
- Original Message - From: Maurice van Peursem [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 27, 2005 6:33 PM Subject: MySQL database design documentation Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? For the most part, _any_ good database design book for _any_ decent relational database should do the job for you. That's because all (?) of the professional grade databases use the same SQL and the same normalization techniques to decide what columns belong in what tables and what primary and foreign keys should be used. Therefore, a good design book for DB2 or Oracle or Sybase would probably tell you almost exactly the same things as a good design book specifically intended for MySQL. You will still need to use the MySQL reference to help you with places where the MySQL syntax is slightly different than the syntax used by the other database but this really shouldn't happen too often. However, if you want a design book specifically written for MySQL, you may want to look at http://www.informit.com/articles/article.asp?p=30885rl=1. I should stress that I don't have this book, nor have I read it cover to cover. But the sample chapter on database design is pretty good, so, if the rest of the book is as good, you should come out okay. In fact, you may find that the sample chapter alone, which you can read online for free, may tell you everything you really want to know and save you the cost of the book. No guarantees on that but it's a starting point anyway. By the way, I have not seen any other MySQL Design books so there may be others that are better. The URL I've given you actually mentions some other books specifically for MySQL that may suit your personal learning style better. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
A couple of good links for databases. Database Design (quick and dirty, but gets the points across): http://www.geekgirls.com/menu_databases.htm - the from scratch side SQL: Basics: http://www.sqlcourse.com (you probably already know this stuff - but just in case. semi-Advanced: http://sqlcourse2.com (joins are specifically at http://sqlcourse2.com/joins.html). Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- life is a game... so have fun. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
Relational Database Design Clearly Explained, Second Edition ISBN: 1558608206 The original edition was my first primer on relational databases. It was an excellent read. Ben Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database characterset
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 10:43 To: mysql@lists.mysql.com Subject: mysql database characterset Hi there, Is the mysql database always in UTF8 characterset for MySQL 4.1 and up? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Yep.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.7/70 - Release Date: 11/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database problems
Hello. with php scripts. Now I am getting the message Unable to load database indicated by configuration file or something similiar when trying to connect to any database running on the server when the mysql user is running @localhost. Please, send the exact error messages and warnings. If you are able to connect using mysql command line client then this is rather a php issue. Dwayne Hottinger [EMAIL PROTECTED] wrote: Greetings all, Im new to mysql and have inherited several mysql databases and everything has been going well until lately. Most of my webpages come from mysql databases with php scripts. Now I am getting the message Unable to load database indicated by configuration file or something similiar when trying to connect to any database running on the server when the mysql user is running @localhost. I can however login at terminal as the mysql user and look at the database with no problems. My mysql version is 3.23.58 (upgraded from yum), php version 4.3.10, server is Fedora Core 2 kernel 2.6.5-1.358smp. Im pretty new to mysql so be gentle and easy in any help. Everything was working fine prior to Friday of last week. Mysqld.log show nothing other than start and restarts that I initiated trying to get things working. thanks, ddh -- Dwayne Hottinger Network Administrator Harrisonburg City Public Schools -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database problems
Are your php scripts by any chance called phpWebSite? If so, you might find more help here: http://www.phpwsforums.com/ What you're getting is almost definitely a PHP script catching some kind of error trying to connect to the database server. You should have a file called configure.php or something similar. Make sure the database settings are correct. kgt Dwayne Hottinger wrote: Greetings all, Im new to mysql and have inherited several mysql databases and everything has been going well until lately. Most of my webpages come from mysql databases with php scripts. Now I am getting the message Unable to load database indicated by configuration file or something similiar when trying to connect to any database running on the server when the mysql user is running @localhost. I can however login at terminal as the mysql user and look at the database with no problems. My mysql version is 3.23.58 (upgraded from yum), php version 4.3.10, server is Fedora Core 2 kernel 2.6.5-1.358smp. Im pretty new to mysql so be gentle and easy in any help. Everything was working fine prior to Friday of last week. Mysqld.log show nothing other than start and restarts that I initiated trying to get things working. thanks, ddh -- Dwayne Hottinger Network Administrator Harrisonburg City Public Schools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database and user creation from script file
[EMAIL PROTECTED] wrote: Hi, I have to create user and database using script file. My requirements are given below. 1. Login as root 2. Execute the script file for database and user creation. 3. Exit My script file should have mysql -u root create database mnms; user creation command The script file will be called in Win batch file and the same batch file will be executed. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Perhaps reading the manual might be part of your exercise. Try http://dev.mysql.com/doc/mysql/en/index.html Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database and user creation from script file
The -e option with mysql allows you to run queries and would allow doing user creation etc as well. Aman Raheja [EMAIL PROTECTED] wrote: Hi, I have to create user and database using script file. My requirements are given below. 1. Login as root 2. Execute the script file for database and user creation. 3. Exit My script file should have mysql -u root create database mnms; user creation command The script file will be called in Win batch file and the same batch file will be executed. Thanks, Narasimha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database
[snip] Okay, extreme newbie. I probably just missed it but I went to the mysql site to download the database so that I could use it at work and I am having a problem finding it. The spot I expected it to be in there wasn't a link for it. Anybody got any ideas? [/snip] Try here http://dev.mysql.com/downloads/mysql/4.0.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database
I hope you went at least to www.mysql.com ;) - Second tab Developer Zone. - First tab Downloads from the second menubar. - Select version of your database - Select your OS - Pick a mirror - Have fun Rik Op donderdag 16 december 2004 14:28, schreef Serenity Schindler: Okay, extreme newbie. I probably just missed it but I went to the mysql site to download the database so that I could use it at work and I am having a problem finding it. The spot I expected it to be in there wasn't a link for it. Anybody got any ideas? ~Melanie~ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Database Designer
Have you tried DBDesigner4 by fabForce? Andreas Ahlenstorf wrote: Hi! I'm looking for a good graphical database designer, supporting the latest stable release of MySQL, MyISAM and InnoDB tables and foreign keys. So far there are a lot of products. But I need one, which runs on Windows and MacOS X. Do you have a good suggestion? Regards, Andreas -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] Global: www.volume4.com We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Database Designer
Schalk Neethling wrote: Have you tried DBDesigner4 by fabForce? I'm working with it at the moment. As far as I see, it doesn't work on Mac and unfortunately DBDesigner manages to trash my foreign keys definitions from time to time. Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup
David, - Alkuperäinen viesti - Lähettäjä: David Griffiths [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Tuesday, October 19, 2004 9:33 PM Aihe: Re: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup No worries about the late reply. We took down the master, took a hot backup from the slave (I still need to convert that 30-day license into a permanent one), moved it to the master, started the master, and then took a hot backup and re-initialized the slave. Took all of a few hours, and things are good. We did have some weird crashing issues with this machine while using an LSI RAID card (RAID 5) - ie creating an index killed mysql. We switched to a 3ware SATA card (almost as fast in RAID 0+1, and much cheaper even with wasting more disk space for mirroring) and the problems disappeared. good. Unfort, this corruption occurred about 4 months into setting up MySQL/Innodb - I hope we don't have to go through this every few months. Taking an additional backup from the slave should give us extra redundancy. Corruption and weird crashes could be the result of specific drivers/hardware and/or specific versions of Linux. Do you have any suggestions for tracking these issues, so that any platform/distro issues can be avoided (and hopefully addressed by OEMs and developers)?? I will forward your message to the Linux kernel mailing list. There is little hope of tracking down what exactly is wrong with Opteron platforms. There have been quite many corruption issues with the ordinary 32-bit x86, too. For an unknown reason, x86 corruption became less frequent around the kernel 2.4.20. You can try reporting this problem to your hardware and OS vendors. The following thread contains some virtual memory page fault patch for AMD/Opteron: http://www.ussg.iu.edu/hypermail/linux/kernel/0309.1/1091.html But I did not find information about what kind of bugs it can provoke in Linux. IBM has the Linux Test Project http://www.linuxtestproject.org. I think rigorous (and expensive) testing is the only way to improve the quality of Linux computers. David Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html Heikki Tuuri wrote: David, I am sorry for a late reply. The corruption clearly is in the ibdata file of the production database. InnoDB Hot Backup checks the page checksums when it copies the ibdata files. Since CHECK TABLE fails, the corruption probably is in that table. You can try to repair the corruption by dump + DROP + reimport of that table. innodb_force_recovery cannot fix any kind of corruption. InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127 The corruption has almost certainly happened in the OS or the hardware, because InnoDB checks page checksums before writing them to the ibdata files. Since the lsn stored at the page start differs from what is stamped at the page end, there is corruption at either end of the page. We have received quite a few reports of strange crashes in Opteron/Linux boxes. That suggests there are still OS bugs or hardware flaws in that platform. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html .. From: David Griffiths ([EMAIL PROTECTED]) Subject: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2004-09-30 12:23:37 PST I went to do some work on our database last night (dropping large indexes, which can be time consuming). I checked to ensure that the backup of that evening had run, but noticed that the size of the backup was too small compared to previous days (I'm kicking myself for not emailing the results of the backup to myself every night - I just have a job that verifies that the backup actually ran). So I ran the backup by hand. We have 8 data files, the first 7 being 4 gig in size, and the last being a 10-meg autoextend. This is MySQL 4.0.20 64bit, running on a dual Opteron machine running SuSE 8 Enterprise (64-bit). We are using ibbackup 2.0 beta (which is 64-bit for the Opteron). ibbackup (the Innodb backup utility) complains on the first file. ibbackup: Re-reading page at offset 0 3272818688 in /usr/local/mysql/var/ywdata1 this repeats a few hundred times Then it dumps some ascii: 040930 11:44:14 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex
Re: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup
David, I am sorry for a late reply. The corruption clearly is in the ibdata file of the production database. InnoDB Hot Backup checks the page checksums when it copies the ibdata files. Since CHECK TABLE fails, the corruption probably is in that table. You can try to repair the corruption by dump + DROP + reimport of that table. innodb_force_recovery cannot fix any kind of corruption. InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127 The corruption has almost certainly happened in the OS or the hardware, because InnoDB checks page checksums before writing them to the ibdata files. Since the lsn stored at the page start differs from what is stamped at the page end, there is corruption at either end of the page. We have received quite a few reports of strange crashes in Opteron/Linux boxes. That suggests there are still OS bugs or hardware flaws in that platform. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html .. From: David Griffiths ([EMAIL PROTECTED]) Subject: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2004-09-30 12:23:37 PST I went to do some work on our database last night (dropping large indexes, which can be time consuming). I checked to ensure that the backup of that evening had run, but noticed that the size of the backup was too small compared to previous days (I'm kicking myself for not emailing the results of the backup to myself every night - I just have a job that verifies that the backup actually ran). So I ran the backup by hand. We have 8 data files, the first 7 being 4 gig in size, and the last being a 10-meg autoextend. This is MySQL 4.0.20 64bit, running on a dual Opteron machine running SuSE 8 Enterprise (64-bit). We are using ibbackup 2.0 beta (which is 64-bit for the Opteron). ibbackup (the Innodb backup utility) complains on the first file. ibbackup: Re-reading page at offset 0 3272818688 in /usr/local/mysql/var/ywdata1 this repeats a few hundred times Then it dumps some ascii: 040930 11:44:14 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 55c3ee4d00030c4d00030c4c000374. And at the bottom, 040930 11:44:14 InnoDB: Page checksum 1522485550, prior-to-4.0.14-form checksum 1015768137 InnoDB: stored checksum 1438903885, prior-to-4.0.14-form stored checksum 4028531590 InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127 InnoDB: Page number (if stored to page already) 199757, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 680 ibbackup: Error: page at offset 0 3272818688 in /usr/local/mysql/var/ywdata1 seems corrupt! While we no longer seem to have a backup, we do have a slave (not sure if the corruption propigated to the slave; I know it can happen in Oracle). I have a few questions: 1) Is InnoDB backup correct? This might be a false positive (doubt it though). 2) What are the risks of stopping and starting the database? There is a force-recovery option in inndb, which might fix the corruption. Note that I answered this myself. I ran a check table on one of our larger tables (600,000 rows) which killed the database. It came back up fine. I re-ran the backup - same issue, with the same page checksums, etc. 3) Anyone have any experience with this? Keep in mind that this might be an Opteron/MySQL-64bit issue. Or it might be a general issue in MySQL. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup
No worries about the late reply. We took down the master, took a hot backup from the slave (I still need to convert that 30-day license into a permanent one), moved it to the master, started the master, and then took a hot backup and re-initialized the slave. Took all of a few hours, and things are good. We did have some weird crashing issues with this machine while using an LSI RAID card (RAID 5) - ie creating an index killed mysql. We switched to a 3ware SATA card (almost as fast in RAID 0+1, and much cheaper even with wasting more disk space for mirroring) and the problems disappeared. Unfort, this corruption occurred about 4 months into setting up MySQL/Innodb - I hope we don't have to go through this every few months. Taking an additional backup from the slave should give us extra redundancy. Corruption and weird crashes could be the result of specific drivers/hardware and/or specific versions of Linux. Do you have any suggestions for tracking these issues, so that any platform/distro issues can be avoided (and hopefully addressed by OEMs and developers)?? David Heikki Tuuri wrote: David, I am sorry for a late reply. The corruption clearly is in the ibdata file of the production database. InnoDB Hot Backup checks the page checksums when it copies the ibdata files. Since CHECK TABLE fails, the corruption probably is in that table. You can try to repair the corruption by dump + DROP + reimport of that table. innodb_force_recovery cannot fix any kind of corruption. InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127 The corruption has almost certainly happened in the OS or the hardware, because InnoDB checks page checksums before writing them to the ibdata files. Since the lsn stored at the page start differs from what is stamped at the page end, there is corruption at either end of the page. We have received quite a few reports of strange crashes in Opteron/Linux boxes. That suggests there are still OS bugs or hardware flaws in that platform. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html .. From: David Griffiths ([EMAIL PROTECTED]) Subject: MySQL Database Corruption (InnoDB), according to Innodb Hot Backup This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2004-09-30 12:23:37 PST I went to do some work on our database last night (dropping large indexes, which can be time consuming). I checked to ensure that the backup of that evening had run, but noticed that the size of the backup was too small compared to previous days (I'm kicking myself for not emailing the results of the backup to myself every night - I just have a job that verifies that the backup actually ran). So I ran the backup by hand. We have 8 data files, the first 7 being 4 gig in size, and the last being a 10-meg autoextend. This is MySQL 4.0.20 64bit, running on a dual Opteron machine running SuSE 8 Enterprise (64-bit). We are using ibbackup 2.0 beta (which is 64-bit for the Opteron). ibbackup (the Innodb backup utility) complains on the first file. ibbackup: Re-reading page at offset 0 3272818688 in /usr/local/mysql/var/ywdata1 this repeats a few hundred times Then it dumps some ascii: 040930 11:44:14 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 55c3ee4d00030c4d00030c4c000374. And at the bottom, 040930 11:44:14 InnoDB: Page checksum 1522485550, prior-to-4.0.14-form checksum 1015768137 InnoDB: stored checksum 1438903885, prior-to-4.0.14-form stored checksum 4028531590 InnoDB: Page lsn 3 1070601164, low 4 bytes of lsn at page end 1070609127 InnoDB: Page number (if stored to page already) 199757, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 680 ibbackup: Error: page at offset 0 3272818688 in /usr/local/mysql/var/ywdata1 seems corrupt! While we no longer seem to have a backup, we do have a slave (not sure if the corruption propigated to the slave; I know it can happen in Oracle). I have a few questions: 1) Is InnoDB backup correct? This might be a false positive (doubt it though). 2) What are the risks of stopping and starting the database? There is a force-recovery option in inndb, which might fix the corruption. Note that I answered this myself. I ran a check table on one of our larger tables (600,000 rows) which killed the database. It came back up fine. I re-ran the backup - same issue, with the same page checksums, etc. 3) Anyone have any experience with this? Keep in mind that this might be an Opteron/MySQL-64bit issue. Or it might be a general issue in MySQL. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: MYSQL Database
Which version of PHP would be good for Red hat 7.3? can someone send URL from where I can download the php pl? Jochem van Dieten [EMAIL PROTECTED] wrote:Douglas Sims wrote: You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) Or Linux/Apache/Middleware/PostgreSQL ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
Re: MySql database design.
You can find a number of good starting point data models here: http://www.databaseanswers.com/data_models You might also check out some of the applications on sites like www.hotscripts.com and www.sourceforge.net to see how they have structured their database for similar projects. (or maybe you will find something ready to use). olinux --- Brian Duke [EMAIL PROTECTED] wrote: I need a little help in constructing an order tracking database. We've decided to use MySQL mostly because it's the best supported database out in the wild. Does anyone have an example of an order tracking datamap? A link to a site with the basic flowchart would be a great help. The application is an everyday drycleaner shop. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
Hi You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) are becoming the de facto standards for web-based applications, I think far eclipsing Java (JSP/Servlets) and Microsoft ASP/VB. Unlike Java (which is driven to a large degree by Sun's promotion) and ASP (heavily promoted by MS), LAMP has become so widespread because it's just really good (and cheap).http://news.netcraft.com/archives/web_server_survey.html I personally prefer to program in Perl, which is The Coolest Language Ever Invented, although Java has advantages. C/C++ for server-side programming are great if you have lots of money and time and are concerned handling massive amounts of traffic. ASP (Visual Basic) is really terrible. Although I do a lot of work in it, I don't like it. It does not have the same semantic versatility of C-based languages like Perl. And regular expressions in VB are a heinous pastiche of the true elegance of regular expressions in Perl. I'm sure many people will disagree vociferously with my opinions here and they may have good points also, which I have neglected. Programming languages are like indentation styles - you can do a very fine job with different ones, and yet most people become very particular about their own styles and hate working with others. One might also dispute my argument that LAMP is far more widespread than ASP or Java as the survey I cited doesn't really consider server-side programming language, just servers, but I suspect far more people are running mysql/[php|perl] on linux than anything else and the server-side languages used probably mirror this. Perhaps someone else can offer better statistics. In short, I would use Linux/Apache/MySQL/Perl. Now I'm afraid I will have roused the VB or Java crowds. Perhaps I should sign this with an assumed name? /Alfred E. Neuman/ Seena Blace wrote: Hi, I'm new to this group.I would like to know which frontend tools be good tuned with Mysql database like php,perl etc? I want to develop one application on linux on mysql database which eventually would be webbased.Please suggest what combination would be good. thx -Seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
I'm a java person and I'm happy to say you didn't rouse me. There are many fine quality in lamp and java. I don't know PHP but I've seen some really nice apps written in php. It looks like a nice clean language and very nice for web development. On Tuesday, January 20, 2004, at 03:34 PM, Douglas Sims wrote: Hi You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) are becoming the de facto standards for web-based applications, I think far eclipsing Java (JSP/Servlets) and Microsoft ASP/VB. Unlike Java (which is driven to a large degree by Sun's promotion) and ASP (heavily promoted by MS), LAMP has become so widespread because it's just really good (and cheap).http://news.netcraft.com/archives/web_server_survey.html I personally prefer to program in Perl, which is The Coolest Language Ever Invented, although Java has advantages. C/C++ for server-side programming are great if you have lots of money and time and are concerned handling massive amounts of traffic. ASP (Visual Basic) is really terrible. Although I do a lot of work in it, I don't like it. It does not have the same semantic versatility of C-based languages like Perl. And regular expressions in VB are a heinous pastiche of the true elegance of regular expressions in Perl. I'm sure many people will disagree vociferously with my opinions here and they may have good points also, which I have neglected. Programming languages are like indentation styles - you can do a very fine job with different ones, and yet most people become very particular about their own styles and hate working with others. One might also dispute my argument that LAMP is far more widespread than ASP or Java as the survey I cited doesn't really consider server-side programming language, just servers, but I suspect far more people are running mysql/[php|perl] on linux than anything else and the server-side languages used probably mirror this. Perhaps someone else can offer better statistics. In short, I would use Linux/Apache/MySQL/Perl. Now I'm afraid I will have roused the VB or Java crowds. Perhaps I should sign this with an assumed name? /Alfred E. Neuman/ Seena Blace wrote: Hi, I'm new to this group.I would like to know which frontend tools be good tuned with Mysql database like php,perl etc? I want to develop one application on linux on mysql database which eventually would be webbased.Please suggest what combination would be good. thx -Seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
Douglas Sims wrote: You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) Or Linux/Apache/Middleware/PostgreSQL ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
Lynch 'im!!! :-) On Wed, 21 Jan 2004 13:33, Jochem van Dieten wrote; Douglas Sims wrote: You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) Or Linux/Apache/Middleware/PostgreSQL ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sam Vilain, [EMAIL PROTECTED] Only the ignorant man becomes angry. The wise man understands. --Indian wisdom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
On 9 Jan 2004, at 22:43, Michael Stassen wrote: As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. How does MySQL decide which entry to use when authenticating? Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? Perhaps it's the more exact match? i.e. 192.168.% But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' What if there are two entries - 'localhost' and '127.0.0.1' ? -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
Steve Folly wrote: On 9 Jan 2004, at 22:43, Michael Stassen wrote: As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. How does MySQL decide which entry to use when authenticating? This is documented in the manual http://www.mysql.com/doc/en/Connection_access.html. The basic idea is that mysql sorts the user table from most specific to least, with host taking precedence over user. Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? As I understand it, 192.168.% is more specific than 192.%, so 192.168.100.12 would match 192.168.% Perhaps it's the more exact match? i.e. 192.168.% That's my understanding. But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' Well, I can't imagine why you would put %.168.% in for host. If you did, I think 192.% would be more specific than %.168.%, but the manual is unclear on that. I suppose you could try it and see. What if there are two entries - 'localhost' and '127.0.0.1' ? To mysql, those are not the same. localhost is a unix socket connection, 127.0.0.1 is a TCP/IP connection. So, mysql -u username -p would connect as [EMAIL PROTECTED], but mysql -h 127.0.0.1 -u username -p would connect as [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
On 10 Jan 2004, at 17:47, Michael Stassen wrote: Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? As I understand it, 192.168.% is more specific than 192.%, so 192.168.100.12 would match 192.168.% My thoughts exactly. But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' Well, I can't imagine why you would put %.168.% in for host. If you did, I think 192.% would be more specific than %.168.%, but the manual is unclear on that. I suppose you could try it and see. True, I can't imagine why you would want to use %.168.% either; I was just curious. I've just tried it myself... (OK, so I was lazy before! :) - MySQL appears to prefer 192.% over %.168.% What if there are two entries - 'localhost' and '127.0.0.1' ? To mysql, those are not the same. localhost is a unix socket connection, 127.0.0.1 is a TCP/IP connection. So, mysql -u username -p would connect as [EMAIL PROTECTED], but mysql -h 127.0.0.1 -u username -p would connect as [EMAIL PROTECTED] Makes sense. Thanks very much; I was just curious! Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database, user table, two root accounts
That's not two root accounts. What that means is this: The first line defines privileges for root connecting from localhost The second line defines privileges for root connecting from any remote host. Hence the %. It implies [EMAIL PROTECTED] Hope this helps. Arjun Subramanian Georgia Tech Station 32003 Atlanta GA 30332 Cell: +404.429.5513 http://www.arjunweb.com -Original Message- From: Leo Donahue [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:47 PM To: [EMAIL PROTECTED] Subject: mysql database, user table, two root accounts I am less than 24 hours new to MySql. I have executed the following sql scripts: use mysql; delete from user where User=''; delete from db where User=''; flush privileges; select host, user, password from user; The last sql query yields the following: hostuserpassword - localhost roothexadecimal values. % rootnothing here. Why are there two root accounts? Thanks, ld -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database, user table, two root accounts
Yes, this helps thank you. -Original Message- From: Arjun Subramanian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:16 PM To: 'Leo Donahue'; [EMAIL PROTECTED] Subject: RE: mysql database, user table, two root accounts That's not two root accounts. What that means is this: The first line defines privileges for root connecting from localhost The second line defines privileges for root connecting from any remote host. Hence the %. It implies [EMAIL PROTECTED] Hope this helps. Arjun Subramanian Georgia Tech Station 32003 Atlanta GA 30332 Cell: +404.429.5513 http://www.arjunweb.com -Original Message- From: Leo Donahue [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:47 PM To: [EMAIL PROTECTED] Subject: mysql database, user table, two root accounts I am less than 24 hours new to MySql. I have executed the following sql scripts: use mysql; delete from user where User=''; delete from db where User=''; flush privileges; select host, user, password from user; The last sql query yields the following: hostuserpassword - localhost roothexadecimal values. % rootnothing here. Why are there two root accounts? Thanks, ld -- 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: mysql database, user table, two root accounts
As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. In any case, the [EMAIL PROTECTED] entry you quoted below has no password! To be safe, you should immediately assign it a password or drop it. See http://www.mysql.com/doc/en/Privileges.html and http://www.mysql.com/doc/en/User_Account_Management.html for more. Michael Leo Donahue wrote: Yes, this helps thank you. -Original Message- From: Arjun Subramanian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:16 PM To: 'Leo Donahue'; [EMAIL PROTECTED] Subject: RE: mysql database, user table, two root accounts That's not two root accounts. What that means is this: The first line defines privileges for root connecting from localhost The second line defines privileges for root connecting from any remote host. Hence the %. It implies [EMAIL PROTECTED] Hope this helps. Arjun Subramanian Georgia Tech Station 32003 Atlanta GA 30332 Cell: +404.429.5513 http://www.arjunweb.com -Original Message- From: Leo Donahue [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 2:47 PM To: [EMAIL PROTECTED] Subject: mysql database, user table, two root accounts I am less than 24 hours new to MySql. I have executed the following sql scripts: use mysql; delete from user where User=''; delete from db where User=''; flush privileges; select host, user, password from user; The last sql query yields the following: hostuserpassword - localhost roothexadecimal values. % rootnothing here. Why are there two root accounts? Thanks, ld -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database gets slower over time
At 14:35 -0700 7/8/03, Steve Quezadas wrote: have you done any optimize to your tables ? it should help improving the query performance. it's normal to have memory lower on each day, because your index files are growing, and takes memory. if you're not using innodb or bdb, you can try to run flush threads and flush tables. it might release some memory. ;-) Yeah, that's the thing, the tables don't change. I add maybe 50,000 records in total every three months, but that's about it. Also, no one USES the databse. only like one a day for a few queries. I am thinking that redhat linux, and not mysql, is the culprit since the top reveals that the buffer size is increasing in memory. It's just weird that it gets slower over time, I suspect it's due to RAM. I am using myISAM tables. Is that innodb or bdb? Neither. MyISAM, InnoDB, and BDB are three different storage engines supported by MySQL. - Steve -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database dump
lol of course mysqldump -u username -p databasename databasename.sql -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 3:08 PM To: [EMAIL PROTECTED] Subject: mysql database dump Can I dump a database while the database is running in mysql ? -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database dump
At 1:08 -0400 6/28/03, Asif Iqbal wrote: Can I dump a database while the database is running in mysql ? Yes. You can, for example, use the mysqldump program. mysqldump is a MySQL client program that, like all MySQL clients, requires the server to be running. -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database dump (remotely, How?)
On Sat, Jun 28, 2003 at 06:14:07PM +1000, electroteque wrote: lol of course mysqldump -u username -p databasename databasename.sql I am atempting to backup a database remotely. I added something like the following. mysql GRANT ALL PRIVILEGES ON db.* - TO david@'192.58.197.0/255.255.255.0'; And have access to the database from my home computer now but when I use mysqldump -h hostname -A -p mysql.sql it dumps only a part of the backup and closes. Any clues as to what is going on or what I need to do? I could always have it backup on the server and scp it to my computer but hardheaded me would rather do it simply by dumping it from the remote computer to my home puter via mysqldump :). Thanks for any help. -- Jerry M. Howell II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database dump (remotely, How?)
On Sat, Jun 28, 2003 at 11:35:54AM -0600, Jerry M. Howell II wrote: On Sat, Jun 28, 2003 at 06:14:07PM +1000, electroteque wrote: lol of course mysqldump -u username -p databasename databasename.sql I am atempting to backup a database remotely. I added something like the following. mysql GRANT ALL PRIVILEGES ON db.* - TO david@'192.58.197.0/255.255.255.0'; And have access to the database from my home computer now but when I use mysqldump -h hostname -A -p mysql.sql it dumps only a part of the backup and closes. Any clues as to what is going on or what I need to do? I could always have it backup on the server and scp it to my computer but hardheaded me would rather do it simply by dumping it from the remote computer to my home puter via mysqldump :). Thanks for any help. Nevermind, I found the issue. Forgot to grant myself permisions to access, insert modify, etc the tables. -- Jerry M. Howell II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database - followup
Your spelling of PRIVILEGES is also spelled wrong =) specifying GRANT ALL PRIVILEGES on *.* to a user will make that user a member of the superuser group such as mysql, just without the GRANT OPTIONS, meaning that user's login wouldn't be able to GRANT other users. if you want to specify the database just do a mydb.* meaning all tables within the mydb database. then, mysqlflush privileges; i hope that helps. Respectfully yours, Sherwin T. Ang Systems Administrator Tridel Technologies Incorporated 7F Hanston Building Emerald Avenue, Ortigas Center Pasig City 1605 Philippines Phone: 6345140 Local 1024 Web: http://www.tridel.net Email: [EMAIL PROTECTED] - Original Message - From: Toto Gamez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 2:11 PM Subject: MYSQL Database i got this mesage when creating a database for twig, I just followed the instruction in twig manual on how to create a database but resulted to this [EMAIL PROTECTED] mysql]# mysql -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE DATABASE maildb; Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02; ERROR 1064: You have an error in your SQL syntax near 'PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
Do it like this: mysqlGRANT ALL PRIVELEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mikaela02'; You forgot the hostname which is the @hostname is for Respectfully yours, Sherwin T. Ang Systems Administrator Tridel Technologies Incorporated 7F Hanston Building Emerald Avenue, Ortigas Center Pasig City 1605 Philippines Phone: 6345140 Local 1024 Web: http://www.tridel.net Email: [EMAIL PROTECTED] - Original Message - From: Toto Gamez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 2:11 PM Subject: MYSQL Database i got this mesage when creating a database for twig, I just followed the instruction in twig manual on how to create a database but resulted to this [EMAIL PROTECTED] mysql]# mysql -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE DATABASE maildb; Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02; ERROR 1064: You have an error in your SQL syntax near 'PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database on a Linux ramdisk partition?
Hi, Is it possible to place MySQL data directories on a Linux ramdisk mount? Oh yes. No fragmentation aftertaste. Regards, Gabriel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Design
Hi Brian,
By no means am I a MySQL guru (or any other database server environment
guru, for that matter), but could you simply have a reference table that
indicates the percentage of the grape used in the relevant wine?
So, you might have three tables, Wines, GrapeVariety,
Wines_GrapeVarietiesUsed (or whatever).
In Wines, you record the details of the wine in question. In
GrapeVariety you record the details of the different grapes. In
Wines_GrapeVarietiesUsed, you record the key from the Wine table, the
key from the GrapeVariety table, and the percentage of the grape
variety.
As an example:
Wines:
Wineid, winename, winedescription
1, wine1, wine description 1
2, wine2, wine description 2
3, wine3, wine description 3
GrapeVariety:
Varietyid, varietyname, varietydescription
1, variety1, variety description 1
2, variety2, variety description 2
3, variety3, variety description 3
And then in Wines_GrapeVarietiesUsed:
Wineid, varietyid, percentage
1, 1, 1
2, 2, 1
3, 1, 0.2
3, 2, 0.3
3, 3, 0.5
In this example we have 3 bottles of wine and three varieties. Wines 1
2 use 100 percent (i.e. 1) of varieties 1 and 2 respectively, whereas
wine 3 uses all three grape varieties with 20 percent of variety1, 30
percent of variety2 and 50 percent of variety3.
To perform a query that would depict all of this in a single resultset,
you might do something like:
SELECT w.winename, w.winedescription, v.varietyname,
v.varietydescription, gv.percentage FROM wines w, GrapeVariety v,
Wines_GrapeVarietiesUsed gv WHERE w.wineid = gv.wineid AND v.varietyid =
gv.varietyid;
What this delivers is a recordset that looks something like:
'wine1','wine description 1','variety1','variety description 1','1'
'wine2','wine description 2','variety2','variety description 2','1'
'wine3','wine description 3','variety1','variety description 1','0.2'
'wine3','wine description 3','variety2','variety description 2','0.3'
'wine3','wine description 3','variety3','variety description 3','0.5'
Below are the CREATE TABLE and INSERT INTO statements I used to build
this example.
Hope this helps a little,
All the best,
MW
CREATE TABLE Wines (wineid INT(10) unsigned NOT NULL auto_increment,
winename VARCHAR(100), winedescription TEXT, PRIMARY KEY (`wineid`))
TYPE = MYISAM;
CREATE TABLE GrapeVariety (varietyid INT(10) unsigned NOT NULL
auto_increment, varietyname VARCHAR(100), varietydescription TEXT,
PRIMARY KEY (`varietyid`)) TYPE = MYISAM;
CREATE TABLE Wines_GrapeVarietiesUsed (wineid INT(10) unsigned NOT NULL,
varietyid INT(10) unsigned NOT NULL, percentage float NOT NULL default
'0') TYPE=MYISAM;
INSERT INTO wines (winename, winedescription) VALUES ('wine1', 'wine
description 1');
INSERT INTO wines (winename, winedescription) VALUES ('wine2', 'wine
description 2');
INSERT INTO wines (winename, winedescription) VALUES ('wine3', 'wine
description 3');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety1', 'variety description 1');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety2', 'variety description 2');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety3', 'variety description 3');
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (1,1,1);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (2,2,2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,1,.2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,2,.3);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,3,.5);
-Original Message-
From: Colaluca, Brian [mailto:[EMAIL PROTECTED]]
Sent: Friday, 10 January 2003 6:56 AM
To: [EMAIL PROTECTED]
Subject: MySQL Database Design
I am in the midst of designing a personal database for keeping track of
wines. After perusing through several beginner books on MySQL and PHP,
I
have decided that my next step would be to embark upon database design.
My design is almost complete and normalized, although I do expect to be
making many tweakings as my knowledge progresses.
I have come to a brick wall on one facet of my design, however. I've
come
to understand that having a lot of NULLs in your database may be a sign
of a
poor design. And yet I'm having a problem reconciling this with the
wildly
un-uniform world of wines from around the world. For instance, I would
like
to have a table called GrapeVariety, and have the variety_id be a
primary
key. Another table would be Wine. And yet, one wine could have one
type
of grape or more.
For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB.
Since
my grape variety would presumably be a foreign key in the Wine table,
how
could I specify a certain *percentage* of a foreign key? I've tried
hashing
this out in numerous ways, including the addition of a Blend table
with
multiple primary keys, but anyway I slice it, there will still be an
abundance of NULLs. For
RE: MySQL Database Design
i was playing around with ideas below, so they may be worthless chatter :-) what i was thinking I'll just assume the wine world is not wildly un-uniform... and see where that gets me... :-) and wine is just an object... but the wine has many grape varieties sounds a hell of a lot like one to many in sql thanks Jim ===start chatter n = null anything else = not null all attributes analysis a1 a2 a3 a4 a5 a6 a7 wine1 x n x n x n x wine2 y x x n n x n wine3 z x x x x n n 8 nulls common attributes table parent ID a1 a3 wine1 x x wine2 y x wine3 z x special attributes table 1 child0 ID a5 a7 wine1 x x wine3 x n special attributes table 2 child1 ID a2 a6 wine2 x x wine3 x n special attributes table 3 child2 ID a4 wine3 x 2 nulls ===end chatter -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 6:09 PM To: Colaluca, Brian Cc: [EMAIL PROTECTED] Subject: Re: MySQL Database Design Colaluca, Brian wrote: I have come to a brick wall on one facet of my design, however. I've come to understand that having a lot of NULLs in your database may be a sign of a poor design. And yet I'm having a problem reconciling this with the wildly un-uniform world of wines from around the world. For instance, I would like to have a table called GrapeVariety, and have the variety_id be a primary key. Another table would be Wine. And yet, one wine could have one type of grape or more. Just an idea ... to get your head spinning (and some sample queries): Wine - ID int unsigned not null auto_increment primary key, Name ... Winery ... Grapes - ID int unsigned not null auto_increment primary key, Name ... Vineyard? ... GrapesInWine - WineID int unsigned not null, GrapesID int unsigned not null, Percentage int unsigned not null ... where Percentage is between 0 and 100. Then you can, to insert a wine named Foo with 50% of each Grape1 and Grape2: INSERT INTO Wine (Name) VALUES (Foo); SELECT @WinesID := last_insert_id();# I'm using server variables here for the sake of demo ... INSERT INTO Grapes (Name) VALUES (Grape1); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, @GrapesID, 50); INSERT INTO Grapes (Name) VALUES (Grape2); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, @GrapesID, 50); Then, to find out what's in the wine named Foo: SELECT * FROM Grapes LEFT JOIN GrapesInWine ON Grapes.ID = GrapesID LEFT JOIN Wine ON WinesID = Wine.ID WHERE Wine.Name = Foo; Or, to find the amounts of Grape1 in all wines: SELECT * FROM Wine LEFT JOIN GrapesInWine ON WineID = Wine.ID LEFT JOIN Grapes ON Grapes.ID = GrapesID WHERE Grapes.Name = Grape1; -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Design
Blend will be a cross reference with a one to many relationship This is very simplified but an example of your data could be: Select * from Wine; ++--+ | WineID | WineName | ++--+ | 1 | XYZ | ++--+ Select * from Grape; +-+---+ | GrapeID | GrapeName | +-+---+ | 1 | GrapeA| | 2 | GrapeB| +-+---+ Select * from Blend; +-++-++ | BlendID | WineID | GrapeID | Percentage | +-++-++ | 1 | 1 | 1 | 80 | | 2 | 1 | 2 | 20 | +-++-++ This is obviuously very simplified, just trying to give you a quick response so you can move ahead in your development without being stuck on this. There might be some disagreement on the naming conventions I have illustrated. Use what you like best. -Original Message- From: Colaluca, Brian [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 12:56 PM To: [EMAIL PROTECTED] Subject: MySQL Database Design For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB. Since my grape variety would presumably be a foreign key in the Wine table, how could I specify a certain *percentage* of a foreign key? I've tried hashing this out in numerous ways, including the addition of a Blend table with multiple primary keys, but anyway I slice it, there will still be an abundance of NULLs. For while the majority of wines may only contain one grape, there could be wines that have up to 5 or 6 in varying percentages. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Database Design
Colaluca, Brian wrote: I have come to a brick wall on one facet of my design, however. I've come to understand that having a lot of NULLs in your database may be a sign of a poor design. And yet I'm having a problem reconciling this with the wildly un-uniform world of wines from around the world. For instance, I would like to have a table called GrapeVariety, and have the variety_id be a primary key. Another table would be Wine. And yet, one wine could have one type of grape or more. Just an idea ... to get your head spinning (and some sample queries): Wine - ID int unsigned not null auto_increment primary key, Name ... Winery ... Grapes - ID int unsigned not null auto_increment primary key, Name ... Vineyard? ... GrapesInWine - WineID int unsigned not null, GrapesID int unsigned not null, Percentage int unsigned not null ... where Percentage is between 0 and 100. Then you can, to insert a wine named Foo with 50% of each Grape1 and Grape2: INSERT INTO Wine (Name) VALUES (Foo); SELECT @WinesID := last_insert_id();# I'm using server variables here for the sake of demo ... INSERT INTO Grapes (Name) VALUES (Grape1); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, @GrapesID, 50); INSERT INTO Grapes (Name) VALUES (Grape2); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, @GrapesID, 50); Then, to find out what's in the wine named Foo: SELECT * FROM Grapes LEFT JOIN GrapesInWine ON Grapes.ID = GrapesID LEFT JOIN Wine ON WinesID = Wine.ID WHERE Wine.Name = Foo; Or, to find the amounts of Grape1 in all wines: SELECT * FROM Wine LEFT JOIN GrapesInWine ON WineID = Wine.ID LEFT JOIN Grapes ON Grapes.ID = GrapesID WHERE Grapes.Name = Grape1; -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql database replication
I highly recommend you update the slave to the same version as the master. Mark D. Wallace wrote: I'm trying to set up a replication database and cannot get the slave to parse the log files on the master. The slave is connecting to the master as indicated in the log file. I have tried to connect directly to the master database from the mysql client on the slave and have no problem using the replication username and password. The master server is mysql-3.23.37-pc-linux-gnu-i686 The slave server is mysql mysql-3.23.51-pc-linux-gnu-i686 From the Mysql site, these two are compatible as a slave master pair. The error message from the slaves error log reads. 14:28:38 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 020813 14:28:38 Could not parse log event entry, check the master for binlog corruption This may also be a network problem, or just a bug in the master or slave code. (WHAT IS THE NAME OF THE LOG FILE THE SLAVE IS TRYING TO READ mysql.log?) 020813 14:28:38 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'FIRST' position 4 020813 14:28:38 Slave thread exiting, replication stopped in log 'FIRST' at position 4 WHAT is log 'FIRST' at position 4 referencing? Thanks for any help you can provide. Mark Wallace - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database design, one column, 10 entries?
You need to add an additional table, favorites. It should have three columns: favoriteID INT, userID INT, favoriteTypeID INT REFERENCES FavoriteType. Plus the additional column for the rating, assuming it's associated with a favorite. Each row represents a single preference (this way users can have an arbitrary number of preferences). Each user would be associated with multiple rows in the table. The favoriteID is so that you can delete/update favorites easily; you want to be able to talk about a particular row. You might want a SELECT like this: SELECT * FROM favorites WHERE userID = $id ORDER BY rating DESC LIMIT 0,10 to get the top ten favorites for user identified by $id. You might also want to make an additional table, favoriteType, unless each favorite is completely unique (in which case the third column in the table above would have VARCHAR or TEXT type). (You should avoid that kind of design if possible, because most likely anything you want to rank is going to have similarities across users) favoriteTypeID INT favoriteLabel VARCHAR You should do some research on normalization and database design. There are some good books on the topic. Quinten On Sun, 11 Aug 2002, david wrote: Date: Sun, 11 Aug 2002 00:43:14 -0400 From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL database design, one column, 10 entries? I am creating several tables in MySQL and linking via primary keys. I am held up on one issue, for one row in one table i have a column 'favorites' where i want to hold up to 10 unique entries, how do i implement this? userTable userId varchar(20) name varchar(30) email varchar(40) preferences userId varchar(20) styles varchar(20) favorites preferences userId styles favorites rating 01 'modern''#1 sleek' 15 '#2 ultra-sleek'20 '#3 un-sleek' 12 '#4 plain' 9 etc, up to 10 or so. 02 'gothic''#1 dark' 21 '#2 tall' 4 '#3 scary' 2 etc, etc,... I can't just make it a really long varchar(1000) because of the other columns that act on the same data. I hope that this is an appropriate question for this MySQL list. TIA -david - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database design
At 8/11/2002, you wrote: MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Hi Lloyd, Spending plenty of time designing the database is be the best decision you can make. If the base is solid and flexible you'll end up with a solid and flexible application and not rewriting database structure will every small feature request. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd I'm no guru, but I'd like to tell you my view on this as about a year ago I struggled same battle and I think I learned a lot from it. Real gurus out there please feel free to correct my writings :) The design should always be related to what the relations there are between items you describe with the database. Here I see you are building a database of personnel and gear they use, groups they belong to and dirs they can access etc. But not much relativity there now. When normalization is on level 5 you see mostly id's :) Ask questions. Think examples of queries you will have to do. Ask yourself what if someone asks me to pull out data that has . Build and test queries to get that data. Make assumption that there is a LOT of data. Think space usage. The questions I would ask about your db structure are e.g.: Will data be repeated (in a table)? - there is now a lot of repeated data. A computer table could hold just model id and serial, and then have id's for cpu, hdd, ram, ports, manufacturer etc. An IP table could hold IP's, and there is not much point storing an IP with a computer as one computer will definitely have more than one IP's. Same way: OSes table could be OSes osID brandID displaynameID date_added date_modified or with even more normalization OSes osID osdataID .and with that you'd have an extra table. OSes osdataID os_name os_manufacturer os_release_data os_price os_etc plus to that you'd have a separate table OSes_to_comps OSes_to_comps_id compID osID date_added date_modified .which will let you have one computer have more than one OS (perfectly possible). 'OSes_to_comps_id' is really not needed but I like to have it there anyway. More questions: How do you link a person to a device? Or several persons to same device? Can one person have more than one device? What if one person has two computers and 4 displays? How do you link a device to a group (list all devices of a group) How do you link gear together? Answer to all above: use intermediate tables, like the 'OSes' example above. I would do a table 'person_to_computer' which would have 'PersID' and 'compID'. This way one person can have several computers. Using same method: 'person_to_display' table would just link a person to a display. These intermediate tables take very little room (just use two int cols or so - use same int lenght as you autoincrement keys) and make searching e.g. what displays a person has? much easier. They also make it possible to have complex relationships with minimal effort. SELECT displays.make, displays.model, personnel.firstName, personnel.lastName FROM displays, personnel, personnel_to_display WHERE personnel_to_display.dispID = displays.dispID AND personnel_to_display.persID = personnel.persID Display detail could be splitted to several tables just like in OSes example (you could build manufacturer and model tables which are shared accross the database), so that you could get display info by SELECT manufacturer.manufacturer_id manufacturer.manufacturer_name, model.model_id model.model_name FROM manufacturer, model, displays WHERE displays.manufacturer_id = manufacturer.manufacturer_id AND displays.model_id = model.model_id Heavy normalization will most likely make programming the application more complex, but it will pay off in speed and flexibility to change and add things. PS. About table names: - mixing case will make you make
Re: MySQL database design
on 8/11/02 2:47 PM, Pekka Saarinen, typed: At 8/11/2002, you wrote: MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Hi Lloyd, Spending plenty of time designing the database is be the best decision you can make. If the base is solid and flexible you'll end up with a solid and flexible application and not rewriting database structure will every small feature request. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd I'm no guru, but I'd like to tell you my view on this as about a year ago I struggled same battle and I think I learned a lot from it. Real gurus out there please feel free to correct my writings :) The design should always be related to what the relations there are between items you describe with the database. Here I see you are building a database of personnel and gear they use, groups they belong to and dirs they can access etc. But not much relativity there now. When normalization is on level 5 you see mostly id's :) Ask questions. Think examples of queries you will have to do. Ask yourself what if someone asks me to pull out data that has . Build and test queries to get that data. Make assumption that there is a LOT of data. Think space usage. The questions I would ask about your db structure are e.g.: Will data be repeated (in a table)? - there is now a lot of repeated data. A computer table could hold just model id and serial, and then have id's for cpu, hdd, ram, ports, manufacturer etc. An IP table could hold IP's, and there is not much point storing an IP with a computer as one computer will definitely have more than one IP's. Same way: OSes table could be OSes osID brandID displaynameID date_added date_modified or with even more normalization OSes osID osdataID .and with that you'd have an extra table. OSes osdataID os_name os_manufacturer os_release_data os_price os_etc plus to that you'd have a separate table OSes_to_comps OSes_to_comps_id compID osID date_added date_modified .which will let you have one computer have more than one OS (perfectly possible). 'OSes_to_comps_id' is really not needed but I like to have it there anyway. More questions: How do you link a person to a device? Or several persons to same device? Can one person have more than one device? What if one person has two computers and 4 displays? How do you link a device to a group (list all devices of a group) How do you link gear together? Answer to all above: use intermediate tables, I believe that what you called intermediate table books are calling table map, or something with the word map in it. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was banned or something! Thank you, thank you, and thank you again. like the 'OSes' example above. I would do a table 'person_to_computer' which would have 'PersID' and 'compID'. This way one person can have several computers. Using same method: 'person_to_display' table would just link a person to a display. These intermediate tables take very little room (just use two int cols or so - use same int lenght as you autoincrement keys) Could you please elaborate a bit on, ...use same int as you autoincrement keys... -for example which keys should (or must be?) be autoincremented? and make searching e.g. what displays a person has? much easier. They also make it possible to have complex relationships with minimal effort. SELECT displays.make, displays.model, personnel.firstName, personnel.lastName FROM displays, personnel, personnel_to_display WHERE personnel_to_display.dispID = displays.dispID AND personnel_to_display.persID = personnel.persID Display detail could be splitted to several tables just like in OSes example (you could build manufacturer and model tables which are shared accross the database), so that you could get display info by SELECT
Re: MySQL database design
At 8/11/2002, you wrote: I believe that what you called intermediate table books are calling table map, or something with the word map in it. Hi Lloyd, Books are nice to have (you can lie down to a sofa or sit in the loo with a book), and what I started with (SAMS Teach Yourseft MySQL in 21 days) are great to start with, but they don't get you very far in design process. The best resource is to design things out on a paper and coding tests on real data. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was banned or something! Thank you, thank you, and thank you again. Np. Nice to hear it was useful! :) Could you please elaborate a bit on, ...use same int as you autoincrement keys... -for example which keys should (or must be?) be autoincremented? I meant to say that if you refer to a key from table A from another table (B) you must use the same data type in both. If you have an autoincrement key int(11) on one table and you use it as int(5) on some intermediate table you'll end up having problems pretty soon. There should be (can be?) only one autoincrement key per table. Autoincrements are useful for stuff that you need to refer later by id, like adding to a list of photographs, list of car parts - the autoincrement system takes cares you don't have duplicate ids and that whenever you insert a new item you'll have a new unique key. Heavy normalization will most likely make programming the application more complex, but it will pay off in speed and flexibility to change and add things. I needed to be told that. because books seem to want you to shy away from over-normalization (beyond level 3). Well, the books don't tell much about normalizing much because the big companies take a lot of money to do it for you :) I think other reason is that writers think that keeping focus too long on one subject is not good for the reading process. Who knows. But it is not hard to normalize, and actually the further you go the more clearly you'll see the real data in your database. Pekka - Pekka Saarinen http://photography-on-the.net - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database design, one column, 10 entries?
I am not very sure I understand the questionbut maybe column type 'enum' is what you are looking for. - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 10, 2002 9:43 PM Subject: MySQL database design, one column, 10 entries? I am creating several tables in MySQL and linking via primary keys. I am held up on one issue, for one row in one table i have a column 'favorites' where i want to hold up to 10 unique entries, how do i implement this? userTable userId varchar(20) name varchar(30) email varchar(40) preferences userId varchar(20) styles varchar(20) favorites preferences userId styles favorites rating 01 'modern''#1 sleek' 15 '#2 ultra-sleek'20 '#3 un-sleek' 12 '#4 plain' 9 etc, up to 10 or so. 02 'gothic''#1 dark' 21 '#2 tall' 4 '#3 scary' 2 etc, etc,... I can't just make it a really long varchar(1000) because of the other columns that act on the same data. I hope that this is an appropriate question for this MySQL list. TIA -david - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL Database updates
I need help with MySQL. I just moved my servers and have new DNS names and IP addresses. I need to update the databases to reflect the changes. I used PhpAdmin to make some of the changes, but I am still getting Internal Server Error's. Any help in directing me for help would be appreciated. Thanks. Ingrid Kast Fuller CityScope Net 3910 Fairmont Parkway #264 Pasadena, TX 77504-3066 (713) 477-6161 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql database quota solution for help!!
Hi. If you want someone to consider answering your problem, you should write in a manner that it is understandable what you want. I.e. give a detailed description of what your requirements are and what you have already considered and why it won't work. Also have a look at the mailing list archive as quota issues come up regularly and you may find an answer there. Bye, Benjamin. On Sat 2002-06-01 at 17:54:43 +0800, [EMAIL PROTECTED] wrote: mysql database quota solution for help!! -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database files
Those files got so big that I had no more space on my hard drive and queriess would stop returning without any message telling me what was going on. All for the log files? Doesn't that seem like something that should be changed (atleast for the default install) ? It seems to me that there should be a way to set a limit. I didnt see one. Augey Dicky Wahyu Purnomo wrote: On Tue, 14 May 2002 18:49:48 -0400 Augey Mikus [EMAIL PROTECTED] wrote: what are the host-bin.001 002 003 etc.. files in the mysql_dir/var directory? On my system they range in size from bytes to gigs. What are they and can they be cleaned? it's mysql binary log file ... if you don't need the file first ... you can delete the file, and if you don't want the file appear again you can remove some lines from /etc/my.cnf : log-bin = binlog-do-db = and restart your mysql daemon ... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: MySQL database files
Augey, Wednesday, May 15, 2002, 4:25:15 PM, you wrote: AM Those files got so big that I had no more space on my hard drive and AM queriess would stop returning without any message telling me what was AM going on. All for the log files? Doesn't that seem like something that AM should be changed (atleast for the default install) ? It seems to me AM that there should be a way to set a limit. I didnt see one. You can turn off logging or you can delete old log files in regular intervals. AM Augey -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database files
On Tue, 14 May 2002 18:49:48 -0400 Augey Mikus [EMAIL PROTECTED] wrote: what are the host-bin.001 002 003 etc.. files in the mysql_dir/var directory? On my system they range in size from bytes to gigs. What are they and can they be cleaned? it's mysql binary log file ... if you don't need the file first ... you can delete the file, and if you don't want the file appear again you can remove some lines from /etc/my.cnf : log-bin = binlog-do-db = and restart your mysql daemon ... -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database files
they r the binary log files... Ritu On Tue, 14 May 2002, Augey Mikus wrote: what are the host-bin.001 002 003 etc.. files in the mysql_dir/var directory? On my system they range in size from bytes to gigs. What are they and can they be cleaned? Augey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Questions
* [EMAIL PROTECTED] 1. Is it a good idea to have all the columns in one big table ? This will depend on the nature of your data. You say you have many varchar columns. If many of them contains the same value, you could get substantial speed/space improvements by splitting (normalizing) the table. 2. Does mysql encounter problems with indexes (speed, performance) when having large amount of rows ? No, the problem with big table is when you _don't_ have an index, or when your query can not use any index. The key is to avoid a full table scan. 3. What kind of server configuration (hardware) for the dedicated MySQL-Server would you suggest to get a decent performance ? I don't know much about this, but you should make sure you get enough RAM, because the indexes can be cached, making queries faster. 4. How many concurrent threads/queries can we expect to achieve ? You said 100 concurrent queries... that should be possible. (The default value of the server variable 'max_connections' is 100.) 5. Shall we look into replicating (master/slave) to get better performance Yes. :) ? If yes, how would you suggest a possible setup. The nature of your application may put some limits on what you can achieve. One approach may be to have a single master, handling all updates, and multiple slaves, load balanced to handle all SELECT queries. 6. Is char() a better choice than varchar() in terms of speed when queries are run (we are aware of the fact that char() requires more disc space) ? Mysql can optimize the use of a table with fixed width. This means _all_ fields must be char/int/date, _none_ can be varchar, text or blob. If this is not possible, there is no gain in using char over varchar. Hope this helps, even if my name is not Jeremy. ;) -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database changes for japanese Lang..
On Saturday, April 06, 2002 at 5:32 PM, Aravind NagaVenkata Gorthy asked: Could any one tell me what all the chages I have to make in order to make my existing (english version) of MySQL database compatable to the Japanese language. It's a little hard to read your mind. Have you read section 4.6 of the manual? Do you know what parts of the database will store Japanese data, what parts of stored Japanese data will need to be search and/or sorted, that sort of thing? Joel Rees Alps Giken Kansai Systems Develoment Suita, Osaka - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql database privilege problems...
On Sat, 6 Apr 2002 18:54:14 -0800 (PST) Kip Kramer [EMAIL PROTECTED] wrote: Hi All, After working happily for months, my MySQL (v. 3.23.48-1) database has begun displaying the following problem: Whenever I log in as mysql root and try to change privileges for my database as follows: grant insert, select on MyDB_Name to MyExistingUserName; I get the error that the database mysql is read only. Is there an easy way to re-create the mysql permissions database and rebuild the permissions from scratch? I tried renaming the mysql db to mysql.bak and then restrating mysql, but it would not start until I renamed the file as it originally was. The error came from OS side, more exactly is Filesystem ... you have to change the file permission which related to error. Nothing to be done with mysql ;-) -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL DATABASE BACKUP
Yes and no If you are taking info from a live database you would have it in a steady state to copy it. This means locking the tables. But once you have a copy of the table all you would need to do is update the changes from the logs. Using replication for this I is the most simple way but would mean a second copy of MySQL but you would only need a low spec system.. I hope this helps... Simon -Original Message- From: Kathy Reyes [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 21:37 To: mysql Subject: MYSQL DATABASE BACKUP I am trying to backup a table in mysql without locking my tables it this posible - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL DATABASE BACKUP
Yes. Setup replication and peform the backup on the slave. Provided this is your only purpose for having a slave, and there are no queries running on the slave, the specs can be a lot lower than your master/production machine. -Original Message- From: Kathy Reyes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 1:37 PM To: mysql Subject: MYSQL DATABASE BACKUP I am trying to backup a table in mysql without locking my tables it this posible - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL DATABASE BACKUP
not quite sure what you mean by locking. each table will be locked during readout for the dump automaticly for the time the select runs and delivers data to the dumping program. it is not needed to lock the tables (more than one) in advance for a dump, but that, might better for dataintegrety. Lars Kathy Reyes wrote: I am trying to backup a table in mysql without locking my tables it this posible - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database; MS SQLserver connection;
How about trying to avoid using ODBC? Check out: http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=22472lngWId=1 or http://www.icarz.com/mysql/ Craig At 10:53 AM 3/12/2002 +0100, you wrote: Hi there, I have a problem with MySQL and the connection to it. What I want is to connect a Visual Basic application with a MySQL database with ADO. The only problem (and maybe more) is that there isn't a ODBC driver available to connect to the database. For company security it is not possible to install or modify system configuration. So the problem must be solved server sided. It is possible to connect with MS SQL server ODBC driver to the MySQL database by installing a conversiontool on the MySQL server? Or is there an other solution for my problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql Database Replication Question
On Thu, Mar 07, 2002 at 08:37:00AM -0600, Thi Cao wrote: All, How can I notify an application running on the slave server that the slave database has just updated itself by means of replication. The reason I ask is that I know MySql currently doesn't support triggers, but my application needs to be notified when an update on the slave has just taken place. Once again, thanks for your time. Hm, that's a little unusual. Could the application look at the output of SHOW SLAVE STATUS once in a while, maybe? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 28 days, processed 988,263,631 queries (407/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySql Database Replication Question
Jeremy, I'm not sure I follow. What information from that command would the app be looking for specifically. Maybe you're saying the app should look at the position of the master's binary log file. Is that what you suggest? Thi -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 10:42 AM To: Thi Cao Cc: MySQL Subject: Re: MySql Database Replication Question On Thu, Mar 07, 2002 at 08:37:00AM -0600, Thi Cao wrote: All, How can I notify an application running on the slave server that the slave database has just updated itself by means of replication. The reason I ask is that I know MySql currently doesn't support triggers, but my application needs to be notified when an update on the slave has just taken place. Once again, thanks for your time. Hm, that's a little unusual. Could the application look at the output of SHOW SLAVE STATUS once in a while, maybe? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 28 days, processed 988,263,631 queries (407/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Replication
On Wed, 2002-03-06 at 14:26, Thi Cao wrote: When I grant privileges as follows, the slave gets updated: GRANT FILE ON *.* to repl@'%' identified by 'some_password'; Could someone please tell me why I must grant the FILE privilege on all databases for the repl (slave) to be able to update one particular database? MySQL Manual: 4.2.7 Privileges Provided by MySQL: The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read or write any file that the MySQL server can read or write. 4.10.3 How To Set Up Replication 2. Set up special a replication user on the master with the FILE privilege and permission to connect from all the slaves. If the user is only doing replication (which is recommended), you don't need to grant any additional privileges. For example, to create a user named repl which can access your master from any host, you might use this command: GRANT FILE ON *.* TO repl@% IDENTIFIED BY 'password'; Thi Cao wrote: Hello everyone, This is my first attempt at MySQL database replication. Can't seem to get it to work yet. The problem reported in the error log of the slave server is as follows: Error reading packet from server: Access denied for user 'repl@slave_host' (Using password: YES) (read_errno 0,server_errno=1045) I believe it has something to do with the way I granted privileges to the user repl. I did the following: GRANT FILE ON dbname.* to repl@'%' identified by 'some_password'; Did you do a FLUSH PRIVILIGES after that? That is needed in some cases. You don't need to flush privileges when using grant/revoke commands. -- dsoares (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Replication
ds, If I understand you correctly, that also means that the user repl can access the mysql database where all the privilege info is stored and see the permissions, passwords, and such. Yes/No? Thi -Original Message- From: ds [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 11:02 AM To: Thi Cao Cc: MySQL Subject: RE: MySQL Database Replication On Wed, 2002-03-06 at 14:26, Thi Cao wrote: When I grant privileges as follows, the slave gets updated: GRANT FILE ON *.* to repl@'%' identified by 'some_password'; Could someone please tell me why I must grant the FILE privilege on all databases for the repl (slave) to be able to update one particular database? MySQL Manual: 4.2.7 Privileges Provided by MySQL: The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read or write any file that the MySQL server can read or write. 4.10.3 How To Set Up Replication 2. Set up special a replication user on the master with the FILE privilege and permission to connect from all the slaves. If the user is only doing replication (which is recommended), you don't need to grant any additional privileges. For example, to create a user named repl which can access your master from any host, you might use this command: GRANT FILE ON *.* TO repl@% IDENTIFIED BY 'password'; Thi Cao wrote: Hello everyone, This is my first attempt at MySQL database replication. Can't seem to get it to work yet. The problem reported in the error log of the slave server is as follows: Error reading packet from server: Access denied for user 'repl@slave_host' (Using password: YES) (read_errno 0,server_errno=1045) I believe it has something to do with the way I granted privileges to the user repl. I did the following: GRANT FILE ON dbname.* to repl@'%' identified by 'some_password'; Did you do a FLUSH PRIVILIGES after that? That is needed in some cases. You don't need to flush privileges when using grant/revoke commands. -- dsoares (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Replication
rep1 would gain FILE access to all ( not sure what the really entails ) it would not have SELECT access, so can not read from it mind you if they have enough info to get in with rep1 access, then they could just replicate everything on thier own server and read till thier hearts content -Original Message- From: Thi Cao [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 12:04 PM To: Thi Cao Cc: MySQL Subject: RE: MySQL Database Replication ds, If I understand you correctly, that also means that the user repl can access the mysql database where all the privilege info is stored and see the permissions, passwords, and such. Yes/No? Thi -Original Message- From: ds [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 11:02 AM To: Thi Cao Cc: MySQL Subject: RE: MySQL Database Replication On Wed, 2002-03-06 at 14:26, Thi Cao wrote: When I grant privileges as follows, the slave gets updated: GRANT FILE ON *.* to repl@'%' identified by 'some_password'; Could someone please tell me why I must grant the FILE privilege on all databases for the repl (slave) to be able to update one particular database? MySQL Manual: 4.2.7 Privileges Provided by MySQL: The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read or write any file that the MySQL server can read or write. 4.10.3 How To Set Up Replication 2. Set up special a replication user on the master with the FILE privilege and permission to connect from all the slaves. If the user is only doing replication (which is recommended), you don't need to grant any additional privileges. For example, to create a user named repl which can access your master from any host, you might use this command: GRANT FILE ON *.* TO repl@% IDENTIFIED BY 'password'; Thi Cao wrote: Hello everyone, This is my first attempt at MySQL database replication. Can't seem to get it to work yet. The problem reported in the error log of the slave server is as follows: Error reading packet from server: Access denied for user 'repl@slave_host' (Using password: YES) (read_errno 0,server_errno=1045) I believe it has something to do with the way I granted privileges to the user repl. I did the following: GRANT FILE ON dbname.* to repl@'%' identified by 'some_password'; Did you do a FLUSH PRIVILIGES after that? That is needed in some cases. You don't need to flush privileges when using grant/revoke commands. -- dsoares (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Replication
Yes, I did flush the privileges when nothing else worked but that didn't do the trick either. Thanks for trying. -Original Message- From: James Housley [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 6:00 PM To: Thi Cao Cc: MySQL Subject: Re: MySQL Database Replication Thi Cao wrote: Hello everyone, This is my first attempt at MySQL database replication. Can't seem to get it to work yet. The problem reported in the error log of the slave server is as follows: Error reading packet from server: Access denied for user 'repl@slave_host' (Using password: YES) (read_errno 0,server_errno=1045) I believe it has something to do with the way I granted privileges to the user repl. I did the following: GRANT FILE ON dbname.* to repl@'%' identified by 'some_password'; Did you do a FLUSH PRIVILIGES after that? That is needed in some cases. Jim -- /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - [EMAIL PROTECTED] http://www.FreeBSD.org The Power to Serve [EMAIL PROTECTED] http://www.TheHousleys.net [EMAIL PROTECTED] http://www.SimTel.Net - Studies show that 1 out of every 4 Americans suffer some form of mental illness. So look at your three best friends, if they are okay it is YOU! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Database Replication
When I grant privileges as follows, the slave gets updated: GRANT FILE ON *.* to repl@'%' identified by 'some_password'; Could someone please tell me why I must grant the FILE privilege on all databases for the repl (slave) to be able to update one particular database? Thanks everyone. Thi -Original Message- From: James Housley [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 6:00 PM To: Thi Cao Cc: MySQL Subject: Re: MySQL Database Replication Thi Cao wrote: Hello everyone, This is my first attempt at MySQL database replication. Can't seem to get it to work yet. The problem reported in the error log of the slave server is as follows: Error reading packet from server: Access denied for user 'repl@slave_host' (Using password: YES) (read_errno 0,server_errno=1045) I believe it has something to do with the way I granted privileges to the user repl. I did the following: GRANT FILE ON dbname.* to repl@'%' identified by 'some_password'; Did you do a FLUSH PRIVILIGES after that? That is needed in some cases. Jim -- /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - [EMAIL PROTECTED] http://www.FreeBSD.org The Power to Serve [EMAIL PROTECTED] http://www.TheHousleys.net [EMAIL PROTECTED] http://www.SimTel.Net - Studies show that 1 out of every 4 Americans suffer some form of mental illness. So look at your three best friends, if they are okay it is YOU! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Database Replication
Thi Cao wrote: Hello everyone, This is my first attempt at MySQL database replication. Can't seem to get it to work yet. The problem reported in the error log of the slave server is as follows: Error reading packet from server: Access denied for user 'repl@slave_host' (Using password: YES) (read_errno 0,server_errno=1045) I believe it has something to do with the way I granted privileges to the user repl. I did the following: GRANT FILE ON dbname.* to repl@'%' identified by 'some_password'; Did you do a FLUSH PRIVILIGES after that? That is needed in some cases. Jim -- /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - [EMAIL PROTECTED] http://www.FreeBSD.org The Power to Serve [EMAIL PROTECTED] http://www.TheHousleys.net [EMAIL PROTECTED] http://www.SimTel.Net - Studies show that 1 out of every 4 Americans suffer some form of mental illness. So look at your three best friends, if they are okay it is YOU! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL database in a File system
12.02.02 05:56:48, D Bamud [EMAIL PROTECTED] wrote: I need to create a database in MySQL that I need to bundle with my program for distribution. The user will have its own copy of MySQL running. User [...] file system database bundle will be done before running the program. How to create such database in MySQL. create the DB on your server, grab the files from the filesystem and include them into your setup, have the setup copy them to the desired place, install the client ... and simply let it use the new database. (works w/o restarting MySQL) that's the way I'm currently doing it with a setup for a windoze server/client installation. yours Andreas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL Database Specs
Hello, I am interested in knowing :- 1) The maximum number of tables possible in MYSQL Database 2) The maximum number of columns that are in a table. http://www.bitbybit.dk/mysqlfaq/faq.html#ch9_0_0 / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mySQL database files help
Chase == Chase Peeler [EMAIL PROTECTED] writes: There is alot of important info in those tables, and I really need them. Sorry to broadcast this to the whole list, but Chase's supplied email appears to be invalid, or at least bouncing at the moment: Chase, I'm in the NCBI part of NLM, and have experience with Debian and MySQL. Drop me a line if you'd like; I'm more than happy to help out with your problem. john. -- john s. j. anderson [EMAIL PROTECTED] +1.301.594.6087 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mySQL database files help
You will not get 3.22 to recognize MyISAM format tables. You need to either: 1. Upgrade your server to 3.23 or 2. Find a running 3.23 server, copy your table files to that server, ALTER TABLE xxx TYPE=ISAM; on all your tables to convert back to older ISAM format, and copy your table files back. Of course, option 1 is prefered. dpk - Original Message - From: Chase Peeler chase@mail To: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 4:50 PM Subject: mySQL database files help I was running a mySQL server under slackware for a while, it was interacting with php on an apache webserver, and everything worked fine. There were a few times where I would mess linux up and need to re-install,in which case I would move my database files to my windows partition, re-install linux, move the database files back (database files being everything in /var/lib/mysql) and everythign worked fine. I just decided to change to Debian, and followed the same procedure. After moving my database files back to the /var/lib/mysql directory, and running mysql, if I try to do anything with my tables, i would get the message dbfile.ISM is missing. When I looked in my directory, all the files were of the format dbFile.MYD, dbFile.MYI and dbFile.frm. As far as I know, the files have always been of these extensions (instead of .ISM, .ISD and .frm). If they have not, I have no idea how they changed. Does anyone know how to get mySql (version Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) ) to either recognize my current files as valid, or how to get the data in those files into something that mySQL will recognize. Thank you very much. There is alot of important info in those tables, and I really need them -- -Chase - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mySQL database files help
It appears that your change to debian was also a change from the current 3.23 mysql version to the old 3.22 version ( major releases ). The old version, 3.22, does not support MyISAM tables, so you'll need to upgrade to the new version. Debian stable, aka potato, uses 3.22, but both woody + sid ( testing + unstable ) are using 3.23. I have no problems running apache + php4 + mysql on debian unstable, if you are concerned about upgrading to the 'non' stable trees. I dont know if you can find a potato-fied mysql-3.23 .deb out there, but it may be available. dpk - Original Message - From: Chase Peeler chase@mail To: [EMAIL PROTECTED] Sent: Monday, September 17, 2001 8:05 PM Subject: mySQL database files help I was running a mySQL server under slackware for a while, it was interacting with php on an apache webserver, and everything worked fine. There were a few times where I would mess linux up and need to re-install, in which case I would move my database files to my windows partition, re-install linux, move the database files back (database files being everything in /var/lib/mysql) and everythign worked fine. I just decided to change to Debian, and followed the same procedure. After moving my database files back to the /var/lib/mysql directory, and running mysql, if I try to do anything with my tables, i would get the message dbfile.ISM is missing. When I looked in my directory, all the files were of the format dbFile.MYD, dbFile.MYI and dbFile.frm. As far as I know, the files have always been of these extensions (instead of .ISM, .ISD and .frm). If they have not, I have no idea how they changed. Does anyone know how to get mySql (version Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) ) to either recognize my current files as valid, or how to get the data in those files into something that mySQL will recognize. Thank you very much. There is alot of important info in those tables, and I really need them. -Chase - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL] Database size
Date |Fri, 3 Aug 2001 17:21 +0200 From |TIGNAC BRUNO [EMAIL PROTECTED] Hello! TB --- Reçu de CMB.TIGNABR 0298426574 03-08-01 17.21 TB Hello, TB I plan to use MySQL/InnoDB to store about 13 GB of data, into 40 tables. One TB of the tables is over 5 GB, and a second over 1,5 GB. Do you think I can use TB MySQL/InnoDB ? In short words: YES. ___ For technical support contracts, visit https://order.mysql.com/ This email is sponsored by SWSoft, http://www.asplinux.ru/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Grigory Bakunov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL] Database size
Hi! At 02:59 PM 8/7/01 +0300, you wrote: Grigory Bakunov writes: Date |Fri, 3 Aug 2001 17:21 +0200 From |TIGNAC BRUNO [EMAIL PROTECTED] Hello! TB --- Reçu de CMB.TIGNABR 0298426574 03-08-01 17.21 TB Hello, TB I plan to use MySQL/InnoDB to store about 13 GB of data, into 40 tables. One TB of the tables is over 5 GB, and a second over 1,5 GB. Do you think I can use TB MySQL/InnoDB ? In short words: YES. ___ For technical support contracts, visit https://order.mysql.com/ This email is sponsored by SWSoft, http://www.asplinux.ru/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Grigory Bakunov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com Next time provide more info. Actually largest tablespace in InnoDB is 4 Gb, but a single table can be spanned over as many tablespaces as you wish. Heikki, am I right ? The totality of your data files is called the 'tablespace'. The tablespace can be 4 billion database pages (page == 16 kB) in size. A single table is allowed to extend to the whole tablespace. A data file should be 4 GB, or 2 GB if your file system only supports 2 GB files. Regards, Heikki -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL] Database size
I hate to ask this on the list, but I have tried everything to unsubscribe and nothing works. Any suggestions? Thanks. -Original Message- From: TIGNAC BRUNO [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, August 03, 2001 11:00 AM Subject: [MySQL] Database size --- Reçu de CMB.TIGNABR 0298426574 03-08-01 17.21 Hello, I plan to use MySQL/InnoDB to store about 13 GB of data, into 40 tables. One of the tables is over 5 GB, and a second over 1,5 GB. Do you think I can use MySQL/InnoDB ? Thank you. Bruno Tignac 03-08-01 17.21 Envoyé - - mysql(a)lists.mysql.com -- Ce message et toutes les pieces jointes (ci-apres le message) sont confidentiels et etablis a l'intention exclusive de ses destinataires. Toute utilisation ou diffusion non autorisee est interdite. Tout message etant susceptible d'alteration, l'emetteur decline toute responsabilite au titre de ce message s'il a ete altere, deforme ou falsifie. --- This message and any attachments (the message) are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. As e-mails are susceptible to alteration, the issuer shall not be liable for the message if altered, changed or falsified. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL database on BSDI UNIX 4.0
[EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: database,sql,query,table If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. You have written the following: Our new provider is running on BSDI UNIX 4.0. But I see no platform download for this flavour of Unix. Which platform download should I choose which I can be sure will install prpoerly on BSDI UNIX 4.0 ? A. Chater - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL database running on a CD?
- Is it possible to use mySQL for this purpose? Yes - Is it possible to set the database to be read-only? If you use the compress command for your data in the database in not only makes the files smaller is also makes them read only.. - We need to transfer the database structure/data to the new database. I have seen the script 'oracledump.pl' that dump an Oracle database into a mySQL dump file. Does this works fine? Sorry I have not tried this so don't know Simon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Database Replication
On Sat, May 19, 2001 at 01:18:42PM -0500, David Gullett wrote: I have 10 or so MySQL servers in the field, all writing data to a database of the same name. I would like to set up replication from all masters back to a single slave server and have each master write to a different database. Is there an easy way to do this, short of running multiple instances of MySQL on the slave server? Currently, each slave can only have a single master. So you're looking at multiple slave instances. If you don't need completely real-time replication, you might consider rsync or something similar. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 5 days, processed 35,090,354 queries (79/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql database export
Check with your ISP. You need shell access. ashok wrote: hi, sir,we have to upload mysql database on our website,the isp people has enable mysql for our websites,now we wanted to upload or export the mysql table, please give us the suggetion where to upload the tables and how odbc has to be enable, when we are connecting to our site through ftp we are getting 5 folders mention below. 1:httpdocs 2:pd 3:log 4:webusers 5:speacial out there folder in which folder we have to upload the mysql database. waiting for reply regards laiq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql database export
Usually it is not in the folders that are listed below. It is in one of the server's folder that you don't usually have access to view. Since you have only ftp you need to install phpMyAdmin from phpMyAdmin.net on your server, htdocs. Export your database using mysqldump dbname somename.sql connect to phpMyAdmin on your server and there will be a box to load your text file. That should upload your database Adrian On Tue, 10 Apr 2001, ashok wrote: hi, sir,we have to upload mysql database on our website,the isp people has enable mysql for our websites,now we wanted to upload or export the mysql table, please give us the suggetion where to upload the tables and how odbc has to be enable, when we are connecting to our site through ftp we are getting 5 folders mention below. 1:httpdocs 2:pd 3:log 4:webusers 5:speacial out there folder in which folder we have to upload the mysql database. waiting for reply regards laiq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Database and NAS
Patrick Calkins wrote: Hello all; Are there any known problems with storing the database files on a NAS (network attached storage) device?? also, can you run multiple MySQL daemons pointing to one common database, so you could have a "cluster" of database servers and one database?? Thanks! Patrick You can only have one data repository and multiple servers if you never write to your database.. Unfortunately unless you use replication mysql doesn't handle mutliple servers pointing at the same data, one server has no way of knowing that cached indexes are out of date based on updates by another server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Database and NAS
On Thu, Mar 15, 2001 at 05:07:08PM -0700, Steve Ruby wrote: Patrick Calkins wrote: Hello all; Are there any known problems with storing the database files on a NAS (network attached storage) device?? also, can you run multiple MySQL daemons pointing to one common database, so you could have a "cluster" of database servers and one database?? Thanks! Patrick You can only have one data repository and multiple servers if you never write to your database.. Unfortunately unless you use replication mysql doesn't handle mutliple servers pointing at the same data, one server has no way of knowing that cached indexes are out of date based on updates by another server. Even with it's external locking support? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
