Re: is 'start' a keyword?

[Jigal van Hemert]

From: "rik onckelinx" <[EMAIL PROTECTED]>
> where "FULLTEXT" was causing the error. My workaround was renaming (in the
sql
> create table scirpt)
>
>  fulltext -> ful_text
>  option -> optio_

The easiest solution to preventing these errors from popping up from time to
time is to put backticks (`) around names of databases, tables and
columns... INSERT INTO `fulltext` SET (...)  ; SELECT * FROM `insert`.`from`
WHERE (...)
PHPMyAdmin uses this also.

You can tell mysqldump to include these quotes with the option -Q :
--quote-names, -Q
Quote database, table, and column names within ``' characters. If the server
SQL mode includes the ANSI_QUOTES option, names are quoted within `"'
characters. As of MySQL 4.1.1, --quote-names is on by default, but can be
disabled with --skip-quote-names.


Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Copy table?

[Philippe Poelvoorde]

John Mistler wrote:
Is there a way to make an exact copy of a table and give the copy a new
name?
Thanks,
John

From the doc :
As of MySQL 3.23, you can create one table from another by adding a 
SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;
have a look here :
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: match a fulltext search with a "-" dash, can't match if - exist

[Bertrand Gac]

> mysql> select * from fullsearch where match (title,body) against
('018-E');
> Empty set (0.00 sec)
>
>
> it returns an empty set, is it possible to also search with "-" dash?
chars?


I'm not an expert but others will correct me :

In a fulltext search, the search string must be at least 4 characters ?
Otherwise, Mysql ignore it ?

Bertrand.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: flush and flush delay

[roland]

Thanks for this. Very useful.

In particular your idea of using a RAM disk sounds interesting but I am not 
sure how you imagine I use it? I presume that on power down everythign on a 
ramdisk is lost so on every power=up I would have to put my data directory on 
the RAM-disk while the settings in mysql have the path to the data directory 
on the RAM-disk as the datadir?

Would there be any advice on how to set up the tables for maximum performance 
given the usage specifications as described in the earlier mail?

Regards,

Roland

On Sunday 17 October 2004 17:50, Michael Stassen wrote:
> Those 2 variables are explained in the manual
> .
>
>flush
>  This is ON if you have started mysqld with the --flush option. This
>  variable was added in MySQL 3.22.9.
>
>flush_time
>  If this is set to a non-zero value, all tables will be closed every
>  flush_time seconds to free up resources and sync unflushed data to
> disk. We recommend this option only on Windows 9x or Me, or on systems with
> minimal resources available. This variable was added in MySQL 3.22.18.
>
> The --flush startup option is also explained in the manual
> .
>
>--flush
>  Flush all changes to disk after each SQL statement. Normally MySQL
> does a write of all changes to disk only after each SQL statement and lets
> the operating system handle the synching to disk...
>
> Both seem to do the opposite of what you want.
>
> Let me start by saying that I've never done anything like what you are
> trying, so you may get better advice from others.  That said, I'm not
> surprised you're having trouble finding a way to get mysql to do what you
> want, because some of your requirements (data not written to disk, loss of
> data OK) are the exact oppposite of what one usually looks for in a db and
> what mysql was designed for.
>
> Note also that the question of writes is not just a matter of tables.
> Changing data often involves updating indexes, which are also written to
> disk.
>
> I'm wondering if the description of --flush doesn't hold the key, "Normally
> MySQL does a write of all changes to disk only after each SQL statement and
> lets the operating system handle the synching to disk."  This led me to
> think an alternative might be to solve this at the OS/filesystem level,
> instead of in mysql.  That is, find an OS/filesystem combination which
> behaves as you require: writes in memory, flush to disk only when asked.
> This, in turn, led me to wonder if you've considered putting the db
> (mysql's data directory) on a RAM disk, which you back up to flashdisk as
> required. In the event of a power failure, you would restore the RAM disk
> from the flashdisk and continue.  In that case, no modifications to mysql
> would be required.
>
> Michael
>
> roland wrote:
> > Hello,
> >
> > I am using a MySQL database for a somewhat odd application. In this
> > application I will have relatively small tables and relatively few
> > tables.
> >
> > I would like to set the variables so that all the operations on the
> > database takes place in RAM and that it does not write to disk unless
> > explicily given a command to do so. Both on select and update operations
> > which will be what most operations will consist of. the reason for this
> > is firstly speed but almost more importantly I am using flashdisk which
> > has a limited life (baout 200) write actions and my application will
> > performing update operations on a single entry at anything up to 1000
> > times per second. It is absolutely not critical should I lose the values
> > in case of a powerdown. In fact it would suffice to not use the tables on
> > the disk at all except to provide default values.
> >
> > From the documentation I gathered that MySQL refers to writing to disk as
> > a flush operation. Doing a SHOW VARIABLES reveals that there are
> > variables called
> >
> > flush   OFF
> > flush_delay 0
> >
> > Since I have made no modifications yet these are default values. I must
> > misunderstanding something because this would imply that automatic
> > writing to disk is already inhibited and that seems unlikely.
> >
> > My question is thus whether and if yes how i can control (minimize at
> > least) the writing to disk.
> >
> > Also given the smallness of my tables what would be the best way of
> > setting up the database (what table type?) for maximum performance?
> >
> > Thanks for any help.
> >
> > Regards,
> >
> > Roland.

-- 
-"I have not failed. I've just found 10,000 ways that won't work."

-(Thomas Alva Edison 1847-1931)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

match a fulltext search with a "-" dash, can't match if - exist

[Louie Miranda]

this is a working example i found on mysql.com

this is my example of fullsearch
mysql> desc fullsearch;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| id| int(10) unsigned |  | PRI | NULL| auto_increment |
| title | varchar(200) | YES  | MUL | NULL||
| body  | text | YES  | | NULL||
+---+--+--+-+-++
3 rows in set (0.00 sec)


my data:
mysql> select * from fullsearch;
++---+---+
| id | title | body  |
++---+---+
|  1 | MySQL Tutorial| DBMS stands for DataBase ...  |
|  2 | How To Use MySQL Well | After you went through a ...  |
|  3 | Optimizing MySQL  | In this tutorial we will show ... |
|  4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ...   |
|  5 | MySQL vs. YourSQL | In the following database comparison ...  |
|  6 | 018-E | Test for Title Item COde search language, etc |
|  7 | MySQL Security| When configured properly, MySQL ...   |
++---+---+


my search the title with a "-" code

mysql> select * from fullsearch where match (title,body) against ('018-E');
Empty set (0.00 sec)


it returns an empty set, is it possible to also search with "-" dash? chars?


-- 
Louie Miranda
http://www.axishift.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: COUNT Problem

[Jose Miguel Pérez]

Hi Shaun!

I beg you pardon, my last message was incomplete! I will quote the
last lines from my previous post:

---8<- Cut here ---8<---
>
>   - Thanks to the LIMIT clause, we get only the first result, which by
> the way is one of the projects with the most assigned tasks. If you want
to
> further select which one of the projects with the most assigned tasks you
> want, you must order by another column, like date (or filter out the
> projects in the WHERE clause).
> 
>   Given said that, 
>
>   Cheers,
>   Jose Miguel.
---8<- Cut here ---8<---

It was my intention to finish the message as:

"Given said that, if you want more information on how this select
works, do not hesitate to ask. ;-)"

Again, I apologize for the confusion.

Cheers,
Jose Miguel.





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Chris]

