Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 01:03 (+0100), Eric Braswell wrote: Does that make sense? Did I misunderstand? That's exactly what I'm doing right now. In my test network: MySQL 4.0 - 192.168.0.32 (mysql4.myhost) MySQL 5.0 - 192.168.0.33 (mysql5.myhost) But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 to a) keep both servers on different IPs with DNS names and b) let system applications connect via the localhost interface which lets me put additional security into it by only allowing access from localhost for these applications, while all other users may connect from everywhere. And of course, connecting to 127.0.0.1 is the obvious way in a small webhosting environment, but that's not so important, I can tell my users to change their database connection to the new name (mysql4) when I migrate to the new server. Or is there another way to only allow certain users to connect from localhost? As I think about it, a local TCP forwarder would accept connections on localhost, but MySQL won't see that, so this wouldn't work anyway. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 I'm going to assume you are using some kind of Unix-like platform. When you connect to localhost, you are actually connecting by default through a Unix socket file, not TCP/IP, because it is much faster. Thus it is perfectly possible to do what you outline without having to specify multiple IPs in the bind-address option. External connections will use TCP/IP, internal will use a unix socket file. If you wanted to use -only- the unix socket file to connect to a particular instance, thus completely disallowing external connections, you can use the skip-networking option. I think the clearest description of this is actually: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html As you can see, the same concept works on Windows, except through named pipes. To verify what type of connection you are using, use the status command. You'll see a line like: Connection: Localhost via UNIX socket -or- Connection: somehostname via TCP/IP Or is there another way to only allow certain users to connect from localhost? Of course. Grant privileges only to connect to localhost. See http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html Or use skip-networking to turn off TCP/IP for that instance. As I think about it, a local TCP forwarder would accept connections on localhost, but MySQL won't see that, so this wouldn't work anyway. Completely unnecessary! You are far better off simply using the default behaviour of connecting through a socket, not TCP/IP, when connecting from the same machine. What you are wanting to do is not that unusual, and I'm confident you'll find there are ample options to get the setup that works best for you. I strongly suggest you read up in the manual on how MySQL handles TCP/IP and local connections. Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 20:02 (+0100), Eric Braswell wrote: I'm going to assume you are using some kind of Unix-like platform. Correct, it's a Debian Linux x86. When you connect to localhost, you are actually connecting by default through a Unix socket file, not TCP/IP, because it is much faster. Thus it is perfectly possible to do what you outline without having to specify multiple IPs in the bind-address option. External connections will use TCP/IP, internal will use a unix socket file. I know this strange behaviour, but it's not working for me. Because I'm running two servers, I needed to alter the socket names so that in the end, clients won't find the default socket location anymore. So I'm not connecting to localhost but to 127.0.0.1, which works again. If you wanted to use -only- the unix socket file to connect to a particular instance, thus completely disallowing external connections, you can use the skip-networking option. None of the MySQL servers should not be reachable by TCP/IP at all, so skip-networking is not what I'm looking for. Or is there another way to only allow certain users to connect from localhost? Of course. Grant privileges only to connect to localhost. You mean say localhost as hostname for the GRANT command? From my experience (which is actually not so clear in this) this doesn't work. Granting access only for connections from localhost when connecting to the server actually from the same host but through its external IP/hostname, it won't let me in. Need to test it further. I guess the correct way would be to allow access from the external IP of the server? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 09.04.2006 01:03 (+0100), Eric Braswell wrote: Does that make sense? Did I misunderstand? That's exactly what I'm doing right now. In my test network: MySQL 4.0 - 192.168.0.32 (mysql4.myhost) MySQL 5.0 - 192.168.0.33 (mysql5.myhost) But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 Yves, You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. Then your clients won't have to change anything and they can migrate to MySQL5 when they're ready. Or am I mistaken? - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: history not working -- it's a bug!
I installed 5.0 and 5.1-beta on three different system (all are Fedora Core 4), and each time when I use the command-line client, I can not use the familiar up-arrow feature that normally lets you scroll through queries issued during previous command-line sessions. My history is completely blank. the $HOME/.mysql_history file does exist, but all it's got in it is: _HiStOrY_V2_ in the first line. Please help! what am i doing wrong? to my surprise, i just discovered that connecting to the server over remote IP, with a command-line client on the remote machine, the command-line history DOES get preserved. But, that, of course, is due to the separate functionality of server and client (as I am now clued in by this), so that .mysql_history is a client-only thing, and, in the case of remoteness, it's the client machine that writes to its own local .mysql_history file. So, why is my client failing to write to .mysql_history? This appears to be an out-of-the-box, default setting, or am I wrong? Does it work for anyone out there? Please help! After giving up on this a few weeks ago (time scarcity) i returned to it today and discovered this: http://lists.mysql.com/commits/2515 It seems my problems stemmed from the above bug. If you build mysql yourself without the ./configure option --without-readline, which is what i had been doing, your version will fail to copy stuff from .mysql_history.TMP to .mysql_history after a session, in effect, leaving the history blank. Currently there appear to be two solutions: 1) give ./configure the --without-readline option (not sure what the tradeoffs are here, but there's no metion of any harm in he above post.) 2) apply msvensson's patch (see above link) I tried applying the patch, but it failed for me; however, #1 restored my command-line history functionality. The bug exists in both 5.0 and 5.1.7-beta versions. I am assuming the patch should work for both, since there is no diff-erence between the files /version-source-root/cmd-line-utils/readline/readline.c , which is the file that needs to be patched. I am surprised no one responded about this. Is there not anyone else out there besides me doing their own build !? Surely they should have noticed non-functioning command history. I understand that the moral of the story is RTFBR (read the fine bug report), but, still... andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reserevd Error -7776 -- Urgent
C K wrote: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK I first encountered this issue yesterday, when mucking around with Access 2003. When I dropped the timestamp(14) field from my table, everything worked ( no #DELETED# after updating records ). I tested with about 3 records. No guarantees it work work the 4th time ... -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New MySQL Transaction Engine Announced
On Friday, 7 April 2006 at 9:52:46 -0500, mos wrote: In case anyone is interested in where MySQL is going with transactions http://www.computerworld.com.au/index.php/id;1262876365;relcomp;1 Before anybody goes overboard here based on the Subject: line, read the article. This is not New MySQL Transaction Engine Announced, but New MySQL Transaction Engine to be Announced: MySQL is due to unveil a new transaction database engine at its upcoming user conference later this month, according to a company executive. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpEQUM6FGFYV.pgp Description: PGP signature
database backup and restore
Dear all, I have mysql 4.1.11 on my current server, i need my database restore another server.. (another server mysql version is 4.1.11 same.) And now... I just created new my database to new server following command: CREATE DATABASE dbname DEFAULT CHARACTER SET latin5 COLLATE latin5_turkish_ci; because our database and tables uses latin5 and turkish_ci collation. How can i get correct backup and restore for this character sets? example; mysqldump -uUSER -pDBNAME /path/backup.sql and restore; mysql -uUSER -pDBNAME /path/backup.sql is this correct for me? OR mysqldump --default-character-set=latin5 -uUSER -pDBNAME /path/backup.sql and restore; mysql --default-character-set=latin5 -uUSER -pDBNAME /path/backup.sql some peoples suggest me --default-character-set= variable for mysql 4.1.x backups and restore, is this correct? also if we backup database --default-character-set option, restore command is same option will need to use? Which method is good for me? if someone can help me for this, i'm pretty happy. Thanks for all, and sorry for very bad english. _ Yagmura yakalanmamak için sadece semsiyenize degil, MSN hava durumuna güvenin! http://www.msn.com.tr/havadurumu/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New to TRIGGER and CALL. Example gives errors.
I'm trying to follow the example in the manual to create a trigger: http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html #DROP TRIGGER upd_check; delimiter // CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys FOR EACH ROW BEGIN IF NEW.skey 1 THEN SET NEW.skey = 1; ELSEIF NEW.skey 9 THEN SET NEW.skey = 9; END IF; END;// delimiter ; All I'm trying to do is enforce that my starkeys.skey column is always in the range of 1 through 9. I was planning to start with this example and work my way up. Ideally it should check on UPDATE or INSERT. The manual recommended: It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to invoke the same routine from within several triggers. But I don't know how to do that yet. vmware public_html # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 But I just get these errors: Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter // CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys FOR EACH ROW BEG' at line 2 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF NEW.skey 9 THEN SET NEW.skey = 9' at line 1 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '// delimiter' at line 1 (0 ms taken) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Effective-dating
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a Begin date and an End date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an Expires date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an Effective as of date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's Hi-Performance MySQL and the MySQL Reference
Manual.
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
effective dating has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. Then your clients won't have to change anything and they can migrate to MySQL5 when they're ready. Or am I mistaken? From Yves' last post, I concur with this. It's definitely not optimal -- you are substantially reducing performance to avoid a simple parameter that you could easily script (I assumed you would be prepared to simply specify the socket name as a parameter), but if your requirement is that clients must connect using default settings, then this is one way. Personally I'd find another. Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
