On Thu, Feb 26, 2004 at 09:50:39AM -0700, Sasha Pachev wrote:
If I understood the problem correctly, the answer to it is actually
undefined. If you order by lastPostTime, the records with the same
lastPostTime value can be returned in any order.
I guess to accomplish your goal you could add
On Thu, Feb 26, 2004 at 10:49:08AM -0700, Sasha Pachev wrote:
SELECT COUNT(*)
FROM topics
WHERE lastPostTime $postTime
OR (lastPostTime = $postTime AND id $id);
Can you just add id $id to the where clause?
No, that won't work because id is only used to disambiguate the order
of two rows
Say I have this query:
SELECT *
FROM topics
ORDER BY lastPostTime DESC;
How would I modify it to answer the question How many rows would be
returned before the row that has topics.id = $x?
I was thinking of something like this:
$xPostTime = SELECT lastPostTime FROM topics WHERE id = $x;
On Sat, Jan 24, 2004 at 01:56:53PM -0600, Dan Nelson wrote:
You didn't say what OS you're using, but the below top output looks
like Linux's procps top, which doesn't tell you your paging rate. Run
vmstat 1 and watch the si and so columns. Just because swap is being
used doesn't mean you're
My machine appears to be swapping excessively, degrading performance.
Note the high load average, combined with the mostly idle CPU and a
lot of swap space being used.
3:11am up 47 days, 2:13, 12 users, load average: 6.06, 4.79, 3.19
482 processes: 480 sleeping, 1 running, 1 zombie, 0
I have the following columns in a table:
posts.id
posts.parentId REFERENCES posts.id
This represents a tree that has a single root node, and each node can
have 0 or more children. posts.id is the id number of a node, and
posts.parentId is the id number of that node's parent (is NULL for the
root
Does anyone know why I'm getting this error? Does it have to do with
character locale problems (notice the search.glyphs LIKE part)?
This code used to work fine before, but lately it's been getting
MySQL server has gone away errors every time it executes, so I'm
confused.
Breakpoint 2, main
On Sun, Jan 12, 2003 at 08:12:35PM -0700, Rodney Broom wrote:
I'm trying to delete 5 million rows...
Do you have to do this often?
Sort of. I have a process that logs to MySQL, and I want to delete old
log entries. I haven't been deleting old entries at all recently
because it will freeze up
sql, table
I'm storing a SHA1 checksum as varchar(20) binary in my application.
After running a test, it seems MySQL will strip trailing spaces from a
varchar column, even if it is binary! That means if the last character
of my SHA1 checksum happens to be a space, MySQL will corrupt it.
What
On Sun, Dec 29, 2002 at 11:09:47PM -0600, Paul DuBois wrote:
At 5:28 -0500 12/29/02, Philip Mak wrote:
sql, table
I'm storing a SHA1 checksum as varchar(20) binary in my application.
Other people have addressed other aspects of your message, but I'm
curious why you're using a VARCHAR(20
I have a daemon which uses MySQL (INSERT INTO) to log. The problem is,
whenever I try to do anything with the log table that takes a long
time, it makes the daemon freeze until the table is free again.
I'm thinking of making the daemon use INSERT DELAYED instead, so that
it won't get blocked.
sql, query
Right now, every day I run the equivalent of a mysqlhotcopy on my
database (read-lock the tables, copy the files, unlock the tables).
This freezes up everything (websites, etc.) that uses the database for
10 minutes. That is too long.
Is there a way to get a snapshot of the database
sql,query
Which way is faster?
Way 1:
SELECT *
FROM users
LEFT JOIN boardAdmins ON boardAdmins.userId = users.id
LEFT JOIN boardMembers ON boardMembers.userId = users.id
WHERE id = 5;
Way 2:
SELECT * FROM users WHERE id = 5;
SELECT * FROM boardAdmins WHERE userId = 5;
SELECT * FROM
On Fri, Nov 22, 2002 at 06:56:53PM -0500, Michael T. Babcock wrote:
On Fri, Nov 22, 2002 at 06:20:14PM -0500, Philip Mak wrote:
sql,query
Why not just:
SELECT * FROM users, boardAdmins, boardMembers WHERE id = 5;
You're not really 'joining', since boardAdmins and boardMembers
don't
What are all these rei-bin.* files in this MySQL data directory? Are
they needed? They're taking up a lot of disk space. They seem to be
some sort of log.
horderei.aaanime.net.pid rei-bin.008 rei-bin.016
linkwars rei-bin.001 rei-bin.009 rei-bin.017
I can't get CREATE TEMPORARY TABLE to work due to a privilege problem.
Does anyone know what I'm doing wrong?
mysql update user set create_tmp_table_priv='Y';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql flush privileges;
Query OK, 0 rows affected (0.00
On Thu, May 30, 2002 at 05:53:03PM +0300, Dmitry Alyabyev wrote:
Is it possible to set up one mysql server *only* for listening on
socket and transfer client requests to another one server through
network connection ? Of course I mean return results too :-)
It sounds like what you want can be
On Thu, May 30, 2002 at 08:15:41AM -0700, Ryan Barber wrote:
2) Why would these sample words have no match?
Cannot match against when, most, like, goes Why are these
words not in the index? The file contains 1 million title records
and all of these words are in the file many times.
Those
On Wed, May 29, 2002 at 02:10:07PM -0700, ddd wrote:
I have a question, is possible to search on mysql 4.0 in fulltext
index the exact phrase ?
http://www.mysql.com/doc/F/u/Fulltext_Search.html suggests that it is
possible. Here is the relevant excerpt:
The boolean full-text search capability
I have a bunch of processes running on the same machine that should be
able to send unicast, multicast and broadcast messages to each other.
Messages should be received in the same order that they were sent.
I'm trying to figure out how to implement that. These processes are
already sharing the
I made an Excel spreadsheet containing grades of students in my class,
like this:
(name) (email) (assignment 1 grade)(assignment 2 grade)
For when a student did not submit an assignment, I just left the field
blank.
I'm trying to import the spreadsheet into a MySQL table of the
following
On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote:
it set the integer columns to 0 if they were blank in the text file.
How can I make it set those to NULL instead?
You'll have to preprocess the file to convert empty fields to \N.
Damn, that's what I thought. I guess I'll have to
One thing's been bothering me for a while: When I create a user and
database in MySQL, the user always ends up with an extra entry with
host='%' and password=''. How is this happening? This is how I create
a new database and user:
mysql create database xxx;
Query OK, 1 row affected (0.01 sec)
On Tue, Feb 19, 2002 at 04:39:10PM +0100, Peter Banik wrote:
you should explicitly specify host/password in the GRANT statement, like
this:
GRANT ALL ON xxx.* TO user@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
This way the user will only granted access from the specified
As far as I can tell, mysqlhotcopy does not provide a way of specifying
the password anywhere other than the command line (e.g. it doesn't seem
to read .my.cnf).
Isn't this a security risk? I noticed that mysqlhotcopy *does* change its
ps entry after it starts in order to mask the arguments, but
On Sun, 30 Dec 2001, Bogdan Stancescu wrote:
You can usually try providing an empty -p parameter and be asked for the
password afterwards.
Doesn't seem to work:
[mysql@lina mysql]$ mysqlhotcopy -u root -p test .
DBI-connect(;host=localhost;mysql_read_default_group=mysqlhotcopy)
failed:
[table]
Is there a significant CPU consumption difference between this:
SELECT * FROM posts ORDER BY lastPostTime, id
compared to this:
SELECT * FROM posts ORDER BY lastPostTime
My gut feeling tells me that they should almost take the same amount of
time to execute, because 'id' only needs
[table]
Does TEXT have any significant disadvantages compared to VARCHAR? We need
to store a bit of text, but in some cases it can exceed 255 characters, so
we have to use TEXT.
I'm guessing that the only difference is that the length counter needs 2
bytes instead of 1 byte of space, right?
I have a FreeBSD 4.4-STABLE machine with two CPUs running mysql-3.23.44.
I'm wondering about how to make MySQL use both CPUs. Am I correct in
saying:
1. The binary distribution of MySQL for FreeBSD from www.mysql.com does
not make use of both CPUs.
2. The only way to make MySQL use both CPUs on
On Fri, 16 Nov 2001, Jeremy Zawodny wrote:
Where can I find instructions for compiling MySQL on LinuxThreads?
Try out the FreeBSD MySQL port. I've been told that it's trivial.
What's the URL for that?
-
Before posting,
I am running Apache with mod_perl (Apache::DBI is off) with connections to
MySQL.
I'm finding that the number of connections used by perlhttpd seems to be
too high. I did this:
[root@sg3 root]# ps -A | grep mysqld | wc
88 3532743
[root@sg3 root]# /etc/httpd/bin/perlhttpdctl restart
Does anyone know what happened here? Notice the strangely formatted
output from the second command. I did a repair table but it doesn't
seem to detect any table corruption.
mysql repair table pairings;
+---++--+--+
| Table | Op | Msg_type |
Let's say I performed the following query using Perl DBI:
$row = $dbh-selectrow_hashref(~);
SELECT fanfics.handle, authors.handle
FROM fanfics, authors
WHERE fanfics.aid = authors.aid
~
I won't be able to access both fanfics.handle and authors.handle this
way, because they're called handle. I
On Tue, 4 Sep 2001, ahmed wrote:
i hope that some one send me a programme illustrating how to access
to mysql database with c api ..
I run a site that has a free e-mail forwarding service. The following
program is what runs .qmail-default.
#define HOST localhost
#define USER username
#define
On Tue, 4 Sep 2001 [EMAIL PROTECTED] wrote:
I am trying to use limit and order by in the same query for paging of
records. But faceing the problem that query does not return any row.
After removing of limit every thing is working fine.
Is it really a bug. Any solution.
Going by what you
[table sql stupid spam filter]
On Tue, 4 Sep 2001, [EMAIL PROTECTED] wrote:
order by articleId desc limit 1 , 20
That will cause no rows to be returned if there are less than 20 results.
Use this instead:
ORDER BY articleId DESC LIMIT 0, 20
It starts counting at 0 instead of 1.
I see... if adding ORDER BY articleId DESC LIMIT 0, 20 causes a query to
start returning 0 rows, then that sounds like something is wrong.
Try using the REPAIR TABLE command on all your tables from inside MySQL,
e.g.:
REPAIR TABLE article;
REPAIR TABLE asubject;
REPAIR TABLE atype;
REPAIR TABLE
On Tue, 4 Sep 2001, Curtis Spencer wrote:
I have a text file with around 25 fields but I only want 5 of them.
They are not the first 5 fields that I want. Is there a way to skip
fields using LOAD DATA INFILE so I don't have to build a 25 field table
and then cut it down?
Read the manual
Is there a good way that I can store an array in a single row using MySQL?
I was taught that under a clean relational database design, arrays should
be stored one element per row. However, this makes retrieving the entire
array less practical, especially when I have an array of arrays.
I've
On Mon, 3 Sep 2001, Chris Cameron wrote:
I'm sure this is disscused somewhere, but I'm not sure how I'd look for
it.
Is it possible to supply a thesaurus-like file for mysql so when you
go %oil% it finds petrol gas and/or lubricant?
As far as I know, there is no way to do that natively in
On Wed, 22 Aug 2001, Marty McCoy wrote:
I'm performing a FULLTEXT search against varchar columns of one of our
tables. One of our columns contains embedded serial numbers such as
02-123456 and 04-234567. The problem that when I do a FULLTEXT search
against 02-123456, it brings back results
On Mon, 20 Aug 2001, Michiel Leegwater wrote:
insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking,
CRvan,CRtot,PR,Categorie from table2;
This doesn't work, it says Column count doesn't match value count at row 1
I understand the problem. But I can't use my ID column in
On Sun, 19 Aug 2001, Sinisa Milivojevic wrote:
If this is happening on Windows, we truly can not do anything about
it.
You could help there by forcing all table names to be lower-case by
starting mysql service with a corresponding option.
No, it's happening on Linux. Here's a transcript of
I have a database called animelyrics. I want to make it so that the
files inside this database are readable to the UNIX user animel, so that
I can do backups easier.
I have done chgrp animel * on the directory that contains the files of
the database animelyrics, but new tables that I create are
On Sun, 19 Aug 2001, Lars Bruun Hansen wrote:
mysql show tables from test;
++
| Tables_in_test |
++
| MyTable|
++
mysql alter table MyTable add (y char(1));
mysql show tables from test;
++
| Tables_in_test |
On Sat, 18 Aug 2001, Philippe Paravicini wrote:
could you not add the 'animel' user to the 'mysql' group?
I have done chgrp animel * on the directory that contains the files of
the database animelyrics, but new tables that I create are group mysql
instead of group animel. Is there a way
On Fri, 17 Aug 2001, Christopher Teli wrote:
How can I check if an index exists on a table???
Is there some where I can query the admin tables?
SHOW INDEX FROM tablename
-
Before posting, please check:
On Sat, 18 Aug 2001, Fai wrote:
Does any body know how to prevent mysql server looking for user specific
optional file ( .my.cnf)? So, user cannot put the optional file (.my.cnf) in
his home directory to affect the mysql server behaviour.
Even if user can put the optional file in his home
On Fri, 17 Aug 2001, hanan khader wrote:
Hi everybody
I want to make backing up for my databases from the server into my pc, I
login to the server with the administrator username and password, how could
this backing up be done? and is there any risk that i should be aware of ?
is there
On Fri, 17 Aug 2001, Mysql Mailing List wrote:
If he sends in select * from table, we would like the mysql actaully
execute select * from table where hisID=1000;
Basically, we want mysql database to screen the query sent from a certain
user, and apply some conditions on that query.
Is it
It may be that MySQL is simply spawning too many processes for your
machine to handle.
I might be wrong, but here's something to try:
1. Set MySQL max_connections to 45.
2. Set httpd MaxClients to 50.
That will limit the number of processes MySQL is allowed to start, and
also makes sure that
On Fri, 17 Aug 2001, Cal Evans wrote:
Select * from product where title = 'This title has a \' in it.';
This query does not work. Can anyone tell me how to build a query that will
return that title?
Are you sure you're not overlooking something? I just tried the following
commands and had
Doesn't doing it that way preclude using $dbh-quote? That could mess up
if the name had a single quote in it.
One idea I had was to do something like this:
my ($f, $d); # form data, database data
for (qw(friendly parent intentional address port timeout priority)) {
$f{$_} = $q-param($_);
On Thu, 16 Aug 2001, mysql wrote:
@HOSTNAME@: command not found
Does anyone know how to solve that ? I didn't find anything on the net
or on the mysql.com homepage.
You're getting that from support-files/mysql.server, yes?
I've gotten that message too and was puzzled by it. I just replaced
On Thu, 16 Aug 2001, Theo Richel wrote:
Is it possible to store html in Mysql-fields? Where can I find more
info in this?
Did you need MySQL to treat the HTML in some special way? I'm confused as
to why you're asking this question.
I would think that you can just use the TEXT data type to
I've had an ongoing problem where the SQL statements in my perl programs
come out rather messily.
Does anyone have specific and general suggestions on how to write embedded
SQL code cleanly in perl programs? Here's an example of an ugly piece of
SQL statement I wrote recently:
$dbh-do
On Thu, 16 Aug 2001, Rodney Moses wrote:
This doesn't work, it has really wacky results:
#balance of all accounts
select a.id, (sum(o.amount) - sum(p.amount)) from
accounts as a left join orders as o on a.id = o.account_id left join
payments as p on a.id = p.account_id
group by a.id;
On Thu, 16 Aug 2001, Rodney Moses wrote:
Thanks for the reply Philip!
Unfortunately that doesn't work. I should have clarified that there never
are null values in the 'amount' fields.
Even if you don't have NULL values in the 'amount' fields, doing the LEFT
JOIN will cause NULL values to
I have an application that requires the storage of various flags (by
flag, I mean a variable that is either 1 or 0).
What's a good way of doing this? I've thought about two ways:
Method 1: Make a TINYINT column for each flag
I can use TINYINT NOT NULL datatype for storing each flag. The
I have a user on my system with a normal shell account, and a normal MySQL
account that owns a database.
How can this unpriviledged user back up his database using mysqlhotcopy?
I've run into two problems:
1. He has to login as the MySQL root user in order to be able to execute
FLUSH TABLES.
Your query will work in every case, EXCEPT when either Current or
Temporary has 0 rows, because then there's nothing to join. I'm not sure
if doing a join is a clean way of doing this though.
If you know that Current will never be empty (but temporary might be),
then this query would work:
I have a table called stats with:
user varchar(40) not null primary key,
count mediumint not null.
I want to do something like this:
UPDATE stats SET count=count+1 WHERE user='$user';
But if there is no row in the stats table having user='$user', that
statement will not create the row.
The
: submitter ID
Originator:Philip Mak
Organization:
-Philip Mak ([EMAIL PROTECTED])
MySQL support: none
Synopsis: manual errata
Severity: non-critical
Priority: low
Category: mysql
Class: doc-bug
Release: mysql-3.23.36 (Official MySQL RPM)
Environment
Some months ago I had the same question and I looked at many different
Windows MySQL GUI clients.
The best one I know of is Mascon. They have a free version for download.
http://www.scibit.com/Products/Software/Utils/Mascon.asp
-Philip Mak ([EMAIL PROTECTED
shows that it needs a database name.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread
between /var/lib/mysql/mysql.sock and /tmp/mysql.sock so
that both of them are treated as the same file. Then everything should
work.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com
, performance will suffer.
-Philip Mak ([EMAIL PROTECTED])
-
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
that
explains my.cnf better, if it doesn't have one already.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list
Is this correct? And how would I do step #1?
Thanks,
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request
that I can
use for searching MySQL text columns?
Thanks,
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive
$B$3$l$O1Q8l$N(BML$B$G$9!#F|K\8l$N(BML$B$r;H$C$F2$5$$!#!J$9$_$^$;$s$,!"F|K\8l$N(BMySQL$B$N(BML
$B$O$I$3$K$"$k$N$,J,$j$^$;$s!#!K(B
(This is an English mailing list. Please use a Japanese mailing list.
Sorry, but I don't know where a Japanese MySQL mailing list is.)
-Philip M
ssage body into individual separate files? It runs fine right
now, but I'm worried that it won't scale up under significant load.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (
ssage body into individual separate files? It runs fine right
now, but I'm worried that it won't scale up under significant load.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (
.asp?num=32)
So if you are dumping/posting a lot of messages into the table the
select performance is going to suffer.
Inserts shouldn't be too common; just once every time the mailing list
receives a new message (no more than 100
site is very heavily loaded. (I
had a website that got 3 million page views a month and it fit in 40
simultaneous connections, but it was all static files so queries could be
served quickly.)
-Philip Mak ([EMAIL PROTECTED])
-
Be
sarily together. (Unless I
use LIKE "%Sailor Moon%", but that's inefficient...)
Is the above statement correct?
What techniques do people here use to overcome that limitation? (Using a
program separate from MySQL for indexing text,
for mysql/bin.
So PLEASE!!! if someone can help me
Thanks in advance
Find the correct path for the mysql directory. I think it might be in
/usr/bin/*mysql* and /var/lib/mysql. Try typing locate mysql, or find /usr
-name "*mysql*"
Then chown those files.
-Philip Mak ([EMAIL
from ffml where num=floor(rand()*max(num))+1;
ERROR : Invalid use of group function
Anyone else have an idea?
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual
es never
change once they've been created, and the ones that do change (e.g. when a
message gets replied to, its page needs to link to the new reply) can be
found and updated relatively simply. So I could just write out a lot of
this stuff to static HTML pages for the ultimate performance boost.
temporary directory that
MySQL uses? (It seems to be working, but I just want to make sure)
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
ht
sage:
Can't connect to MySQL server on 'localhost' (10061)
Does anyone know what I did wrong?
Thanks,
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
ht
81 matches
Mail list logo
