Re: Any explanation for this, please
On Wed, 2006-11-29 at 14:31 -0500, Mikhail Berman wrote: Dear List, Could I get an explanation why 1 fails, but 2 works. 1. Update statement preceded with explain, fails: explain update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; ERROR 1064 (42000): 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 'update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker =' at line 1 Explain only works with tables and SELECT queries: http://dev.mysql.com/doc/refman/4.1/en/explain.html -- Pat Adams Digital Darkness Promotions Dallas Music Wiki http://digitaldarkness.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on a NAS
On Tue, 2006-11-28 at 17:58 +0100, Stefan Onken wrote: Can you explain this a little bit more ? I am not the guy who set it up, so I would like to go back them and say Well, You cannot do this, because... :) http://dev.mysql.com/doc/refman/5.1/en/multiple-servers.html describes the pitfalls of running multiple servers on the same machine about halfway down the page in the bold 'Warning' paragraph. -- Pat Adams Digital Darkness Promotions Dallas Music Wiki http://digitaldarkness.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: java mysql alias is being displayed blank?
On Wed, 2006-10-11 at 16:03 -0700, ADAM CZECH wrote: Does anyone know why a mysql alias would not display in the return a result? When I try to access say the first name with it's alias f_name, it is blank? , but it works for its column name first_name? This becomes more of a problem with subselects because how does one alias it? Servlet sql: sql = SELECT + u.id as user_id, + u.first_name as fname, + You're aliasing it as fname, not f_name. -- Pat Adams Digital Darkness Promotions Dallas Music Wiki http://digitaldarkness.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Keys should not both be set for column...
On Sat, 2006-09-30 at 12:51 -0700, Brian Dunning wrote: phpMyAdmin is giving me the following warning: PRIMARY and INDEX keys should not both be set for column `referer` Here is what the table looks like: CREATE TABLE `myspacemap_visitors_2` ( `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `lat` double NOT NULL default '0', `lon` double NOT NULL default '0', `referer` int(12) NOT NULL default '0', PRIMARY KEY (`referer`,`lat`,`lon`), KEY `creation` (`creation`,`referer`), KEY `referer` (`referer`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; What does this warning mean, and should I do anything about it or just ignore it? Everything is working fine. MySQL is trying to tell you that you've got the same index defined twice. Since the leftmost column in your primary key is referer, you do not need it as its own index. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com signature.asc Description: This is a digitally signed message part
Re: How To Pronounce MySQL
On Mon, 2006-06-12 at 13:32 -0400, Jesse wrote: Um, did anyone actually answer Jesse's question? The consensus seems to be that the correct way to pronounce it is My S-Q-L, Not My Sequel. So, that's the way I'm going to pronounce it. Actually, most of the people I say the name to have no idea what I'm talking about anyway. I only rarely have vocal communication with someone who would even know what a database is, let alone what MySQL is. So, I guess in the long run, it doesn't really matter, but just for those rare occassions when I'm talking with someone who knows what it is, I want to pronounce it right. Most of my technical conversations occur through e-mail or newsgroups like this one, in which I spell it MySQL, and the way it's pronounced doesn't matter. :-) According to the manual (http://dev.mysql.com/doc/refman/5.0/en/what-is.html): The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we don't mind if you pronounce it as “my sequel” or in some other localized way. -- omni Check out the Dallas Music Wiki http://digitaldarkness.com The information contained in this e-mail, if any, is often incorrect and probably plagiarized. It is intended solely for the amusement of the addressee. If you are not the intended recipient, my bad. Any action taken or omitted to be taken in reliance on the information in this message is your problem. Please notify me immediately if you have received it in error by reply e-mail and then delete this message from your system and any files in it's vicinity. I endeavour to ensure that my emails and any attachments are free from viruses, content, value or other contaminants. However, I cannot accept any responsibility might something worthwhile accidentally slip in. I therefore recommend you do not read them at all just to be sure. Please note that the statements and views expressed in this email and any attachments are completely chosen at random by the author and do not necessarily represent anything coherent, relevant or useful. signature.asc Description: This is a digitally signed message part
Re: Quick Linux/MySQL performance questions. (fwd)
On Fri, 2006-05-12 at 10:59 -0400, RV Tec wrote: Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. NPTL stands for Native POSIX Thread Library, so yeah, it's using NPTL. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
RE: Help with subqueries... MAX() and GROUP BY
On Fri, 2006-04-28 at 00:13 -0700, Daevid Vincent wrote: The problem is now that I can't get the right data. mysql select max(created_on), user_id, id from logs group by user_id; +-+-++ | max(created_on) | user_id | id | +-+-++ | 2006-04-25 20:10:59 | NULL| 4 | | 2006-04-27 23:48:27 | 1 | 50 | -- 456 | 2006-04-27 22:18:35 | 2 | 16 | -- 431 +-+-++ The max date is correct but that isn't the correspoinding action id, they should be 456 and 431 instead... If you don't include id in either an aggregate function or the GROUP BY clause, MySQL 'helps' you by choosing a seemingly-random value to stick in the id field. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: Combining ansi and theta joins bug?
On Fri, 2006-04-21 at 11:42 -0500, Duzenbury, Rich wrote: Is there some known bug about combining theta and ansi style joins in the same query? As I say, this works on a 4.1 server, and it will be troublesome to convert all of the old queries in order to upgrade. In MySQL 5.0.12 they changed the way MySQL handles joins to conform to the ANSI standard. http://dev.mysql.com/doc/refman/5.0/en/join.html Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
RE: Form value editing
On Thu, 2006-04-20 at 19:15 -0400, fbsd wrote: can someone please just send me or post a example of editing feilds of a mysql database within an html form and then updating the values thanks alot These should tell you everything you need to know. http://www.php.net/manual/en/ref.mysql.php http://dev.mysql.com/doc/refman/5.0/en/insert.html http://dev.mysql.com/doc/refman/5.0/en/update.html http://dev.mysql.com/doc/refman/5.0/en/select.html -- omni Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: mysql_query gives Resource id #3 error
On Sat, 2006-03-11 at 12:53 -0500, fbsd_user wrote:
$sql = SELECT logon_id
FROM members
WHERE logon_id = '$logonid' AND logon_pw =
'$logonpw';
$result = mysql_query($sql) or die('Query failed. ' .
mysql_error());
print $result; shows Resource id #3
Where can I find meaning for what this means?
And why does mysql_error() not contain the description of this
error?
And why was the 'or die' condition not taken?
Try print($result[0]) or print($result['logon_id']); $result is a handle
to the result set, not something you can print. It's the same thing as
if you tried to print out the return value of mysql_connect, which
should return a resource id. There wasn't an error with the query, so
the or die shouldn't execute, and mysql_error should return null.
--
Pat Adams
Digital Darkness Promotions
Check out the Dallas Music Wiki http://digitaldarkness.com/tiki
signature.asc
Description: This is a digitally signed message part
Re: inquiry
On Wed, 2006-02-22 at 03:47 -0800, Anago Chima wrote: Q: What do the exams cost? A: Both exams are offered at the local equivalent of US$200 / EUR 170*. This question and answer was copied from certification FAQ. Please can somebody tell me 'both' means in the answer here. Does it mean that the price for both MySQL Developer Exam I II are US $200? ie US $200 cover the two exams $200 for the core certification, and $200 for the Professional Certification. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: install trouble, perl DBI
On Mon, 2005-12-12 at 15:20 +, Lewis Ashley Foster wrote: warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 Suggested resolutions: perl-DBI-1.32-5.i386.rpm But I have already installed perl dbi 1.40 like this with no errors: rpm -ivh perl-DBI-1.40-5.src.rpm You're installing the source RPM, which you would then have to build and install yourself. Try installing the perl-DBI RPM without src in the filename. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with foreign keys, new to mysql
On Wed, 2005-08-24 at 06:11 -0500, John Gonzales wrote: CREATE TABLE `journal` ( `journal_id` int(10) unsigned NOT NULL auto_increment, `journal_category` int(10) unsigned NOT NULL default '1', `journal_datetime_created` timestamp NOT NULL default CURRENT_TIMESTAMP, `journal_datetime_modified` timestamp NOT NULL default '-00-00 00:00:00', `journal_title` varchar(50) NOT NULL default 'no title', `journal_entry` blob NOT NULL, PRIMARY KEY (`journal_category`), KEY `journal_category` (`journal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 [jg.] CREATE TABLE comments ( comment_id INT, comment_journal_id INT, INDEX jrn_id (journal_id), FOREIGN KEY (comment_journal_id) REFERENCES journal (journal_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB; I got this as a respsone: Key column 'journal_id' doesn't exist in table From the manual: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. You've got an INT in comments table and an int(10) unsigned in the journal table. They need to match in signedness. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
Re: BLOCK SELECT INTO OUTFILE ?
On Wed, 2005-08-24 at 10:11 -0300, Alejandro Gad wrote: Hi, I am going to implement a mysql hosting, and I would to make a question, if a mysql-user with only a SELECT privilege make this query: SELECT * FROM table1 INTO OUTFILE '/mysqldb/data/test.sql'; the result is a file in this path with the content of the table. I could think that a malicious user can do several querys like this and fill my disk. How could I secure this command ? From the manual: http://dev.mysql.com/doc/mysql/en/select.html The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. The file cannot currently exist, which among other things prevents files such as /etc/passwd and database tables from being destroyed. If you take away the FILE privilege they won't be able to write out the filesystem. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
RE: Linux vs. Windows?
On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
RE: Linux vs. Windows?
On Fri, 2005-08-12 at 09:30 -0600, Duke, Brian wrote: Like crashing, auto-rebooting, memory leaking, program cost, etc... -Original Message- From: Pat Adams [mailto:[EMAIL PROTECTED] Sent: Friday, August 12, 2005 9:21 AM To: mysql@lists.mysql.com Subject: RE: Linux vs. Windows? On Fri, 2005-08-12 at 09:55 -0500, Jay Blanchard wrote: [snip] Same machine, any performance difference? [/snip] Yes. Linux will consistently outperform Windows in many areas. Except for those areas that it doesn't. In all seriousness, what operating system you use should not be based on expected performance. It should be based on your ability to administer it. If I were to test the performance of MySQL on two identical machines, one running Windows and one running Debian, I can almost guarantee that the Linux box would blow the Windows box away. On the flip side, my company would be better off running MySQL on a Windows box rather then one of our Solaris or AIX boxen, since none of us know much about them (they're maintained by our corporate office). Even though Linux more or less acts like its big UNIX cousins, the nitty gritty details of system administration, security, and patching are much difference. So put your database on whatever platform you're comfortable running. If you can secure a Windows box and make it stable, use Windows. If you can secure a Debian or RedHat or insert flavor of UNIX/Linux here, use it. You'll get a much higher return on investment from making sure that the server itself is set up correctly and the tables and queries that run on it are set up correctly then you will tuning e2fs parameters on a Linux box, or whatever it is that Windows admins do to make their boxen faster. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
Re: True randominess
On Thu, 2005-08-04 at 15:00 -0400, Scott Hamm wrote: I've noticed that rand() do not change on each query request. Is there a way I could get a TRUE randominess into MySQL? http://dev.mysql.com/doc/mysql/en/mathematical-functions.html Are you using RAND() or RAND(n)? Using RAND() makes MySQL choose its own seed (the documentation doesn't specify what seed it will use). If you choose to seed the random number generator (for example, RAND(3)) and then start using RAND() it will produce a repeatable sequence. mysql SELECT rand(3), rand(), rand(); +--+--+--+ | rand(3) | rand() | rand() | +--+--+--+ | 0.18109050875631 | 0.75023213843837 | 0.20788919665654 | +--+--+--+ 1 row in set (0.00 sec) mysql SELECT rand(), rand(), rand(); +--+--+--+ | rand() | rand() | rand() | +--+--+--+ | 0.78874959870125 | 0.32008043427028 | 0.23415340598128 | +--+--+--+ 1 row in set (0.01 sec) mysql SELECT rand(3), rand(), rand(); +--+--+--+ | rand(3) | rand() | rand() | +--+--+--+ | 0.18109050875631 | 0.75023213843837 | 0.20788919665654 | +--+--+--+ 1 row in set (0.00 sec) Notice that the numbers after calling RAND(3) are in the same sequence. However, in answer to your question, there is no way to get TRUE randomness in a computer system. Even cryptographically secure random number generators can be predicted under absolutely identical circumstances. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
Re: True randominess
On Thu, 2005-08-04 at 14:44 -0500, 2wsxdr5 wrote: There are also several places that you can get a reasonably random number for the seed from your machine. The amount of free disk space, unless that doesn't change much on your machine. The amount of free RAM, (up time mod cpu usage). Any number of things could be used that are not very predictable, if at all. But again, those aren't truely random. They're random-enough for the average web applications. The original poster, if memory serves, asked if it was possible to get true random numbers from MySQL. True random numbers can't be predicted even if I know everything about your system. Because computers are predictable beasts, the random number generators that they used are constrained by the hardware limits. But it's really just a semantic difference. Seeding with digits from the least significant part of a UNIX timestamp would be sufficient to seed a RNG randomly enough for average web applications, but it's not truely random, since a web log will show what time the user hit the application, and you can figure out what the RNG was seeded with at that point. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
Re: Help need search database
On Fri, 2005-07-29 at 14:00 +0200, Gregory Machin wrote: I need search all the tables in a database for a single string. I'm trying to figure out how, where and what other tables exponent cms saves it text pages and references to, so i can finish writing a mass page import module In short can mysql do a recursive search, through all the table's in a database .. I'm not aware of a way to do a recursive search, but I've had to solve a similar problem before. This command: grep -H --binary-files=text search_string *MYD | cut -f1 -d: | sort -u run in a database directory on a *NIX box (or Windows with Cygwin) will print out the tables that contain search_string. It takes a while, but it gives you the information you're looking for. I probably wouldn't run that on my tables if they're being written to, but I haven't had a problem (yet). -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
