Re: ResultSet NotUpdatabelProblem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Hello: I have recently posted the message attached at the bottom of this one to the mailing list. Since then, I have continued to work the sporadic and troublesome errors that are described in that attached message on otherwise perfectly working and proven code. I now have some insights that I would like to share with the group and solicit their thoughts and ideas as to what the root cause(s) may be. [snip] Todd, Would you mind filing a bug report with a testcase at http://bugs.mysql.com/ ? This issue would get the proper attention it needs if you use that 'channel'. Thanks! -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBW7BFtvXNTca6JD8RAhP0AKDE4i8+lj5CCFGitdo41mW/U1t3tgCeMTII 7/QoWU8myY2J1FZFQoBRX9E= =8Mac -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving field characteristics
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ian Gibbons wrote: On 27 Sep 2004 at 11:36, Rhino wrote: snip I really have no idea how Java is getting the information. I haven't tried very many of the metadata methods yet so I don't know how much Java can actually see and what is hidden. But I do know that it can see the descriptions of the columns. I had assumed that Mark Matthews, the guy who develops the JDBC drivers, had persuaded the other MySQL developers that the JDBC drivers *had* to be able to see the metadata and got permission to do that well in advance of the developers making the metadata available via the command line in the normal way for SQL catalogs. But that was strictly a wild guess; you may be totally correct in your assumptions. Hi, Maybe the Java driver is simply running this query: USE database; SHOW FIELDS FROM `tablename`; and caching the result? Regards Ian Ian, The JDBC driver uses this form (but does not cache): SHOW FIELDS FROM `databasename`.`tablename` -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBW7E3tvXNTca6JD8RAvsOAKCuZAYNMdODpmu5Nu4jUgrrYQyU/QCgugL2 53yHLg8A1r9KHTXxdMJIvv0= =Edut -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leasing time on a superfast mysql box
I didnt seeing it making it to the list, so please allow me to resend it. (B (B (B (B (B Hi Mark, (B (B I have the parts for 4 Opteron systems here (Dual CPU machines) (B (B All Types 844 to 848 luying right in front of me. (B 2x Sata HDD (B 2x SCSI HDD (B 6-12GB MEMORY (B (B I also have (B (B Fedora Core 1 (B Suse Linux 9.0 (B Turbo Linux 8 (B Mandrake 10 rc1 (B Windows 64 BETA (B (B What I am currently figuring out is how to get the SiL 3114 driver (B installed. I already spend a week (without success). (B http://www.be-known-online.com/modules/newbb/viewforum.php?forum=46 (B http://www.be-known-online.com/modules/newbb/viewtopic.php?topic_id=214forum=46 (B (B I give myself max another week before its up and running (I have to do it (B besides my real job). (B (B I would be using a dynamic dns connection unless you will afford a static (B IP (30$ /month). (B (B Following the Opteron discussions closely I would recommend Suse Linux. (B (B If this could be of interest than please contact me privately and let me (B know your requirements. The servers will be based in Tokyo/Japan. (B (B Best regards (B (B Nils Valentin (B Tokyo / Japan (B (B http://www.be-known-online.com/mysql (B (B (B (B (B (B (B I have a large database of zip codes with longitude and latitude of (B each, and I periodically generate a lookup table for each zip showing (B all zip codes within various radii. The process takes a day on my poor (B workstations 2.5GHz CPU, but I need to do it faster. Does anyone know of (B somewhere I can borrow or lease some time on a very fast mysql server to (B do this? All I need is mysql and perl on the machine. All processing (B including the trig is done by mysql. (B (B Thanks, (B (B Mark (B (B (B -- (B MySQL General Mailing List (B For list archives: http://lists.mysql.com/mysql (B To unsubscribe: (B http://lists.mysql.com/[EMAIL PROTECTED] (B (B (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird kind of join
This is the first one I tried, it works great, thanks
- Original Message -
From: Gordon [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; 'Diana Castillo' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, September 29, 2004 8:05 PM
Subject: RE: weird kind of join
You might also try
FROM table_a
INNER JOIN table_b
ON table_b.code = substring_index(table_a.code,';',1)
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the final
delimiter (counting from the left) is returned. If count is negative,
everything to the right of the final delimiter (counting from the right)
is
returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
This function is multi-byte safe.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 29, 2004 12:35 PM
To: Diana Castillo
Cc: [EMAIL PROTECTED]
Subject: Re: weird kind of join
try this (not tested):
FROM table_a
INNER JOIN table_b
ON table_b.code LIKE concat(table_a.code,';%')
or this:
FROM table_a
INNER JOIN table_b
ON table_b.code RLIKE concat('^',table_a.code,';')
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
http://dev.mysql.com/doc/mysql/en/Regexp.html
It's not going to be as quick as a direct lookup because of the CONCAT()
but at least we preserve the possibility of using an index for
table_b.code.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Diana Castillo [EMAIL PROTECTED] wrote on 09/29/2004 12:39:40 PM:
is there anyway to do a joint between a table that has codes like
this 10004;XXX or DE;YYY
with a table that has just the first part e.g 10004 or DE as the code
There is no set length to the code , all I know is that it is the
part before the semicolon.
so, I can't say
FROM table_a INNER JOIN table_b ON (table_a_code =
left(table_b.code,2))
because I will only match the ones that have 2 character codes.
Diana Castillo
Global Reservas, S.L.
C/Granvia 22 dcdo 4-dcha
28013 Madrid-Spain
Tel : 00-34-913604039 Ext 216
Fax : 00-34-915228673
email: [EMAIL PROTECTED]
Web : http://www.hotelkey.com
http://www.destinia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing problem with UTF8 in 4.1.4?
In article [EMAIL PROTECTED], Kevin Cowley [EMAIL PROTECTED] writes: If I knew why I wouldn't be asking. Now by our reconing the key of the fields is 343 bytes, encoding in UTF8 makes that key 343 bytes Not 1000 since under utf8 each character is encode in 8 bits. What makes you think so? Under UTF8 each character is encoded in 8, 16, 24, or 32 bits - it depends on the character. So a conservative guess is that each UTF8 character needs 4 bytes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
In article [EMAIL PROTECTED],
Aleksandr V. Dyomin [EMAIL PROTECTED] writes:
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method?
It's good if you expect the UPDATE normally to succeed. Otherwise,
you should first try the INSERT.
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
sometable set keyfield='somekeyvalue', count=1
Why this happens? On some hosts this messageis very rare, but on others
- so often... I cant understand reason :(
You have a race condition:
* Client 1 tries UPDATE, sees that it fails
* Client 2 tries UPDATE, sees that it fails
* Client 1 does INSERT - okay
* Client 2 does INSERT - duplictae key error
If you think this happens seldom, do the following:
1. Try UPDATE
2. If it fails: try INSERT
3. If it fails due to a duplicate key error: repeat step 1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexing problem with UTF8 in 4.1.4?
-Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED] Sent: 30 September 2004 12:16 To: [EMAIL PROTECTED] Subject: Re: Indexing problem with UTF8 in 4.1.4? In article [EMAIL PROTECTED], Kevin Cowley [EMAIL PROTECTED] writes: If I knew why I wouldn't be asking. Now by our reconing the key of the fields is 343 bytes, encoding in UTF8 makes that key 343 bytes Not 1000 since under utf8 each character is encode in 8 bits. What makes you think so? Under UTF8 each character is encoded in 8, 16, 24, or 32 bits - it depends on the character. So a conservative guess is that each UTF8 character needs 4 bytes. Because we're using an Latin character set which is encodable in 8 bytes or 16 for the Greek/Russian cyrilic character sets. However as posted by Jeremy March, MySQL automatically encodes UTF8 characters as a 3 byte representation, which explains the error we're seeing. ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Secure access to the Data structures and data within MySQL
Perhaps someone can provide me with some of his or her experiences if looking at MySQL to implement a secure from structure manipulation in a commercial application using MySQL. I am currently evaluating the possible migration of my app to MySQL as the basis. I already have tested the creation of structures and exporting of the 100+ tables and looked at viewing the data via Delphi on internal networks and over dial-up lines. I have also viewed the creation of Open Database copies that users can do What If analysis and structure manipulation with. This has been a successful start I am pleased to say Part of my is into the security of the data structures. I need to know that the creation of a structure and data I use that users/hackers would 1) not be able to access and manipulate the data structures and 2) only access the data according to the user definitions set under my applications administration utilities. The reason for this is that the data I deal with is both sensitive and relatively complex in its relationships. Data Integrity is vitally important. I do not want anyone having the chance of manipulating the live data structures and data other than via the application. That is not to say that users cannot manipulate their own data. In a controlled environment I currently provide an export of the data to an open format (which now includes MySQL) that users can manipulate in any way, manner or form they like. It is only the Live data that needs to be closely controlled and handled. This model has proved successful over the last 13 years but I am now looking for a new database engine. The pricing model is understood and am happy with the Commercial License approach and with me looking to roll out 200+ licenses over a 24 month period, this is not an issue. The issue is can it work in a similar controlled manner as my current application. If not where are the issues and can I live with the differences. One worry is the ability for users/hackers etc to overlay the user access database i.e. what to do if you forget the administration password scenario. This procedure will then allow open access to the data structures and the information of the application and the live data. I need to block this capability. What have others done to keep data structure security within their control only? Being a Windows house I am really only looking at the Windows version (at the moment). I use other high level languages, which doesnt include C++ i.e. I dont have the compiler to make my own special MySQL version. I don t really want to create my own special binaries anyway. I am not sure what encryption models are available. An external encryption library isnt really useful because this would block users using 3rd party report writers to gain read-only access to the data structures to write their reports. If an internal encryption mechanism is available then I would like to hear about it. Sorry about the message length, but I am trying to give a little bit of background to cover the more obvious Why dont you or Do you questions. Regards Kerry
Re: importing data into mysql from oracle using a text file
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Thanks, it is working for the mentioned format. Is there any method for importing directly the spooled file from oracle without changing the file format into the required format like using tab and newline. I don't know Oracle, but maybe two named pipes might work: mkfifo pipe1 mkfifo pipe2 oracleexport pipe1 mysql db -e load data infile 'pipe2' sed -e whatever pipe1 pipe2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (if !update then insert) sequence - result Duplicate key :(
Hi,
To solve this you can use the REPLACE command.
Regards,
Laercio.
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs
Sent: quinta-feira, 30 de setembro de 2004 08:25
To: [EMAIL PROTECTED]
Subject: Re: (if !update then insert) sequence - result Duplicate key :(
In article [EMAIL PROTECTED],
Aleksandr V. Dyomin [EMAIL PROTECTED] writes:
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method?
It's good if you expect the UPDATE normally to succeed. Otherwise, you
should first try the INSERT.
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
into sometable set keyfield='somekeyvalue', count=1
Why this happens? On some hosts this messageis very rare, but on
others
- so often... I cant understand reason :(
You have a race condition:
* Client 1 tries UPDATE, sees that it fails
* Client 2 tries UPDATE, sees that it fails
* Client 1 does INSERT - okay
* Client 2 does INSERT - duplictae key error
If you think this happens seldom, do the following:
1. Try UPDATE
2. If it fails: try INSERT
3. If it fails due to a duplicate key error: repeat step 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]
Error after upgrading to 4.1.5-gamma
All, a number of error situations point to some kind of problem that I have not been able to get to the bottom of so far, and cannot access some tables after upgrading to 4.1.5-gamma. First I got 'Could not fetch Schema Tables, MySQL Error Number 0' in MySQL Query Browser when trying to open some DB's (not all are affected), then, in the mysql command-line utiliy Didn't find any fields in table 'Foo' 'Show tables' shows the table alright, but 'describe' complains: ERROR 1017 (HY000): Can't find file: 'Foo.MYI' (errno: 2) I made a backup of all db files before the upgrade and there is also no .MYI file there, so I'm at a loss what's going on. Here is what I tried so far, after studying the documentation: # myisamchk Foo myisamchk: error: File 'Foo' doesn't exist After looking at this some more it dawned on me that 'Foo' is not a MyISAM but an ISAM table, as evidenced by the presence of Foo.ISD, Foo.ISM and Foo.frm So why does 4.1.5-gamma not recognise ISAM tables, and how do I get it to? I upgraded from 4.0.1. Cheers, Caro _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Graphical program to describe table relationships
Hi All, This is more of a general SQL database question than a MySQL question, but since I use MySQL almost exclusively I thought I would ask here. Up until now, when designing a database, I always plotted out the relationships on paper, with pen/pencil. I've filled large pieces of paper with all the tables and their relationships. However, now I will be doing one of these large projects, and the client wants to see how I propose to do all the relationships. I would like to have something more professional than a 24x36' piece of paper with my scribblings all over it. This has been quite good enough for me, although it is decidedly low-tech. I've seen commercial applications that allow you to do this sort of thing, but I do not have much of a budget. I am wondering if anyone can direct me to a free program that allows me to do this sort of thing? I would prefer some sort of client side application that will allow me to click and create new tables, relationships, and so forth. It does not need to actually *do* anything, i.e., it does not need to create or execute SQL queries. I just want to model the relationships. Suggestions? What do other people use to model their database? Thanks for any input! Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Graphical program to describe table relationships
Hi Joshua, Hi All, This is more of a general SQL database question than a MySQL question, but since I use MySQL almost exclusively I thought I would ask here. Up until now, when designing a database, I always plotted out the relationships on paper, with pen/pencil. I've filled large pieces of paper with all the tables and their relationships. However, now I will be doing one of these large projects, and the client wants to see how I propose to do all the relationships. I would like to have something more professional than a 24x36' piece of paper with my scribblings all over it. This has been quite good enough for me, although it is decidedly low-tech. I've seen commercial applications that allow you to do this sort of thing, but I do not have much of a budget. I am wondering if anyone can direct me to a free program that allows me to do this sort of thing? I would prefer some sort of client side application that will allow me to click and create new tables, relationships, and so forth. It does not need to actually *do* anything, i.e., it does not need to create or execute SQL queries. I just want to model the relationships. Suggestions? What do other people use to model their database? Hi, I use DbDesigner http://www.fabforce.net/dbdesigner4/. It has some bugs but at all it's a great graphical designing tool. And it's free. -- Use the force - read the source Piotr Duszynski mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out of Memory: Killed process mysqld
Hi people! Since I've upgraded from MySQL 4.1.0 to MySQL 4.1.5 my system keeps crashing. The only messages I see on the screen are: Out of Memory: Killed process 18440 (mysqld). Out of Memory: Killed process 18441 (mysqld). Out of Memory: Killed process 18442 (mysqld). Out of Memory: Killed process 18443 (mysqld). Out of Memory: Killed process 18444 (mysqld). Out of Memory: Killed process 18447 (mysqld). ... Then, the system is totally hanging, I never can do a correct shutdown. I must put the power off and reboot the system, then the system will run until the next same crash. Does anybody know where the problem can be? My system: - Red Hat 9 - Mysql 4.1.5 - PHP - Apache Thanks for every reply... Bye, Uli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question - Input limitations on mysql client?
As I mentioned in my post, I did wind up using a perl script, and I will continue down that path in the future. I was mainly wondering about limits to the mysql client for quick-n-dirty testing (as someone who is learning MySQL, as opposed to developing for a production environment). Thanks for pointing out the mysqlimport tool - that sounds like what I should really be using instead of cut-n-paste... Ted At 08:08 PM 9/29/2004, Andrew Kreps wrote: On Wed, 29 Sep 2004 10:05:29 -0400, Ted Byrne [EMAIL PROTECTED] wrote: Is the apparent line length limit a restriction imposed by the client? (And does it apply if you are piping or redirecting output from another process or a file?) It sounds like you should either be using a scripting language (like Perl or PHP), or using the command line tool mysqlimport or LOAD DATA INFILE from the mysql client. Any of those methods should allow you to insert long rows. -- 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-4.0.21 -- manpage formatting bug + patch
I'd like to suggest the attached patch fixing a manpage formatting bug. regards Peter Breitenlohner [EMAIL PROTECTED]diff -ur mysql-4.0.21.orig/man/mysqlaccess.1.in mysql-4.0.21/man/mysqlaccess.1.in --- mysql-4.0.21.orig/man/mysqlaccess.1.in 2004-09-07 00:29:40.0 +0200 +++ mysql-4.0.21/man/mysqlaccess.1.in 2004-09-30 15:10:17.0 +0200 @@ -1,6 +1,6 @@ .TH mysqlaccess 1 19 December 2000 MySQL @MYSQL_BASE_VERSION@ MySQL database .SH NAME -.BR mysqlaccess \- Create new users to mysql. +mysqlaccess \- Create new users to mysql. .SH USAGE mysqlaccess [host [user [db]]] OPTIONS .SH SYNOPSIS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A run away query? SOLVED
[snip] SELECT COUNT(DISTINCT( `cycle_flag`)) as numCycles FROM `crmReserve01`.`tblCDR` WHERE `cycle_flag` LIKE '___20__C_' [/snip] Query kept running and although stated as 'Killed' in processlist was hung up and using resources like a hawg... 1. MySQL had to be restarted 2. Repaired the table in question Works just fine now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
In article [EMAIL PROTECTED], Laercio Xisto Braga Cavalcanti [EMAIL PROTECTED] writes: Hi, To solve this you can use the REPLACE command. The problem is that Aleksandr wants to increment a counter, not set it to some fixed value. How could you use REPLACE for that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Old and new clients with MySQL 4.1.3
On 27.09.2004 07:08 (+0200), Donny Simonton wrote: Look for OLD_PASSWORD in the manual. Basically create a new user and then change the password with the OLD_PASSWORD function and then pre-4.1 clients can connect without any problems. We use it all of the time. Why is that not documented, again? http://dev.mysql.com/doc/mysql/de/Function_Index.html http://dev.mysql.com/doc/mysql/search.php?q=OLD_PASSWORDlang=decharset=iso-8859-1 (The search only finds a lot of password garbage. Could someone please fix it?) -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error after upgrading to 4.1.5-gamma #2
After some more study it turns out that my real problem seems to be that ALTER TABLE does not work: mysql alter table Foo type=myisam; ERROR 1017 (HY000): Can't find file: 'Foo.MYI' (errno: 2) The question remains, how do I get 4.1.5-gamma to recognize my ISAM tables? Cheers, Caro _ Express yourself instantly with MSN Messenger! Download today - it's FREE! hthttp://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
client ip address
Is there a variable or function in MySQL which resolves to the connecting client's IP address? I have SQL clients which will be connecting through a NAT firewall. I want to keep a record of IP of the client that connected to it. It would look something like this: UPDATE clients SET ipaddr=INET_ATON( @CLIENT_IP ) WHERE [EMAIL PROTECTED]; ^^ what goes here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Meaning of 1:1, 1:1 generalization, 1:n, 1:n non identifying, n:m
Hi All, I've been taking a look at DB Designer 4, and looking through the documentation (http://www.fabforce.net/dbdesigner4/doc/index.html) I am a little unclear on some of their nomenclature: '1:1' - Ok, one to one. Got it. '1:1' generalization - Don't know this. Obviously different somehow from one to one, but how? '1:n' - One to many, I assume. '1:n non identifying' - Nonidentifying? What does this mean? 'n:m' - Many to many? Again, not sure. Can anyone help clarify? Thanks! -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql ssl support
Hi All, I'm confused by seemingly conflicting information between the mysql doc, the high performance mysql book, and a mysql press release (http://www.mysql.com/news-and-events/press-release/release_2003_05.html) regarding mysql's SSL support. What's the difference between mysql 4.0's SSL support and 4.1's SSL support? I appreciate anyone's clarification. Thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: client ip address
Use this variable: $REMOTE_ADDR. Such that: $client_IP = $REMOTE_ADDR; UPDATE clients SET ipaddr= $client_IP WHERE [EMAIL PROTECTED]; I hope this is useful for your code. Mazhar Bilen - Original Message - From: Laszlo Thoth [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 30, 2004 8:11 PM Subject: client ip address Is there a variable or function in MySQL which resolves to the connecting client's IP address? I have SQL clients which will be connecting through a NAT firewall. I want to keep a record of IP of the client that connected to it. It would look something like this: UPDATE clients SET ipaddr=INET_ATON( @CLIENT_IP ) WHERE [EMAIL PROTECTED]; ^^ what goes here? -- 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]
Please help with query to show duplicate addresses... TIA!
I am trying to come up with a query that shows duplicate last names and the order numbers for each occurance of. I can get as far as determining the duplicates but my query result only outputs one order for each. Here is my current query, an example of the results and an example of the results I want. P.S. This is just an example, looking at duplicate last names is seldom of any practical value! Thanks for any help! customer_last_name order_number +---+-+ +smith + 1 + +smith + 2 + +smith + 3 + +-+ SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names FROM orders GROUP BY customer_last_name HAVING (duplicate_customer_last_names 1) Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +-+-+ Desired Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with query to show duplicate addresses... TIA!
Please post the structure of your orders table. (SHOW CREATE TABLE orders). I need to know what you are using as a primary key in order to help you to uniquely identify each duplicated row. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Paul Fine [EMAIL PROTECTED] wrote on 09/30/2004 02:22:44 PM: I am trying to come up with a query that shows duplicate last names and the order numbers for each occurance of. I can get as far as determining the duplicates but my query result only outputs one order for each. Here is my current query, an example of the results and an example of the results I want. P.S. This is just an example, looking at duplicate last names is seldom of any practical value! Thanks for any help! customer_last_name order_number +---+-+ +smith + 1 + +smith + 2 + +smith + 3 + +-+ SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names FROM orders GROUP BY customer_last_name HAVING (duplicate_customer_last_names 1) Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +-+-+ Desired Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: client ip address
Quoting Mazhar Bilen [EMAIL PROTECTED]: Use this variable: $REMOTE_ADDR. Such that: $client_IP = $REMOTE_ADDR; UPDATE clients SET ipaddr= $client_IP WHERE [EMAIL PROTECTED]; This doesn't appear to work: I don't think the MySQL server recognizes $REMOTE_ADDR as anything meaningful. Perhaps you're referring to the case in a PHP or Perl script where $REMOTE_ADDR is meaningful, and this value is being substituted into the string before the query is sent to MySQL? Unfortunately that solution is not appliable to my problem. I'm talking directly from a remote NAT'ed client to an SQL server without any Perl/PHP intermediary. I need a way to identify the client's IP address from the bare SQL server. mysql SELECT NOW() FROM nodes; 2004-09-30 11:27:53 mysql SELECT $REMOTE_ADDR FROM nodes; ERROR 1054 (42S22): Unknown column '$REMOTE_ADDR' in 'field list' mysql select $REMOTE_ADDR FROM nodes; $REMOTE_ADDR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: client ip address
You might look at the USER() and CURRENT_USER() functions. http://dev.mysql.com/doc/mysql/en/Information_functions.html However, I think those will end up showing you the NAT server's address and not the user's un-NATed address. If it were simple to de-NAT an address, most firewalls would become very ineffective. That's what NAT is there to do, protect the user behind the firewall by obscuring their internal address, right? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Laszlo Thoth [EMAIL PROTECTED] wrote on 09/30/2004 02:34:35 PM: Quoting Mazhar Bilen [EMAIL PROTECTED]: Use this variable: $REMOTE_ADDR. Such that: $client_IP = $REMOTE_ADDR; UPDATE clients SET ipaddr= $client_IP WHERE [EMAIL PROTECTED]; This doesn't appear to work: I don't think the MySQL server recognizes $REMOTE_ADDR as anything meaningful. Perhaps you're referring to the case in a PHP or Perl script where $REMOTE_ADDR is meaningful, and this value is being substituted into the string before the query is sent to MySQL? Unfortunately that solution is not appliable to my problem. I'm talking directly from a remote NAT'ed client to an SQL server without any Perl/PHP intermediary. I need a way to identify the client's IP address from the bare SQL server. mysql SELECT NOW() FROM nodes; 2004-09-30 11:27:53 mysql SELECT $REMOTE_ADDR FROM nodes; ERROR 1054 (42S22): Unknown column '$REMOTE_ADDR' in 'field list' mysql select $REMOTE_ADDR FROM nodes; $REMOTE_ADDR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
views/cursors in mysql 4.0
Hi, How to implement views/cursors in mysql 4.0?. Normally in mysql how the selected data is maintained in the recordsets?. Is there any other alternatives to implement views/cursors in mysql 4.0?. Please advise me for the better solution. 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.
Sync db
I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with query to show duplicate addresses... TIA!
If you are using 4.1, you can look into the GROUP_CONCAT function. Otherwise, try a self join like this: SELECT A.LastName,A.OrderNum,B.OrderNum FROM Orders AS A LEFT JOIN Orders AS B ON A.LastName=B.LastName WHERE A.OrderNum!=B.OrderNum ORDER BY A.LastName That joins the Order table with itself on LastName and filters out the non-duplicates, since there will always be at least one match, by filtering out Order Numbers that match for the duplicate last names. It will output something like this: A.LastName A.Order B.OrderNum - smith 1 2 smith 1 3 jones 5 10 jones 5 12 jones 5 23 I don't know if the not equal syntax I used != is valid for your version of MySQL. I know it works in 4.1. That shows that smith has order 1,2, and 3. Jones has 5,10,12 and 23. On Sep 30, 2004, at 2:22 PM, Paul Fine wrote: I am trying to come up with a query that shows duplicate last names and the order numbers for each occurance of. I can get as far as determining the duplicates but my query result only outputs one order for each. Here is my current query, an example of the results and an example of the results I want. P.S. This is just an example, looking at duplicate last names is seldom of any practical value! Thanks for any help! customer_last_name order_number +---+-+ +smith + 1 + +smith + 2 + +smith + 3 + +-+ SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names FROM orders GROUP BY customer_last_name HAVING (duplicate_customer_last_names 1) Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +-+-+ Desired Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: views/cursors in mysql 4.0
Views and Cursors are not available in MySQL 4.x. Usually your scripting language handles cursors for you (having them in 4.x would do you little good as stored procedures don't exist yet. Look at the current 5.x development for cursors and stored procedures). What language do you script/program with? What would you like to have done with your views and/or cursors if they _were_ available? Workarounds exist for almost every purpose but some of them use language-specific or library-specific options. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 03:01:51 PM: Hi, How to implement views/cursors in mysql 4.0?. Normally in mysql how the selected data is maintained in the recordsets?. Is there any other alternatives to implement views/cursors in mysql 4. 0?. Please advise me for the better solution. 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.
MySQL Database Corruption (InnoDB), according to Innodb Hot Backup
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: views/cursors in mysql 4.0
Hi, Thank you for your response. Strictly we have to use mysql 4.0 only. Actually the source data base is oracle 7.3, we need to migrate that database to mysql 4.0 . In oracle 7.3 views/cursors are used. That is why i need an alternative for views/cursors in mysql 4.0. We use C/C++ for Programming. could you please explain me the alternatives for views/cursors in mysql 4.0 and how to implement those in mysql 4.0?. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Fri 10/1/2004 12:40 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: views/cursors in mysql 4.0 Views and Cursors are not available in MySQL 4.x. Usually your scripting language handles cursors for you (having them in 4.x would do you little good as stored procedures don't exist yet. Look at the current 5.x development for cursors and stored procedures). What language do you script/program with? What would you like to have done with your views and/or cursors if they _were_ available? Workarounds exist for almost every purpose but some of them use language-specific or library-specific options. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 03:01:51 PM: Hi, How to implement views/cursors in mysql 4.0?. Normally in mysql how the selected data is maintained in the recordsets?. Is there any other alternatives to implement views/cursors in mysql 4. 0?. Please advise me for the better solution. 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. 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.
RE: views/cursors in mysql 4.0
Views - instead of running queries against views, you will have to run your queries against the underlying tables. This may mean lots of changes to your SQL statements. What once appeared as a single query may now have to be performed in multiple steps to achieve the same results. Usually, this kind of redesign actually improves application performance as you no longer rely on a set of table abstractions which require additional overhead to maintain. Cursors - Any SQL-scripted processing you did using cursors will have to be replaced with C/C++ routines that perform the same functions. You navigate recordsets according to the methods exposed by whichever library you use to interact with the server. Consult the documentation for the library you will use for specific details. Sorry I couldn't be more exact in my descriptions but you were not very clear on your requirements. Please respond with more detailed descriptions of exactly what you need from the database and I am sure someone on the list can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 03:24:52 PM: Hi, Thank you for your response. Strictly we have to use mysql 4. 0 only. Actually the source data base is oracle 7.3, we need to migrate that database to mysql 4.0 . In oracle 7.3 views/cursors are used. That is why i need an alternative for views/cursors in mysql 4.0. We use C/C++ for Programming. could you please explain me the alternatives for views/cursors in mysql 4.0 and how to implement those in mysql 4.0?. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Fri 10/1/2004 12:40 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: views/cursors in mysql 4.0 Views and Cursors are not available in MySQL 4.x. Usually your scripting language handles cursors for you (having them in 4.x would do you little good as stored procedures don't exist yet. Look at the current 5.x development for cursors and stored procedures). What language do you script/program with? What would you like to have done with your views and/or cursors if they _were_ available? Workarounds exist for almost every purpose but some of them use language-specific or library-specific options. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 03:01:51 PM: Hi, How to implement views/cursors in mysql 4.0?. Normally in mysql how the selected data is maintained in the recordsets?. Is there any other alternatives to implement views/cursors in mysql 4. 0?. Please advise me for the better solution. 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. 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.
RE: views/cursors in mysql 4.0
Hi, Thank you very much for your reply. The existed system , a network management tool , is developed using C as program lanuage and oracle 7.3 as the database. There are 3 layers in the database like table layer, view layer and DBview layer. The DBView layer communicates through API's with both table and view layers. Views and tables are created dynamically through API's. Here Views are created for each table (reason i too donot know). And used some stored procedures at module level and some triggers on some table before/after updating. The above existed system's whole database layer including table layer/views layer and DBView layer to be migrated to mysql 4.0 with out innodb. Need to change the API's also. So, the views/stored procedurs (some of them used cursors)/ triggers need to be converted to mysql 4.0 from oracle 7.3. That is the requrement. Views - instead of running queries against views, you will have to run your queries against the underlying tables. This may mean lots of changes to your SQL statements. What once appeared as a single query may now have to be performed in multiple steps to achieve the same results. Usually, this kind of redesign actually improves application performance as you no longer rely on a set of table abstractions which require additional overhead to maintain. --- If i want to use the same data by querying underlying tables again how to use that?. where to store that data for using again?. Shall i need to write the same query again when i need the same data?. In the existed system views are created only once at runtime. could you please explain me in detail. Cursors - Any SQL-scripted processing you did using cursors will have to be replaced with C/C++ routines that perform the same functions. You navigate recordsets according to the methods exposed by whichever library you use to interact with the server. Consult the documentation for the library you will use for specific details. --- Supggest me the best API's to perform SQL- scripted performance?. Could you please mention what are all the libraries we needed for the general cursor processings. Given the oracle code which is used the cursors, could you please mention the equivalent libraries for those CREATE procedure pstub(pname varchar2, uname varchar2, stubSpec in out varchar2, stubText in out varchar2, flags varchar2 := '6') is rc varchar2(40); ty varchar2(5); cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) is select line from sys.pstubtbl where (una is null or username = una) and (dbna is null or dbname = dbna) and lun = luna and lutype = luty order by lineno; begin -- main sys.pstubt(pname, uname, '', flags, rc); if rc like '$$$%' then stubText := rc; return; end if; if not (rc = 'PKG' or rc = 'SUB') then stubText := '$$$ other'; return; end if; stubSpec := ''; stubText := ''; if rc = 'PKG' then for s in tub(uname, '', pname, 'PKS') loop stubSpec := stubSpec || s.line; end loop; end if; if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if; for s in tub(uname, '', pname, ty) loop stubText := stubText || s.line; end loop; end; Could you please let me know the alternatives for stored procedures and Triggers in mysql 4.0. thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Fri 10/1/2004 1:16 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: views/cursors in mysql 4.0 Views - instead of running queries against views, you will have to run your queries against the underlying tables. This may mean lots of changes to your SQL statements. What once appeared as a single query may now have to be performed in multiple steps to achieve the same results. Usually, this kind of redesign actually improves application performance as you no longer rely on a set of table abstractions which require additional overhead to maintain. Cursors - Any SQL-scripted processing you did using cursors will have to be replaced with C/C++ routines that perform the same functions. You navigate recordsets according to the methods exposed by whichever library you use to interact with the server. Consult the documentation for the library you will use for specific details. Sorry I couldn't be more exact in my descriptions but you were not very clear on your requirements. Please respond with more detailed descriptions of exactly what you need from the database and I am sure someone on the list can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 03:24:52 PM: Hi, Thank you for your response. Strictly we have to use mysql 4. 0 only. Actually
Re: views/cursors in mysql 4.0
Could you please let me know the alternatives for stored procedures and Triggers in mysql 4.0. The only alternative is application code. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: views/cursors in mysql 4.0
My comments embedded below [EMAIL PROTECTED] wrote on 09/30/2004 04:18:02 PM: Hi, Thank you very much for your reply. The existed system , a network management tool , is developed using C as program lanuage and oracle 7.3 as the database. There are 3 layers in the database like table layer, view layer and DBview layer. The DBView layer communicates through API's with both table and view layers. Views and tables are created dynamically through API's. Here Views are created for each table (reason i too donot know). And used some stored procedures at module level and some triggers on some table before/after updating. The above existed system's whole database layer including table layer/views layer and DBView layer to be migrated to mysql 4.0 with out innodb. Need to change the API's also. So, the views/stored procedurs (some of them used cursors)/ triggers need to be converted to mysql 4.0 from oracle 7.3. That is the requrement. triggers and stored procedures won't exist until MySQL 5.X. All of the administration that you automated through triggers will now have to be coded into your application. Any action that you had coded in a stored procedure will now have to be recoded into your application. Views - instead of running queries against views, you will have to run your queries against the underlying tables. This may mean lots of changes to your SQL statements. What once appeared as a single query may now have to be performed in multiple steps to achieve the same results. Usually, this kind of redesign actually improves application performance as you no longer rely on a set of table abstractions which require additional overhead to maintain. --- If i want to use the same data by querying underlying tables again how to use that?. where to store that data for using again?. Views are not stored bits of information. They are queries that you query against. If you need to store information, use either a table or a temporary table. Shall i need to write the same query again when i need the same data?. In the existed system views are created only once at runtime. could you please explain me in detail. Where you once were able to query the results of a query (the contents of a view), you will have to rewrite your SQL statements so that they get their data directly from the tables the views would have abstracted for you. You lose one layer of abstraction by not having views available. Cursors - Any SQL-scripted processing you did using cursors will have to be replaced with C/C++ routines that perform the same functions. You navigate recordsets according to the methods exposed by whichever library you use to interact with the server. Consult the documentation for the library you will use for specific details. --- Supggest me the best API's to perform SQL- scripted performance?. Could you please mention what are all the libraries we needed for the general cursor processings. Given the oracle code which is used the cursors, could you please mention the equivalent libraries for those According to this page: http://dev.mysql.com/downloads/ You have several options of APIs. Use which one fits your development environment. CREATE procedure pstub(pname varchar2, uname varchar2, stubSpec in out varchar2, stubText in out varchar2, flags varchar2 := '6') is rc varchar2(40); ty varchar2(5); cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) is select line from sys.pstubtbl where (una is null or username = una) and (dbna is null or dbname = dbna) and lun = luna and lutype = luty order by lineno; begin -- main sys.pstubt(pname, uname, '', flags, rc); if rc like '$$$%' then stubText := rc; return; end if; if not (rc = 'PKG' or rc = 'SUB') then stubText := '$$$ other'; return; end if; stubSpec := ''; stubText := ''; if rc = 'PKG' then for s in tub(uname, '', pname, 'PKS') loop stubSpec := stubSpec || s.line; end loop; end if; if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if; for s in tub(uname, '', pname, ty) loop stubText := stubText || s.line; end loop; end; This stored procedure would need to be recoded. Possibly as a class's method or as a stand alone function. No matter where in your code it resides, you will have to manually manage the records and values once automated for you by the cursor. You would run a query to get select line from sys.pstubtbl where (una is null or username = una) and (dbna is null or dbname = dbna) and lun = luna and lutype = luty order by lineno; Then step through those results one record at a time until you exhaust your results. During each step through your results you would need to duplicate the processing that occurred during each step of the cursor through its recordset. Could you please let me know
Re: client ip address
Quoting [EMAIL PROTECTED]: You might look at the USER() and CURRENT_USER() functions. http://dev.mysql.com/doc/mysql/en/Information_functions.html Aha! I think this is what I was looking for: the client host from which [I] connected. I'll have to do some in-query string processing to extract the host and resolve the IP but that's at least doable. However, I think those will end up showing you the NAT server's address and not the user's un-NATed address. If it were simple to de-NAT an address, most firewalls would become very ineffective. That's what NAT is there to do, protect the user behind the firewall by obscuring their internal address, right? Yes, but fortunately I'm looking for the NAT server's address, not the client's NAT'ed address. The client knows its address and could insert this into the SQL query itself. Both the client and the server also know their own addresses (of course). But the only person who knows the un-NAT'ed address is the SQL server: the clients don't even necessarily know they're being NAT'ed. That's why I need to get this information from the server-side. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: views/cursors in mysql 4.0
Hi, Thank you very much for your reply. So for the cursors result set C API's will be suitable ..right?. 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.
Re: Please help with query to show duplicate addresses... TIA!
Brent Baisley wrote: If you are using 4.1, you can look into the GROUP_CONCAT function. Otherwise, try a self join like this: SELECT A.LastName,A.OrderNum,B.OrderNum FROM Orders AS A LEFT JOIN Orders AS B ON A.LastName=B.LastName WHERE A.OrderNum!=B.OrderNum ORDER BY A.LastName That joins the Order table with itself on LastName and filters out the non-duplicates, since there will always be at least one match, by filtering out Order Numbers that match for the duplicate last names. It will output something like this: A.LastNameA.OrderB.OrderNum - smith12 smith13 jones510 jones512 jones523 No, it won't. It will produce output like this: +--+--+--+ | LastName | OrderNum | OrderNum | +--+--+--+ | jones| 10 |5 | | jones| 12 |5 | | jones| 23 |5 | | jones|5 | 10 | | jones| 12 | 10 | | jones| 23 | 10 | | jones|5 | 12 | | jones| 10 | 12 | | jones| 23 | 12 | | jones|5 | 23 | | jones| 10 | 23 | | jones| 12 | 23 | | smith|2 |1 | | smith|3 |1 | | smith|1 |2 | | smith|3 |2 | | smith|1 |3 | | smith|2 |3 | +--+--+--+ 18 rows in set (0.08 sec) Each row in A is paired with each non-matching row in B. I don't know if the not equal syntax I used != is valid for your version of MySQL. I know it works in 4.1. It is. http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html That shows that smith has order 1,2, and 3. Jones has 5,10,12 and 23. The answer is in there, but it's a mess. A simple SELECT LastName, OrderNum FROM orders would do, except you want to leave out the rows with unique LastName values. The following should work: # Collect the non-unique last names CREATE TEMPORARY TABLE lastnames SELECT LastName FROM orders GROUP BY LastName HAVING COUNT(*) 1; # Find the rows with the non-unique last names SELECT orders.LastName, OrderNum FROM orders, lastnames WHERE orders.Lastname = lastnames.lastname; ORDER BY orders.Lastname, OrderNum; +--+--+ | LastName | OrderNum | +--+--+ | jones|5 | | jones| 10 | | jones| 12 | | jones| 23 | | smith|1 | | smith|2 | | smith|3 | +--+--+ 7 rows in set (0.01 sec) # Clean up DROP TABLE lastnames; With 4.1 and subqueries, this becomes: SELECT LastName, OrderNum FROM orders WHERE LastName IN (SELECT LastName FROM orders GROUP BY LastName HAVING COUNT(*) 1) ORDER BY Lastname, OrderNum; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compare schemas
Does anybody know of any free tools to compare two mysql schemas? Thanks.
Configure Statement
Hello: While reading Red Hat / Apache 2 + SSL /PHP /mySQL http://www.suteki.nu/howto.html, I came across the following configure state (Page 3 Section 2): ./configure --with-openssl --with-isam --prefix=/usr/local/mysql As you all know this configures the mySQL. My question is; what does --with-openssl does? Why it is needed? How when it becomes useful? This is a handful of a question but I sure like to learn!! Hope somebody give a clear (with example if possible) answer. Thanks. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
