MYSQL :: Configuration And Usage Of The Relation Feature

MySQL 5.0, Bug Or Feature?
Just recently changed my version of MySQL to 5.0 (I think it is, lol) from my 4.x. Anyway, I noticed that the auto incrementing fields don't auto increment if I leave the insertion as empty. This is causing a lot of grief on my installation scripts, which rely on the autoincrementing fields to obtain the ids. My question is, is this a bug of MySQL in the Windows environment, or is a feature to force data validation?

Enable SSL Feature
I'd like to launch a mysql server with SSL protocol enabled. I'm working on
windows XP OS. I didn't find clear information about how to do that. In the
user manual I can find in the 5.6.7.2 section that I have to launch an
"@command{configure}" command with some options. The problem is : I can't
find a "configure" command. The only thing I'm able to find is a shell
script called "conf.sh" but I'm working on windows. Do I have to install
cygwin or that kind of stuff ? I've read somewhere else that I had to
compile mysql to be able to use it in SSL mode. I'm a bit lost with those
informations.

Mysql Security Feature
I wanted to add in mySql security features.

The very basic kind. How should i add in?

Optional Relation
i have one table which has 4 optional relations many-to-one with 4 other tables. With DB designer I have defined 4 optional relations, but when i do the forward engineering the script contains 4 foreign keys, which are a too strong tie from implementation's point of view.

Setting Up A Relation
Is there any reason why two tables created with a relation in MySQL
wouldn't adhere to what you would think is a property of the relation?

Case in point: I have a 1:n 'relation' where I can insert values into
the dependent table while making up an entry for the foreign key from
the independent table (whether it exists or not). I shouldn't be able
to do that, but MySQL doesn't complain.

Foreign Key Constraints - Many To Many Relation
I have 3 tables and there is an issue that I can't resolve, so looking for advice

The table(s) are Products, Catalogue and Catagories. A Product has one or more Catagory(ies), taken from the Catagories table, and mapped with the Catalogue table, ie
- Products
SCH1000 ... ...
- Catagories
193 Books ... ...
- Catalogue
SCH1000 193
There are two constraints on the Catalogue table; One for Products and One for Catagories, so if you delete a Product, the relation is deleted from Catalogue, and likewise if you delete a Catagory, the relation is delete from Catalogue.

However, for example, if you delete a Catagory, then the Product(s) assigned to that Catagory is not deleted and still remains, so you have a broken relation. So my question is this... What changes do I need to make, so that if a Catagory is deleted, the related rows are removed also? I need the row(s) deleted both from the Catalogue table, and the Products table.

But MySql (rightly so) will not allow me to put foreign key constraints on either the Catagories, nor the Products tables. The many to many relationship is a requirement and is not something I can get around.

Entity Relation Ship
I am trying to find entities and their attributes, the entities are listed in the problem description and I have to find them, but dont know where to start and how to find them. DO they come in noun forms like classes in programming.

Delete Cascade In Many To Many Relation
I have table A and B that has many to many relation (Table C is the many to many)

I want that when i delete records from A, the records from C and B will also delete.

I set on delete cascade in C table FK (for A and B)

but when i delete records from A it delete only A,C.

Is there any way to delete A,B,C when i delete records from A???

I know that the problem is that this is many to many , so there is an option that some records from B will be delete despite the fact that they have also relation with records that has not deleted, but i dont care to delete those records.

Select Records Without Relation.....
I've 2 tables and the relation between them is the id. I want only select the records from table A which are not present in table B!

Db Design - The Mythical 1:1 Relation
I'd like to split an entities's attributes because they won't get
equally frequently requested. So I can save memory and disk access time.
The integrity of the splitted record should be retained.

The key points would be:
1) devide the data collumns on n tables
2) enforce the existence of all parts of a record by table design

The best workable way I can think with only two tables is 1:(0,1)
which is not exactly the deal. It provides that table_b can hold 0 or 1
part relating to a part in table_a, by a foreign key in table_b and an
UNIQUE.

I tried another way with
1) creating table_a
2) creating table_b with FK to table_a
3) alter table_a with a FK to table_b

