At 10:47 PM 6/16/2011, Johan De Meersman wrote:
- Original Message -
From: Bennett Haselton benn...@peacefire.org
Do you happen to know the answer to my other problem -- if I have
TEXT and BLOB columns but all my other columns are fixed-length, can
I still get the benefit
At 11:45 AM 6/14/2011, Johan De Meersman wrote:
- Original Message -
From: Bennett Haselton benn...@peacefire.org
modifications. (For example, the question I asked earlier about
whether you can declare extra space at the end of each row that is
reserved for future columns
I'm looking for some tips tricks documentation that explains how
different data types in rows are stored at the file level (in MyISAM
tables, at least), and how to optimize tables for faster queries,
updates, table definition modification, etc. based on this knowledge.
For example, I've
At 05:46 AM 6/14/2011, Carlos Eduardo Caldi wrote:
Hello Bennett
On the Mysql developer site have a grate documentation, try the
links above.
http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html
http://dev.mysql.com/doc/refman/5.0/en/data-size.html
Thanks, this gets
At 02:53 AM 10/2/2009, Joerg Bruehe wrote:
Hi Bennett, all!
Bennett Haselton wrote:
At 08:24 AM 9/25/2009, Dan Nelson wrote:
In the last episode (Sep 25), Bennett Haselton said:
I have a script that runs several times in the evening, and on each
run it
adds several thousand entries
I have a script that runs several times in the evening, and on each
run it adds several thousand entries to a table.
On the first run, it adds the entries rather slowly. But then on all
subsequent runs (usually about a minute or two later), the many
inserts go a lot faster. This is true
At 08:24 AM 9/25/2009, Dan Nelson wrote:
In the last episode (Sep 25), Bennett Haselton said:
I have a script that runs several times in the evening, and on
each run it
adds several thousand entries to a table.
On the first run, it adds the entries rather slowly. But then on all
AM 9/15/2009, Bennett Haselton wrote:
When I install and start the MySQL server on a new machine, it outputs:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin
When I install and start the MySQL server on a new machine, it outputs:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h
At 03:49 PM 8/25/2003 +0300, Egor Egorov wrote:
Bennett Haselton [EMAIL PROTECTED] wrote:
I found a way to do this before, but I didn't write down how I did it,
so I
don't remember it now. And I've searched http://www.mysql.com/doc/ in
vain.
What's the command to show all current locks
At 10:33 AM 8/21/2003 -0700, Bennett Haselton wrote:
[already posted to mailing.database.mysql newsgroup but not to list; sorry
for cross-post]
I found a way to do this before, but I didn't write down how I did it, so
I don't remember it now. And I've searched http://www.mysql.com/doc
[already posted to mailing.database.mysql newsgroup but not to list; sorry
for cross-post]
I found a way to do this before, but I didn't write down how I did it, so I
don't remember it now. And I've searched http://www.mysql.com/doc/ in
vain.
What's the command to show all current locks on a
can edit
the mysql user table directly and change the N to Y in the
appropriate fields. Then you should probably restart the mysqld daemon.
On Sun, 2003-08-17 at 01:16, Bennett Haselton wrote:
I'm trying to port my MySQL tables for a database called tracerlock
from
one server to another
I'm trying to port my MySQL tables for a database called tracerlock from
one server to another. On the old server, in the /var/lib/mysql/tracerlock
directory, there was a .MYD, .MYI and .frm file for every table in
the database. So after creating a database called tracerlock on the new
At 05:54 PM 8/16/2003 -0400, Rajesh Kumar wrote:
Bennett Haselton wrote:
I'm trying to port my MySQL tables for a database called tracerlock
from one server to another. On the old server, in the
/var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and
.frm file for every table
that, and then did flush privileges, then logged out and logged
back in to mysql as bhaselto, but I still got the error table 'test' is
read only when trying to insert rows into it.
Any idea on how to do something differently with GRANT to make it work?
-Bennett
At 04:16 PM 8/16/2003 -0700, Bennett
At 10:20 PM 8/12/2003 +0300, Victoria Reznichenko wrote:
As far as I can tell from reading
http://www.mysql.com/doc/en/GRANT.html
I followed the GRANT syntax
correctly for creating a new user; why can't I connect to MySQL as that
user?
Remove from table user entry for ''@'localhost' and
While logged in to my Linux server as root, I went in to MySQL and (with no
databases selected, so that the GRANT statement would apply globally),
ran the command:
mysql grant all privileges on * to bhaselto identified by 'password';
where password is, of course, the password I wanted to use
it fixed soon -- any help is appreciated.
-Bennett
At 10:27 PM 7/26/2003 -0700, Bennett Haselton wrote:
Jackson,
Thanks for your help. Two follow-ups:
a) Is there a command to show all the table locks, or locks on a
particular table? I searched http://www.mysql.com/doc/en/index.html
Is there any way to do a long update/delete on a table such that you can
view the progress as the command runs, so that long before it's over, you
have some idea of what the total running time will be?
The way I did it was to write a perl script that takes the total range of
values for the
I have a MySQL query running inside a CGI script on my site that, at random
intervals, seems to take 10-20 seconds to complete instead of less than 1
second. I spent so much time trying to track this down that I wrote a
script which runs once a minute on the site, which (a) captures the output
then other processes start to queue up. Maybe this query is
getting stuck behind other queries or a single slow query.
Just a thought.
-Jackson
On Saturday 26 July 2003 11:00, Bennett Haselton wrote:
I have a MySQL query running inside a CGI script on my site that, at
random
intervals, seems to take
I have a database in which one MYD file has apparently been corrupted:
mysql use tracerlock;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Didn't find any fields in table 'news_feed_item'
Database changed
I have a database called TracerLock. Right now it is in some kind of state
where queries involving the user table will hang:
SELECT count(*) FROM user;
but queries involving any other table run fine:
mysql select count(*) from news_article;
+--+
| count(*) |
+--+
| 1335037 |
Isn't the following:
SELECT * FROM t1, t2 where tl.ID=t2.foreignKey;
logically equivalent to:
SELECT * FROM t1 LEFT JOIN t2 ON t1.ID=t2.foreignKey WHERE t2.ID IS NOT
NULL;
Because I had a query in the first format that took about two minutes to
run on MySQL, and then I changed it into the
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
says that for some sub-queries you can create temporary tables, but I
couldn't find any examples in the tutorial that show the use of a temporary
table. Are there any?
Or, if you're feeling generous, could you just tell me how to do this
I'm trying to use the IN operator as described in my databases textbook
from college. This query:
select count(*) from user where user.ID in (select ID from user);
is supposed to evaluate to count the number of rows in the 'user' table
where the ID field is in the set of all ID field values
At 09:36 PM 12/1/2001 -0700, Ashley M. Kirchner wrote:
Bennett Haselton wrote:
Is it possible to set a column to have no possible default value, so if
you
try to create a record that doesn't specify a value for that column,
you'll
get an error?
Set it to NOT NULL
NOT NULL
Is it possible to set a column to have no possible default value, so if you
try to create a record that doesn't specify a value for that column, you'll
get an error?
For example, if I have a table where the date field should always be
set. I took out the default NULL value, but that just
.)
-Bennett
At 04:39 PM 11/22/2001 +1300, Quentin Bennett wrote:
Hi,
From previous message (I don't run linux), I believe that linux ps lists
all
threads, and mysqld does create a thread for each connection.
Regards
Quentin
-Original Message-
From: Bennett Haselton [mailto:[EMAIL
Row sizes in MySQL tables can't exceed 65535 bytes (BLOB and, presumably,
TEXT fields are not counted towards this total). That means you can't
defined a table where the *possible* size of a row might be larger than
that -- e.g. a column of type VARCHAR(255) gets counted as taking up 256
Just wondering if anyone who knew the answer to this might have missed it
before -- sorry to keep nagging but I really need to find out how, or
whether, you can determine which fields in a table are foreign keys
referencing another table.
Is it not possible to determine, after a table is
In a database that enforced referential integrity, if you say that a field
in one table is a foreign key referencing another table, then any value in
the foreign key field in the first table must reference an existing row in
the second table. (Right?)
But suppose you have a database storing,
After converting the data in my project from text files to MySQL tables,
things are certainly working more smoothly than they were before, but since
I use objects (in Perl, which barely supports objects, but that's another
story) I have to write a lot of annoyingly repetitive code to create
At 10:37 AM 11/15/2001 +, [EMAIL PROTECTED] wrote:
Dear Bennett,
On Thu, 15 Nov 2001, Bennett Haselton wrote:
After converting the data in my project from text files to MySQL
tables,
things are certainly working more smoothly than they were before, but
since
I use objects (in Perl
My databases textbook from college says that specifying an attribute as an
index means that the data will be stored in such a way that lookups on
that attribute are faster, and specifying an attribute as a key means
that its values have to be unique.
But MySQL seems to use KEY and INDEX to
Is there a way to call describe on the temporary table that's generated
when you do a join or other type of query -- e.g. I can get a temporary
table by doing the following (useless) query:
mysql select * from user,pet where user.id=pet.id;
I created the persons and shirts tables as described in the MySQL
tutorial:
http://www.mysql.com/doc/e/x/example-Foreign_keys.html
such that the owner field in shirts is a foreign key referencing the
persons table. However, describe shirts does not indicate that the
field is a foreign key:
http://www.mysql.com/doc/n/o/node_357.html
explains the different string data types and the storage requirements:
Column Type Storage required
[...]
VARCHAR(M) L+1 bytes, where L = M and 1 = M = 255
[...]
TINYBLOB, TINYTEXT L+1 bytes, where L 2^8
[...]
These two
MySQL documentation says that CHAR and VARCHAR types are case-insensitive:
http://www.mysql.com/doc/C/H/CHAR.html
But I have a table with a column of type VARCHAR(255), and if I do a query
like
select * from user where LOCATE('Bennett', emailaddress) 0;
then the results are computed
Say I have two running programs and both of them periodically want to
increment a value in a database. How can I do this so that the increments
will be performed correctly even if the two programs try to do them at the
same time?
If I have code like this:
$x =
At 04:26 PM 11/2/2001 -0600, Paul DuBois wrote:
At 2:12 PM -0800 11/2/01, Bennett Haselton wrote:
How do you change the order of columns in MySQL tables? I assume the
tables have a concept of column order, since the DESCRIBE command always
lists the columsn in the order in which they were
I've observed that if I run a simple SELECT * command on a two-row table,
the query takes anywhere from half a second to four seconds:
mysql select * from pet;
+--+-+-+--++---++
| name | owner | species | sex | birth | death |
How do you change the order of columns in MySQL tables? I assume the
tables have a concept of column order, since the DESCRIBE command always
lists the columsn in the order in which they were created.
I figured that the place to look would be the syntax page for the ALTER
TABLE statement:
I'm doing experiments with the table pet where the field birth is of
type date. How come
UPDATE pet SET birth = a2b WHERE name = snort;
gives:
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
but
UPDATE pet SET birth = ab WHERE name = snort;
gives:
Query OK, 0
At 12:18 PM 10/30/2001 -0600, Dan Nelson wrote:
In the last episode (Oct 30), Bennett Haselton said:
I created one table with the command:
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species
VARCHAR(20), sex CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT
NULL
At 02:32 PM 10/30/2001 -0700, Steve Meyers wrote:
What would be ideal would be to use auto-incremented numeric fields as
primary key fields, and then have a special field in each table
designated
as the user-friendly field. That way, when you want to view the
contents
of a table, the
Is there any pre-written code that allows users, authenticated over the
Web, to view MySQL tables in their database as HTML tables? Desirable
features for something like that would include:
- tables are displayed in row-and-column format, with a few blank rows at
the bottom where you can
I created one table with the command:
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex
CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL);
and another one with the command:
CREATE TABLE pet2 (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex
that got appended to the end of
$sql_query. Removing that newline made the query work.
-Bennett
At 03:25 PM 8/13/2001 +0200, Ian Barwick wrote:
On Sunday 12 August 2001 01:01, Bennett Haselton wrote:
I've written a CGI script that runs a database query on a MySQL
database (I
know
I'm still testing out MySQL and I'm trying to find the my.cnf file referred
to at
http://www.mysql.com/doc/O/p/Option_files.html
The page says to look in:
/etc/my.cnf
DATADIR/my.cnf
defaults-extra-file (The file specified with --defaults-extra-file=# )
~/.my.cnf
but none of these files
At 06:04 PM 8/8/2001 -0500, Paul DuBois wrote:
At 2:50 PM -0700 8/8/01, Bennett Haselton wrote:
The first page of the MySQL tutorial at:
http://www.mysql.com/doc/C/o/Connecting-disconnecting.html
says that when you connect to the MySQL server, it authenticates you
based on a username
DELETE FROM tablename;
where tablename is the name of the table.
-Bennett
At 05:23 PM 8/9/2001 -0400, Dmitry Kashlev wrote:
How can I erase all data except for the fields in mysql table? I just want
to fill all data again in the same table.
Dmitry
http://www.mysql.com/doc/U/s/User_names.html
says:
MySQL encrypts passwords using a different algorithm than the one used
during the Unix login process. See the descriptions of the PASSWORD() and
ENCRYPT() functions in section 6.4.12 Miscellaneous Functions. Note
that even if the
I gave the same password to user root and user bhaselto, using the
statements:
UPDATE user SET Password=PASSWORD('') WHERE user='root';
UPDATE user SET Password=PASSWORD('') WHERE user='bhaselto';
(using the real password instead of '', of course). The 'user'
table
Is there any way -- using either MySQL directly, or (ideally) using the DBI
perl module -- to get a list of fields from a (possibly empty) table? (If
the table is nonempty, you can just use a perl function to get the first
record, and then apply keys() to the returned reference to get the
The first page of the MySQL tutorial at:
http://www.mysql.com/doc/C/o/Connecting-disconnecting.html
says that when you connect to the MySQL server, it authenticates you based
on a username and password that you enter, unless the server is also
configured to allow anonymous access, in which
57 matches
Mail list logo