Frederic Wenzel wrote:
On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh
<[EMAIL PROTECTED]> wrote:
 

A Project will have 1 or more tasks assigned to it. Using the following
query, how can I modify it so that I can find out the largest number of
tasks assigned to a group of projects.
SELECT P.*, T.*
FROM Projects P, Tasks T
WHERE P.Project_ID = T.Project_ID
AND Project_ID > 2;
   

A subselect may help:
SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS
 rows FROM Tasks GROUP BY Project_ID) AS maxi;
Don't know ATM if it can be done more easily, but a query like this
should probably work.
Fred
 

It can be done without a sub-query:
SELECT
 COUNT(T.Project_ID) as Yourvar
FROM Projects P
LEFT JOIN Tasks T
 USING(Project_ID)
WHERE P.Project_ID > 2
GROUP BY P.Project_ID
ORDER BY COUNT(T.Project_ID) DESC
LIMIT 1
;
That *should* work, barring any typos or ommisions I may have made. I 
used LEFT JOIN because of personal preference, it can be done other way(s).

Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: COUNT Problem

[Jose Miguel Pérez]

Hi Shaun!

> A Project will have 1 or more tasks assigned to it. Using the 
> following 
> query, how can I modify it so that I can find out the largest 
> number of 
> tasks assigned to a group of projects.
> 
> SELECT P.*, T.*
> FROM Projects P, Tasks T
> WHERE P.Project_ID = T.Project_ID
> AND Project_ID > 2;
> 
> So if Project A has 3 tasks and Project B has 4 tasks I need 
> the query to 
> return 4.

