Re: MySQL Administrator crashes upon launch...
Wolfram Stebel wrote: Am 30.09.2005 16:29 Uhr schrieb "Nuno Pereira" unter <[EMAIL PROTECTED]>: in Users/user/Library/Logs/CrashReporter/java.crash.log one mile of traces Regards Wolfram ** Host Name: Erde Date/Time: 2005-09-30 15:13:52.351 +0200 OS Version: 10.4.2 (Build 8C46) Report Version: 3 Command: MySQL Administrator Path:/Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator Parent: WindowServer [70] Version: ??? (1.1.0) PID:2192 Thread: 0 Exception: EXC_BAD_ACCESS (0x0001) Codes: KERN_PROTECTION_FAILURE (0x0002) at 0x Thread 0 Crashed: 0 libSystem.B.dylib 0x900031e8 strlen + 8 1 libSystem.B.dylib 0x9001f880 sscanf + 88 2 com.mysql.MySQLToolsCommon 0x100354e0 myx_get_mysql_version + 96 3 com.mysql.MySQLToolsCommon 0x100355b4 myx_get_mysql_major_version + 24 4 com.mysql.MySQLToolsCommon 0x10001e50 -[MConnectionPanel(Private) connectionFinished:] + 216 5 com.apple.Foundation 0x92890760 __NSFireMainThreadPerform + 276 6 com.apple.CoreFoundation 0x9077c108 __CFRunLoopPerformPerform + 104 7 com.apple.CoreFoundation 0x9074bc8c __CFRunLoopDoSources0 + 384 8 com.apple.CoreFoundation 0x9074b1bc __CFRunLoopRun + 452 9 com.apple.CoreFoundation 0x9074ac3c CFRunLoopRunSpecific + 268 10 com.apple.HIToolbox0x93129ac0 RunCurrentEventLoopInMode + 264 11 com.apple.HIToolbox0x931290cc ReceiveNextEventCommon + 244 12 com.apple.HIToolbox0x93128fc0 BlockUntilNextEventMatchingListInMode + 96 13 com.apple.AppKit 0x93623e44 _DPSNextEvent + 384 14 com.apple.AppKit 0x93623b08 -[NSApplication nextEventMatchingMask:untilDate:inMode:dequeue:] + 116 15 com.apple.AppKit 0x9362006c -[NSApplication run] + 472 16 com.apple.AppKit 0x937108bc NSApplicationMain + 452 17 com.mysql.Administrator0x5a24 _start + 392 (crt.c:267) 18 com.mysql.Administrator0x5898 start + 48 Thread 1: 0 libSystem.B.dylib 0x9002ca78 semaphore_wait_signal_trap + 8 1 libSystem.B.dylib 0x9003125c pthread_cond_wait + 508 2 com.apple.Foundation 0x9288a0a0 -[NSConditionLock lockWhenCondition:] + 68 3 com.apple.AppKit 0x936c07f8 -[NSUIHeartBeat _heartBeatThread:] + 324 4 com.apple.Foundation 0x92882f34 forkThreadForFunction + 108 5 libSystem.B.dylib 0x9002c3b4 _pthread_body + 96 Thread 2: 0 libSystem.B.dylib 0x9002ca78 semaphore_wait_signal_trap + 8 1 libSystem.B.dylib 0x9003125c pthread_cond_wait + 508 2 com.apple.Foundation 0x92886448 -[NSConditionLock lock] + 52 3 com.apple.Foundation 0x928905a4 -[NSObject(NSMainThreadPerformAdditions) performSelectorOnMainThread:withObject:waitUntilDone:modes:] + 716 4 com.apple.Foundation 0x928902b8 -[NSObject(NSMainThreadPerformAdditions) performSelectorOnMainThread:withObject:waitUntilDone:] + 120 5 com.mysql.MySQLToolsCommon 0x10002040 -[MConnectionPanel(Private) connectThread:] + 144 6 com.apple.Foundation 0x92882f34 forkThreadForFunction + 108 7 libSystem.B.dylib 0x9002c3b4 _pthread_body + 96 Thread 0 crashed with PPC Thread State 64: srr0: 0x900031e8 srr1: 0x1200f030 vrsave: 0x cr: 0x82002202 xer: 0x lr: 0x9001f880 ctr: 0x900031e0 r0: 0x9001f880 r1: 0xbfffe310 r2: 0x0044 r3: 0x r4: 0x100ae024 r5: 0xbfffe4e8 r6: 0xbfffe4ec r7: 0x r8: 0x6e48476d r9: 0x r10: 0x10034a04 r11: 0xa00063fc r12: 0x900031e0 r13: 0x r14: 0x0001 r15: 0x r16: 0x0001 r17: 0xb2d0 r18: 0x r19: 0x0035ce40 r20: 0x r21: 0x r22: 0x0001 r23: 0x0030cb80 r24: 0x r25: 0x0030cb88 r26: 0x0030cc98 r27: 0x046a r28: 0xbfffe34c r29: 0x r30: 0x100ae024 r31: 0x9001f830 Binary Images Description: 0x1000 -0x54fff com.mysql.Administrator ??? (1.1.0) /Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator 0x1000 - 0x100d6fff com.mysql.MySQLToolsCommon ??? (1.0) /Applications/MySQL Administrator.app/Contents/Frameworks/MySQLToolsCommon.framework/Versions/1. 0.0/MySQLToolsCommon 0x8fe0 - 0x8fe51fff dyld 43.1/usr/lib/dyld 0x9000 - 0x901a6fff libSystem.B.dylib /usr/lib/libSystem.B.dylib 0x901fe000 - 0x90202fff libmathCommon.A.dylib /usr/lib/system/libmathCommon.A.dylib 0x90204000 - 0x90257fff com.apple.CoreText 1.0.0 (???) /System/Library/Frameworks/ApplicationSe
Re: timing problem
Tony Leake wrote:
On Thu, 2005-09-29 at 16:15 +0100, Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
This one is interesting in terms of concurrency...
Is the app2 the one responsable for setting the flag? I supose that it is.
If that happens it's important that app2 doesn't mess with the flag, i
mean, it may lead to problems if app2 sees that app1 writes the sales
data, app2 sees it, starts reading it, app1 writes more data and app2
sets the flag without seeing that app1 have written more data, and sets
the flag without reading the new one.
Make this work without problems can be tricky, and I don't see a good
solution to this in five minutes. When app2 reads data what app does
with it? How app2 sees what was the last data she read?
--
Nuno Pereira
I don't think there is any concurency problem:
App 2 sets the flag and then polls for it to be unset, it is unset by
app 1 when it has written all of the sales data. meanwhile app 2 is
polling for the flag to be unset again and does nothing until it is, if
app 1 dies before unsetting the flag app then app 2 will sit in a loop
forever.
That isn't true if you are using transactions.
when the flag is unset, app 2 reads it, process it and posts it to a
website via xmlrpc
app 2 site in a shell script while loop: (pseudocide)
while (1){
start app 2
sleep (60)
}
so there can never be more than one instance of app 2 running.
tony
You may be right, but app2 is running while app1 is...
You know better if there are problems...
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem
Marco Baar wrote: of cause your right. hello dear Nuno Pereira, I use mysql since 2 years and collected about 50 MB user data. I ve got a performance problem during selecting the main table. The table has about 200.000 rows and I make a simple select. I only select over 4 attributes with "=", no joins, no subselects etc. The select needs 0,3 secs I think thats normal. But I had to change it and now it has an ORDER BY LIMIT 50. Now the select take 3,5 secs and slows down my computer completely. (The sound stops) 2nd question: I wanna run my tool as a daimon or server in background. It must not slowing down my PC, but it does during SQL command (e.g. bigger selects ). Is it possible im multiuser mode to set a user to low priority. This user is the daimon so it can run in background without making problems. So long Marco Baar Germany --- Ursprüngliche Nachricht --- Von: Nuno Pereira <[EMAIL PROTECTED]> An: Marco Baar <[EMAIL PROTECTED]> Kopie: mysql@lists.mysql.com Betreff: Re: Performance problem Datum: Thu, 29 Sep 2005 11:24:00 +0100 What? Please post in english. Marco Baar wrote: Hallo, Ich benutze mysql seit 2 Jahren. Inzwischen haben sich 50MB Nutzdaten angesammeln und hab ein Problem mit der Performance während der Abfrage meiner "Haupttabelle". Die Tabelle hat ca 200.000 Datensätze und ich mache im verhältnis zu anderen Abfragen doch ein eher simplen select. Ich selektiere mit Prüfung auf 4 Attribute (mit "=" ) und es dauert 0,2-0,4 sekunden. Damit kann ich leben. Diese Abfrage musste ich abwandeln und will mir nur die ersten 50 Ergebnisse anzeigen, die nach Datum sortiert sind. Diese allerdings dauert ~3,5 sekunden und legt meinen Rechner lahm. Sogar der Sound wird unterbrochen. 2. Frage: Ich würde dies gerne als Daimon oder Server im Hintergrund laufen lassen, so dass mich die Datenbankabfragen nicht im Laufenden Betrieb stören. Trotzdem sollen doch relativ complexe Abfragen gemacht werden. Ist es möglich, bestimmte Benutzer mit sehr geringer Priorität auszustatten, dass deren Abfrage nicht über einen bestimmten %-Wert der Cpu-last geht und somit den laufenden Betrieb nicht stören? Vielen Dank für die Bearbeitung meiner Anfrage. Marco Baar -- Nuno Pereira Maybe a post in the list would be good... 1st question) There are no miracles: Order the data takes time. A solution would be to place indexes on the columns in the order by. It would also help to run something like this from time to time: "ALTER TABLE main_table ORBER BY order_columns". That would help to have the rows ordered. 2nd question) You can have limits on the accounts in terms of querys per hour, number of connections, and thinks like that, not select priority. See this: http://dev.mysql.com/doc/mysql/en/user-resources.html Does it help? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timing problem
[EMAIL PROTECTED] wrote: Tony Leake <[EMAIL PROTECTED]> wrote on 09/29/2005 07:08:24 AM: Hi I have 2 applications communicating via a mysql database the db is 4.1.8 running on a debian linux system. All tables are innodb app 1 1 runs on a windows machine, is written in c# and talks to the db with odbc app 2 runs on the same machine as the db and is writtin in php. Here's the problem app 1 writes sales data to a table, when it is finished it unsets a flag in another table to indicate that it is finished app 2 polls for the flag to be unset, then reads the sales data. Sometimes whem app 2 reads the data there is nothing to be read, i am logging the queries that app 2 is using to do the inserts, then by the time i can open up a terminal and query the table manually the data is there. The only thing I can assume it that there is some kind of timing issue and the data is not fully written when I try to read it, this doesn't happen every time and may only be when the server is loaded. Does this happen? If so what can I do about it, would putting the inserts into one big transaction help? At the moment All of the inserts are done by implicit commits. Sorry for the essay, i an just trying to fully document what I know. Thanks tony When it comes to problems, more information is better. The key here is that you are making multiple changes from app1 that really should be within a transaction. The entire process of writing sales data and unsetting a flag from app1 needs to be transacted. That way the other server (app2) will either have consistent data or will never find out that anything was going on in the first place. The good thing is that you are already using InnoDB for all of your tables. This makes wrapping your process in a transaction fairly simple. Before you begin the "sales data write", issue the command "START TRANSACTION;". Do your writes and unset your flag. Then if all seems to have completed correctly, issue the command "COMMIT;" and if something went wrong issue the command "ROLLBACK;". The trick to making this work is that everything that happens between "START TRANSACTION" and "COMMIT" happens on the same connection. You cannot start a transaction from one connection and finish it from another. Depending on how your application (app1) is designed, you may have to do a little work to make sure that you use the same connection for the entire process. Once you add those two commands, app2 should never see that flag unset unless the sales data is actually available. For more detais, RTFM: http://dev.mysql.com/doc/mysql/en/commit.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine This one is interesting in terms of concurrency... Is the app2 the one responsable for setting the flag? I supose that it is. If that happens it's important that app2 doesn't mess with the flag, i mean, it may lead to problems if app2 sees that app1 writes the sales data, app2 sees it, starts reading it, app1 writes more data and app2 sets the flag without seeing that app1 have written more data, and sets the flag without reading the new one. Make this work without problems can be tricky, and I don't see a good solution to this in five minutes. When app2 reads data what app does with it? How app2 sees what was the last data she read? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem
What? Please post in english. Marco Baar wrote: Hallo, Ich benutze mysql seit 2 Jahren. Inzwischen haben sich 50MB Nutzdaten angesammeln und hab ein Problem mit der Performance während der Abfrage meiner "Haupttabelle". Die Tabelle hat ca 200.000 Datensätze und ich mache im verhältnis zu anderen Abfragen doch ein eher simplen select. Ich selektiere mit Prüfung auf 4 Attribute (mit "=" ) und es dauert 0,2-0,4 sekunden. Damit kann ich leben. Diese Abfrage musste ich abwandeln und will mir nur die ersten 50 Ergebnisse anzeigen, die nach Datum sortiert sind. Diese allerdings dauert ~3,5 sekunden und legt meinen Rechner lahm. Sogar der Sound wird unterbrochen. 2. Frage: Ich würde dies gerne als Daimon oder Server im Hintergrund laufen lassen, so dass mich die Datenbankabfragen nicht im Laufenden Betrieb stören. Trotzdem sollen doch relativ complexe Abfragen gemacht werden. Ist es möglich, bestimmte Benutzer mit sehr geringer Priorität auszustatten, dass deren Abfrage nicht über einen bestimmten %-Wert der Cpu-last geht und somit den laufenden Betrieb nicht stören? Vielen Dank für die Bearbeitung meiner Anfrage. Marco Baar -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie MySQL/PHP question re output formatting
Bill Whitacre wrote:
I can get this to work just fine:
Comes out $23,999.39
I'd like use the number_format() thingie on an array returned from a
mysql_query.
My current program snippet looks like:
$res = mysql_query("SELECT org, COUNT(*), SUM(annual_cost) AS
cost FROM a05
GROUP BY org ORDER BY cost DESC",$dbh);
if (!$res) {
echo mysql_errno().": ". mysql_error ()."";
return 0;
}
print "";
while ($thearray = mysql_fetch_array($res)) {
printf(" {$thearray[org]}
{$thearray["COUNT(*)"]}
$ {$thearray[cost]} ");
}
print "";
and works fine -- see <http://ibbmonitor.com/sked_1.php>, 3rd block of
stuff down.
If I replace
{$thearray[cost]}
with
number_format({$thearray[cost]}, 2)
I get
$ number_format(7842554.24, 2)
The issue is that PHP replaces $thearray[cost], with the contents of
that variable (that is an array, it doesn't matter). But in the second
case it replaces the same thing ($thearray[cost]), with the contents of
the variable, but you want to place there the result of the function.
To do date change the first line from
printf(" number_format({$thearray[cost]}, 2)
to
printf(" ".number_format({$thearray[cost]}, 2)."
in the cell where I would expect to get
$ 7,842,554.24
Any idea what I'm doing wrong?
Clearly, I don't understand arrays very well.
Thanks VERY much for any help on this.
bw
---
Bill Whitacre
[EMAIL PROTECTED]
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.26 has been released
Problem solved, at least for the most and major distributions and packages. Diana Soares wrote: Just a note: the main site still reports version 4.0.25-0. I dowloaded version 4.0.26 from a mirror, but it was necessary to tweak the URL. Congratulations for this fantastic project, Diana Soares On 9/8/05, Joerg Bruehe <[EMAIL PROTECTED]> wrote: Hi, MySQL 4.0.26, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Added the mysql_get_client_version C API function to the embedded server library. (It was present in the regular client library but inadvertently omitted from the embedded library.) (Bug #10266 (http://bugs.mysql.com/10266)) Bugs fixed: * An optimizer estimate of zero rows for a non-empty InnoDB table used in a left or right join could cause incomplete rollback for the table. (Bug #12779 (http://bugs.mysql.com/12779)) * Query cache is switched off if a thread (connection) has tables locked. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385 (http://bugs.mysql.com/12385)) * For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380 (http://bugs.mysql.com/11380)) * On Windows, applications that used the embedded server made it not possible to remove certain files in the data directory, even after the embedded server had been shut down. This occurred because a file descriptor was being held open. (Bug #12177 (http://bugs.mysql.com/12177)) * Creation of the mysql group account failed during the RPM installation. (Bug #12348 (http://bugs.mysql.com/12348)) * Attempting to repair a table having a fulltext index on a column containing words whose length exceeded 21 characters and where myisam_repair_threads was greater than 1 would crash the server. (Bug #11684 (http://bugs.mysql.com/11684)) * When two threads compete for the same table, a deadlock could occur if one thread has also a lock on another table through LOCK TABLES and the thread is attempting to remove the table in some manner and the other thread want locks on both tables. (Bug #10600 (http://bugs.mysql.com/10600)) Bye, Joerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES_DECRYPT output
Hello all, It seems that you can't change the status from closed (or can't repeat) to another. The only hope you have is someone in the developing teem test your bug with the combination you used: MySQL + ASP.NET + MyODBC. But I continue to say that the problem is not in MySQL Server, maybe in MyOBDC, but probably in ASP.NET. Try to post it in the list win32.mysql.com so that anyone in the win32 area can help, and a developer see it, test it and has the same problem. M DR wrote: I tried changing the status, but it seems only MySQL staff can do it (so the message says), so I can't do that. Any idea how it's possible to do so? From: Nuno Pereira <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Re: AES_DECRYPT output Date: Wed, 07 Sep 2005 12:19:01 +0100 I suppose that you have to change the status to Open, or people suppose that there aren't any changes. It would be nice that it would be tested in ASP.NET because it can be simply a problem with of type conversion in .NET, I haven't see the problem (and I don't work with .NET). M DR wrote: Hi, I have reported the bug a while ago, but it seems not all bugs are taken seriously. I reported it and one of the researchers couldn't replicate the problem, so I provided a sample project in ASP.NET where the problem can be seen very easily, but nobody reacted (probably because the status has been set to "Can't repeat"). See for the entry in the bugs database: http://bugs.mysql.com/bug.php?id=12872 Any idea how I can help fixing the problem nonetheless? From: Gleb Paharenko <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Re: AES_DECRYPT output Date: Mon, 29 Aug 2005 23:15:26 +0300 Hello. > And I guess you also refer to the strange behavior that it's different when > adding an ORDER BY or GROUP BY, right? Yes, in my opinion, ORDER BY shouldn't change the column's type. "M DR" <[EMAIL PROTECTED]> wrote: > Hi, > > Thanks for your answers, > > What is according to you the bug? Should it always return a string format > value? Or should it always return a system.byte[] format value? > > And I guess you also refer to the strange behavior that it's different when > adding an ORDER BY or GROUP BY, right? > > Kind regards, > > Martin -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Speel online games via MSN Messenger http://messenger.msn.nl/ -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES_DECRYPT output
I suppose that you have to change the status to Open, or people suppose that there aren't any changes. It would be nice that it would be tested in ASP.NET because it can be simply a problem with of type conversion in .NET, I haven't see the problem (and I don't work with .NET). M DR wrote: Hi, I have reported the bug a while ago, but it seems not all bugs are taken seriously. I reported it and one of the researchers couldn't replicate the problem, so I provided a sample project in ASP.NET where the problem can be seen very easily, but nobody reacted (probably because the status has been set to "Can't repeat"). See for the entry in the bugs database: http://bugs.mysql.com/bug.php?id=12872 Any idea how I can help fixing the problem nonetheless? From: Gleb Paharenko <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Re: AES_DECRYPT output Date: Mon, 29 Aug 2005 23:15:26 +0300 Hello. > And I guess you also refer to the strange behavior that it's different when > adding an ORDER BY or GROUP BY, right? Yes, in my opinion, ORDER BY shouldn't change the column's type. "M DR" <[EMAIL PROTECTED]> wrote: > Hi, > > Thanks for your answers, > > What is according to you the bug? Should it always return a string format > value? Or should it always return a system.byte[] format value? > > And I guess you also refer to the strange behavior that it's different when > adding an ORDER BY or GROUP BY, right? > > Kind regards, > > Martin -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DIV-function?
Martijn Tonies wrote: I wonder how I'll get a div-function in SQL? I dunno, maybe by looking in the manual? From [1]: Division: mysql> SELECT 3/5; -> 0.60 [1] http://dev.mysql.com/doc/mysql/en/arithmetic-functions.html Gustav is looking for the DIV-function. But it is there |DIV| Integer division. Similar to |FLOOR()| but safe with |BIGINT| values. He never said he wanted integer division... he just said "I want to divide t[w]o columns and make a new column based on the result." Maybe Gustav can say if this is what he wants, but it seems that it is. Ehm, no. He specifically asked for a DIV function, not a "division". :-) See above. With regards, Martijn Tonies -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 4.0.12 to 4.0.25
Osvaldo Sommer wrote: Hi List I look up on the online manual for directions for this kind of upgrade but i found nothing. Is there something I have to do extra or the upgrade so smoth? Osvaldo Sommer See http://lists.mysql.com/mysql/186726 -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Jan Roehrich wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql> show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ It can be a firewall problem... Are you sure that the slave didn't try to connect to the master, and got no response, or got a connection refused? Check the bin-log of the slave for errors in the bin-log. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT ... JOIN with NULL values
Sorry for replying to mysqlf, but it really solved the problem: there were another JOIN that had to be transformed to a LEFT JOIN. Thanks. Nuno Pereira wrote: [EMAIL PROTECTED] wrote: Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/26/2005 02:08:50 PM: Hi list, I have a query like this (the original is very big and names of columns/tables are in portuguese): SELECT * FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id) For a schema like this: CREATE TABLE tbl2 ( id INT NOT NULL auto_increment PRIMARY KEY, data_col2 int NOT NULL ) ENGINE=InnoDB; CREATE TABLE tbl1 ( id INT NOT NULL auto_increment PRIMARY KEY, id_tbl2 int NULL, data_col1 int NOT NULL, INDEX (id_tbl2), FOREIGN KEY (id_tbl2) REFERENCES tbl2(id) ON UPDATE CASCADE ) ENGINE=InnoDB; but i have NULL values in t.id_tbl2. Here is some data: mysql> INSERT INTO tbl2 VALUES (NULL, 11),(NULL, 12); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tbl2; ++---+ | id | data_col2 | ++---+ | 5 |11 | | 6 |12 | ++---+ 2 rows in set (0.00 sec) mysql> INSERT INTO tbl1 VALUES (NULL, 1, 123),(NULL, 2, 456), (NULL, NULL, 789); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tbl1; ++-+---+ | id | id_tbl2 | data_col1 | ++-+---+ | 1 | 1 | 123 | | 2 | 2 | 456 | | 3 |NULL | 789 | ++-+---+ 3 rows in set (0.00 sec) The above query gives: mysql> SELECT t.id, t.data_col1, u.data_col2 FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id); ++---+---+ | id | data_col1 | data_col2 | ++---+---+ | 1 | 123 |11 | | 2 | 456 |12 | ++---+---+ 2 rows in set (0.01 sec) But should be ++---+---+ | id | data_col1 | data_col2 | ++---+---+ | 1 | 123 |11 | | 2 | 456 |12 | | 3 | 789 | NULL | ++---+---+ Any sugestion? -- Nuno Pereira Change your INNER JOIN to a LEFT JOIN and all will be as you wanted. SELECT * FROM tbl1 t LEFT JOIN tbl2 u ON (t.id_tbl2=u.id) Shawn Green Database Administrator Unimin Corporation - Spruce Pine This solved the problem to this test (bad) case, but not to real situation. Monday I will place the full test. -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT ... JOIN with NULL values
[EMAIL PROTECTED] wrote: Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/26/2005 02:08:50 PM: Hi list, I have a query like this (the original is very big and names of columns/tables are in portuguese): SELECT * FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id) For a schema like this: CREATE TABLE tbl2 ( id INT NOT NULL auto_increment PRIMARY KEY, data_col2 int NOT NULL ) ENGINE=InnoDB; CREATE TABLE tbl1 ( id INT NOT NULL auto_increment PRIMARY KEY, id_tbl2 int NULL, data_col1 int NOT NULL, INDEX (id_tbl2), FOREIGN KEY (id_tbl2) REFERENCES tbl2(id) ON UPDATE CASCADE ) ENGINE=InnoDB; but i have NULL values in t.id_tbl2. Here is some data: mysql> INSERT INTO tbl2 VALUES (NULL, 11),(NULL, 12); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tbl2; ++---+ | id | data_col2 | ++---+ | 5 |11 | | 6 |12 | ++---+ 2 rows in set (0.00 sec) mysql> INSERT INTO tbl1 VALUES (NULL, 1, 123),(NULL, 2, 456), (NULL, NULL, 789); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tbl1; ++-+---+ | id | id_tbl2 | data_col1 | ++-+---+ | 1 | 1 | 123 | | 2 | 2 | 456 | | 3 |NULL | 789 | ++-+---+ 3 rows in set (0.00 sec) The above query gives: mysql> SELECT t.id, t.data_col1, u.data_col2 FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id); ++---+---+ | id | data_col1 | data_col2 | ++---+---+ | 1 | 123 |11 | | 2 | 456 |12 | ++---+---+ 2 rows in set (0.01 sec) But should be ++---+---+ | id | data_col1 | data_col2 | ++---+---+ | 1 | 123 |11 | | 2 | 456 |12 | | 3 | 789 | NULL | ++---+---+ Any sugestion? -- Nuno Pereira Change your INNER JOIN to a LEFT JOIN and all will be as you wanted. SELECT * FROM tbl1 t LEFT JOIN tbl2 u ON (t.id_tbl2=u.id) Shawn Green Database Administrator Unimin Corporation - Spruce Pine This solved the problem to this test (bad) case, but not to real situation. Monday I will place the full test. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT ... JOIN with NULL values
Hi list, I have a query like this (the original is very big and names of columns/tables are in portuguese): SELECT * FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id) For a schema like this: CREATE TABLE tbl2 ( id INT NOT NULL auto_increment PRIMARY KEY, data_col2 int NOT NULL ) ENGINE=InnoDB; CREATE TABLE tbl1 ( id INT NOT NULL auto_increment PRIMARY KEY, id_tbl2 int NULL, data_col1 int NOT NULL, INDEX (id_tbl2), FOREIGN KEY (id_tbl2) REFERENCES tbl2(id) ON UPDATE CASCADE ) ENGINE=InnoDB; but i have NULL values in t.id_tbl2. Here is some data: mysql> INSERT INTO tbl2 VALUES (NULL, 11),(NULL, 12); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tbl2; ++---+ | id | data_col2 | ++---+ | 5 |11 | | 6 |12 | ++---+ 2 rows in set (0.00 sec) mysql> INSERT INTO tbl1 VALUES (NULL, 1, 123),(NULL, 2, 456), (NULL, NULL, 789); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tbl1; ++-+---+ | id | id_tbl2 | data_col1 | ++-+---+ | 1 | 1 | 123 | | 2 | 2 | 456 | | 3 |NULL | 789 | ++-+---+ 3 rows in set (0.00 sec) The above query gives: mysql> SELECT t.id, t.data_col1, u.data_col2 FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id); ++---+---+ | id | data_col1 | data_col2 | ++---+---+ | 1 | 123 |11 | | 2 | 456 |12 | ++---+---+ 2 rows in set (0.01 sec) But should be ++---+---+ | id | data_col1 | data_col2 | ++---+---+ | 1 | 123 |11 | | 2 | 456 |12 | | 3 | 789 | NULL | ++---+---+ Any sugestion? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create "serial number" by select
Gyurasits Zoltán wrote: Hi, Yes! And the "SELECT" contain some table! SELECT (??) FROM table1 INNER JOIN table2 I have a problem with the follow solution: SET @count:=0; SELECT @count:[EMAIL PROTECTED] AS `count`, `value` FROM `table`; I can't execute more select in my system. (Delphi software) :( Couldn't you use the index of the row of the result set to define that "serial"? Only 1 select allowed! Because it is the reporting system... 1 output is 1 select! Best Regards! Zoli - Original Message - From: "Jason Pyeron" <[EMAIL PROTECTED]> To: Sent: Thursday, August 25, 2005 5:23 PM Subject: Re: create "serial number" by select On Thu, 25 Aug 2005, Pooly wrote: Why not adding an auto_increment column to your data ? 2005/8/25, Gyurasits Zoltán <[EMAIL PROTECTED]>: I can't build "serial number" in table1! Because he is not allowe to modify the table, pick any reason. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'ODBC'@'localhost' instead of 'root'@'invalid_hostname.com' ?!??
Sorry for replying to my own post, but here is the manual page of PHP
about mysql functions: http://www.php.net/mysql and specificly to
mysql_connect: http://www.php.net/manual/en/function.mysql-connect.php
Nuno Pereira wrote:
Martin Olsson wrote:
Hi,
I'm using PHP/MySQL to development a web-based application. I just
upgraded both PHP and MySQL and I noticed that some of my MySQL calls
are now broken and I don't see any easy way to fix them.
My script calls mysql_connect() and does very careful error checking.
It's important that I can tell apart invalid hostname errors from say
incorrect username/password errors. Previously, I did this by checking
mysql_errno(), where 2003 would mean bad hostname and 1045 would
indicate bad username/password.
In the latest PHP/MySQL bundle I downloaded this functionality changed
though. In this new version, whenever I feed it with a invalid
hostname it will fallback to localhost (where I actually have a
database running) and then it will subsequently report a 1045 instead
of a 2003.
Oddly enough, the exact error message (mysql_error) I get attached to
this 1045 says; mysql_error()==
Access denied for user 'ODBC'@'localhost' (using password: NO)
This means that, the user "ODBC" (without quotes) on mysql machine
"localhost" (without quotes, again), can't login to the server without
using a password (the part of "(using password: NO)"). The error number
is correct, bacause you have invalid username/password on that server
(for user ODBC).
Now, what is this about? I sure did not feed it with a username
'ODBC'. I do use Windows 2005 but how could ODBC possibly be related
to this? (I assume ODBC means that [Microsoft?] database connection
thing, I'm not sure exactly what is it; i've never used it). For the
record, the actual username I specified in the call with the invalid
hostname was "root".
It seems that you did not specified correctly the username to access the
server, and without password.
The correct syntax is, in your case,
$link = mysql_connect("localhost", "root", "mysql_root_password");
I strongly sugested that you provide a password to root, and login as
another user with your PHP application, obviously with a password. So,
in that case, change the mysql_connect() invocation to something like this:
$link = mysql_connect("localhost", "php_app_user", "user_password");
(Obviously, replace user_password with the password for your
php_app_user of your mysql server).
---
So, the question is how can I tell these errors apart?
Why is mysql_error() reporting back that it tried to connect to
localhost as user "ODBC" when I asked it to connect to an invalid
hostname as user "root" ?!?!
sincerly,
martin
--
Nuno Pereira
Estagiário
Carclasse - Comércio Automóveis, S.A.
Lugar Sr. dos Perdões - Ribeirão (Famalicão)
Telf.: 252 330 550 - Tlm: 965 215 076
email: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'ODBC'@'localhost' instead of 'root'@'invalid_hostname.com' ?!??
Martin Olsson wrote:
Hi,
I'm using PHP/MySQL to development a web-based application. I just
upgraded both PHP and MySQL and I noticed that some of my MySQL calls
are now broken and I don't see any easy way to fix them.
My script calls mysql_connect() and does very careful error checking.
It's important that I can tell apart invalid hostname errors from say
incorrect username/password errors. Previously, I did this by checking
mysql_errno(), where 2003 would mean bad hostname and 1045 would
indicate bad username/password.
In the latest PHP/MySQL bundle I downloaded this functionality changed
though. In this new version, whenever I feed it with a invalid hostname
it will fallback to localhost (where I actually have a database running)
and then it will subsequently report a 1045 instead of a 2003.
Oddly enough, the exact error message (mysql_error) I get attached to
this 1045 says; mysql_error()==
Access denied for user 'ODBC'@'localhost' (using password: NO)
This means that, the user "ODBC" (without quotes) on mysql machine
"localhost" (without quotes, again), can't login to the server without
using a password (the part of "(using password: NO)"). The error number
is correct, bacause you have invalid username/password on that server
(for user ODBC).
Now, what is this about? I sure did not feed it with a username 'ODBC'.
I do use Windows 2005 but how could ODBC possibly be related to this? (I
assume ODBC means that [Microsoft?] database connection thing, I'm not
sure exactly what is it; i've never used it). For the record, the actual
username I specified in the call with the invalid hostname was "root".
It seems that you did not specified correctly the username to access the
server, and without password.
The correct syntax is, in your case,
$link = mysql_connect("localhost", "root", "mysql_root_password");
I strongly sugested that you provide a password to root, and login as
another user with your PHP application, obviously with a password. So,
in that case, change the mysql_connect() invocation to something like this:
$link = mysql_connect("localhost", "php_app_user", "user_password");
(Obviously, replace user_password with the password for your
php_app_user of your mysql server).
---
So, the question is how can I tell these errors apart?
Why is mysql_error() reporting back that it tried to connect to
localhost as user "ODBC" when I asked it to connect to an invalid
hostname as user "root" ?!?!
sincerly,
martin
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Sebastian wrote: well i managed to solve the problem myself, and im no sql genius... i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. i think that is about all the improvement i can get.. but if there is still room for more speed i'd like to know.. Sebastian wrote: Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE ) - output of EXPLAIN sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage It seems to me that an index on maps_rating.rating, maps.userid, user.userid might help. Also making the query "ALTER TABLE maps ORDER BY maps.dateline DESC" once a day or more, would help the ordering. I may be saying too include much indexes (probably the first), but it may not make bad at all (probably updates/inserts would be slower). Making some tests might help to see what is the best. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. The first is what I like more. While in the first to mark as deleted (or restore), you only have to change one column, and in the second, you have to move (and move again to restore) from one table to another. Getting the value from the normal value to store it in the second could lead to a problem Implement the first in a developed schema, is just add a column of type bool (for example) with the default beeing not deleted. The second has the other problem of a change in the schema of the normal table has to be done in the deleted values table. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). This lets you to have two different tables of deleted values. Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a If you mean "The Primary key for Table1 is auto-generated" by using auto_increment, it is not impossible. You can just copy the entire contents of the row. different table. However if we just flag the record as deleted the restoring is quite easy. As I said. Any thoughts/ideas ? Just my opinion, and it seems to be the opinion of mambo developers, as they implement the deletion of values to restore like this way, and they have also a published column. If they have done this they would need 4 tables: published_and_not_deleted, published_and_deleted, not_published_and_not_deleted and not_published_and_deleted. I would say that the second is very bad. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cache queries
Paco Martinez Rodriguez wrote: Hi.. I'm using "cache queries" with MySQL 4.0.13 during three days and I get a little better speed. Value of Query_cache_size = 20 Mb. This is 20 Mega Bits. I suppose you meant to mean 20 MegaBytes (20MB), or even 20 Mibibytes (20MiB). I have two questions. 1.- Is there any way to see which queries are being stored ?? About this I don't know. Does anyone? 2.- How can I delay "qcache_lowmen_pruenes" ?? In three day values of it is 64421 Is it too ?? By increasing Query_cache_size, you get qcache_lowmen_pruenes lower after a while. THis may be because of the Qcache_free_blocks value, beeing the free mem ammount about half total mem (Qcache_free_memory =~ 10MB) +-+--+ | Variable_name | Value| +-+--+ | Qcache_queries_in_cache | 3106 | | Qcache_inserts | 447577 | | Qcache_hits | 2697410 | | Qcache_lowmem_prunes| 64421| | Qcache_not_cached | 1421991 | | Qcache_free_memory | 10226504 | | Qcache_free_blocks | 418 | | Qcache_total_blocks | 6997 | | Threads_cached | 0| +-+------+ Thank you !!! -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
Sebastian wrote: Nuno Pereira wrote: [EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. I sort of agree with you guys but i think there are downsides to both methods.. if i do update the comment column when comments are added / deleted then that is an extra query each time someone posts. in an hour we can get several hundred comments, that also means several hundred db queries. at least i figured i can update these fields every 5 minutes or so and save some extra calls each time someone posts a comment. i guess i have to figure out which method would be best when dealing with a lot of traffic, but i think it will be neglible. right now that subquery runs in under .25 ms with 50,000 records to count.. not too bad i guess. You can see the number of updates, deletes, selects by performing a SHOW STATUS LIKE 'com_'; This shows how many queries of each types the MySQL server got since his last reboot. It can help you decide the best method, and the impact of making the extra query per insert/delete. If you update the count each _N_ seconds, the readers will see an old value and they could, for example, not post a reply, because they didn't saw the value being updated. I think that normally people prefer the first, or you could the value of _N_ be smaller (1 minute, for example). -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
[EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
[EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undefined variable: authed
Trevor Tregoweth wrote: Hi There I am trying to run a simple password /php / mysql script for a web page and get the following errors, it works fine on a earlier versions of php / mysql PHP Notice: Undefined variable: help_out_uid in /var/www/html/lcc/secure/secure.php on line 87, PHP Notice: Undefined variable: authed in /var/www/html/lcc/secure/secure.php on line 34 mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386) PHP 4.3.9 (cgi) (built: Oct 20 2004 14:52:04) This is not a MySQL problem, it is a PHP problem. Are those variables containing the values from a form? If that is the case, it seems to me that you were using a 4.0 PHP version. Try to initialize the variables like this: $help_out=$_REQUEST['help_out']; $authed=$_REQUEST['authed']; Thanks for you help Trevor -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Like Clause in Perl? Works fine in MySQL control center!
Knowing not too much from perl, I would like to remember the last
paragraph of Jeremiah:
> Keep in mind that interpolation is work, so using one of the
> single quotes
> strings which does not search your string for variables to
> replace is going
> to be higher performance than the double quoted version, although the
> difference may be a little or a lot depending on how many
> times the string
> is interpreted (if it is in a loop or something).
If performance is a problem, don't forget this.
John Trammell wrote:
From 'perldoc perldata':
Variable substitution inside strings is limited to scalar
variables, arrays, and array or hash slices. (In other
words, names beginning with $ or @, followed by an optional
bracketed expression as a subscript.)
You can check this from the command line:
% perl -le 'print "$s -- @s -- %s"'
-- -- %s
So the '%' isn't the issue here. The issue is certainly the (mis)use of
join(), as was pointed out by a previous poster.
-Original Message-
From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED]
Sent: Monday, July 25, 2005 9:14 AM
To: Siegfried Heintze; mysql@lists.mysql.com
Subject: Re: How to use Like Clause in Perl? Works fine in
MySQL control center!
When you use double quotes for strings in Perl, Perl looks
through your
strings for variables like $foo, and replaces them with the
current value of
$foo. This is called interpolation. When you use single quotes, it
considers your string a literal.
So when you use double quotes, you need to escape any special
characters
like $ % " or @. When you use single quotes, the only
character you have to
worry about is '. Here are ways you could make this string work.
Double quotes with special characters escaped (due to interpolation)
"SELECT 'David!' LIKE '\%D\%v\%'"
Single quotes with double quote usage for the SQL quoting (no
escaping
required)
'SELECT "David!" LIKE "%D%v%"'
Single quotes with single quotes escaped for the SQL quoting
'SELECT \'David!\' LIKE \'%D%v%\''
Keep in mind that interpolation is work, so using one of the
single quotes
strings which does not search your string for variables to
replace is going
to be higher performance than the double quoted version, although the
difference may be a little or a lot depending on how many
times the string
is interpreted (if it is in a loop or something).
- Original Message -
From: "Siegfried Heintze" <[EMAIL PROTECTED]>
To:
Sent: Friday, July 22, 2005 4:03 PM
Subject: How to use Like Clause in Perl? Works fine in MySQL
control center!
I'm having trouble getting the like clause to work. It
seems to work fine
in
the MySQL Control Center 9.4.beta. I'm using MySQL 4.0.23-debug.
use DBH;
my $sth = DBH->prepare("SELECT 'David!' LIKE '%D%v%'");
$sth->execute();
my $row;
print join(@$row,",")."\n" while ($row = $sth->fetch);
This does not print a "1" in perl. It just prints a ",".
I've posted a query on this in [EMAIL PROTECTED] with no luck.
Anybody have any suggestions?
Thanks,
Siegfried
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE WITH row
[EMAIL PROTECTED] wrote: Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/05/2005 02:41:45 PM: Following the "SELECT DISTINCT" topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira I think you want to use a variable... SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; UPDATE int_contxtd_details SET params = @NewValue; It works. I remembered to use the variable, SET @right_params=SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; but id didn't work, because the syntax was incorrect. In this case, even if you overwrite the value where id=35, you will be giving back the same value it started with so no harm. Running the UPDATE statement without a WHERE clause will be faster because it won't have to check ID values for every row to make sure it's OK to update that row. Make sense? Yes, thanks. PS. The other way I could think to write this query was with a self-join but I don't think it would be as fast as using the variable. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE WITH row
Following the "SELECT DISTINCT" topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
Scott Noyes wrote: Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? This is not really what I want, the reply from Shawn Green is just what I want. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
[EMAIL PROTECTED] wrote: Depending on what version server you are running, the GROUP_CONCAT() function may be an option for you: SELECT params, GROUP_CONCAT(ID) FROM table GROUP BY params; I'm using 4.1.10a-standard-log Server version, and this is just what I wanted. Thanks. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT
Hello list, I'm getting the distinct 'params' columns from a table with this query SELECT DISTINCT params FROM table; but this gets the rows with distinct 'params' cols, but I want to know from which row each params correspond, like this (which is not correct) SELECT id, DISTINCT params FROM table; and the query based in a recent post doesn't work select distinct on (params), params, id from table order by params, id I checked the syntax and the later isn't correct. Any ideas? -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not connect to Mysql server with Number 2003
Nguyen, Phong wrote: All, In a few minutes ago, I can connect to mysql. Log out and can not login with error "Mysql error Number 2003 can't connect to Mysql server.." Check this: http://dev.mysql.com/doc/mysql/en/access-denied.html It is a good idea, for now, to check the error logs, there should be an explanation about how the server crashed Any idea, please help! Thank you..Nguyen -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.23a defunct
Arek H wrote: Nuno Pereira wrote: Post here the result of a ps ajxf, but it seems to me that you didn't started properly mysql. You should start MySQL with mysqld_safe, not with mysqld. Here it is PPID PID PGID SID TTY TPGID STAT UID TIME COMMAND 0 1 0 0 ? -1 S0 0:04 init [3] 1 2 1 1 ? -1 S0 0:00 [keventd] 1 3 1 1 ? -1 SN 0 0:00 [ksoftirqd_CPU0] 1 4 1 1 ? -1 S0 0:00 [kswapd] 1 5 1 1 ? -1 S0 0:00 [bdflush] 1 6 1 1 ? -1 S0 0:00 [kupdated] 110 1 1 ? -1 S< 0 0:00 [mdrecoveryd] 111 1 1 ? -1 S0 0:00 [kjournald] 144 1 1 ? -1 S0 0:00 [kjournald] 145 1 1 ? -1 S0 0:00 [kjournald] 146 1 1 ? -1 S0 0:00 [kjournald] 147 1 1 ? -1 S0 0:00 [kjournald] 148 1 1 ? -1 S0 0:00 [kjournald] 1696969 ? -1 Ss 0 0:02 /usr/sbin/syslogd 1727272 ? -1 Ss 0 0:00 /usr/sbin/klogd -c 3 -x 1 132 132 132 ? -1 Ss 0 0:00 udevd 1 233 1 1 ? -1 S0 0:00 [khubd] 1 696 696 696 ? -1 Ss 0 0:00 /sbin/dhcpcd -d -t 10 eth0 1 943 943 943 ? -1 Ss 0 0:00 /usr/sbin/inetd 1 947 947 947 ? -1 Ss 0 0:06 /usr/sbin/sshd 947 16144 16144 16144 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 16144 16147 16144 16144 ? -1 S 1000 0:06 | \_ sshd: [EMAIL PROTECTED]/0 16147 16148 16148 16148 pts/021532 Ss1000 0:00 | \_ -bash 16148 16881 16881 16148 pts/021532 S0 0:00 | \_ bash 16881 21532 21532 16148 pts/021532 R+ 0 0:00 | \_ ps ajxf 947 21108 21108 21108 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 21108 2 21108 21108 ? -1 S 1000 0:00 \_ sshd: [EMAIL PROTECTED]/1 2 21112 21112 21112 pts/121132 Ss1000 0:00 \_ -bash 21112 21123 21123 21112 pts/121132 S0 0:00 \_ bash 21123 21132 21132 21112 pts/121132 S+ 0 0:00 \_ tail -f syslog 1 958 957 957 ? -1 S0 0:00 /usr/sbin/crond -l10 1 960 960 960 ? -1 Ss 2 0:00 /usr/sbin/atd -b 15 -l 1 1 963 963 963 ? -1 Ss 0 0:00 sendmail: accepting connections 1 966 966 966 ? -1 Ss 25 0:00 sendmail: Queue [EMAIL PROTECTED]:25:00 for /var/spool/clientmqueue 1 9726161 ? -1 S0 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var 972 9996161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --use 999 10006161 ? -1 S 27 0:01 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql - 1000 10016161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10026161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10036161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10046161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10056161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10066161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10076161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10086161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1 976 976 976 ? -1 Ss 0 0:00 /usr/sbin/gpm -m /dev/mouse -t ps2 1 1010 1010 1010 tty1 1010 Ss+ 0 0:00 -bash 1 1011 1011 1011 tty2 1011 Ss+ 0 0:00 /sbin/agetty 38400 tty2 linux 1 1012 1012 1012 tty3 1012 Ss+ 0 0:00 /sbin/agetty 38400 tty3 linux 1 1013 1013 1013 tty4 1013 Ss+ 0 0:00 /sbin/agetty 38400 tty4 linux 1 1014 1014 1014 tty5 1014 Ss+ 0 0:00 /sbin/agetty 38400 tty5 linux 1 1015 1015 1015 tty6 1015 Ss+ 0 0:00 /sbin/agetty 38400 tty6 linux 1 17378 17378 17378 ? -1 Ss 0 0:00 /usr/sbin/httpd 17378 17379 17378 17378 ? -1 S 99 0:27
Re: MySQL 4.0.23a defunct
Arek H wrote: Hi Im running a stock install of Slackware 10.1 with the default setup of mysql. Whats happening is when I hit the reload button several times on a page that uses php and accesses mysql I get defunct and zombies start showing when viewing top. I also get this when I run a web based apache bench on my server with the Requests set to 25 and Concurrency set to 5. I checked my domains error logs and there is nothing there. I looked in the error log located at /var/lib/mysql/ano.err and here are the last few lines of it. Im new to linux so this is the only error log I could find for mysql . Memory status: Non-mmapped space allocated from system: 5204056 Number of free chunks: 15 Number of fastbin blocks:0 Number of mmapped regions: 11 Space in mmapped regions:22401024 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 4901384 Total free space:302672 Top-most, releasable space: 131176 Estimated memory (with thread stack):27605080 050731 15:57:12 mysqld ended 050731 16:35:00 mysqld started 050731 16:35:02 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.23a' socket: '/var/run/mysql/mysql.sock' port: 0 Source distribution 050731 16:40:59 /usr/libexec/mysqld: Normal shutdown 050731 16:40:59 InnoDB: Starting shutdown... 050731 16:41:00 InnoDB: Shutdown completed 050731 16:41:00 /usr/libexec/mysqld: Shutdown Complete 050731 16:41:00 mysqld ended 050731 16:42:17 mysqld started 050731 16:42:19 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.23a' socket: '/var/run/mysql/mysql.sock' port: 0 Source distribution Also here is what top shows 20797 root 12 0 1060 1060 824 R 1.3 0.2 0:18.60 top 20993 mysql 11 0 000 Z 1.0 0.0 0:00.03 mysqld 20999 mysql 10 0 15612 15m 3020 S 0.6 3.1 0:00.02 mysqld 21007 mysql 11 0 000 Z 0.6 0.0 0:00.02 mysqld 21017 mysql 13 0 15632 15m 3020 S 0.6 3.1 0:00.02 mysqld 1000 mysql 17 0 15592 15m 3020 R 0.3 3.1 0:01.89 mysqld 20991 mysql 9 0 000 Z 0.3 0.0 0:00.01 mysqld 21013 mysql 10 0 15632 15m 3020 S 0.3 3.1 0:00.01 mysqld 21015 mysql 11 0 15632 15m 3020 S 0.3 3.1 0:00.01 mysqld 1 root 8 0 228 228 200 S 0.0 0.0 0:04.86 init 2 root 9 0 000 S 0.0 0.0 0:00.05 keventd 3 root 19 19 000 S 0.0 0.0 0:00.00 ksoftirqd_CPU0 4 root 9 0 000 S 0.0 0.0 0:00.00 kswapd 5 root 9 0 000 S 0.0 0.0 0:00.00 bdflush 6 root 9 0 000 S 0.0 0.0 0:00.02 kupdated 10 root -1 -20 000 S 0.0 0.0 0:00.00 mdrecoveryd 11 root 9 0 000 S 0.0 0.0 0:00.06 kjournald 44 root 9 0 000 S 0.0 0.0 0:00.16 kjournald 45 root 9 0 000 S 0.0 0.0 0:00.26 kjournald Thanks Arek Post here the result of a ps ajxf, but it seems to me that you didn't started properly mysql. You should start MySQL with mysqld_safe, not with mysqld. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Bruce Dembecki wrote: On Aug 1, 2005, at 4:58 AM, Nuno Pereira wrote: Jason Pyeron wrote: sorry, reply to error here On Fri, 29 Jul 2005, Nuno Pereira wrote: Michael Stassen wrote: You can, but why are you reinventing the wheel? Option files have already been provided for this purpose. In what way is storing the batch user password in 'password_file' better than than storing it in an option file? Storing in an option file didn't work, so I use this option. Could you please explain storing in an option file did not work? I tried to use the "-p=password" option, (replacing password with the right password, of course), but it didn't work. Get it from a file with the options I wrote is my current solution. I don't like to store it in a global option file, but I may consider to use it from an user option file, if that is possible. Again.. this is the correct way to do it... You can setup a user option file (.my.cnf in the users home directory) and that file should include lines like this: [client] user=myUsername password=myPassword The user line is optional if the OS username and the MySQL username are the same. You can protect it by making the user's home directory difficult to get to for other users and giving the file read only permissions for the user in question, and no permissions for other users. Note the format here - password=myPassword - using -p=password is not the way to pass the information in an option file. The password parameter needs to appear under the [client] tag, or the [mysql] tag if you onyl want it to apply to the mysql command line client and none of the other clients that would read the option file. Best Regards, Bruce Note1: I just forwarded to the list, because this could help others. Note2: PLEASE use the "Reply All" option when replying to the list. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Where do you see that? I didn't type the command to execute the mysql client, and "database" is not the password, is the default database of the session. So, in the command mysql -u user -p database the user is user, the password is prompted interactively, and the default database is database. The command mysql -u user -px xxx database was not typed by me, I typed something like mysql -u user -ppassword database The difference to the above is that the password is in the command, and is password. Also, in the command that I said that I use to use in my scripts, the password is "typed" with no space between password and the argument -p, like mysql -u user -p`cat password_file` database (It is a *nix systems only feature, at least in Linux) Next time send to the list, also. Dan Tappin wrote: You need to remove the space between -p and the password. Example: My user:pass is root:foobar So my command would be: mysql -u root -pfoobar NOT mysql -u root -p foobar Dan On Aug 1, 2005, at 9:09 AM, Nuno Pereira wrote: Hello Edwin, Ehrwin, $ mysql --version mysql Ver 14.7 Distrib 4.1.10a, for pc-linux-gnu (i686) I tried it several times, and the password always appears to me in like $ ps -ef | grep mysql mysql -u user -px xxx database When I write the password interactively, no replaced password appears: $ ps -ef | grep mysql mysql -u user -p database The difference could be the version of the client (probably not the server). Edwin Cruz wrote: Mhm daemon2# uname -r -s FreeBSD 5.2.1-RELEASE daemon2# mysql --version mysql Ver 14.10 Distrib 5.0.6-beta, for unknown-freebsd5.2.1 (i386) using EditLine wrapper daemon2# mysql -utr8 -ptr8 & daemon2# ps -ef | grep mysql 230 p0- IL 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --datadir=/var/lib/mysql 41064 p0 IL+0:00.02 mysql -h157.87.200.57 -utr8 -ptr8 daemon2# mysql -utr8 -p & Enter password: daemon2# ps -ef | grep mysql 230 p0- IL 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --datadir=/var/lib/mysql 41079 p0 SL+0:00.02 mysql -h157.87.200.57 -utr8 -p I can see my pwd if I do a "ps" -Original Message- From: Eugene Kosov [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 7:03 AM To: Ehrwin Mina Cc: Nuno Pereira; mysql@lists.mysql.com Subject: Re: mysql command line execution Ehrwin Mina wrote: That isn't true. If you make a ps, you will see something like "mysql -p x ". As I said before, you can use something like: "mysql -uUser --password=`cat password_file` db" FYI, Nuno is correct you cannot see the password in the 'ps' and my scripts is just an example you can modify it for more security like putting it a config file or much better if you can use perl. Don't forget the user privileges only. Hm... May be it's OS (or MySQL version) depending stuff but following shows me you're wrong. 1) > uname -r -s FreeBSD 5.3-RELEASE > mysql --version mysql Ver 14.7 Distrib 4.1.6-gamma-nightly-20041014, for unknown- freebsd5.3 (i386) > mysql -ptest -utest & > ps | grep mysql 63841 p5 T 0:00,02 mysql -ptest -utest 2) > uname -r -s SunOS 5.9 > mysql --version mysql Ver 14.11 Distrib 5.0.9-beta, for pc-solaris2.9 (i386) using readline 5.0 > mysql -ptest -utest & > ps -ef | grep mysql | grep test kea 22646 22644 0 15:56:02 pts/40:00 mysql -piss_pwd - uiss_usr -- Nuno Pereira -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Hello Edwin, Ehrwin, $ mysql --version mysql Ver 14.7 Distrib 4.1.10a, for pc-linux-gnu (i686) I tried it several times, and the password always appears to me in like $ ps -ef | grep mysql mysql -u user -px xxx database When I write the password interactively, no replaced password appears: $ ps -ef | grep mysql mysql -u user -p database The difference could be the version of the client (probably not the server). Edwin Cruz wrote: Mhm daemon2# uname -r -s FreeBSD 5.2.1-RELEASE daemon2# mysql --version mysql Ver 14.10 Distrib 5.0.6-beta, for unknown-freebsd5.2.1 (i386) using EditLine wrapper daemon2# mysql -utr8 -ptr8 & daemon2# ps -ef | grep mysql 230 p0- IL 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --datadir=/var/lib/mysql 41064 p0 IL+0:00.02 mysql -h157.87.200.57 -utr8 -ptr8 daemon2# mysql -utr8 -p & Enter password: daemon2# ps -ef | grep mysql 230 p0- IL 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --datadir=/var/lib/mysql 41079 p0 SL+0:00.02 mysql -h157.87.200.57 -utr8 -p I can see my pwd if I do a "ps" -Original Message- From: Eugene Kosov [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 7:03 AM To: Ehrwin Mina Cc: Nuno Pereira; mysql@lists.mysql.com Subject: Re: mysql command line execution Ehrwin Mina wrote: That isn't true. If you make a ps, you will see something like "mysql -p x ". As I said before, you can use something like: "mysql -uUser --password=`cat password_file` db" FYI, Nuno is correct you cannot see the password in the 'ps' and my scripts is just an example you can modify it for more security like putting it a config file or much better if you can use perl. Don't forget the user privileges only. Hm... May be it's OS (or MySQL version) depending stuff but following shows me you're wrong. 1) > uname -r -s FreeBSD 5.3-RELEASE > mysql --version mysql Ver 14.7 Distrib 4.1.6-gamma-nightly-20041014, for unknown-freebsd5.3 (i386) > mysql -ptest -utest & > ps | grep mysql 63841 p5 T 0:00,02 mysql -ptest -utest 2) > uname -r -s SunOS 5.9 > mysql --version mysql Ver 14.11 Distrib 5.0.9-beta, for pc-solaris2.9 (i386) using readline 5.0 > mysql -ptest -utest & > ps -ef | grep mysql | grep test kea 22646 22644 0 15:56:02 pts/40:00 mysql -piss_pwd -uiss_usr -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Jason Pyeron wrote: sorry, reply to error here On Fri, 29 Jul 2005, Nuno Pereira wrote: Michael Stassen wrote: You can, but why are you reinventing the wheel? Option files have already been provided for this purpose. In what way is storing the batch user password in 'password_file' better than than storing it in an option file? Storing in an option file didn't work, so I use this option. Could you please explain storing in an option file did not work? I tried to use the "-p=password" option, (replacing password with the right password, of course), but it didn't work. Get it from a file with the options I wrote is my current solution. I don't like to store it in a global option file, but I may consider to use it from an user option file, if that is possible. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Michael Stassen wrote: Nuno Pereira wrote: Michael Stassen wrote: Ehrwin Mina wrote: Jeff, You can make a shell script or a php script or a perl script by that way you can hide the commands you need to execute. eg. Make a shell script (myshell.sh) #!/bin/sh myuser=dbuser mypasswd=dbpassword mydb=dbname myhost=localhost myport=3306 db1=mysql -u$myuser -pmypasswd -Dmydb -h$myhost -P$myport echo "repair table employee" | $db1 echo "unlock table " | $db1 exit This is no more secure, as it still puts the password on the command line. Your script amounts to echo "repair table employee" | mysql -udbuser -pdbpassword -Ddbname -hlocalhost -P3306 echo "unlock table " | mysql -udbuser -pdbpassword -Ddbname -hlocalhost -P3306 The password is on the command line of the commands issued by the script, so it can be seen with ps. That isn't true. If you make a ps, you will see something like "mysql -p x ". From the manual <http://dev.mysql.com/doc/mysql/en/password-security.html>: shell> mysql -u francis -pfrank db_name This is convenient but insecure, because your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence, but there is still a brief interval during which the value is visible. You see? The client overwrites the password (producing the "x "), but it is visible via ps until then. That makes you vulnerable to ps sniffing. The recommended two methods for secure entering of passwords: * Use -p without the password for interactive clients (you get prompted for the password). * Use an option file to store the password. This works for both interactive and non-interactive jobs. See the manual page referenced above for the details. As I said before, you can use something like: "mysql -uUser --password=`cat password_file` db" See http://lists.mysql.com/mysql/186720. You can, but why are you reinventing the wheel? Option files have already been provided for this purpose. In what way is storing the batch user password in 'password_file' better than than storing it in an option file? Storing in an option file didn't work, so I use this option. In fact, it is worse. Your shell executes `cat password_file` to get "password", then executes `mysql -uUser --password="password" db`. Again, the password is briefly visible to ps, until the client overwrites it. Michael -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line execution
Michael Stassen wrote: Ehrwin Mina wrote: Jeff, You can make a shell script or a php script or a perl script by that way you can hide the commands you need to execute. eg. Make a shell script (myshell.sh) #!/bin/sh myuser=dbuser mypasswd=dbpassword mydb=dbname myhost=localhost myport=3306 db1=mysql -u$myuser -pmypasswd -Dmydb -h$myhost -P$myport echo "repair table employee" | $db1 echo "unlock table " | $db1 exit This is no more secure, as it still puts the password on the command line. Your script amounts to echo "repair table employee" | mysql -udbuser -pdbpassword -Ddbname -hlocalhost -P3306 echo "unlock table " | mysql -udbuser -pdbpassword -Ddbname -hlocalhost -P3306 The password is on the command line of the commands issued by the script, so it can be seen with ps. That isn't true. If you make a ps, you will see something like "mysql -p x ". As I said before, you can use something like: "mysql -uUser --password=`cat password_file` db" See http://lists.mysql.com/mysql/186720. But ensure that the password_file has access restrictions like -r, so that only the owner can read it. The solution is to put the password in an option file (usually .my.cnf) instead. The client, mysql, will read the password from the option file, without making it available to ps. Michael -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
Hello Averyanov, [EMAIL PROTECTED] wrote: Hello Nuno, Tuesday, July 26, 2005, 8:53:33 PM, you wrote: [EMAIL PROTECTED] wrote: (...) When i try to execute the following query (...) i get this error: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2 (here CONCAT(field2, '-') is replaced with CONCAT(field2, '') ) does anyone know what the matter is? Did you try to make the first query for a second time, when you execute the second query? It seems that the server abords or closes the connection meanwhile. Did you waited too long to write/send the query? Each time i try to execute the 'buggy' query the server crashet. It crashes IMMEDIATELY (without any human-perceptible timeout) Can you provide the output of the logs? Check this for more help http://dev.mysql.com/doc/mysql/en/gone-away.html Does following the steps in that page helps? Try this query and output the result SHOW VARIABLES LIKE '%time%'; mysql> SHOW VARIABLES LIKE '%time%'; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | datetime_format | %Y-%m-%d %H:%i:%s | | delayed_insert_timeout | 300 | | flush_time | 0 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | long_query_time | 10| | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | slow_launch_time | 2 | | sync_replication_timeout | 0 | | system_time_zone | MSD | | time_format | %H:%i:%s | | time_zone| SYSTEM| | wait_timeout | 28800 | +--+---+ 16 rows in set (0.00 sec) The variables seems OK. Next time reply TO THE LIST, others could help. -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
[EMAIL PROTECTED] wrote: (...) When i try to execute the following query (...) i get this error: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2 (here CONCAT(field2, '-') is replaced with CONCAT(field2, '') ) does anyone know what the matter is? Did you try to make the first query for a second time, when you execute the second query? It seems that the server abords or closes the connection meanwhile. Did you waited too long to write/send the query? Check this for more help http://dev.mysql.com/doc/mysql/en/gone-away.html Try this query and output the result SHOW VARIABLES LIKE '%time%'; if you don't find a solution. -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Databases in a different location than the default
Tim Holmes wrote: >> Hello. >> >> You could find the clues in the error log. See: >> >> http://dev.mysql.com/doc/mysql/en/error-log.html >> >> Good Afternoon: >> >> >>> I have rebuilt by web / database server from bare metal this morning. >>> The computer is running Fedora Core 3, and is fully patched and up to >>> date. >>> The MySQL version is 4.1.12-1, and I have installed the following >>> components >>> - Server >>> - Client >>> - Compat-libs >> >> >>> All were installed from the RPMs provided by MySQL.com >>> To start of, I am fairly new to administering database servers, and > > > to > >>> using them, so if my question seems a little elementary, please >>> understand and answer accordingly. >>> My question is this. >>> My databases are located on a different physical machine from the one >>> running the database server - (for backup etc reasons) >>> The databases reside in /home/mysql - that's an NFS share, which I > > > know > >>> for a fact is valid, as I have other data being used from the share >>> the default my.cnf is listed here: >>> >>> [mysqld] >>> datadir=/var/lib/mysql >>> socket=/var/lib/mysql/mysql.sock >>> >>> >>> >>> [mysql.server] >>> user=mysql >>> basedir=/var/lib >>> >>> >>> >>> [safe_mysqld] >>> err-log=/var/log/mysqld.log >>> pid-file=/var/run/mysqld/mysqld.pid >> >> >>> When I start the mysql server with this configuration, it appears to >>> start in good order. >>> It is my understanding that in order to have the server utilize my >>> databases that are on the other machine, I need to change the datadir >>> directive in the my.cnf file to read >>> datadir=/home/mysql >>> when I do this however, mysql fails to start - it waits for a period > > > of > >>> time and gives me a failed error. >>> Can someone please explain to me what is going wrong, and what I can > > > do > >>> to fix it. - I am not interested in having someone fix this for me, I >>> just need some guidance so that I can learn how to fix it myself, so >>> when it happens again, I will know what to do. > > > > [Tim Holmes] > Gleb, et. al. > > As you suggested, I have checked out the log files and this is what I > have found: > > > 050713 11:00:09 mysqld started > 050713 11:00:09 [Warning] Asked for 196608 thread stack, but got 126976 > 050713 11:00:09 [ERROR] Can't start server: Bind on TCP/IP port: Address > already in use > 050713 11:00:09 [ERROR] Do you already have another mysqld server > running on port: 3306 ? > 050713 11:00:09 [ERROR] Aborting > > 050713 11:00:09 [Note] /usr/sbin/mysqld: Shutdown complete > > 050713 11:00:09 mysqld ended > > > This suggests to me a communications problem on either the database > server, or the file server where the databases reside. > > I guess the next question is how do I check to see whats going on here. The error says that you tried to start another server, listening in the same (default) port 3306, which is not possible. Try to start in a different port (like 33306, or 3307). How do you start mysql? A suggest create a different start script for the other databases (/home/mysql), that uses a different my.conf, where you have the other parameters on the database > > I tried telnet 192.168.0.5:3306 and got the following > > [EMAIL PROTECTED] log]# telnet 192.168.0.5:3306 > 192.168.0.5:3306/telnet: Name or service not known > > This may suggest that telnet is not installed, or it may indicate > another problem You used the wrong syntax, you should have tried # telnet 192.168.0.5 3306 (With a space instead of a semicolon) It should prompt you with some imperceptible data, where you can see the version of MySQL Server in the middle. Close it with Ctrl+D > > > Any suggestions are welcome > > TIM > > > -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving a whole txt file in a database
Sebastian wrote: the example i just showed you doesn't need to use delimited syntax. it will load the entire contents of the file into the column you specify.. try it a test: CREATE TABLE `table_test` ( `content` mediumtext NOT NULL ) ENGINE=MyISAM; Mediumtext can be too much, depending on the size of files you store. See http://dev.mysql.com/doc/mysql/en/string-types.html for more details about other *text data-types. create a txt file 'data.txt' with some text, then run query: LOAD DATA INFILE '/path/to/data.txt' INTO TABLE table_test (content); P.S. click 'reply all' so others on the list can see the messages. Gregory Machin wrote: Sorry for not clarifying, I have looked into LOAD DATA INFILE '/path/to/data.txt' INTO TABLE tb1 (col1,col2,...); but by my understanding it only works for delimited text files, the text files are html and php web pages that need to be migrated to CMS.When i tried with one file i ended up with 24 empty fields in the column.. On 7/21/05, Sebastian <[EMAIL PROTECTED]> wrote: what you mean by "whole txt" the entire contents of the file or the actual file itself? to load the contents in it: LOAD DATA INFILE '/path/to/data.txt' INTO TABLE tb1 (col1,col2,...); otherwise the column has to be blob type, using normal insert query: INSERT INTO table VALUES (data...) Gregory Machin wrote: Hi all How does one save a whole txt file in a largetext column ? Ihave found l lots on delimited filesbut non on saving a whole text file . Many thanks -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random rows selection
Michael Monashev wrote: > How to select 5 random rows from big table with WHERE clause? Maybe something like this: SELECT col1, col2 FROM table WHERE col3=123 AND (id_col=RAND() OR id_col=RAND() OR id_col=RAND() OR id_col=RAND() OR id_col=RAND()) the problem is that there is the (low) probability of 2 equal id_cols get randomly generated, return only 4 different rows > This query very slow on 1 mln rows: > SELECT col1, col2 FROM table WHERE col3=123 ORDER BY RAND() LIMIT 5 but that problem can also occur in your slow query Note: I have already sent this privatly forgeting to sen do the list -- Airconditioners and computers have one thing in common: Once you open windows everything fails. Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to know if a migration might have problems
Hi all, I have a MySQL server version "4.1.10a, for pc-linux-gnu (i686)" installed (from official binary RPMs) and running. My question is if there are problems from updating the server to the most current version of the 4.1.x series, specially things that fail to work, or database curruption. I supose that there are not because they are from the same serie and the first has a minor version of 10a, but I want to be sure. Where can I find an official, current/updated, compiled list of migration problems from any version to another? This is for future updates. -- Airconditioners and computers have one thing in common: Once you open windows everything fails. Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: table export problem
Hi all, Juan Pedro Reyes Molina wrote: I have been able to create your table in my mysql 4.1.12 I think your remote server is in mysql 4.0 series I had the same problem some days ago. I can almost garantee that the local server is running MySQL 4.1.x, and I can garantee (99.99% sure) that remote server is running MySQL 4.0.x server or older. Recommend to update remote server, and if it is not possible, change the last line to ) ENGINE=InnoDB AUTO_INCREMENT=303 ; and it probably works. If that don't works, what are the versions off the local and the remote servers? [EMAIL PROTECTED] wrote: (...) I am trying to create a table on the remote server from a table I created on my local sever but it never seems to work CREATE TABLE `sheet1` ( `id` int(10) NOT NULL auto_increment, `title` varchar(255) NOT NULL default '', `fname` varchar(255) NOT NULL default '', `sname` varchar(255) default NULL, `job_title` varchar(255) default NULL, `organisation` varchar(255) default NULL, `email` varchar(255) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `postcode` varchar(255) default NULL, `office_tel` varchar(255) default NULL, `mobile` varchar(255) default NULL, `fax` varchar(255) default NULL, `web` varchar(255) default NULL, `add_info` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ; There seems to be a problem with the last line (this is exported from my local server). I am just learning about mySql as I go so have no real clue about CHARSET and ENGINE (which I believe may be the problem) ENGINE is the engine of the table, and DEFAULT CHARSET is the character set to use by default for the DB. I recommend you to read the chapters of MySQL manual related to charset/character set and to engines. This is the error 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=303' at line 18 and this is what the manual says (not very helpful) a.. Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d This last line seems to be a bug in your remote version of the DB, but it is not bad, and may be fixed in an earlier version of MySQL Any help will be appreciated. Is this enought? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLDump - Command line password
Cabbar Duzayak wrote: ... I have setup cronjobs to take daily backups of my db using mysqldump. But the problem is, mysqldump requires the password to be passed via command line, which means anyone on the same machine can take a peek at my password using "top", "ps -ef", etc. Is there a way of avoiding this, i.e. making it read the password from some file, etc? Or, is there any other alternative I can use? In my backup script, I have something like this mysqldump --password=`cat /path/to/pass_file` --otherOptionsToMysqldump where pass_file is a file with only one line with the password for the username that you use, without trailing spaces, and --otherOptionsToMysqldump are the rest of the arguments that you pass to mysqldump, like -u (for username), or the DB that you want to dump. Don't forget to use the fullpath to the pass_file in case that it doesn't work (or if you want to sleep at night :)). Don't forget to restrict the read access to that file to the root (recommend) or some user (yours?). I recommend you to test the command in a command line, and see if it works, but when it works at command prompt, test if it dumps when it runs from the cron file. Rich: I won't recommend it, because it is a security hole: any user with access to a login in the Machine could access to the DB without restrictions, if he founds that the particular username that is in the my.cnf he has no restrictions to access the DB. Rich and Cabbar: But that also remembered me to set GRANT priviliges of that user only to SELECT, and then he can only read the DB (what could be bad), and not alter it, but the you would need another user to alter the DB. -- Airconditioners and computers have one thing in common: Once you open windows everything fails. Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: DROP FUNCTION doesn't work]
[EMAIL PROTECTED] wrote: > mysql> create function betatouni returns string soname 'libbeta2.so'; > ERROR 1026 (HY000): Error writing file 'mysql.func' (errno: 121) Using perror from Linux, I saw: $ perror 121 Error code 121: Remote I/O error You probably have communications problems. Maybe you should try placing the remote FileSystem in the local disk of the MySQL server, in order to avoid this kind of problems. (Forgot to send to the list.) Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect from remote computer
Probably one problems with permissions. Salama hussein wrote: I can't connect to Mysql from a remote computer yet I can log in fine locally. When I telnet from a remote computer to port 3306, I get a response. The response wasn't meaningful but it was a response. Any ideas? Salama -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