Now that was a 1:1 relation.
Obviously I couldn't insert anymore. A insert in table_a would violate
the existence integrity constraint to table_b.

SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS = 0;
insert table_a ...
insert table_b ...
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

would let me insert but the inserted data wouldn't be checked at all.
That way I could forget using this relation constraint alltogether.

Relation Diagram Tool
Are there any free entity relationship diagraming tools for MySql 5.0 ?

Size Of DB In Relation To System Specs
i'm running a server with two dual core processors at 3 Ghz and 8 GB RAM. The size of the MySQL-DB on this server is 1.5 GB. Most of these 1.5 GB (70%) are not processed during normal usage.

At the moment everything is running fine, but i'm expecting the DB to grow during the next month, so I ask myself at which size the system speed will start to noticeable slow down.

How To Avoid Having Thousands Of Records In A Many-to-many Relation
I'm building a web administration system for my company. We keep all our contacts from other organizations in this system (stored in a MySQL database): name, addres, telephone etc.

One feature of the system is that you can select a number of contacts and collectively send them an email. This works fine.

But: some of my co-workers need to know which of the contacts has received a specific email from the system.
So I was considering a setup like the following:

Table one: contacts (name, addres etc.)
Tabel two: emails (subject, text, creation day, day of delivery etc.)
Table three: many-to-many table holding one row for each email that has been send to a specific contact (autonum ID, ID of email and ID of contact).

My problem is that I can predict that this table will have LOTS of records in no time, as my co-workers are sending out many emails to a lot of contacts.

So: are there any better ways?
I thought about storing all contact IDs that has received a specific email in a text field in that email's record in the database - but then I'm not sure of the performance when I have to find out if someone specific has gotten a specific email etc.

Is There GUI Tool To Make Relation Between Tables
i have mysql database and i want to make relation between 2 tables
is there Giu tool for this purpose?

Query That Finds Last Row In Relation To Current Row (read For More)
It's been almost a year since I last did SQL and I can't for the life of me figure out how to do this again.

Pretty much I have a table like so:

id | ip | time

I want to get a result set that contains each unique ip, how many times it appears in the table, and the last time it appears.

/tmp Usage
are there any best practices for /tmp on a std *nix install for MySQL, and if so, what are they ? Also i can't seem to find any more information on what specifically you'd require a LARGE /tmp for in MySQL.

C# Usage
Does anyone know if C# will work with mysql??? I know it does C++, but I don't know if the connectors or whatever are out there for C#

MAX() Query Usage
I have a database of marks and I need to find out the maximum mark taken by each student in any subject. For example If am a student and I have got 60 in Language, 70 in Maths, 80 in Science and 90 in Social Studies, I would need the result "90" and the relevant Social Science subject to be displayed so that I know am good at Social Science of all the subjects.

Likewise, my friend got 65 in commerce, 70 in accountancy, 80 in economics and 90 in Applied Maths. As you may see here, the subjects are all different in both these cases. I only need to know this following result

That I am good at Social Studies and
my friend is good in Applied Maths.

The table name is marks. The fields are subject_id, mark_obtained, student_id.

When I try this query.

Select subject_id, max(mark_obtained) from marks group by student_id

In this case, I get the correct maximum mark, but the subject id displayed is incorrect. Can anyone tell me what am doing wrong here?

Getting CPU Usage Of A Query
There are some various queries running on my site that appear to use a whole lot of CPU resources under certain conditions which I haven't identified.

Is there any way I can see which queries are using up how much of the CPU instead of just being able to see the general process id for MySQL? I use cPanel and WHM to manage the server.

Index Usage
I have two tables, one is Student, which has studentid, personid, grade, fees as its columns. The other one is Person, which has personid, firstname, lastname, address as its columns. When given a studentid, I need to get the student' info, such as
lastname from the db tables.

I can do it with two queries as the following:

1. mysql> select personid FROM Student where studentid=1;
2. mysql> select lastname FROM Person where personid="the id I got above";

Or I can do it with one query:

3. mysql> select p.lastname from Student s, Person p where s.studentid=1 AND s.personid=p.personid