This question is very easy to answer, but I think it's very difficult to
explain to you. For the first thing, you haven't added a GROUP BY clause,
which is the first thing you should do to begin with. It's strange you
didn't put an GROUP BY function, and if you don't know how it works, please
read a book on SQL first.

Given the tables you have, this query will do the work, without subqueries:

SELECT P.Project_ID, COUNT(T.Project_ID) as HowMany
FROM Projects P
LEFT JOIN Tasks T USING(Project_ID)
GROUP BY P.Project_ID
ORDER BY HowMany DESC
LIMIT 0,1

Column 1 (ProjecT_ID) is the Project ID with the most assigned tasks, and
Column 2 (HowMany) lists how many taks are actually asigned.

It is worth pointing out two notes about the above query:

- The query will return 0 as "HowMany" and the Project_ID for the
project which has no task assigned if this is the case. I mean, thanks to
the LEFT JOIN, we take into account projects with no taks attached. (This is
not possible with a straigh join like yours).
- Thanks to the LIMIT clause, we get only the first result, which by
the way is one of the projects with the most assigned tasks. If you want to
further select which one of the projects with the most assigned tasks you
want, you must order by another column, like date (or filter out the
projects in the WHERE clause).

Given said that, 

Cheers,
Jose Miguel.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Frederic Wenzel]

On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh
<[EMAIL PROTECTED]> wrote:
> A Project will have 1 or more tasks assigned to it. Using the following
> query, how can I modify it so that I can find out the largest number of
> tasks assigned to a group of projects.
> 
> SELECT P.*, T.*
> FROM Projects P, Tasks T
> WHERE P.Project_ID = T.Project_ID
> AND Project_ID > 2;

A subselect may help:

SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS
  rows FROM Tasks GROUP BY Project_ID) AS maxi;

Don't know ATM if it can be done more easily, but a query like this
should probably work.

Fred

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Merlin, the Mage]

Hi,

 Tihs looks to me as too few information.

 What is in your tables (the information)?

 What does the query return (a empty set)? Maybe 'cause in your where clause 
where you have Project_ID >2 you should have Project_ID=2? Or you have 
several projects with ID>2 and you want tasks for all of them?

 Or the query don't even run as Project_ID exists in both table and you should 
specify a table alias (T.Project_ID or P.Project_ID)?

themage

