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 stopped
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 yo
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;
SELE
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
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
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
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 boardMemb
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 real
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 w
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. But,
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 s
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 mes
I have a table in a database. I'm trying to delete 5 million rows from
it. But whenever I try to do this, while the delete command is
executing access to the other tables in the database are blocked
(which freezes up a website that runs off that database, even though
that website doesn't use the ta
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
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 (
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)
m
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 specifi
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 f
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 hav
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 s
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 capabilit
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
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.
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 s
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 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
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
disad
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 wo
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
("REPLA
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.
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
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 rep
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 s
On Fri, 17 Aug 2001, Grigory Bakunov wrote:
> echo "select * from test;"|mysql test;
Actually, for one liner commands you don't have to use echo| to mysql.
You could use the -e command line switch, e.g.:
mysql -e"SELECT * FROM test" test
Here is the definition for the -e switch:
-e, --execu
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
-
Before posting, please check:
http://www.mysql.com/manua
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 ther
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.
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 Ap
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 h
I'm using the Tangram perl module, which makes MySQL act like an object
oriented database.
My database has a table of stories (STORY). Each STORY is written by an
AUTHOR. So, the database design is like this:
CREATE TABLE story (
id int PRIMARY KEY NOT NULL,
title VARCHAR(80),
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 crea
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
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".
In Apache web server, the command used to start/stop the web server also
has an option called "configtest". One can type "apache configtest" and it
will tell you if the configuration file has a valid syntax. This is to
save you from the situation where you modified the configuration file,
then sto
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 transcrip
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 colu
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
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 thou
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 native
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"
#d
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 yo
[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 TABL
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
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
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
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
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
[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 to
[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?
-
Hi all,
We are currently using MySQL for our database driven website. Currently,
we have about 100,000 users. In January, we will be getting 2 million new
registered users on our website.
We're buying a $50,000 Sun box to run the database server on. We're
deciding whether we should switch to Ora
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, bu
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:
On Sat, 29 Dec 2001, Tom Jones wrote:
> I'm fairly new to MySQL and I was wonder if there was a way to, well let's
> say encrypt a password in a column? I would like to have a users table in my
> database and in it I would like to have a password column, but I don't want
> the password in plain t
is, or should I
put the message 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 chec
is, or should I
put the message 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 chec
rs in
http://qd.anifics.com/read.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 mailin
connections, and
it shouldn't use that many unless your website 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])
---
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 t
k. And it's exactly the same 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*&qu
in the WHERE clause.
mysql> select num 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://w
e 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.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please chec
$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.)
-
Es gibt eine deutsche MySQL-Sendenliste:
There is a German MySQL mailing list:
[EMAIL PROTECTED]
"subscribe mysql-de [EMAIL PROTECTED]"
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www
d
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 re
ll text searching package 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/
rack of what the names of my databases are? The perldoc
for mysqlhotcopy shows that it needs a database name.
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http:
h name for /tmp/mysql.sock. This will create a
symbolic link 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])
e compressed.
However, 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 t
said, I do think the MySQL manual should include a section 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 (
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
Repeat:
>Fix:
>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
>Releas
e:
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 (th
isplay the text blob as one very long
line that is difficult to edit.
Criteria:
- fairly stable
- free
- can directly edit the data by clicking on rows
- can edit text blobs in a multiline text area
Thanks,
-Philip Mak ([
t way of setting the 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)
89 matches
Mail list logo
