Re: Deleting from one table blocks other tables?
From: Philip Mak [EMAIL PROTECTED] I'm trying to delete 5 million rows... Do you have to do this often? ...access to the other tables in the database are blocked... How do you mean blocked? Do you mean that you're getting an error message someplace, or that other operations take way too long, or something else? Any suggestions on how I can delete those rows without causing a lot of downtime? Do the big operations at a time when your site isn't busy. Like at night. sql (stupid filter...) Yes, but at least they're trying. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: converting text to hypertext
From: Rick Tucker [EMAIL PROTECTED]
Those addresses aren't being recognized as links when I output
an html table from my queries.
[ snip ]
I figured there would by a hypertext datatype of some sort...
Do you mean like this:
CREATE TABLE stuff (
link hypertext
);
INSERT INTO stuff (link) VALUES ('http://www.rbroom.com/');
SELECT * FROM stuff;
a href=http://www.rbroom.com/;http://www.rbroom.com//a
If so, then the answer is that there isn't such a data type. If you want a link, then
you'll have to make it yourself. I suggest making your column a varchar(). Then, you
~might~ get the data with a query like this:
SELECT CONCAT(CONCAT('a href=', link, ''), link, '/a') FROM stuff;
---
Rodney Broom
President, R.Broom Consulting
http://www.rbroom.com/
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: subselect workaround help?
From: Lefevre, Steven [EMAIL PROTECTED] ...I think I need a subselect... It's rare that a sub-select is actually ~needed~, but it does happen. You can almost always get around it with a JOIN of some sort. Final result should look like Student | Class -- Steve Lefevre | Math101 Stacy Adams | Intro to SQL How about: SELECT Students.Name as Student, Classes.Name as Class FROM Students, Classes WHERE Students.ClassID = Classes.ClassID On a side note: Some of your schema naming will eventually cause you trouble, or at least win you some grief from other programmers. - A common idiom is to name a table for what ONE record of data will represent. That means that you'd name the table that holds student information as 'student' instead of 'Students' since one record only contains one student. - It's a bit redundant to have to say 'Students.StudentID'. It might make more sence to readers to see 'student.id' and 'student.class_id'. - You'll notice that I've been using all lower case names. MySQL will allow loose case of column names, but not table names. So, you can say: SELECT name FROM Students; But not: SELECT Name FROM students; If you always use lower case, then you and your co-workers will always know how to type in names without having to get out a copy of the schema first. OK, I'll stop preaching now. Let us know if you need more help with this. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using Perl DBI quote() method with NULL values?
From: Jeff Snoxell [EMAIL PROTECTED]
...I'm using a map statement...
...through the quote system...
Short answer: Don't do that.
The longer answer:
# Prepare the SQL using 'bind' operators.
$sth = $MyDatabase-prepare(qq{
INSERT INTO MyTable
(col1, col2, col3)
VALUES
(?, ?, ?)
});
$sth-execute(@values);
None of the other silly quoting stuff we're talking about applies to what you are
doing. Just use bind variables and ALL of your problems will go away.
Hollar if you need a hand with this, it can be a bit strange the first time.
---
Rodney Broom
President, R.Broom Consulting
http://www.rbroom.com/
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mod_perl and mySql
From: Chris Faust [EMAIL PROTECTED]
Thanks Paul, that tells me to stop going crazy over it at least :)..
Yeah, don't go crazy.
I didn't see this in the thread, so I'll include it; a snippet from the Apache::DBI
docs:
PerlModule Apache::DBI # this comes before all other modules using DBI
Do NOT change anything in your scripts. The usage of this
module is absolutely transparent !
The difference in performance between now and before mod_perl is just so
amazing I was trying to squeeze everything I could out of it.
If Perl loading was a big problem for you, then mod_perl should provide an enormous
win. There are some things to be careful of with mod_perl and DBI, but they are rare.
A quick example, a lot of our scripts declare their own package name:
# test.mpl
# Some set up work here...
my $obj = TestModPerl-new;
# Other optional stuff here.
package TestModPerl;
sub new {
# get a DB connection and do other things.
}
# Methods that do the work
If this were a straight CGI script, the DB handled would go out of scope at program
termination and the connection would close. In mod_perl, the DB handle's DESTROY()
method will NEVER be called, and the connection will stay open without ever getting
used again. The result? You run out of connections as soon as you have handled
max_connections requests. To solve this, just use Apache::DBI like shown in the docs.
By the way, your first post included this piece of code:
my $database = DBI:mysql:database=$db_name:host=$db_location;
$db = DBI-connect($database,$db_username,$db_password) or
die Cannot Connect: . $db-errstr();
This won't quite work. If the DB connection fails, then $db won't be defined and you
won't be able to say: $db-errstr(). Instead, your statement could read:
$db = DBI-connect($database,$db_username,$db_password) or
die Cannot Connect: . $DBI::errstr;
---
Rodney Broom
President, R.Broom Consulting
http://www.rbroom.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DateTime Calculations
From: Peter Abilla [EMAIL PROTECTED] (Column Two - Column One) = Total Minutes This is quite a hack, and there's probably a better way. But it works: SELECT ROUND( (unix_timestamp(column_one) - unix_timestamp(column_two)) /60 ) as my_minutes; I've scoured the mysql site and haven't found something that addresses this. Any ideas? I found this by searching for 'date'. Then, after learing how to use UNIX_TIMESTAMP(), I searched for 'round' (on a hunch) and found that MySQL has a rounding function that fixed my number for me. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql:WHERE IN SYNTAX
From: Peter Abilla [EMAIL PROTECTED] In this example, I have the table 'bar' and the column 'fooID'. It sounds to me like you've actually got two tables. If this is the case, you can do something like: SELECT bar.foo FROM bar, baz WHERE baz.fooID in (baz.fooID) Which would give you all of the bar records whos fooID links to baz.fooID. If you really are working with just one table, then I suspect that you haven't told us everthing. If you did this: SELECT foo FROM bar WHERE fooID in (bar.fooID) you'll probably get every record in the table. By any chance, is bar.fooID a delimited list of IDs? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: hello to new guy
From: Norm Lamoureux [EMAIL PROTECTED] In the DOS database... each organization is seperate... ...exported all data as a text file ...individual web pages for each one. Individual web pages for each one, does this refer to items like: http://www.informsarnialambton.org/listing/36.HTM I would like to have only one database... So the question is how to combine your databases? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
From: Norm Lamoureux [EMAIL PROTECTED] Sounds like I am confusing everyone... I only have one database with over 800 records The data on the website is the result of my database. I wish to search on words in each record. Search with what? Do you mean the page at: http://www.informsarnialambton.org/search.htm --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Executing concurrent select statements from perl on MySQL ISAM tables(fwd)
Sorry, Geetika. It looks like I lost this thread in the mix.
Have you tried some Perl like this:
# concurrent.pl
# Check start time.
for my $n (1..$DO_TRIES) {
print qq{Doing # [$n] PID [$$]\n};
my $pid = fork();
if ($pid == 0) {
print qq{Child [$$] running command.\n};
exec(qq{echo '$query' | $MYSQL $DATABASE /dev/null});
}
}
# Check end time
# Do some math and make a report.
---
Rodney Broom
President, R.Broom Consulting
http://www.rbroom.com/
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl scripts to run concurrent commands on MySQL
From: Michael T. Babcock [EMAIL PROTECTED] Read up on 'fork'... ... (wait for pid1, pid2) ... Geetika, Michael's snippet is better (required) for the end result that you are after. When you get to the point of actually wanting the numbers, you will need to wait for the children to finish. Since it still looks to me as though you may be having dificulting getting concurrent processes to run, my example will get you started. But like Michael said, read up on fork: perldoc -f fork It's only about a page long, and really isn't rocket science. Hollar if you still need a hand. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LIMIT in MySQL
From: Jim Esten [EMAIL PROTECTED] LIMIT is LIMIT starting record,number of records...no? From the 4.0.0-alpha docs, section 6.4.1: The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): mysql select * from table LIMIT 5,10; # Retrieve rows 6-15 If one argument is given, it indicates the maximum number of rows to return: mysql select * from table LIMIT 5; # Retrieve first 5 rows In other words, LIMIT n is equivalent to LIMIT 0,n. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
Hi Norm, It sounds to me like we have some lack of clerity. Let me see if I can place some useful definitions: - CSV A text format for representing data. - CSV file A file written in the CSV format. - Database A place to store data. - Program Software that does something. - Database engine A program that manages the data in a database - Database program A program that interfaces with a database engine. - Search engine A database program that looks through some known data set to find information based on the user's request. - Import The action of loading data into a database. This is often accomplished by a database program taking data from something like a CSV file and reformatting that data to be suitable for the database engine being used. It sounds to me like you may still need to get these: - Database - Database engine - Database program - Search engine Am I any closer? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
From: Amer Neely [EMAIL PROTECTED] I think what Norm is trying to do... is search a DOS-based database using a script from Excite. The data has to be updated on the server, but in a format searchable by the script. Ohhh, that makes sence for his descriptions so far. Why not ask your ISP whether MySQL is installed... He mentioned in the first message that they have MS SQL Server, but he isn't allowed to use it. You will need a 'front-end' to access the database... ...in PHP or Perl... This is the big question I have, What CGI languages are available to him? If none, and only this built-in search engine, then we need to know these things: - What format does this search engine accept data in? - What mechanism(s) are available for importing? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
From: Norm Lamoureux [EMAIL PROTECTED] I downloaded the lastest version of MySQL the other day but it all looks greek to me, not like a database like ACCESS or Alpha Five. Now I'm with you! You're talking about a database being a program with a nifty front end for the users as well as a back end and possibly even built-in CGI stuff. Whereas we've been thinking of a database as a wholey backend system that has to get data loaded and front end access from another source. I was told that they might be a PGP?? PGP is Pretty Good Privacy. I think you mean PHP, and I have no idea what that stands for. (Poorly Hacked Perl?) Making some asumptions: - You can get all of your data exported into something useful like CSV. - You can run Perl on this server. You could use a Perl module like DBD::CSV against your CSV files and have it act like a real database. This would mean a little programming, or at least a little searching on the web. But I'm sure that you could find pre-built, free scripts that will do most if not all of what you're looking for. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hel to new guy
From: Norm Lamoureux [EMAIL PROTECTED] ...I had to create 800 web pages... ...make a text file from our DOS database. I am wondering which program to put the data in. Hi Norm, It sounds to me like you are trying to build a data driven web site on a server with no database available, as aposed to trying to figure out MySQL. Is that right? If so, what level of CGI options do you have? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A SUM with more decimal places than the values being SUMed?
From: Andrew Kuebler [EMAIL PROTECTED] I'm running a query to SUM a Decimal field with the configuration of DECIMAL(6,2) and I get a result looking like: 56.5515009 Hi Andrew, I just did this (v 3.23.52): mysql create table dd ( myfloat decimal(6,2)) mysql insert into dd (myfloat) values (56.123456789), (1234.5678); mysql select * from dd; +-+ | myfloat | +-+ | 1234.57 | | 56.12 | +-+ mysql select sum(myfloat) from dd; +--+ | sum(myfloat) | +--+ | 1290.69 | +--+ Unless I'm missing something, this works as it should. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Executing concurrent select statements from perl on MySQL ISAM tables(fwd)
From: Geetika Tewari [EMAIL PROTECTED] ...perl script fork several processes... ...I think my 'select' statements are not happening concurrently. Why do you think that? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mailing list mysql@lists.mysql.comµÄ×Ô¶¯»ØÐÅ
Is anybody else getting this mail when they post? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Original Message - From: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 25, 2002 11:15 Subject: mailing list [EMAIL PROTECTED]µÄ×Ô¶¯»ØÐÅ ÄãºÃ£¬Äú·¢¸øÎÒµÄÐżþÒѾµ½´ïÎÒµÄÓÊÏ䣬¶àл ÍõïÃñ === Ãâ·Ñµç×ÓÓÊÏä http://home.sina.com.cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hel to new guy
Hi Norm, I gather that you are wanting to bring both databases together into a single MySQL database. In order to do that, you'll have to have the mysql service running on your web server. (Not actually true, but true enough for this conversation) However, it sounds to me like you don't have the option of installing any software on this server. Is that right? If so, you may want to look consilidating the two databases yourself on a different system, like the workstation that you're developing from. Then put all of the data on the web server. Of course, you'll still need some way of storing and accessing the data from your web site. If the web server has Perl installed and available to you, then you're in luck. Actually, you could use any number of CGI programming languages if they are installed, but Perl is the one that I can help you with. Let us know some more about your working enviroment and we'll go from there. From: Norm Lamoureux [EMAIL PROTECTED] No experience, I am a volunteer just trying to figure things out, do a better jon that I am right now, I have to have two seperate databases and I hate that. I was told that My SQL can doi it, it has a web thingy with it. Rodney Broom wrote: It sounds to me like you are trying to build a data driven web site on a server with no database available, as aposed to trying to figure out MySQL. Is that right? If so, what level of CGI options do you have? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
From: Norm Lamoureux [EMAIL PROTECTED] ...700 individual web pages that EXCITE does a search for... Are these web pages actual individual files, or are they simply information in a database someplace that needs to get displayed? I don't know whether my server has these programs, I can check. I would doubt that the server is going to have any of your DOS software installed. I'd be willing to bet money that you are going to have to port this data yourself. Since you aren't funded, you may want to just take a brute force aproach. That is, maintain a list of all the information yourself. Say on your home computer. Then, update the web pages by hand as needed. I may be missing what you need, so let me know. Hey, everybody else. Any input? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Magazine article database
From: Michael Picco [EMAIL PROTECTED] I am interested in creating a magazine article database using MySQL and Python, as an attempt to learn both. A very good idea, learning by doing. It occurred to me that an attempt such as this could be reinventing the wheel. Well, yes. But isn't that the point? You want to learn to invent wheels and intend to pratice wheel making to reach that end. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Alternative for NOT EXISTS
From: Victor Kirk [EMAIL PROTECTED] SELECT u.uname FROM users u WHERE NOT EXISTS (SELECT * FROM team_members=20 WHERE team_id =3D 7 AND user_id=3Du.user_id); It sounds to me like you are trying to ask the database: Tell me all the users that aren't in team 7. If so, this should work: SELECT u.uname FROM users u LEFT JOIN team_members t ON u.user_id =3D t.user_id AND t.team_id =3D 7 WHERE t.team_id is NULL --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql vs. Oracle and concat ||
From: MySQL [EMAIL PROTECTED] select numer ||','|| text from Table: SELECT CONCAT(numer, '||', text) FROM Table; --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Delete sql statement
From: Abhi [EMAIL PROTECTED] Could you pls let me know how to delete duplicate records in a table?=20 DELETE FROM my_table WHERE [predicate] LIMIT 1; --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment ID of Inserted Row
From: Steven Kreuzer [EMAIL PROTECTED] SELECT MAX(id_field) FROM table On Thursday, September 19, 2002, at 12:55 PM, Davis, Tim wrote: What is the SQL to get the created AutoInc ID from a row that I have just inserted? More formally, you can do this: SELECT LAST_INSERT_ID(); --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Queries Per Second
From: Mike Hillyer MH Is there any way to get a real time queries per second in MySQL as MH opposed to the average given when I request the server status? From: Dan Nelson DN Request status twice, with a 1-second delay in between. I think what Mike is looking for is an average across a time period when queries are actually being run. If we let the server sit idle all day, then the average isn't very useful. Mike, Are you looking for benchmark data, or statistics on your setup? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - sql spam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Simple SQL
From: Peter Sampson [EMAIL PROTECTED] Select DISTINCT Month FROM tblNews The problem is that the months are shown alphabetical not in calendar order. I'm assuming that your months are stored as names like: January, February, March or Jan, Feb, Mar If so, I think that sorting in SQL will be a bit clunky. I would advise either storing your dates as dates, or using an outside program to sort your month names. Of course, somebody else might know of something simple that I'm missing. --- Rodney Broom President, R.Broom Consulting - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: joins vs excess fields
A quick note, I've read Tom's email about using a hanger table and simply don't agree. From: olinux [EMAIL PROTECTED] I have a table with a large number of news articles. For my curiosity, how many article records is a large number? A few thousand, or tens of millions? The question is whether to 1) add a field to the database that most records will not use This is the option that will create the fewest records. If you need to make a single query and be finished, this is the way I'd go. 2) add a table to hold image path names and require a JOIN in order to retrieve. Let's figure what type of JOIN you mean. The MySQL docs seem to indicate that a LEFT JOIN isn't all that bad to run, but my benchmarking doesn't agree. So let's assume you want a straight join. If you were doing that, then you'd only get back articles which have an image path defined because of this type of a joining predicate: articles.id = article_images.article_id In that case, why are you taking the overhead of scanning two tables? Why not scan one table with a predicate like: AND article_type = 'feature' I'm betting that typical usage for you will be a single query something like this: SELECT * FROM articles WHERE keywords like '%good article%' Then you'll display all the records that you've gotten. If this assumption is correct, then I'd strongly suggest compacting your data as much as possible. That is, use one table for data that is on a one-to-one relationship and not related to anything else. Of course, the best thing to do after all of the discussion is to simply try it out. I had a very simular situation a month or two back where I was told that the JOINs weren't a problem. I redisigned the schema in the way that I've indicated and got an improvement of many times (5-12 times faster). In short, we can add up what the docs say all we like, but nothing beats actuall clinical testing. --- Rodney Broom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: functions
Good morning Daniel, From: Daniel Jarrett [EMAIL PROTECTED] ...for mysql to perform daily operations.. You bet, it's called 'cron'. MySQL doesn't have anything like scheduling built in. And, arguably, it shouldn't. Since that type of functionality is the responsibility of the operating envoriment and not of the application. If you are using a UNIX like OS, type 'man cron'. If not UNIX, hollar and I'm sure that somebody can tell you how to work it out on your system. --- Rodney Broom sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Having lots of tables
From: Agustín Fernández [EMAIL PROTECTED] Is there any reason to avoid having a large number of tables (which have the same structure)? You might run into a limit on the number of open file handles, but you'd have to check the MySQL docs for how he handles it, and possibly your OS docs to see what the limit is. Would doing just-one-big-table (with a forum-id) be advisable? Very possibly. 100,000 records isn't huge, but it could be big enough to put a load on your machine. Unless you find a definative answer in the docs, I'd try testing both. --- Rodney Broom Programmer, R.Broom Consulting - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Running Mysql On Host other than localHost
From: Kevin Fialkowski [EMAIL PROTECTED] ...so that I can access it from another machine. Hi Kevin, This isn't a matter of how you start the service, but of your user privileges. Users are not only known by user name and password, but also by the host they are coming from. Start with this doc: http://www.mysql.com/doc/R/e/Request_access.html --- Rodney Broom SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLDump
From: Ben Ocean [EMAIL PROTECTED] I can't even run mysqldump --help without getting that error! What am I doing wrong? 'mysqldump' is a system command, not an SQL command. You need to run it from the command prompt. --- Rodney Broom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Power ?
From: Richard Spangenberg [EMAIL PROTECTED] Can mySQL handle... ...as well as Oracle. MySQL is different than the big databases like Oracle, Informix, etc. MySQL is comparatively small and fast, also cheap. As such, it doesn't come with all of the bells and whistles that others might. You may not need built-in tools like clustering when you see the relative cost. For instance, if you have to pay $50,000 to get an Oracle solution to a problem, or could use MySQL and spend $10,000 on in-house programming for that solution, which is better? --- Rodney Broom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DESPERATE NEED FOR HELP!!!!!
From: Balteo [EMAIL PROTECTED] Why don't you create a php script that generates SQL instead of HTML; i will repopulate your database acting as a dump? Not a bad idea. John, be carefull not to mess up whatever cache is currently in place by changing something that will confuse the cache. Maybe download a website ripper and get a copy of all of your content. Then look at solutions from there. At the least, you may be able to go through this content to parse out some of the data you need. Further, what cache is this? Probably not MySQL. Maybe you can COPY off the existing cache on disk and work with copy directly? (which sounds like what you were wanting to do.) --- Rodney Broom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Not null not working??
From: Andrew Hazen [EMAIL PROTECTED] If it was blank, the sql statement would fail... Nope, you're passing an empty string with '$login'. NULL is not empty, it's less that that. It's nothing. What you want is: emplogin=$login Rather than: emplogin='$login' ...is SOMETIMES blank where is should be Robyn. Yep, but I'll be that a query from the MySQL client never says the word NULL. --- Rodney Broom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Password function
From: Kenneth Hylton [EMAIL PROTECTED]
Basically, if you encrypt the password, then you have to look it up using
the password function, too.
What he means is this:
SELECT user_id FROM user WHERE PASSWORD(user_pass) = PASSWORD('my_var')
---
Rodney Broom
Programmer: Desert.Net
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Password function
From: Lee, Andrew [EMAIL PROTECTED]
If user_id and passwd are fields in your table and the passwd field is
encrypted :
SELECT user_id FROM user WHERE passwd = PASSWORD('plaintext_user_pass')
Woops, I was typing too fast Ramiro. Andrew's query is right and mine is wrong.
---
Rodney Broom
Programmer: Desert.Net
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PigeonRank
From: Curtis Maurand [EMAIL PROTECTED] y'all are kidding, right? http://www.google.com/technology/pigeonrank.html --- Rodney Broom Programmer: Desert.Net sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: backup databases
From: David McInnis [EMAIL PROTECTED] Could you share your script for doing the date thing with the rest of us? That sounds useful. From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] mysqldump -uUsername -pPassword --all-databases tmp.sql tar -cf MySQL-Backup-DATE.tar.gz -z tmp.sql rm -f tmp.sql I use a script (to determine DATE)... How about: mysqldump -uUsername -pPassword --all-databases tmp.sql \ tar -cf MySQL-Backup-`date +%Y-%m-%d`.tar.gz -z tmp.sql \ rm -f tmp.sql Try this for an example that doesn't change anything: echo MySQL-Backup-`date +%Y-%m-%d`.tar.gz --- Rodney Broom Programmer: Desert.Net sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why does my delete not work :(
Good morning Sunny,
From: sunny [EMAIL PROTECTED]
...substituting SELECT with DELETE doesn't work :(
That's right, that's how MySQL works. And it doesn't support sub-queries for this
case, either. So you can't say:
delete from table where field in (select field from other_table)
I'd suggest doing this from another language, like Perl. For instance:
$list_list = $dbh-selectall_arrayref(qq{
SELECT messages.topicid
FROM messages
LEFT OUTER JOIN main
ON messages.topicid=main.topicid
WHERE main.topicid is null
});
for my $row ( @{$list_list} ) {
$dbh-do(qq{DELETE FROM messages WHERE topicid = $row-[0]});
}
Note, my example is rough, and not tuned for performance. But you get the idea. Hollar
if you still need a hand with this.
---
Rodney Broom
Programmer: Desert.Net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fetching Data based on the day
From: Soheil Shaghaghi [EMAIL PROTECTED]
SELECT AnnouncementID,Title,DateShown FROM ANNOUNCEMENT WHERE
DateShown=NOW() ORDER BY DateShown DESC
...I only want to look at the month, and the day
SELECT AnnouncementID,Title,DateShown
FROM ANNOUNCEMENT
WHERE substring(DateShown,6,5) = substring(NOW(),6,5)
ORDER BY DateShown DESC
Try these for an explination:
select substring('2001-02-03 04:05:06', 6,5) as mon_day,
substring('2001-02-03 04:05:06', 6,2) as mon,
substring('2001-02-03 04:05:06', 9,2) as day;
select substring(now(), 6,5) as mon_day,
substring(now(), 6,2) as mon,
substring(now(), 9,2) as day;
---
Rodney Broom
Programmer: Desert.Net
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [OT] html to perl to mysql
From: Bob T [EMAIL PROTECTED] The browser displays a new page which displays the perl source code. How do I run the perl script from the browser This means that Apache isn't executing your code, instead he's just reading in the text of myperlscript and sending it to the browser. You need to tell Apache to execute this script. Try adding something like this to your Apache config: AddHandler cgi-script .cgi This assumes that your script is actually called myperlscript.cgi. You'll also have to make sure that the file is formatted properly. For instance, your MS Windows might want the shebang at the top to look a certain way, maybe like this: #! perl --- Rodney Broom Programmer: Desert.Net sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I Sort by Relevance?
From: Walter D. Funk [EMAIL PROTECTED] ...I'd like to sort them so as the result set becomes like this What language, if any, are you working in, other than SQL? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: binary expansion?
From: adam nelson [EMAIL PROTECTED] It's been a while since I've done this, does anybody remember: I'd like a query that looks for lawyers... Have you thought about using an unsigned int and doing bitwise math? --- Rodney Broom Programmer: Desert.Net sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL as reporting database between corporate db and users is it OK?
From: Philippe Cand [EMAIL PROTECTED] - Is there a way to script MySQL to execute a list of commands then execute the load query? Yep, Perl makes this whole list of tasks simple. Or possible, depending on your stance. If you don't write any Perl, then I'd say to use a batch script and write things to files for temporary storage durring the query/load process. But in the end, you'll almost certainly still need to do some text processing of some kind in between the databases. So I'd suggest getting some proceedural language involved. - Is it possible to query a corporate db from within MySQL using existing native Oracle SQL queries? No. - Is it possible to have these queries ran automatically every nights? Yep. It looks like you need to support NT, so I'd say to look into the built in scheduler. You'll have to create a batch file (*.bat) and have the scheduler run that. - I will have to load daily about 10M rows accross about 20 tables . Is going to fit in a 3 hours time frame? Good question, but very dependant on your hardware and current load. I'd say to try some tests. - Which would be the best MySQL version for this application... The probably isn't a lot of difference for your application. I could install MySQL on the D drive of a win2k/1GB Ram server, I have 25GB free on the D drive. is it going to be OK? Probably, accept for the fact that you are using a MicroSoft OS instead of some flavor of UNIX. I'm afraid that I just don't know how much of your performance will get eaten up by NT itself. --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQLDump
From: Boaz Yahav [EMAIL PROTECTED] 1. I was wondering if there is any way to backup MySQL information other than MySQLDump? In some cercomstances, you can simply copy the database file, but this can possese real problems. Are you running your backups durring peak hours, or in the middle of the night when nobody is using the database? Is there a way to use MySQLDump without overloading? % nice -n 20 mysqldump ... --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: very tough query
From: Daren Cotter [EMAIL PROTECTED] ...what happens when I want to delete a poll? Do it machanically. That is, do a query in advance to find the data that needs to be deleted, then go through and delete it. The logic for joining a delete can be written, but MySQL doesn't support it. (Hey folks, does anything else support joined deletes?) By the way, since poll_votes has a PK of aim_screenname/answer_id, folks can give each answer for a given poll once. As apposed to just one answer per poll. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql user table is read only by root
From: Ari Kahn [EMAIL PROTECTED] However, whatever method I try mysql tell me that the user table is read only. It sounds to me like root doesn't have sufficient permissions granted to write to that table, but I could be wrong. There's something in the docs about accessing after you've lost a password, but I don't recall where. Maybe you have another user with write permission to that table who can update roots permissions. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment problem
From: Chetan Lavti [EMAIL PROTECTED] 1. When I am inserting the values and the Query fails, the increment should not took place and It doesn't. 2. When I delete a particular row from the table there should not be any gap in the auto increment sequence. This doesn't work. Although you can drop and rebuild the column something like this (assuming that 'id' is the primary key): ALTER TABLE jack drop id; ALTER TABLE jack AUTO_INCREMENT=1, ADD id int unsigned not null auto_increment default '0', ADD primary key (id); --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to rewrite this statement.
From: Mat Murdock [EMAIL PROTECTED] ... Like *java* Try: Like %java% I would bet the the aseriscs are some non-SQL extention of MS Access. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Problem
From: [EMAIL PROTECTED] My query select * from table1,table2 WHERE table1.ID = table2.code Query results 1 2 2 Desired results 1 2 Try: SELECT * FROM table1,table2 WHERE table1.ID = table2.code GROUP BY table2.code You'll find this someplace in: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html But it's a rather thick document. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl core API
Has anybody ever talked seriously about a Perl API for MySQL? I'd like to be able to access all phases of the MySQL deamon with Perl in much the same way we access Apache via mod_perl. Things like: - Query rewrite - Data manipulation, pre-storage - Data manipulation, post-query - Prodedures --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl core API
From: Colin Faber [EMAIL PROTECTED] With the up and coming libmysqld library this should be possible; libmysqld? Sounds interesting, do you have an URL? Though I don't really see any advantages to this given the front end interface we have now (DBD::mysql) works like a charm. As a quick example, I want to be able to define my own datatypes inside the DB. I might do something like storing an image and a bunch of metadata about that image in a single blob() vi Storable::freeze(). Then provide some Perl code plugged into the DB that lets me look at the data inside of blob()s in order to query over images at the SQL level. At this point, I can access the DB from any interface I like: JDBC, DBD::mysql, the mysql client, etc. Regarding procedures, This is something that should be handled by the rdbms it self and not your middleware. Yep, you're right, but I'm not after middle-ware. I'm after a hook into the RDBMS itself. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tweaking MySQL on a RaQ (or any server) Help please.
From: James Riordon [EMAIL PROTECTED] ...and I have not come up with a way to make our machine more efficient. Hi James, I'm not a MySQL expert, and I don't have a lot of experience with finite optimizations of the programs you are using. However, I do have a little bit of experience with making money. If you are having to spend this much effort on getting stability, maybe it would be more cost effective to spend $1000 on another machine and move MySQL to that? This might also require bying some additional rack space, but it wouldn't need to cost any additional bandwidth charge if the machine that MySQL is currently on is the only one that needed to connect to it. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: authentication
From: Don Smith [EMAIL PROTECTED] our other alternative ms sql server 2000 which has the ability to authenticate off samba Uh, oh! Another of our brethren is being tempted by the Dark Side. I doubt that MySQL could be easily coerced into external authentication, since the existing authentication is just info in the mysql database. But, there's always a solution to using a MicroSoft solution. Here's one plan: Set up some type of hook into the password assignment tool for Samba. When a password gets changed, update MySQL accordingly. I haven't worked with Samba, so I don't know how passwords are managed there, but I'm sure that you could at least write a wrapper around the call to the Samba password tool to get this done. Make sense? If not, let us know. I'll help if you need. --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: copying databases
Hi Bryan, Start with this: % mysqldump --help From: Bryan McCloskey [EMAIL PROTECTED] ...make a copy of my database % mysqldump --opt database_one copy.sql ...data itself is not important... ...better if I could _not_ copy the thousands of rows... % mysqldump --opt -d database_one copy.sql To reload into database_two: % mysql [options] database_two copy.sql # Or % cat copy.sql | mysql [options] database_two If you need to create a database: % mysqladmin create database_two --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count() does not count correctly?
From: Jens Mildner [EMAIL PROTECTED] Every year from years.year should be returned along with the number of games that have been released in that year... Years in which no games were released are returned with a gamecount of 1. This is a side effect of the JOIN syntax. Try this: SELECT years.year AS yearlist, count(years.year) AS gamecount FROM years, games WHERE years.year = games.release_year GROUP BY years.year ORDER BY gamecount DESC, yearlist --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie Question: Data Type Choice
From: Kevin Campbell [EMAIL PROTECTED] What's the best data type to use for storing guest book comments in my database table. The best data type would be the kevins_gb_comment data type, but I don't think that's implimented in MySQL yet. If all you are doing is storing and retrieving the data, then you'll probably want to use the text() type. If you will also want to do some searching, then you might want to use a text() column to hold the display data and set up a few processed varchars() to hold interesting strings to match searches against. But the whole searching thing can become a pretty big subject. --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL on Valueweb Server - Max number of fields
From: Tom Strom [EMAIL PROTECTED] I have a table with about 3900 records. The table consists of 7 fields... ...Id, name, address, address cont'd, city, state, phone#. I don't actually know anything about WinMysqlPro, but maybe I can help set up some of the base information that we'll need. - What are the exact field names you are importing from? - What are the exact field names that get created? - What program/method are you using to create the DB3 file? Other folks probably already know, but I'm confused on this part. --- Rodney Broom Programmer: Desert.Net Spam filter: sql, database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! Inserts way too slow in big db
From: Steve Rapaport [EMAIL PROTECTED] CREATE TABLE White ( Your table definition isn't exactly acurate, it's missing some column declorations and things. But that's OK, I didn't really need to see it created. Maybe I can offer some programatic help instead of DB design or MySQL help. I notice that you're schema has about 15 indexes, I presume that this is to allow for a variaty of fast searching formats from some program, yes? If so, there may be a way to rethink the interface to this data. How are you using this data? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: connecting to mysql from different computer
Good morning Kamal, From: [EMAIL PROTECTED] More so will i need to open MySQL server on my local machine before running MySQL client to connect to the remote machine e.g. No. The service (mysqld) is strictly assigned to managing the local database(s) and to serve access to those databases to whomever it sees fit. The question of who is seen as fit is determined by the GRANT statements used earlier. The client simply connects to a MySQL server someplace. That server might be local, or it might be on the other side of the planet. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: connecting to mysql from different computer
From: [EMAIL PROTECTED] I have a similar problem... Just to be sure, it's helpful if you could send us the message that your getting when you see the connection problem, but this sounds like the same thing. Can this sovle my sleeplessness problem? Probably. Anytime I can't sleep, I just look for some sound advice. If that doesn't work, then I like to try some warm milk and a couple of my wife's chocolate chip cookies. --- Rodney Broom Programmer: Desert.Net Spam Filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: new user basic questions
From: [EMAIL PROTECTED] I am new to dababases in general... Learn SQL (which it sounds like you've done). It's almost always going to be the standard tool between various database vendors and APIs. ...would like to use Visual Basic ...to edit the database. I won't make any MickySoft vs. *NIX cracks. I'm not a VB person, but I'm thrilled to see students working with real world tools. (In school, all I got was an abacus.) I searched briefly on the mysql.com site and found a few listings. http://www.mysql.com/doc/manual.php?search_query=VBdepth=0 In short, it still looks like ODBC is the way to go. I've had folks connect their MS Access and Crystal Reports via ODBC to my MySQL databases and only had limited problems. For instance, the int() in MySQL was too big for Crystal Reports. -at least one way to connect to the Mysql database with VB6 To use ODBC, you set up a user/pass/host in MySQL to allow clients to connect. Then you set up a DSN on your MS Windows machine that points at the MySQL host, using the proper user/pass. You'll need the ODBC drivers, I can only suggest checking your VB docs, and/or the ODBC Data Sources option in the Control Panel to figure this out. As for writing the actual connect statement, I don't have the foggiest idea. Check the docs that came with your compiler. -what commands I can use in VB6... I can't help you there. Definately the docs that came with your compiler. ...a site that will point out how to use the commands ~Maybe~ microsoft.com. My experience is that their site is usually not very helpful. -can I use an SQL book for reference on commands Yes, or the MySQL docs (section 6 on the mysql.com site). Unless you run into one of the many situations where MicroSoft is claiming that they are using a standard when they really aren't, then most of your SQL92 knowledge should just work through any API on any SQL database. At the very least, if VB6 offers a direct SQL interface to MySQL, then you should be able to do anything with SQL that MySQL says it supports. ...only a senior in high school Only? In just a few years, you and your pals will be running the world. ...so I have no one to turn to except the internet and searching through the archives is the only help I have had so far. Heh, join the club. Don't worry about your resources not being human beings. Finding and maintaining a good set of documentation that works for you is a big part of what makes you salable in the real world. Life on the job is an open book test; you don't have to know everything, you just have to know how to find it fast enough to make your boss happy. Any help with any of those questions is much appreciated. I'm very encouraged to see a newcomer here. I hope that you get plenty of assistance from this group. Hint, hint. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: adding space fill to field
From: Jeff Small (NWCC) [EMAIL PROTECTED] ...how do you fill in the rest with forced spaces? char pads, but you may not want to alter your database. Why do you ~have~ to have 21 bytes? Can the device you're pulling this data into be modified to handle things more intelegently? Another option might be to pipe the output through a filter. I should think that a little Perl or sed would be nice. --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: connecting to mysql from different computer
From: Dan [EMAIL PROTECTED] I can connect... on the same computer ...different machine it won't work. I suspect that you need to add an entry at the MySQL host for the machine that you are connecting from. This can be done with a GRANT statement: - Format: grant all privileges on databaseName.tableName to 'userName'@'hostName' identified by 'somePassword'; - Example: grant all privileges on myDB.* to 'clyde'@'coolPlace.org' identified by 'secretPass'; Then you'll need to reload the access privileges. It can be done at the command line like this: % mysqladmin -u rootUserName reload Remember, you may need to add the -p switch and suply a password for this command. Is this enough info? --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Searching for partial dates
From: jeff [EMAIL PROTECTED] I need to be able to search by partial dates. What is the best way to search? You ~could~ do something harsh like: SELECT *, substring(myDate, 1, 2) as month FROM myFunnyTable WHERE month = '06' But unless you are only going to need this data for a short while, I'd certainly reload it into a better format. If you know that you'll have a bunch of funny queries to make, like sometimes just month, and sometimes just the year, then it might be worth breaking the date into several different formats. Like: daysmallint month smallint year smallint whole_date datetime My suggestion would be to use some Perl to accomplish this. I'm sure that other folks could show you how to do it all in SQL, but not me. You should understand, the data migration process will be as slow as your current queries. But you'll pay a price once and not have to pay it again and again. Let me know if you choose the Perl route and need a hand. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Querry trouble
From: MYSQL [EMAIL PROTECTED] I need to run a querry to return the following: date, email, client from table2 where the email address in table2 isn't already in table1. Try this: SELECT * FROM table2 LEFT JOIN table1 ON table1.email = table2.email WHERE table1.email is NULL; --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL - Got Signal 11 - URGENT
From: Jonathan Hilgeman [EMAIL PROTECTED] On a related note, any Perl gurus know how to force a Perl cron job to stay below a certain CPU utilization? Not a Perl thing. Do a man on 'nice'. --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't stop mysql / possible bug?
[EMAIL PROTECTED] wrote: I changed the script and now all is well. However I have two concerns: 1) Paranoid about the password being in this script. Is there a way around this. chown root:root /etc/rc.d/init.d/mysqld chmod go-rx /etc/rc.d/init.d/mysqld Just to be clear, if the permissions somehow already include 'write' for group or other, then this chmod won't remove that. I'd suggest this: % chmod 500 /etc/rc.d/init.d/mysqld % ls -l /etc/rc.d/init.d/mysqld -r-x-- 2 root root 4040 May 29 11:20 /etc/rc.d/init.d/mysqld --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Maximum # of Columns
From: Sparta Cruz [EMAIL PROTECTED] Do you think this was due to software constraints(MySQL), or do you think it had to do with hardware constraints? I'm sure it was software. Possibly the MyISAM tables I'm using. -Original Message- From: Rodney Broom [mailto:[EMAIL PROTECTED]] I had to do something like this the other day. I tested and was only able to create 2945 columns per table. If you ~could~ keep the data in a single file per record, then maybe you'd bennefit from putting all of the data (formatted) into a text or blob. --- Rodney Broom Programmer: Desert.Net Spam filter: sql database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Treating multiple records as a single-multiple value
In your examples, ID 2 should NOT get returned. What I'm after is analogous to a Perl
hash where you could say:
if (!exists $colors{2}-{blue}) {
...
}
Make sence?
---
Rodney Broom
Programmer: Desert.Net
Spam filter: sql database
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem adding table
From: rc [EMAIL PROTECTED] is data a reseverd word in mysql? No. On Wed, 31 Oct 2001, Richard W. Wood wrote: I've been trying to add the following table to my database, and I keep getting the error below. I copy-pasted your SQL and it worked. I'm using 3.23.38. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl Module Help
From: Patrick Nelson [EMAIL PROTECTED] Trying to get the benchmark 3.23.43 installed but it says I need MYSQL-DBI-perl-bin which I can't seem to locate. Hmm, I couldn't find that either. Can you tell me where you've found benchmark, I'll have a look at it and see if I can answer your question. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl Module Help
From: Patrick Nelson [EMAIL PROTECTED] RH 6.2 with the following MySql stuff installed (by rpm) Unfortunately, I could only find 'bench' in RPM. Since that requires me to install the package (which I'm not willing to do without know what's going to happen), I can't continue. I appoligize. Please, let me know if you bump into a tarred source of this package. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: error in your SQL syntax near 'order by 'Variable_name' ????
From: [EMAIL PROTECTED] MySQL said: You have an error in your SQL syntax near 'order by 'Variable_name' ASC' at line 1 Any ideas as to what this means, and how to fix it? Hi Dave, How 'bout sending us the entire SQL statement, please. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to add mysql to my startup
From: Bertrand TACHAGO [EMAIL PROTECTED] % cp /usr/local/mysql/support-files/mysql.server \ /etc/rc.d/init.d/mysql.server % chmod 544 /etc/rc.d/init.d/mysql.server I had done it but it doesn't work % ln -s /etc/rc.d/init.d/mysql.server \ /etc/rc.d/rc3.d/S99mysql What is the importance of this command? It links one file to another. Try this: % man ln --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: database documentation
From: Ravi Raman [EMAIL PROTECTED] if you (or anyone else) finds/has found anything better i'd be interested to know. I've used Visio here and again, mostly V.6. It involves a lot of legwork since I have to manually deal with all of the lines representing relationships, but it creates a very nice output that I can export in many formats. I know that Visio Professional 5.0c can use some interesting relationship tools that handle most of the line manipulation between items. I've tried the same tools in later versions and they plainly didn't work (erred out). I've thought of getting and installing that older version just for the DB support. I've also looked at several levels of software on these download sites. Not even the pay-ware offerings looked worth having. My Dad sais that ERWin is very powerful and suggested that I buy myself a copy. Then I heard that it runs about 3K and opted not to. --- Rodney Broom Programmer: Desert.Net Spam filter: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Client-side developing
From: Walter Omar Autalán [EMAIL PROTECTED] I want to develop Windows-based client to interact with linux-based MySQL and I can't realize the best programming language suitable for that task. Clients must be run in Windows because customer requirement. There's an ODBC driver for MySQL. I've had folks using it for things like Crystal Reports and it works OK. That is, I haven't seen any problems in the driver itself. However, I remember both Crystal Reports and MS Access having problems with the size of the intergers returned from MySQL. My guess from there (since I don't program outside of *NIX) would be something like C++ (MS or Borland are two big names that come to mind), or even MS Visual Basic. bashing MickySoft You remember what BASIC stands for, right? Beginner /bashing MickySoft --- Rodney Broom Programmer: Desert.Net Spam filter: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Client-side developing
From: Doug Poland [EMAIL PROTECTED] Have you thought about a web based app? I've successfull steered customers from an Access/MDB or VB/MDB applications to HTML based apps talking to *nix/apache/php/mysql. Here, here. I think this is the way to go if you can. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: please help me .........cannot find -lmysqlcliente
From: katty Iniguez [EMAIL PROTECTED] make: /usr/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status make: *** [radius] Error 1 Now, i added the line: /proc/6/fd/mysqlclient - - - (that is where I found lmysqlclient) This location probably isn't what you want. You probably want something inside of your mysql instalation directory. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ER Studio
From: T. S. Parikh [EMAIL PROTECTED] ...I would like to generate a MySQL DB from this data model. ...Er Studio doesnt directly support outputting to MySQL. This is an interesting topic, IMHO. Tim Bunce has a mysql-to-oracle converter that he mentioned on this list: ARCHIVE: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:81696 I haven't actually looked into what's available, but it might be interesting to have a set of utilities for converting between SQL based schemas. Speaking as a Perl programmer, it might be done by having a standardized token representation of SQL that could be used for explaining a schema. Then have plug-in modules for converting this DB independant information from these representations into DB specific SQL. The usage might look like: use SQL::Tokens::MySQL; use SQL::Tokens::Oracle; $tokenized = SQL::Tokens::Oracle::tokenize($oracle_schema); $mysql_sql = SQL::Tokens::MySQL::generate($tokenized); There would be some obvious problems with things like unsupported data types from one DB to another, not to mention things like FKs, views, etc. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Functions as default values
From: Gerald Clark [EMAIL PROTECTED]
Default values must be constants.
Håkan Elmqvist wrote:
Now I want to register who changed it...
=== MY two cents worth:
With MySQL, this sort of thing usually gets handled in a piece of software. In Perl,
we might say something like:
$sth = $dbh-prepare(qq{INSERT INTO my_table (user, data) VALUES (?,?)});
$sth-execute(user(), $data);
I know that I'd like to be able to define internal divisiveness like:
CREATE TABLE my_table (
id int unsigned default my_sequence_routine(),
...
);
But I wouldn't be willing to have this in the engine if it meant a performance hit
during the times when I wasn't this functionality.
---
Rodney Broom
Programmer: Desert.Net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to add mysql to my startup
From: Bertrand TACHAGO [EMAIL PROTECTED] i'm using Linux Red Hat 7.0 and i want to know how to add mysql to my startup. Your MySQL shipped with a file called 'mysql.server', this is the control script. Put that into your RC tree and set it to be executable. I've got this: % cp /usr/local/mysql/support-files/mysql.server \ /etc/rc.d/init.d/mysql.server % chmod 544 /etc/rc.d/init.d/mysql.server % ln -s /etc/rc.d/init.d/mysql.server \ /etc/rc.d/rc3.d/S99mysql --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 123box.co.uk mail delivery system (from:MAILER-DAEMON@123box.co.uk)
Hi guys, From: Carl Troein [EMAIL PROTECTED] Sadly, the admin of this list doesn't give a shit. This is rather obvious from Sorry about that. Here's the answer: [EMAIL PROTECTED] --- Rodney Broom Programmer: Desert.Net sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 123box.co.uk mail delivery system (from:MAILER-DAEMON@123box.co.uk)
From: Will French [EMAIL PROTECTED] Sorry about that. Here's the answer: [EMAIL PROTECTED] And this means what? A little less criptic, please, for use simpletons. Heh, it means that I typed too quickly and wasn't really watching. [EMAIL PROTECTED] As for why I wrote that... Alexander Skwar writes: Could this user please be removed? It's [EMAIL PROTECTED] --- Rodney Broom Programmer: Desert.Net sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL is hogging my box
From: Stefan Pinkert [EMAIL PROTECTED] There are some others with a similar problem(including me). Hi guys. I'd be really interested in knowing things like how many requests, and of what type, are being made of your database(s). If you're making a couple of dozen SELECTs per day, then I'd say that you have a problem, but if you're doing 500,000,000 INSERTs and 1 Bil. SELECTs, then I could see some call for your systems to slow down. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: uploading 200,000 x 5 rows
Hi Carlos, I'm glad to help, but I'd like to clarify several things, first. ...with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) So you have a single file with data for multiple tables? After each sequential parsing (one book) What defines one book? One file, several records from one or more files, or one record from a single file? i upload that info to the database You upload? How is this done, exactly? Do you mean that you run mysql from one machine with the host set for another machine? ...but in 5 tables... So you've split your data into sets that need to get loaded into five destination tables, or you upload five sets of data? (verifying duplication, ids, etc): Does this mean that you are checking for duplicates before loading into MySQL? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock error
Nick Torenvliet wrote: ...I've run ./configure Did you run 'make'? --- Rodney Broom Programmer: Desert.Net Keywords: sql, spam-off - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql install failed on FreeBSD!!
Hi Siva, I know I'm about to get in trouble with the FreeBSD people, but here goes: I'm not a real big fan of FreeBSD's /usr/ports/ thing. My suggestion would be to download the source in a TAR ball, unpack it, run: % configure % make % make install If that doesn't succeed, hollar back with the errors. I expect that this will be much easier, and far less prone to problems. --- Rodney Broom Programmer: Desert.Net SpamKey: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index Question
From: [EMAIL PROTECTED] Can/should I index a timestamp column? Can: Yes. Should: Why not? Should I treat it as a char and limit it to just index on the portion of the data that distinguishes the date Hmm, I wouldn't. Offhand, I'd guess that MySQL handles time/date things as integers. I'm pretty sure that he also handles indexes as some sort of integer based serialization. Meaning that conversion to a string would be an extra step and thereby a loss. I'd say to try it a few different ways (if you have the time on this job) and find out what works best for you. --- Rodney Broom Programmer: Desert.Net Spam filter: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please Advice
From: Shaji Khan [EMAIL PROTECTED] Hi, I am a newbie to MySql and I am planning to use MySql and PHP to develop a shopping cart and other components of a full blown ecommerce site. Is this a good choice for this kind of work or do you guys think some thing like SQL Server and ASP is a better choice. - MySQL is free, MS SQL Server is not (as far as I know). - MySQL is faster than pretty much anything else going with the same features. At least on *NIX type systems, I don't know about MickySoft. - PHP has lots of options, LOTS. Although I've found it to be with concernes. If you write Perl, I would suggest using that. Here's part of why: http:[EMAIL PROTECTED]/msg01457.html http:[EMAIL PROTECTED]/msg01461.html --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: intersection: join query...
From: Anthony E. [EMAIL PROTECTED] This comes up with more records than are in USER_TMP. Is the 'email' column unique in both tables? Heh, sorry about that. Try this: select count(USER_TMP.email) from USER_TMP, USER where USER_TMP.email = USER.email and USER; --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: intersection: join query...
Riza-frasin... --- Rodney Broom Programmer: Desert.Net Your message cannot be posted because it appears to be either spam... database,sql,query,table From: Anthony E. [EMAIL PROTECTED] Unknow column 'USER' in 'where clause'. D'oh! Fast fingers and too many thoughts. select count(USER_TMP.email) from USER_TMP, USER where USER_TMP.email =3D USER.email; --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL question
From: Lance Rochelle [EMAIL PROTECTED] SQL question which I am new to. How would I count the number of times a specific entry is in field. For instance I have a table that has the following two fields select count(*) as number, hostname from your_table group by hostname; --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: max. value from select statement
From: Mike Yuen [EMAIL PROTECTED] The query I have is: select max(mid) from mboard; BUT I tried the following in PHP and it doesn't work: $myquery = SELECT max(mid) FROM mboard; $myresult = mysql_query($myquery); $row = mysql_fetch_object($myresult); print $row-mid is result; I don't do any PHP, but I'm guessing that your return column name is something like 'max(mid)'. Try this: $myquery = SELECT max(mid) as max_mid FROM mboard; $myresult = mysql_query($myquery); $row = mysql_fetch_object($myresult); print $row-max_mid is result; --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Storing Arrays?
From: Philip Mak [EMAIL PROTECTED] Is there a good way that I can store an array in a single row using MySQL? You can simply delimit the data some how. Perhaps with a pipe (|). I was taught that under a clean relational database design, arrays should be stored one element per row. Good teaching. Skipping this misses the point of a relational database. However, this makes retrieving the entire array less practical, especially when I have an array of arrays. That's OK, all you need is a JOIN or two. Keep reading... I've thought about storing an array of words as a VARCHAR() string, and using a FULLTEXT index to search through it, but I'm concerned about efficiency. Good point, quite inefficient. What should I do? Let's say that you've got a table with purchase orders. Each purchase order will have one or more products. We want to see all of the purchase orders and all of the products in each order. Some setup: Table: purchase_order Coumns: id, cust_id Table: po_item Columns: po_id, product_id If you run a join like this: SELECT po.*, prod.* FROM purchase_order po, po_item prod WHERE po.id = prod.po_id You would get results something like this: ++-+---++ | id | cust_id | po_id | product_id | ++-+---++ | 1 | 1 | 1 | 5 | | 1 | 1 | 1 | 6 | | 2 | 1 | 2 | 8 | | 3 | 2 | 3 | 8 | | 3 | 2 | 3 | 9 | | 3 | 2 | 3 | 10 | ++-+---++ Now just itterate over the records returned and load them into whatever you need. If you wanted to do some type of searching, say on the product ID, you could do this: SELECT po.*, prod.* FROM purchase_order po, po_item prod WHERE po.id = prod.po_id AND product_id = 8 Does this help? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problems with perl DBI:DBD mysql
From: Rudy Metzger [EMAIL PROTECTED] ERROR MESSAGE --- Can't locate auto/DBI/do.al in @INC... It sounds like your DBI didn't get built correctly. If you think that it probably was built right, then you can start by trying to find do.al: % find / -name do.al But I don't expect that to turn out the file in an acceptable location. You'll probably have to reinstall DBI. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[OT] Old sources
Hi all, I hate to bring this up in the group, but I haven't found an answer on the site. I've got mysql-3.23.30-gamma-unknown-freebsdelf4.2-i386.tar.gz installed, but I need to recompile a little differently. My problem is that I can't find this version in source. Can anybody tell me where I can find this version in source? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Debugging: Analysis
mysqld is restarting, a lot. So I put it into debug mode, got 200KB+ trace file, and then took mysqld out of debug mode. Now I'm trying to analyze the trace file. The best clue I'm finding are these lines: vio_read: error: Got error 35 during read vio_read: error: Got error 4 during read do_command: error: Couldn't read packet: remain: 4 errno: 4 length: -1 alarmed: -1079059024 do_command: general: Got error reading command from socket socket (8) The first is repeated 150+ times, and the others only once each. Now that I have this information, what do I do with it? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