On Sunday 17 October 2004 20:36, shaun thornburgh wrote:
> Hi,
>
> I have the following two tables in my database:
>
> mysql> DESCRIBE Projects;
> +--+--+--+-+-++
>
> | Field| Type | Null | Key | Default | Extra  |
>
> +--+--+--+-+-++
>
> | Project_ID   | int(11)  |  | PRI | NULL| auto_increment |
> | Client_ID| int(11)  | YES  | | NULL||
> | Project_Name | varchar(100) | YES  | | NULL||
>
> +--+--+--+-+-++
> 3 rows in set (0.00 sec)
>
> mysql> DESCRIBE Tasks;
> ++-+--+-+-++
>
> | Field  | Type| Null | Key | Default | Extra  |
>
> ++-+--+-+-++
>
> | Task_ID| int(11) |  | PRI | NULL| auto_increment |
> | Task_Name  | varchar(40) |  | | ||
> | Project_ID | int(11) |  | | 0   ||
>
> ++-+--+-+-++
> 3 rows in set (0.00 sec)
>
> mysql>
>
> A Project will have 1 or more tasks assigned to it. Using the following
> query, how can I modify it so that I can find out the largest number of
> tasks assigned to a group of projects.
>
> SELECT P.*, T.*
> FROM Projects P, Tasks T
> WHERE P.Project_ID = T.Project_ID
> AND Project_ID > 2;
>
> So if Project A has 3 tasks and Project B has 4 tasks I need the query to
> return 4.
>
> Hope this makes sense!
>
> Thanks for your help

-- 
Merlin, the Mage
www.code.online.pt
www.cultodavida.online.pt

Que o(s) vosso(s) Deus(es) vos abençoe(m)
E a vida vos ame e proteja

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

COUNT Problem

[shaun thornburgh]

Hi,
I have the following two tables in my database:
mysql> DESCRIBE Projects;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra  |
+--+--+--+-+-++
| Project_ID   | int(11)  |  | PRI | NULL| auto_increment |
| Client_ID| int(11)  | YES  | | NULL||
| Project_Name | varchar(100) | YES  | | NULL||
+--+--+--+-+-++
3 rows in set (0.00 sec)
mysql> DESCRIBE Tasks;
++-+--+-+-++
| Field  | Type| Null | Key | Default | Extra  |
++-+--+-+-++
| Task_ID| int(11) |  | PRI | NULL| auto_increment |
| Task_Name  | varchar(40) |  | | ||
| Project_ID | int(11) |  | | 0   ||
++-+--+-+-++
3 rows in set (0.00 sec)
mysql>
A Project will have 1 or more tasks assigned to it. Using the following 
query, how can I modify it so that I can find out the largest number of 
tasks assigned to a group of projects.

SELECT P.*, T.*
FROM Projects P, Tasks T
WHERE P.Project_ID = T.Project_ID
AND Project_ID > 2;
So if Project A has 3 tasks and Project B has 4 tasks I need the query to 
return 4.

Hope this makes sense!
Thanks for your help

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Show databases shows all even if no rights;

[Paul Fierro]

On 10/17/2004 7:12 AM, Michael J. Pawlowsky <[EMAIL PROTECTED]> wrote:

> All priviliges for that user in mysql.user are set to N.
> I know this is hard to read but here are the outputs from user and db.

[snip]

