Re: MySQL: Spam Free (Send/Receive) Emails. HOW?
--On September 9, 2006 12:11:15 AM -0700 deafmickey [EMAIL PROTECTED] wrote: I would like to create a MySQL to protect my subscribers and clients from receiving a spam emails. I can create a new table for MySQL and write new script for PHP MySQL: mysql_query(CREATE TABLE members( members_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), email_address VARCHAR(50)) or die(mysql_error()); mysql_query(CREATE TABLE spam_free( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), members_id (INT), fake_email VARCHAR(50)) or die(mysql_error()); ? PHP: After my client / subscriber sign up as a member of my organization, php autmatically creates a new fake email address with my domain name i.e. doe @ mydomain DOT com in it. PROBLEM: I couldn't find a code or syntax that make mysql to redirect email to my subscriber from my client's email client and vice versa. Once our database matches sender's fake email address, MySQL would redirect sender's email to our member with a real email otherwise die. Is any one of you know the code that can get mysql to work and redirect email without go through php? (not use webmail). I believe its possible but I do not know what code for MySQL. MySQL is not an MTA. This is the job of your MTA. Postfix supports lookups via mysql, might want to try there instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: Spam Free (Send/Receive) Emails. HOW?
--On September 9, 2006 4:28:29 AM -0700 deafmickey [EMAIL PROTECTED] wrote: What is MTA? I am not sure if GoDaddy supports Postfix. I will phone Godaddy to find out. *blinks* OK nevermind. You're not going to be capable of doing what you want to do. What you want to do requires control of the mail server, and an understanding of how they work. There are plenty of howto's and FAQs that cover all of this elsewhere though. MTA == Mail Transfer Agentyou might know it as an SMTP server. Thanks Mel Michael Loftis wrote: --On September 9, 2006 12:11:15 AM -0700 deafmickey [EMAIL PROTECTED] wrote: I would like to create a MySQL to protect my subscribers and clients from receiving a spam emails. I can create a new table for MySQL and write new script for PHP MySQL: mysql_query(CREATE TABLE members( members_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), email_address VARCHAR(50)) or die(mysql_error()); mysql_query(CREATE TABLE spam_free( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), members_id (INT), fake_email VARCHAR(50)) or die(mysql_error()); ? PHP: After my client / subscriber sign up as a member of my organization, php autmatically creates a new fake email address with my domain name i.e. doe @ mydomain DOT com in it. PROBLEM: I couldn't find a code or syntax that make mysql to redirect email to my subscriber from my client's email client and vice versa. Once our database matches sender's fake email address, MySQL would redirect sender's email to our member with a real email otherwise die. Is any one of you know the code that can get mysql to work and redirect email without go through php? (not use webmail). I believe its possible but I do not know what code for MySQL. MySQL is not an MTA. This is the job of your MTA. Postfix supports lookups via mysql, might want to try there instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/MySQL%3A-Spam-Free-%28Send-Receive%29-Emails.-HOW-- tf2242963.html#a6222963 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large size of MySql binary for Power PC
--On September 5, 2006 1:41:43 PM +0530 Arvind Kumar [EMAIL PROTECTED] wrote: Hi! Any help would be appriciated. I found MySQL binaries for embedded system (Processor: Power PC, OS:Linux) There is not much space available in the hard disk. It is around 20 MB. The binary size is large enough, could you please tell me how can i reduce binary size. Stripping, I already did. Disable any engines you won't be using such as bdb and/or innodb and the like at compile time. Switch the optimization flags from -O3 to -Os (size optimized). You can help reduce the size and memory footprints by using a shared library system. 20MB sounds like you're statically linking things. If you use shared libs across the whole system your overall image size will go down. Thanks in Advance Regards, -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
--On September 5, 2006 3:18:21 PM +0200 Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, Any idea of what could be wrong here ? My guess is that the variable is based on the last update timestamp, and the last time that the slave has seen data from the master. If it had been about a minute since the last update, then an update came through and the slave then saw the update it would think oh I'm about a minute behind since my timestamp is about a minute behind the timestamp I just saw. They're not constantly exchanging heartbeats or anything of any kind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: solaris build mysql from source using gcc
Well if you'd share your specific problem I'm sure someone can help, but more importantly why are you building from source anyway? There are binary builds for most Solaris platforms, including 2.10. --On September 1, 2006 2:10:17 PM + [EMAIL PROTECTED] wrote: Hi, Any chance i could get somebody's help on how to build MySQL from source using the gcc compiler on Solaris (10)? Thanks, Charles -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: solaris build mysql from source using gcc
--On September 1, 2006 8:38:21 AM -0600 Michael Loftis [EMAIL PROTECTED] wrote: Well if you'd share your specific problem I'm sure someone can help, but more importantly why are you building from source anyway? There are binary builds for most Solaris platforms, including 2.10. Sorry I meant 10 theremy solaris experience comes from the 'old days' before they went and dropped the 2. from the version scheme. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The length of the sql query
--On August 23, 2006 1:55:36 PM -0400 Emi Lu [EMAIL PROTECTED] wrote: Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! All SQL in/out is limited by the max packet size configuration parameter, however if you're running SELECT's with a multi-megabyte where clause, you'll have other more practical issues. Seriously you probably don't want to do what you're doing. Also MySQL 3.23 is very ancient history now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
select userid,count(text) from blah group by userid; --On August 20, 2006 7:22:59 PM +0100 Peter Van Dijck [EMAIL PROTECTED] wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comunication between Oracle and MYSQL
--On August 14, 2006 9:11:30 PM +0530 balaraju mandala [EMAIL PROTECTED] wrote: Hi All, I need some suggestions from you. I need a comunication between Oracle database with MySQL. http://www.webmethods.com/ They sell software to do this. Or...well... ActiveSoftware/ActiveWorks did which was bought by them. Disclaimer, I worked at Active as the Unix Admin for the Demonstration center. Someone else might have written some software to do it as well. But you either need a third piece of software from someone else or it needs to be part of your app or you need to write it. The databases will not do it for you natively. In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. It is totally new situation for me and i am totally confused. Please help me folks. Thanks in advance. regards, Bala Raju Mandala. -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than 4 CPUs?
--On August 11, 2006 2:44:34 PM -0400 Ed Pauley II [EMAIL PROTECTED] wrote: It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! WellThat may not be entirely true with Opteron's but with Intel's it is. IT's not just a MySQL thing, past the 4th CPU there just isn't much/any gain because you're bottlenecked on RAM and FSB. I've not personally run such a large MySQL machine but I don't know why there'd be a scaling issue assuming your application is designed properly and does it's best to not lock tables. I'd check to make sure you're CPU bound before investing in so much CPU. More likely you're I/O bound. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open file: 'my_table.MYI': Could be the reason of a server reboot?
--On August 8, 2006 11:36:21 AM +0200 thomas Armstrong [EMAIL PROTECTED] wrote: Hi. My Linux server has just restarted due to memory problems. Browsing mySQL logs, I've found out 5,000 lines like these in less than one hour: -- 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) 060808 1:27:39 [ERROR] /usr/sbin/mysqld: Can't open file: 'my_table.MYI' (errno: 145) Can this be the reason of the server reboot? No but that can be a symptom of an unclean shutdown, run repair table my_table on the affected table. The messages you gave below are meaningless, they're part of the normal bootup process. You'll need to look for messages that spat out just before the last bootup. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating -- sort of 2 way
--On August 6, 2006 2:01:15 PM -0700 Enrique Sanchez Vela [EMAIL PROTECTED] wrote: One thing that you could do, is to setup a replicating slave server, freeze the replication, perform backups (to tape/disks etc), then re-enable the replication activity, this way you provide a non-stop service without compromising your data availabilty. I've tried this in the past. Replication on large, non-single-application database servers is hard. When you have over a thousand users on a single (very large) DB server the chances that one of them will trip one of the 'does not replicate correctly to slave and causes slave to stop dead in tracks' bugs is pretty high. We found (even with 4.1 series) that we were restarting or dealing with stopped replication on a daily basis. We stopped trying somewhere around 4.1.11 so things may have improved, but they may not have. For a single application or a limited set of applications it seems to work great and you can whittle out any cases where the app does something that doesn't replicate correctly somehow. With large numbers of apps/people using it we usually had to turn down or turn off the error checking on the slave so it wouldn't stop in it's tracks all the time. This of course caused the issue of you were never totally sure that what was on the slave was what was on the master. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query cache about the federated engine
--On August 2, 2006 5:25:51 PM +0800 wangxu [EMAIL PROTECTED] wrote: I have a problem about the performance of federated engine. The mysql5.0 reference manual says that the FEDERATED tables do not work with the query cache, aren't they? How about the query cache used by the federated engine in the mysql5.1? If the query cache cann't be used with the federated engine tables still, how to enhance the query performance against to the federated tables effectively? Well for one if the machine isn't on the same LAN you'll never have good DB performance -- not sure if this is the case or not, just a helpful hint. For two, the reason why federated doesn't use the query cache is then you have to somehow invalidate the remote cache(s) which would require some form of protocol extension, either in the SQL or the over the wire protocol so that the server with the federated table could ask if it could cache a result, and the server that's serving to the federated client could inform that server when it's cache isn't valid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving Image in Database [again]
--On July 30, 2006 10:42:16 AM -0500 Chris McKeever [EMAIL PROTECTED] wrote: Starting to build a new schema, and one of the items is image storage In the past I have always used filesystem storage. Was wondering if I could get a conversation started on the pros/cons of database storage verse filesystem Bad idea usually in my experience to store it in the DB. This requires the extra steps to transport it over the wire in a mechanism that really wasn't designed for this. I've always had far better performance doing filesystem, or even proxied HTTP requests. Thanks! -- -- please respond to the list .. if you need to contact me direct cgmckeever is the account prupref.com is the domain A href=http://www.prupref.com;Simply Chicago Real Estate/A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disable specific storage engines WITHOUT recompiling?
Hi, I know that bdb has --skip-bdb, and innodb has the ability to be disabled at startup but what about federated, csv, archive, etc? My problem is I don't want to recompile and I don't want to carry a different binary version in our local repository just for the one or two machines on which we need to disable these other engines, especially federated. Any advice? Thanks! -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql error logging
OK I've honestly banged my head against everying, but as far as I can tell MySQL has *NO* real logging support. You have three options, log everything as text, log all queries as binary, or nothing. What I want/NEED is connection logging and some sort of ERROR logging. I'm getting an increasingly large amount of client connections that get apparently dropped during queries to the mysql server (error 2013) but the timeout issue is not possible (first it's default hour long timeout, secondly the connections are NOT persistent) Is there anything that can be done to get mysql to log the errors as they occur with the clients? Or to get it to log when it opens/closes a connection with the client and the disposition of that connection? Something would be better than what it does now which is all-or-nothing. Makes diagnosing anything impossible since I'm handling ~2k queries/sec. We have a binary log (necessary for our slave replica) and aren't under any load crunch nor I/O crunch on the DB drives, we're not experiencing ANY performance issues, just theres a goodly number of these connection dropped by server messages being reported. There has *GOT* to be SOME sort of useful logging in MySQL without having to turn on *full* text logging, which completely kills the server. All I need are connect/disconnect and disconnect reasons, I also *NEED* authentication failures but the thing doesn't log those EITHER. I'm starting to have a hard time believing I'm the only one complaining about this so I must be missing something, but the only logging options I've found cause full logging which can not be done. MySQL Version is 4.0.23 -- Michael Loftis Modwest Operations Manager GPG/PGP -- 0xE736BD7E 5144 6A2D 977A 6651 DFBE 1462 E351 88B9 E736 BD7E -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql *SERVER* error logging
--On Tuesday, March 08, 2005 15:03 -0500 Daniel Fisla [EMAIL PROTECTED] wrote: Under UNIX/linux use syslog, for me /var/log/messages shows login/auth error Mar 6 05:39:11 orion pdns[1865]: gmysql Connection failed: Unable to connect to database: Access denied for user 'root'@'localhost' (using password: NO) This is client logging, I need server logging, IE real logging. Yes this suffices for a lot of people but not for anyone doing database hosting like we are. Now for dropped sockets/connections you need to enable logging for this kind of thing in your connector and/or pooling software. I don't think this exists on the server side as it is not so simple to determine the actual cause. Then something is seriously broken with MySQL if this is the case, a server obviously knows why a client/child disconnected, and should always know this. It should be able to log this, especially in the case of authentication failures. Let's say your router/switch drops connections, to server this looks like TCP socket timeouts and eventually will try to close the connection as such, but was it dropped or was it just left open and not closed properly by the client. It's not a networking issue, wish it was, that'd be really simple to solve. The web cluster runs identical software (totally completely identical, NFS mounted, or imaged from a central tarball) and it's still intermittent and happening from all the servers. The X-Cart software that a number of people run here can log and email errors as they happen and includes data on where as well. It's pretty randomly distributed. This is why it makes sense to track these things in your data connector/pooling, with your kind of load you should have this kind of connection tracing/logging enabled in the client side and/or middle tier anyway. Can't, thousands of individual clients and software, everything from PHP X-Cart apps, to perl and C apps. We don't control the connecting software, just like an FTP, SMTP, HTTP, etc. server. Every other piece of server software can log it's authentication issues and disposition, why can't MySQL? Oracle can do this, Postgres, DB2, etc etc etc. I'm beginning to think that I'm going to have to spend my time to code proper logging inside of MySQL which is a major bummer but being as there's no way to track down errors without logs well I'm kinda stuck. No I'm not picking on you personally, but I am picking on MySQL and MySQL AB. I've had this problem with MySQL before and that's why I prefer personally other DBs, I however, don't get to make this choice, it's the thousands of webhosting clients that make it, not me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql *SERVER* error logging
--On Tuesday, March 08, 2005 15:44 -0500 Daniel Fisla [EMAIL PROTECTED] wrote: I see your point, especially when not being in control of client software. You may be in a hard position. I assume your did already some research into this so I won't give you the RTFM answer. :-) Yes lots :( That's why I was like I *must* be missing something. Adding in your logging code should not be that hard, well, it depends what kind of logging you'd like or need and if you could use some mature C/C++ logging API. syslog is obviously preferred and likely what I'll use...Though the MySQL server may have a bit of framework in there, it obviously has something since it already puts out a very limited set of messages on startup. Just need to find where those happen and start from there :) In the past I had to add logging to IMAP servers and connectors. The problem you will get into is, unless you manage MySQL AB to accept these patches for good into their product, you in essence will be forking their source and will have to re-apply your patches, and do full regression testing, for each MySQL upgrade/version. Yes, I realise this all to well. We're forced to patch/modify various packages slightly for our operation. Up to and including Apache (the scoreboard/status page can't display VHosts/mod_vhost_alias hosts by default, so we patch that in) Under such loads even the slightest memory leak or resource locking can really mess things up. Yup, I'm a very competent coder and am very familiar with coding 'high end robust server level' applications. Or whatever you want to call them. It's amazing how quickly bugs can turn up when you repeat things a million times an hour :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting 4.0.18 to log connects, connect errors, and NOT queries...
IS there any way to get the 4.0.18/4.0 series to log client connects (disconnects maybe too, but that's optional) connect errors (IE password/auth failures) WITHOUT logging every query? Like every time a client gets say ERROR 1045: Access denied for user: [EMAIL PROTECTED]' (Using password: YES) I want a log entry, ESPECIALLY on failures like that, but I don't want nor need to log every query. This seems pretty basic so I have GOT to be overlooking something. -- Michael Loftis Modwest Operations Manager GPG/PGP -- 0xE736BD7E 5144 6A2D 977A 6651 DFBE 1462 E351 88B9 E736 BD7E -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit of 1000 rows?
--On Friday, January 14, 2005 14:39 -0700 Steve Grosz [EMAIL PROTECTED] wrote: I had tried to load a group of records from a Excel spreadsheet, and for the most part it seems to have worked. The problem is that I know there were more than 1000 rows of data to be input, and it stopped at 1000 exactly. Am I missing something to allow more than 1000 rows to be input? Steve This is extremely vague. How exactly are you loading the data, excel doesn't talk to MySQL directly, are you using MyODBC/ODBC drivers? Are you exporting the excel to CSV then importing that, if so how? Did you notice any errors during the import process? What type of data is it, very large fields/columns? some distributions only allow a 1MB max packet size, which equates to BLOBs of no more than 1MB, and single row sizes of no more than 1MB, and depending on how oyu do the import can mean no mor ethan 1MB total data in a single batch. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to specify start-up options when it's running as service?
--On Friday, January 14, 2005 14:59 -0800 Homam S.A. [EMAIL PROTECTED] wrote: I know about my.ini, but I'm not sure how to specify these mysqld start-up options to it: --shared-memory --shared-memory-base-name=MYSQL I tried adding entries like: shared-memory=true shared-memory-base-name=MYSQL shared-memory shared-memory-base-name=MYSQL if an option just needs to show up don't specify args, also make sure it's in the right section, usually [mysqld] But it didn't work. So my question is, How do I specify the above mysqld start-up options when MySQL is running as an NT Service? Thanks! __ Do you Yahoo!? All your favorites on one personal page ? Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Operations Manager GPG/PGP -- 0xE736BD7E 5144 6A2D 977A 6651 DFBE 1462 E351 88B9 E736 BD7E -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Restore MySQL Database from VB?
\. is the MySQL CLI/monitor SOURCE command. read your file in one statement at a time and execute it (search for unquoted/unescaped ;'s at the end of the statements) --On Monday, June 14, 2004 11:32 +0530 Nawal Lodha [EMAIL PROTECTED] wrote: Hi all, I wish to restore an sql file in a mysql database though VB. One way of doing it is writing the Create Database command and the mysql restore command 'mysql -options sql_file' in a batch file and executing it. Is there any other better way of doing it? Btw, I try to make an ADO connection and call the command \. sql_file using Execute/Open method of Connection Object. But that doesn't seem to work. Thanks, Nawal Lodha. -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rank beginnner - very basic question
Briefly: http://dev.mysql.com/doc/mysql/en/GRANT.html GRANT ALL PRIVILEGES ON dbname.* TO user IDENTIFIED BY 'userspass'; CREATE DATABASE dbname; \q mysql -u user -p dbname Password: userspass mysql NEVER *EVER* run ANYTHING using root. That's an administrative only user. Some applications may not even need a grant all privileges for their database, many only require select, lock, update, after the table structure is loaded they don't need to drop/add tables. Don't just do a grant all privileges * to someone either because then they can read, write, and DROP anyones tables or databases. --On Sunday, June 13, 2004 17:17 -0400 Guy Merritt [EMAIL PROTECTED] wrote: I have zero experience, really, with Mysql although I compiled it on a Linux box and it works well for a couple of sites and messageboards that I host from my house. I do this strictly for fun and would like to let some friends and family members have websites as well. Here's what I don't get (well, a tiny protion of what I don't get): when I paid for webhosting I could set up a php messageboard and (if I recall correctly) and I was issued a unique username and password for the mysql database. As it is now, I am required to use my root account and username - and use a different prefix for each instance of phpBB2 (a messageboard) when I install something. Is there a way that users can have sectors, or tables, or whatever that are allocated to them and which they access with their own usernames and passwords? I know I can change my password with /.mysqadmin -u root password my_password - I know of few basics and that's it. And it does work fine - I just don't want to pass out the root account password to people to let them have access for a messageboard or whatever... Thanks Guy Merritt -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Tables Load Slowly
INDEXes grasshopper, INDEXes. --On Sunday, June 13, 2004 11:26 -0700 David Blomstrom [EMAIL PROTECTED] wrote: I have a MySQL table with about 3,500 rows and 30 columns. There are rows for each of the world's nations, each of the 50 states, some 3,000 U.S. counties, Canada's provinces, etc. Obviously, I can't display the entire table on a web page. But even when I do an operation that displays just a single cell, it takes a long, long time to load. So I'm thinking of splitting the table into three tables - Nations, States and Counties. But I wanted to make sure I'm not doing something wrong first. I thought dynamic tables were supposed to process very quickly and that the loading time was associated primarily with the amount of data you want to display on a page. Am I wrong? __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I make empty MySQL use less disk space?
OPTIMIZE TABLE table; --On Sunday, June 13, 2004 20:48 +0200 Martin Olsson [EMAIL PROTECTED] wrote: Hi, I just noticed that the MySQL (win32) program which empty databases take up about 70Mb on my harddrive. I'm deploying MySQL as a part of a greater application (which is much smaller), is there any way I can reduce this size somewhat? I mean like, the examples, directory isnt really necessary is it? So which files can I exclude? mvh. /m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I make empty MySQL use less disk space?
Ahhh wait my bad, I misread your question The distribution includes benchmarks, examples, and a handful of documentation that can all be excluded. You can also use embedded mysql (IE mysql as a DLL sort of). --On Sunday, June 13, 2004 20:48 +0200 Martin Olsson [EMAIL PROTECTED] wrote: Hi, I just noticed that the MySQL (win32) program which empty databases take up about 70Mb on my harddrive. I'm deploying MySQL as a part of a greater application (which is much smaller), is there any way I can reduce this size somewhat? I mean like, the examples, directory isnt really necessary is it? So which files can I exclude? mvh. /m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Tables Load Slowly
--On Sunday, June 13, 2004 12:56 -0700 David Blomstrom
[EMAIL PROTECTED] wrote:
Aha! I added EXPLAIN, like this...
Please read the docs. And use the mysql CLI/monitor tool.
Explain does just that, it EXPLAINs to you, the database programmer, what
the MySQL engine will do when it goes about satisfying your query. In the
case below I can tell you that only a full text search will help (please
also look at reference documentation available at http://dev.mysql.com/ for
that).
In simple terms an index is exactly the same as an index for an
encyclopedia. You give have a key, say the name of a city, and you want to
know where you can find more information on that city. You look that city
up in the index, and it says go to page 3127, so you then flip to page 3127
and read all about say, Portugal. Now say you wanted to know all cities
with a population greater than 5000. The encyclopedia doesn't have an
index for this lets say. So what do you do? You read each and every
single entry for every city. Throwing out the ones that don't match and
writing down the ones that do, very time consuming. But if the
encyclopedia had an index that listed (and ranked) each city by its
population you could look there and quickly find them, and what pages they
are on.
A database index does PRECISELY the same thing for your database server.
IT tells it where (on the disk/in the MYD data file) it can find a record.
IF one doesn't exist for the KEY you're asking for it does what you'd do,
it reads the whole book! A database also needs a little more information
than that to do somethin intelligent, like in my example above with the
populations, this is where *CORRECT* column types come in. For gods sake
if it's an int, store it as one. It'll store smaller, and indexes will
work as you expect on them. A sickeningly common mistake of many beginners
is to use CHAR/VARCHAR or BLOB (TEXT, TINYTEXT...) for everything, and not
to use indexes.
In your case because of the leading % and trailing % wildcards a full table
scan is inevitable. What you want is a full text index, not a normal
index, this allows you to look very efficiently for keywords and pull them
out of the database with great speed. It's akin to an index that lists
every word in a book, and what pages that word occurs on (what records or
tuples in database speak).
Try rewriting your query like this (note how I add LIMIT, this tells the
database not to send us rows we're not going to use or display, this is
another tool you should read up on):
SELECT Name, Residents, Pop, Capital FROM basics WHERE MATCH (Capital)
AGAINST ('volcan') LIMIT 10;
but first execute this on your database:
CREATE FULLTEXT INDEX CapitalFTSIDX (Capital);
Or restrict your searching to things ending in %'s and use a standard
index/key.
HTH!
$res = mysql_query (EXPLAIN SELECT Name, Residents,
Pop, Capital FROM basics
where Capital like '%VOLCAN%') or die
(mysql_error());
but none of my data displays at all.
However, I did indeed strip out all my keys so I could
get my tables published online. I probably only needed
to get rid of the foreign keys, but I didn't want to
take any chances.
Anyway, I added a primary key, but the table still
loads slowly. However, I'm guessing that it isn't
enough to have a key on a table - that key also has to
be cited in your query/select statements. Is that
right?
Thanks.
--
Michael Loftis
Modwest Sr. Systems Administrator
Powerful, Affordable Web Hosting
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
--On Friday, August 22, 2003 1:21 PM -0400 Lefevre, Steven [EMAIL PROTECTED] wrote: that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. - OK, I see how it can give you double the read speed, but how can it give you have the write speed? Does it split the data between disks and then sync them later? No. You write 2x, remember. ;P Your write speed, best case and assuming no other bottlenecks (say an IDE a drive sharing the same controller with another IDE drive, esp. in the same mirror set) will be only as fast as the slowest drive in write mode. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
--On Friday, August 22, 2003 8:37 PM -0600 Jim McAtee [EMAIL PROTECTED] wrote: I don't quite understand the need to read data before any write. Why wouldn't it just calculate the parity of whatever is being written and just write it to disk? Wouldn't there be slack space, as with any disk system? Write a 1 byte file and it uses an N byte block on one disk, plus an N byte parity block on another. This wholly depends on the RAID subsystem, but better than 80% will need to either read the entire stripe, or hold off until they're writing the whole stripe at once. Remember the RAID is below the filesystem layer, and *separate* from it, esp. in the case of a hardware controller. Really big systems may (do) keep 'maps' of the space so they can cheat by not reading a strip when it knows it hasn't been written since (destructive) initialization and is thus all 0's. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Persistent annoying slave binlog corruption...
Recently, and pretty consistently our slave's relay logs have been getting garbage that is not in the master. The symptom is usually a truncated query with a few characters of garbage. The solution for now is to change master to to the errored master bin log and position and have it start replication from the spot where it is. When this happens the IO and SQL thread are found choked, with no error on show slave status, but the MySQL .err log shows the error. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.13 Memory leakage?
I'm noticing that our MySQL 4.0.13 system is probably leaking RAM (uptime ~10 days) ... PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 26046 mysql 9 0 548M 162M 44008 S 5.9 8.0 0:16 mysqld-max ... And it just keeps growing. Even with our admittedly aggressive cache settings it should have stopped a growing several days ago. All processes are now at or about those memory stats. Any ideas? Need any more info? This is a replication, and both slave and master appear to be leaking. The slave doesn't experience a lot of connections and seems to be leaking almost comparatively as much as the master so there is a common codepath somewhere. -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.13 Memory leakage?
With editing removing any passwords or sensitive stuff. (paths is all) # Example mysql config file. # You can copy this to one of: # /usr/local/etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/var) or # ~/.my.cnf to set user-specific options. # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] port= 3306 socket = /var/lib/mysql/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /var/lib/mysql enable-locking safe-show-database set-variable= wait_timeout=300 set-variable= max_connections=250 set-variable= max_user_connections=20 set-variable= key_buffer=384M set-variable= max_allowed_packet=4M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= ft_min_word_len=3 # Try some tuning with query caches. set-variable= query_cache_size=64M # Start logging log-bin server-id = 1 #log-slave-updates # Uncomment the following if you are using BDB tables set-variable = bdb_cache_size=64M set-variable = bdb_max_lock=10 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:128M;ibdata2:128M innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /data/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout --On Monday, June 16, 2003 16:02 -0700 Jeremy Zawodny [EMAIL PROTECTED] wrote: On Mon, Jun 16, 2003 at 04:41:49PM -0600, Michael Loftis wrote: I'm noticing that our MySQL 4.0.13 system is probably leaking RAM (uptime ~10 days) ... PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 26046 mysql 9 0 548M 162M 44008 S 5.9 8.0 0:16 mysqld-max ... And it just keeps growing. Even with our admittedly aggressive cache settings it should have stopped a growing several days ago. All processes are now at or about those memory stats. Any ideas? Need any more info? Without seeing your my.cnf file, it's difficult to say. Care to post it? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 13 days, processed 440,106,660 queries (372/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.13 Memory leakage?
Thanks for the tip, I'll take a look, both slave and master have innodb tables ( a few of our users utilize them). It's atleasta place to look...Though my feeling is it's something else. --On Tuesday, June 17, 2003 3:04 AM +0300 Heikki Tuuri [EMAIL PROTECTED] wrote: Michael, in the case the memory leak would be in InnoDB, you can monitor its memory allocation with SHOW INNODB STATUS\G Total memory allocated 50738427; in additional pool allocated 1762432 Regards, Heikki - Original Message - From: Michael Loftis [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, June 17, 2003 2:10 AM Subject: Re: MySQL 4.0.13 Memory leakage? With editing removing any passwords or sensitive stuff. (paths is all) # Example mysql config file. # You can copy this to one of: # /usr/local/etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/var) or # ~/.my.cnf to set user-specific options. # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] port= 3306 socket = /var/lib/mysql/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /var/lib/mysql enable-locking safe-show-database set-variable= wait_timeout=300 set-variable= max_connections=250 set-variable= max_user_connections=20 set-variable= key_buffer=384M set-variable= max_allowed_packet=4M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= ft_min_word_len=3 # Try some tuning with query caches. set-variable= query_cache_size=64M # Start logging log-bin server-id = 1 # log-slave-updates # Uncomment the following if you are using BDB tables set-variable = bdb_cache_size=64M set-variable = bdb_max_lock=10 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:128M;ibdata2:128M innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /data/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout --On Monday, June 16, 2003 16:02 -0700 Jeremy Zawodny [EMAIL PROTECTED] wrote: On Mon, Jun 16, 2003 at 04:41:49PM -0600, Michael Loftis wrote: I'm noticing that our MySQL 4.0.13 system is probably leaking RAM (uptime ~10 days) ... PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 26046 mysql 9 0 548M 162M 44008 S 5.9 8.0 0:16 mysqld-max ... And it just keeps growing. Even with our admittedly aggressive cache settings it should have stopped a growing several days ago. All processes are now at or about those memory stats. Any ideas? Need any more info? Without seeing your my.cnf file, it's difficult to say. Care to post it? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 13 days, processed 440,106,660 queries (372/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- 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] -- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