Both works. However, I can't tell which one is actually faster, since MySQL shows me "1 row in set (0.00 sec)" for all these three queries. Both studentid and personid are primary keys, so they are automatically indexed.

Will both indexes be used in query number 3? I saw people said that

MySQL could use in a query only one index...Does it mean only one index will be used in query number 3? I am wondering which one will give better performance.

Max(count(*)) Usage?
I have a table with a structure along the lines of

customerId
itemId
copyId

which form a primary key.

I want to know the max occurence of a particular item, based on copyId.

Ie, if 5 customers want itemId 6, 3 want copyId 1, 2 want copyId 2, should return 3 for itemId 6..

I thought something like

select itemId, max(count(*))
from items
group by itemId

might work, but alack, not the case.

Suggestions in how to do this with one query?

Counting Usage Per Day
I want to record how many times an application is used in a day. Is it possible to create a table with a date field that automatically creates a new record each day with the present date as the primary key.

Memory Usage
I've been analysing the memory usage of our server and each SQL thread seems to be taking up 10mb - since our site is basically a search site that means we're using 10mb per user which means with our 2gb RAM we can take around 120-200 simultaneous visitors.

Other than buying more RAM, what can I do to increase the number of simultaneous connections (ie - how can I reduce memory usage?) By my Calculations we need to be able to accept around 500 simultaneous searchers to meet our target.

We will also be increasing the amount of data we have - currently it is around 1.5 million records but that will be gradually increasing to around 10 million - which I'm sure will also increase the memory load on the server. Right now we have 1.4Gb Data and 380Kb overhead.

CURRENT_DATE Usage
I am trying to access some data from a database based on the date.

here is the SQL:

SELECT internallocation.LocationName, breed.BreedName, animal.DateBroughtIn from breed, animal, internallocation where (breed.ID = animal.BreedID AND internallocation.ID = animal.ShelterLocation AND animal.DateBroughtIn = 'CURRENT_DATE()')

This command returns no results... however, if I put in the date like so:

SELECT internallocation.LocationName, breed.BreedName, animal.DateBroughtIn from breed, animal, internallocation where (breed.ID = animal.BreedID AND internallocation.ID = animal.ShelterLocation AND animal.DateBroughtIn = '2005-06-24')

I get the results I am looking for. How can I use CURRENT_DATE to create a report based on the date, so the SQL does not have to be edited every time to change the day?

Wildcard Usage
Here is an example of the code:

$query = "SELECT * FROM table WHERE field = '*' ";

I want to return all the fields from the table where the field is any.

The program is a little more complicated than this. Basically using a dropdown menu, the user can search all, or a particular field. If they select "search all", I need the ability to insert that into the query statement.

Memory Usage
We have Mysql 5.0 running on RH linux. We have noticed that the swap memory increases but never gets released unless we reboot the server every 2-3 weeks. How would I know whats using this swap memory?
Second question which I think maybe related to the first is that mysql is using a lot of memory when I do a top. Is there a way to limit the memory usage of mysql? I have a large table in mysql about 7 GB and I have 8 GB physical memory and swam is set to use 2GB.

What Will Be The Best My.cnf Configuration?
I have a MySQL 5.0.27-standard-log slave server running on a server with

Processor: Dual Xeon E5372 Quad Core (Clovertown)
RAM 12GB
Hd1: 120GB
OS: Linux - CentOS 4

I have 15GB+ data with MyISAM and InnoDB tables. InnoDB tables are not in much use, we perform user searches on MyISAM tables which have FULLTEXT.

Question: What will be the best my.cnf configuration? Below is the current my.cnf ....

Configuration
I'm learning Java and Databases..I need help.

I don't know about databases, but I have to use one... I chose a MySql for use...
How can I configure a MySql Control Center (GUI) or Where I find a tutorial that show me this configuration?

DSN Configuration
I have Groupmail running on a remote machine with a fixed IP and want to set up a database connection from my Server.

I have set up a user but am getting the following error:
Access denied for user 'odbcuser'@host<IP> (using password: yes)
where odbcuser is the user and IP is my remote machine's IP

Configuration
I've downloaded MySQL community server, the one without the installer, and I've unzipped it into c:.