> Paul DuBois wrote:
> 
>> At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote:
>> 
>>> I just noticed that a restricted user to only one database can still
>>> run "show databases;"
>>> and see all the names of the databases in MySQL.
>>> 
>>> You would think that it would only return the databases that that user
>>> is allowed to connect to.
>>> 
>>> Is there a way I can show only those databases that he has rights to
>>> without giving him rights to the mysql  database to use the db table?
>> 
>> 
>> Make sure that the user doesn't have any global privileges that apply
>> to databases.  If the user has such a privilege, SHOW DATABASES will
>> display all databases.  (To check this, look at the privilege columns
>> in the mysql.user table for the user's account record.)

Your grant tables suggest that you are not running MySQL 4.0. As of 4.0.2,
the behavior you describe disappears:

http://dev.mysql.com/doc/mysql/en/Show_database_info.html

Paul


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: flush and flush delay

[Michael Stassen]

Those 2 variables are explained in the manual 
.

  flush
This is ON if you have started mysqld with the --flush option. This
variable was added in MySQL 3.22.9.
  flush_time
If this is set to a non-zero value, all tables will be closed every
flush_time seconds to free up resources and sync unflushed data to disk.
We recommend this option only on Windows 9x or Me, or on systems with
minimal resources available. This variable was added in MySQL 3.22.18.
The --flush startup option is also explained in the manual 
.

  --flush
Flush all changes to disk after each SQL statement. Normally MySQL does
a write of all changes to disk only after each SQL statement and lets
the operating system handle the synching to disk...
Both seem to do the opposite of what you want.
Let me start by saying that I've never done anything like what you are 
trying, so you may get better advice from others.  That said, I'm not 
surprised you're having trouble finding a way to get mysql to do what you 
want, because some of your requirements (data not written to disk, loss of 
data OK) are the exact oppposite of what one usually looks for in a db and 
what mysql was designed for.

Note also that the question of writes is not just a matter of tables. 
Changing data often involves updating indexes, which are also written to disk.

I'm wondering if the description of --flush doesn't hold the key, "Normally 
MySQL does a write of all changes to disk only after each SQL statement and 
lets the operating system handle the synching to disk."  This led me to 
think an alternative might be to solve this at the OS/filesystem level, 
instead of in mysql.  That is, find an OS/filesystem combination which 
behaves as you require: writes in memory, flush to disk only when asked. 
This, in turn, led me to wonder if you've considered putting the db (mysql's 
data directory) on a RAM disk, which you back up to flashdisk as required. 
In the event of a power failure, you would restore the RAM disk from the 
flashdisk and continue.  In that case, no modifications to mysql would be 
required.

Michael
roland wrote:
Hello,
I am using a MySQL database for a somewhat odd application. In this 
application I will have relatively small tables and relatively few tables.

I would like to set the variables so that all the operations on the database 
takes place in RAM and that it does not write to disk unless explicily given 
a command to do so. Both on select and update operations which will be what 
most operations will consist of. the reason for this is firstly speed but 
almost more importantly I am using flashdisk which has a limited life (baout 
200) write actions and my application will performing update operations 
on a single entry at anything up to 1000 times per second. It is absolutely 
not critical should I lose the values in case of a powerdown. In fact it 
would suffice to not use the tables on the disk at all except to provide 
default values.

From the documentation I gathered that MySQL refers to writing to disk as a 
flush operation. Doing a SHOW VARIABLES reveals that there are variables 
called

flush   OFF
flush_delay 0
Since I have made no modifications yet these are default values. I must 
misunderstanding something because this would imply that automatic writing to 
disk is already inhibited and that seems unlikely. 

My question is thus whether and if yes how i can control (minimize at least) 
the writing to disk.

Also given the smallness of my tables what would be the best way of setting up 
the database (what table type?) for maximum performance?

Thanks for any help.
Regards,
Roland.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query help

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
John Mistler <[EMAIL PROTECTED]> writes:

> I need help coming up with the following query:
> My table:

> +-+--+
> | rowID   | dateOfPurchase   |
> +-+--+
> |   1 | '2004-1-17 08:00:00' |
> +-+--+
> |   4 | '2004-1-17 08:03:20' |
> +-+--+
> |   1 | '2004-1-17 08:05:45' |
> +-+--+
> |   2 | '2004-1-17 08:07:11' |
> +-+--+
> |   1 | '2004-1-17 08:09:03' |
> +-+--+
> |   4 | '2004-1-17 08:11:56' |
> +-+--+
> |   1 | '2004-1-17 08:13:24' |
> +-+--+

> I would like to return all rowIDs that do not exist more than 3 times in the
> 08:00:00 - 09:00:00 hour of date '2004-1-17'.

SELECT rowID
FROM tmp
WHERE dateOfPurchase BETWEEN '2004-01-17 08:00:00' AND '2004-01-17 09:00:00'
GROUP BY rowID
HAVING count (*) <= 3


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can MySQL do this?

[Rhino]

Hi Skip,

See my remarks interspersed in your note.

By the way, I'm copying the mailing list so that others can benefit from the
discussion, either now or somewhere down the road via the archives.

Rhino

- Original Message - 
From: "Skip Taylor" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Sent: Sunday, October 17, 2004 2:49 AM
Subject: Re: Can MySQL do this?


> Hello Rhino,
>
> Thank you for taking the time to answer my question.
>
My pleasure!

> You are correct in your assumption that "index=key" and
> "link=join/lookup".
>
> The last time I worked with databases, it was in the late 1980's and
> early 1990's.  I wrote the entire system or used a rapid design tool
> called SIMPLE (System IMPLementation by Example).  The terms
> I used were from what I used at that time.
>
I'm not that surprised that older systems used different terms like "index"
and "join/lookup". In fact, when I first started learning the theory behind
relational databases - also in the late 1980s - I found that the relational
theorists were using rather different vocabulary like 'tuples' and
'relations'. Somehow, those transmuted into different but more widely used
terms but I'm not sure how or why. I'm guessing that the marketing people
for the first relational databases wanted to use terms that were a little
more commonplace and a little less daunting to beginners. And, of course,
everyone was a beginner to relational databases in those days.

> Your terms make more sense to me and that may indicate my
> difficulty with learning MySQL.  I need to let go of the archaic and
> embrace the new!
>
I don't really think you'll have a great deal of difficulty learning MySQL.
The concepts underlying relational databases really aren't that hard. There
will be a bit of new terminology but I think you'll find a lot of it maps
very precisely to things you already know from your earlier work; you just
have to learn the new term for it. At first you'll probably do mental
translations - think 'index' when someone says 'key' - but eventually,
you'll probably just think 'key'.

> After reading your message twice, I think I could actually remove
> the US_States table and write the state list only into the HTML
> code.  Perhaps it would simplify matters.
>
If you know all your state codes by heart and all of your users do as well,
you can probably get away with making the US_States table have only the
state_code column. Personally, I tend to get muddled up with the 'A' states;
for instance, when I was trying to remember the state code for Alaska, I
first wrote 'AL', then remembered that 'AL' is Alabama. Mind you, I'm
Canadian so I have an excuse ;-)

However, I wouldn't get rid of the US_States table altogether (or handle it
with application code) if I were you. Tables like this, often called lookup
tables, can be very handy as a means of enforcing the integrity of your
data. Remember, your US_Cities table will contain a state code for each city
in the table. If you define the state code column of the US_Cities table as
a foreign key of the US_States table, this simple step will ensure that the
only value which can possibly be entered in the state code column of
US_Cities is one of the values in the US_States table.

That may not sound like a big deal but it is. Remember, there are many ways
to access your database, including programs, command lines, and scripts.
While a program could manually check the input from an insert statement and
make sure that the state code matches an internal array of state codes, a
script or command line don't have the same luxury; anyone doing an insert
from the script or command line could easily mess up and use an invalid
state code. Then, you'll have bad data in your US_Cities table. Just imagine
the confusion when your users see New York, NV instead of New York, NY!

Foreign keys are a very valuable aspect of relational databases and you
should use them whenever you can. They completely eliminate the need for any
application code to do the checking!! However, I should mention that you
will need to use the InnoDB "engine" in order to be able to define them and
enforce them automatically. Don't worry, that's no big deal. As I recall,
InnoDB is installed along with the other "engines" so it should be available
on your system already. All you should have to do is add "Type=InnoDB" to
your Create Table statement, like this:

create table US_States
(state_code char(2) not null,
 state_name char(20) not null,
 primary key(state_code))
Type=InnoDB;

> I'm curious.  The language I've seen in MySQL queries reminds me
> of an operating system of long ago named PICK. The operating
> system was a database.   I wonder if it got a start from that?
>
I have heard of PICK and knew that it was an operating system but I've never
used it or learned any of its terminology.

Relational databases originally came out of the IBM research labs in the
1970s as a result of work published by Dr. E.F. Codd, a British
mathematician