I need to configure it to work locally, only in this computer. No internet connection.
Can someone give a clue.I know nothing about server configuration, neither databases. It's to run a weblog server application in this computer.

MySQL 4.0.xx Admin And Usage
I've recently taken over my non-profit's Web site design, administration and maintenance with relatively little MySQL experience (although I have learned a great deal very quickly). My question is, does anyone know where I can get a manual or something like the current manual "Building your Own Website Using PHP and MySQL .."(ordered from sitepoint.com site). This manual is basing the MySQL on the most current version of 5. I'm looking for something similar that addresses the 4.0.xx(27 specifically) of MySQL

LEFT JOIN Usage HELP!
Schema:

Table1
-t1field1 --> primary key
-t1field2
-t1field3

Table2
-t2id
-t1field1 --> foreign key of t1field1
-t2field2
-t2field3
-t2field4

Database Values:
Table1
row1: 1,aaa,123
row2: 2,bbb,123
row3: 3,ccc,123
row4: 4,ddd,123
row5: 5,eee,123

Table2
row1: 1,1,aaa,123
row2: 2,1,jjj,123
row3: 3,1,aaa,123
row4: 4,2,hhh,123
row5: 5,3,hhh,123
row6: 6,2,aaa,123
row7: 7,1,jjj,123

Query:
SELECT
t1.t1field1,
t1.t1field2,
t1.t1field3,
t2.t2field2,
t2.t2field3,
t2.t2field4
FROM
Table1 AS t1
LEFT JOIN
Table2 AS t2
USING(t1field1)
WHERE
t1.t1field2 = 'aaa'

Question:
Where do I insert a "WHERE clause" if i wanted to get t2.field2 with a value of 'jjj' only? or my query is totally wrong.

High CPU Usage (99.9%) On RHEL4
i need your help on this issue. I am running a web server in my orginazation internally with mysql-4.1.12 and RHEL4. Now, its been only one day and using 'top' command its showing mysqld process using cpu 99.9% constantly. This is used by say around 4000 users.

My my.cnf file :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

output of mysqladmin version
..........
...
Threads: 53 Questions: 324297 Slow queries: 588 Opens: 39976 Flush tables: 1 Open tables: 64 Queries per second avg: 67.788

output of mysqladmin extended-status
Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 742 |
| Aborted_connects | 35 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 29401860 |
| Bytes_sent | 1049908921 |
| Com_admin_commands | 2352 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 40895 |
| Com_change_master ...................................................................

Calculating Table Usage
how posiible is it to calculate a table usage in byte.

Analyze Index Usage
I am running a SELECT query that is taking approx. 200+ seconds to complete. I believe I have the tables indexed properly, but apprarently not. How can I determine what indexes a query is using and evaluate the execution plan (as you would in MSSQL)?

CPU Usage Issues On A Windows Box?
As the site receives over 50 simultaneous hits, mysql uses up just about all cpu usage... I have fine tuned everything - can any one offer any suggestions or share similar experiences??

Can I Limit Ram Usage In MySQL?
I just had my server shut down because my hosting company said I was using 350 to 450 megs of ram. This seems nuts to me as I'm only running a shopping cart with about 1000 items with only about 50 visitors per day.

Since they installed mysql on the server, I asked them why they hadn't configured it to work with the parameters of their system.

Their response: "Unfortunately, there is no way to limit the RAM usage for mySQL. I apologize for the inconvenience this may cause however there is no way to do this."

Do they know what they are talking about???

Orders Table - 100% CPU Usage
I'm starting to dive into something that looks like a PHP/MySQL issue that is bogging me down (PHP5, MySQL 4.1, Apache 2 on a Windows XP ws).

(At the end of this message, the PHP code)

We operate a virtual store and there's this administrative tool, loaded by the code included, which lists orders by category (new, shipped, cancelled etc.); on the top of the page, it first shows each category's totals (3 new, 18 shipped etc.). Obviously, these data comes from an "orders" table which, as of today, has 8.103 records.

I did increase already cache and buffer sizes on PHP and that helped tremendously to load the tool *if data doesn't change* in between. When it does, the following happens:

1. CPU usage goes all the way up to 100%, and
2. it takes approx. 45-60 seconds like that until the page ends loading.

I'm no PHP programmer, but it seems to me this has to be related to the code below.

Code:

Lowering Memory Usage?
I was wondering if there's any way I can lower MySQL's = memory

useage? It often uses more than 50000kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at = C:/ and windows calls it cardnumber or something)...

Mysqld Running 100 Pct Cpu Usage
I am testing mysql 4.1 on a Windows Server 2003 with a website running on Internet Information Services. I installed mysqld as a service.

As times goes by, mysqld begins to reach 100 pct cpu usage and doesn't goes below that, Windows usage is slow, folders opens slow, navigating locally turns to be slow, and sometimes it makes the server to freeze or restart.

When this begins to happen, I executed the command "Show processlist" and I got the following result. If somebody can point me where is my mistake or where is the problem or any solutions for this, I'm still in a learning process for mysql. Code:

Key_index_size Setting And Usage
I am using MySQL 4.1.12, compiled from source, on a Linux 2.6.11.10 kernel form the kernel archive. My Hardware is a Dual Xeon 2.8Ghz with 8GB of RAM and U320 SCSI disks. The database I am serving is about 6GB total, with the bulk of the data in 2 big tables, about 1.5 GB each.

The index files for these two tables are 1.4 GB and 415MB. In my /etc/my.cnf file I have the key_index_size set to 3GB, but the most that will get used is 1.49GB, either after doing an "Optimize tables" or an "INSERT INDEX INTO CACHE" statement.

My question is: How does MySQL decide how much of the systems memory to use for the key_index_size and any other of the processes allocatable address space. Also, under linux is the allocatable address space to an individual process 3GB or 4GB?

Allowable MySQL CPU Usage
I am running this query (with subqueries) in MySQL under Windows:
SELECT sum(quantity)/(SELECT sum(quantity) from orders), partID from orders group by partID

I noticed that it runs very slowly. It appears that MySQL is only using about 2% of CPU capacity, for some reason. The query takes almost 20 minutes to run on my table (whereas the same query takes only 1 minute in MS Access!).

Is there a way to allow MySQL to use more of the CPU, so these queries can run more quickly?

Usage Of OPTIMIZE Command
I had a table of more than 800'000 rows where I deleted approx 200'000 rows. The documentation says that I shouldn't normally have to use the OPTIMIZE TABLE command, but performance on this table has not improved. Is it a good idea to use it?

Proper MySQL Usage
I'm looking to use MySQL on my next project, but I don't really know how to set it up so it fits my needs.

I am making a Music Script Basically. All I need to store is Artist Name, Artist Videos, and Artist Songs. (just names of the stuff)

Artist Videos & Artist Songs are unknown, size wise. Different artists are going to have different numbers of songs and videos.

Usage Of DELETE In A Query!
I know just enough about MySQL to insert, update and if necessary alter tables. But I helped someone that was wanting to delete a rwo of data selected with a form which presented all the row and had a link to take them to another page where it was confirmed by clicking another sumbit button that would remove the row selected from the prior page, then they sent a Private Message stating that the code worked, but then mentioned something about having a foreign key and not being able to delete that field. It is my limited understanding but if I had a table and did

DELETE FROM tablename WHERE orderid='$orderid'

then this would remove this row whatever the row with an orderis that matched. I built my own table which merely consisted of an autoincrementing PRIMARY KEY of orderid and when I tested the code the row was in fact removed, so what if there were a foreign key? I don't think it would matter, but after the PM I wonder.

Lowering Memory Usage
there's any way I can lower MySQL's =
memory
useage? It often uses more than 50000kb which I consider to much.. I'm
guessing I should play with my.cnf or whatever it's called (located at =
C:/
and windows calls it cardnumber or something)

Measuring Database Usage
I'm trying to write a backup script, but want to be 'resource smart' about it. I'd like to backup my database every hour or 2, but want to look at the current draw on the DB prior to running the backup. If the draw is within pre specified limits, do the backup, otherwise, sleep for a min then try again.
This may be more of a job for a shell script, but I figured I'd give the mysql forum a try.