Re: Replication: update to data missing

[Mikael Fridh]

Ralf E. Stranzenbach wrote:
i have a mysterious problem regarding a replication set-up of two mysql
databases (4.0.18).
If i change data in the mysql database, this data is not replicated.
If i create a new database on the master, this database does not show on the
slave.
If i change the existing database(s) on the master, this change does not
show up on the slave.
I bet i need some help
$5 says you have the same server-id on both hosts.
--
 ___
|K  | Ongame E-Solutions AB - www.ongame.com
| /\| Mikael Fridh / Technical Operations
|_\/| tel: +46 18 606 538 / fax: +46 18 694 411
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Show databases shows all even if no rights;

[Michael J. Pawlowsky]

All priviliges for that user in mysql.user are set to N.
I know this is hard to read but here are the outputs from user and db.

+---++--+-+-+---
--+-+-+---+-+---+---
---+---++-+++
| Host  | User   | Password | Select_priv | Insert_priv | 
Update_pri
v | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv 
| Proces
s_priv | File_priv | Grant_priv | References_priv | Index_priv | 
Alter_priv |
+---++--+-+-+---
--+-+-+---+-+---+---
---+---++-+++
| localhost | fhgweb | x | N   | N   | N
  | N   | N   | N | N   | N | N
   | N | N  | N   | N  | N 
 |
+---++--+-+-+---
--+-+-+---+-+---+---
---+---++-+++

and from the db table...
mysql> select * from db where User like 'fhgweb';
+---+---++-+-+-+
-+-+---++-++
+
| Host  | Db| User   | Select_priv | Insert_priv | Update_priv | 
Delete_
priv | Create_priv | Drop_priv | Grant_priv | References_priv | 
Index_priv | Alt
er_priv |
+---+---++-+-+-+
-+-+---++-++
+
| localhost | fhgdb | fhgweb | Y   | Y   | Y   | Y
 | N   | N | N  | N   | N 
| N
|
+---+---++-+-+-+
-+-+---++-++
+
1 row in set (0.01 sec)


Paul DuBois wrote:
At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote:
I just noticed that a restricted user to only one database can still 
run "show databases;"
and see all the names of the databases in MySQL.

You would think that it would only return the databases that that user 
is allowed to connect to.

Is there a way I can show only those databases that he has rights to 
without giving him rights to the mysql  database to use the db table?

Make sure that the user doesn't have any global privileges that apply
to databases.  If the user has such a privilege, SHOW DATABASES will
display all databases.  (To check this, look at the privilege columns
in the mysql.user table for the user's account record.)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

flush and flush delay

[roland]

Hello,

I am using a MySQL database for a somewhat odd application. In this 
application I will have relatively small tables and relatively few tables.

I would like to set the variables so that all the operations on the database 
takes place in RAM and that it does not write to disk unless explicily given 
a command to do so. Both on select and update operations which will be what 
most operations will consist of. the reason for this is firstly speed but 
almost more importantly I am using flashdisk which has a limited life (baout 
200) write actions and my application will performing update operations 
on a single entry at anything up to 1000 times per second. It is absolutely 
not critical should I lose the values in case of a powerdown. In fact it 
would suffice to not use the tables on the disk at all except to provide 
default values.

From the documentation I gathered that MySQL refers to writing to disk as a 
flush operation. Doing a SHOW VARIABLES reveals that there are variables 
called

flush   OFF
flush_delay 0

Since I have made no modifications yet these are default values. I must 
misunderstandign something because this would imply that automatic writing to 
disk is already inhibited and that seems unlikely. 

My question is thus whether and if yes how i can control (minimize at least) 
the writing to disk.

Also given the smallness of my tables what would be the best way of setting up 
the database (what table type?) for maximum performance?

Thanks for any help.

Regards,

Roland.

-- 
-"I have not failed. I've just found 10,000 ways that won't work."

-(Thomas Alva Edison 1847-1931)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]