Action after new/update record
Hi, I'm quite new with mySQL, i have bought the book Mastering MySQL4 but i don'f find a solution for my problem there. There are no triggers in version 4 if 'im correct, but i want to export a table (small one) every time there is a new record or a record modified. Whats the way to do this, the manupulation of the data goes with php5 on a website. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error in your SQL syntax
I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories.products_id, categories.parent_id, products_description.products_name, products.products_price, products.products_tax_class_id, products.products_image, specials.specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id) ON specials.products_id = products_to_categories.products_id) INNER JOIN products ON specials.products_id = products.products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id = products_description.language_id WHERE (((categories.parent_id)=285) AND ((languages.languages_id)=1)) i run this query in my computer and work, but in the internet server don´t. If anyone can solve this problem answer me. Daniel Sousa
Access denied for user - I cant work this out
Hi All, I am consistently getting; Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) I am connecting through php with the same script I use all the time, I have checked it and dbl checked it for problems. I can connect to mysql in a prompt and I have added new users with all priv's and connected with them - obviously its more a server issue than mysql (I think)... MySQL version is 4.0.21 PHP Version 4.3.8 On a Win2k server using apache 2 (just a local testing server). Any info would be muchly appreciated. Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Action after new/update record
[snip] I'm quite new with mySQL, i have bought the book Mastering MySQL4 but i don'f find a solution for my problem there. There are no triggers in version 4 if 'im correct, but i want to export a table (small one) every time there is a new record or a record modified. Whats the way to do this, the manupulation of the data goes with php5 on a website. [/snip] Use your application language (PHP) to perform the export. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in your SQL syntax
You have an awful lot of brackets in the query, many of which don't appear to be needed. For example, I don't see why you have brackets in this phrase: AND ((languages.languages_id)=1)) Perhaps removing the unnecessary ones will help the query work better and more consistently on each machine. Are the different machines all running the exact same version of MySQL? Rhino - Original Message - From: Daniel Sousa [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 26, 2005 6:45 AM Subject: error in your SQL syntax I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories.products_id, categories.parent_id, products_description.products_name, products.products_price, products.products_tax_class_id, products.products_image, specials.specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id) ON specials.products_id = products_to_categories.products_id) INNER JOIN products ON specials.products_id = products.products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id = products_description.language_id WHERE (((categories.parent_id)=285) AND ((languages.languages_id)=1)) i run this query in my computer and work, but in the internet server don´t. If anyone can solve this problem answer me. Daniel Sousa No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
drop table is written to bin-log, load table is NOT - why????
Hello, I'm wondering why on a replication client (MySQL 4.0.23) a drop table XYZ is written to the mysql binlog while the following load table XYZ from master is not. This missing binlog-entry in the first repl. client causes a second replication client, which is replicating the first client, (sounds silly but this construction is necessary to filter the entries of a single database out of dozen hosted on the master and to replicate the logentries for this database only) to go out of sync cause it still holds the old values while the first client now is up to date. Help would be appreciated Lutz Maibach EasyCom GmbH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access denied for user - I cant work this out
hi you are not passing a password to mysql - check your code to see if this is correct. Peter -Original Message- From: Christian Biggins [mailto:[EMAIL PROTECTED] Sent: 26 January 2005 12:27 To: mysql@lists.mysql.com Subject: Access denied for user - I cant work this out Hi All, I am consistently getting; Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) I am connecting through php with the same script I use all the time, I have checked it and dbl checked it for problems. I can connect to mysql in a prompt and I have added new users with all priv's and connected with them - obviously its more a server issue than mysql (I think)... MySQL version is 4.0.21 PHP Version 4.3.8 On a Win2k server using apache 2 (just a local testing server). Any info would be muchly appreciated. Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access denied for user - I cant work this out
Hi Peter,
There is a password being suppled - see code (btw, its local testing only,
hence the root user)
//Database Settings
$db_host = 'localhost'; //database hostname
$db_name = 'powerpla_powerplay'; //database name
$db_user = 'root'; //database USER name
$db_pass = 'rootpass'; // database password
function db_connect()
{
if ($dbc = @mysql_connect($db_host,
$db_user, $db_pass)) {
if
(!mysql_select_db($db_name)) {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
} else {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
}
-Original Message-
From: Peter Lovatt [mailto:[EMAIL PROTECTED]
Sent: Thursday, 27 January 2005 12:27 AM
To: Christian Biggins; mysql@lists.mysql.com
Subject: RE: Access denied for user - I cant work this out
hi
you are not passing a password to mysql - check your code to see if this is
correct.
Peter
-Original Message-
From: Christian Biggins [mailto:[EMAIL PROTECTED]
Sent: 26 January 2005 12:27
To: mysql@lists.mysql.com
Subject: Access denied for user - I cant work this out
Hi All,
I am consistently getting;
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
I am connecting through php with the same script I use all the time, I
have checked it and dbl checked it for problems.
I can connect to mysql in a prompt and I have added new users with all
priv's and connected with them - obviously its more a server issue
than mysql (I think)...
MySQL version is 4.0.21
PHP Version 4.3.8
On a Win2k server using apache 2 (just a local testing server).
Any info would be muchly appreciated.
Christian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access denied for user - I cant work this out
The problem is with your php. Just as a test, print
$db_host,$db_user,$db_pass in your function before you try the connect. My
guess is that they will be blank. The reason for this is that you assign
them outside of the function. This means you either need to pass them to the
function or explicitly state that they are global. Since these are all
blank, it is using the defaults, which on windows are localhost, ODBC, and
no password.
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
-Original Message-
From: Christian Biggins
Sent: Wednesday, January 26, 2005 7:30 AM
Subject: RE: Access denied for user - I cant work this out
Hi Peter,
There is a password being suppled - see code (btw, its local testing only,
hence the root user)
//Database Settings
$db_host = 'localhost'; //database hostname $db_name =
'powerpla_powerplay'; //database name $db_user = 'root'; //database USER
name $db_pass = 'rootpass'; // database password
function db_connect()
{
if ($dbc = @mysql_connect($db_host,
$db_user, $db_pass)) {
if
(!mysql_select_db($db_name)) {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
} else {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
}
-Original Message-
From: Peter Lovatt
Sent: Thursday, 27 January 2005 12:27 AM
To: Christian Biggins; mysql@lists.mysql.com
Subject: RE: Access denied for user - I cant work this out
hi
you are not passing a password to mysql - check your code to see if this is
correct.
Peter
-Original Message-
From: Christian Biggins
Sent: 26 January 2005 12:27
To: mysql@lists.mysql.com
Subject: Access denied for user - I cant work this out
Hi All,
I am consistently getting;
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
I am connecting through php with the same script I use all the time, I
have checked it and dbl checked it for problems.
I can connect to mysql in a prompt and I have added new users with all
priv's and connected with them - obviously its more a server issue
than mysql (I think)...
MySQL version is 4.0.21
PHP Version 4.3.8
On a Win2k server using apache 2 (just a local testing server).
Any info would be muchly appreciated.
Christian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out of tablespace when using innodb_file_per_table
Hello, I'm using InnoDB with the innodb_file_per_table option on. But now my .MYD-file has reached the maximum filesize of my filesystem and i have to add a new tablespace. Can someone tell me how? Thanks in advance for your help Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with select distinct
Michael, Thanks for your suggestion. Here's the query that seems to have fixed the problem, without a DISTINCT clause: select Documents.ID, Name, max(DownloadLog.AddedOn) as DownloadedOn from Documents, DocumentFiles, DownloadLog where Documents.ID = DocumentFiles.Document and DocumentFiles.ID = DownloadLog.DocumentFile group by Documents.ID order by DownloadedOn desc limit 10 It seems to work perfectly. Thanks again! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 12:08 AM To: Frederic Wenzel Cc: Gustafson, Tim; mysql@lists.mysql.com Subject: Re: Problems with select distinct Frederic Wenzel wrote: On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen [EMAIL PROTECTED] wrote: I suspect mysql is doing the DISTINCT before the ORDER BY. One of the reasons I avoid DISTINCT when possible. Isn't this supposed to be correct? Ordering has to take place as the very last operation, after any selection and projection, doesn't it? Regards Fred In theory, or in practice? In practice, ordering is sometimes done ahead of time using an index, and DISTINCT may be optimized as a GROUP BY. In theory, ORDER BY sorts the rows and DISTINCT throws out duplicates. So long as we sort by columns included in the SELECT output, it won't make any difference which you do first and which last. The problem comes when we sort on columns not in the selected output. Now that I've thought about it some more, I don't believe order of operations matters at all in that case, because which duplicate rows are thrown away by DISTINCT is not defined. You see? Suppose, prior to DISTINCT or ORDER BY, you have these rows: ID Name +--+--+ 10 Test 1 10 Test 1 11 Test 2 10 Test 1 11 Test 2 Which two rows should DISTINCT keep? Even if you sort first, DISTINCT is under no obligation to choose the first rows it finds. That DISTINCT may be optimized as a GROUP BY is instructive. MySQL lets you do things like SELECT id, name, updated FROM mytable GROUP BY id, name; but the 'updated' column is chosen more at less randomly for each group. That is, for each group, you simply get one value of updated from an undetermined row which belongs to the group. I think that's what's happening here. The lesson is that you cannot use DISTINCT and then ORDER BY an unselected column and get meaningful results. I'm not a big fan of DISTINCT. We get a lot of questions on the list that amount to Here's my query, but it gives me more rows than I want. I tried to fix it by adding DISTINCT, but now I don't get the right result. More often than not, there is a better query which explicitly retrieves precisely the desired rows, with no need for DISTINCT. Michael smime.p7s Description: S/MIME cryptographic signature
RE: Access denied for user - I cant work this out
Hi
If this is verbatim code the connection string is inside the function and
the connection parameters are outside, so they will not be available to the
mysql_connect()
Try
function db_connect()
{
//Database Settings
$db_host = 'localhost'; //database hostname
$db_name = 'powerpla_powerplay'; //database name
$db_user = 'root'; //database USER name
$db_pass = 'rootpass'; // database password
if ($dbc = @mysql_connect($db_host,
$db_user, $db_pass)) {
if
(!mysql_select_db($db_name)) {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
} else {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
}
HTH
Peter
-Original Message-
From: Christian Biggins [mailto:[EMAIL PROTECTED]
Sent: 26 January 2005 13:30
To: 'Peter Lovatt'; mysql@lists.mysql.com
Subject: RE: Access denied for user - I cant work this out
Hi Peter,
There is a password being suppled - see code (btw, its local testing only,
hence the root user)
//Database Settings
$db_host = 'localhost'; //database hostname
$db_name = 'powerpla_powerplay'; //database name
$db_user = 'root'; //database USER name
$db_pass = 'rootpass'; // database password
function db_connect()
{
if ($dbc = @mysql_connect($db_host,
$db_user, $db_pass)) {
if
(!mysql_select_db($db_name)) {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
} else {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
}
-Original Message-
From: Peter Lovatt [mailto:[EMAIL PROTECTED]
Sent: Thursday, 27 January 2005 12:27 AM
To: Christian Biggins; mysql@lists.mysql.com
Subject: RE: Access denied for user - I cant work this out
hi
you are not passing a password to mysql - check your code to see
if this is
correct.
Peter
-Original Message-
From: Christian Biggins [mailto:[EMAIL PROTECTED]
Sent: 26 January 2005 12:27
To: mysql@lists.mysql.com
Subject: Access denied for user - I cant work this out
Hi All,
I am consistently getting;
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
I am connecting through php with the same script I use all the time, I
have checked it and dbl checked it for problems.
I can connect to mysql in a prompt and I have added new users with all
priv's and connected with them - obviously its more a server issue
than mysql (I think)...
MySQL version is 4.0.21
PHP Version 4.3.8
On a Win2k server using apache 2 (just a local testing server).
Any info would be muchly appreciated.
Christian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello MySQLers, I'm trying to define a structure for my database and I'm experience some problems, any comments would be appretiated. This is for a (web) application to handle information about music, lyrics and resources for playing songs. So, the data I'll be managing are: persons, groups, songs, albums and some song-related data that is not important right now. Let's start with the idea that I have a table for each of those kind of data I'm managing, my problem is when I try to relate songs to musicians (a musician can be a person or a group). The relationship happens thru a third table that can stablish if the person or group (the musician) is the author of the lyrics, of the music, an interpreter or whatever is needed. In short, for some relationships, persons and groups are the same sings, and for some others, they are very different things. I've found the following three possible solutions: 1) Have three totally separated tables: persons, groups, songs. Have another table that relates songs to persons or groups, with an enum field that tells if it's relating to one table or the other. The bad thing about this is that I'll have a lot of redundant information: because if I link to a band 1000, I'll be specifing 1000 that it is a band, only one should be enough. 2) Have the persons and the groups in one table: musicians, with a field that indetifies if it's a person or a group. The good thing is that the relationship from songs to to musicians is very simple and it's specified in only one place if it's a band or a person. The bad part is that it's hard to separate groups from persons, it's not as easy as if they were in two separate tables. Soem fields are valid for one type of musician and some fields for another, so, the forms (to submit, modify, show and delete) for each type need extra care. 3) Have master table, musicians, with only the common fields between groups and persons and then have two tables, one for persons and one for groups. This seems like a cleaner solution, but it requires two inserts for each insert of data (wich I would put in a transaction, but I'm stuck with MySQL 3.x) and it my have other problems. Now that I'm thinking about a fourth solution: Have two totally separate tables for groups and persons (this is what I really like) and then, one table to relate songs to persons, and another table to relate songs to groups. The problem with that is that, sometimes, I need to get all the musicians that are related to a song, including both, persons and groups, to just list them, BUT, with some identification if it's a person or a group, so in the listing I can do some exceptions. Any comment is very well appretiated. Thank you. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB94ZlfW48a9PWGkURAnoXAJwKJ/8pUWfWFrSvAUG+lzOWIjB6HQCePc/m bLKQuSNJE6ZsYrWEyPQAhw0= =fdbJ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in your SQL syntax
Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories. categories_id) ON specials.products_id = products_to_categories. products_id) INNER JOIN products ON specials.products_id = products. products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id = products_description.language_id WHERE (((categories.parent_id)=285) AND ((languages.languages_id)=1)) i run this query in my computer and work, but in the internet server don´t. If anyone can solve this problem answer me. Daniel Sousa
Re: A problem of structure
Pupeno wrote: Now that I'm thinking about a fourth solution: Have two totally separate tables for groups and persons (this is what I really like) and then, one table to relate songs to persons, and another table to relate songs to groups. The problem with that is that, sometimes, I need to get all the musicians that are related to a song, including both, persons and groups, to just list them, BUT, with some identification if it's a person or a group, so in the listing I can do some exceptions. - how about... - 6 tables: PERSON * person_id * name GROUP * group_id * name PERSON-TO-GROUP LINK TABLE * person_id * group_id ALBUM * album_id * title SONG * song_id * title * album_id SONG-TO-PERSON-OR-GROUP LINK TABLE * song_id * person_id * group_id * role (performer, lyricist, etc.) - left joins across song-to-person-or-group and person and group will return whichever is the case. - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: drop table is written to bin-log, load table is NOT - why????
Hello Lutz, As far as I know, binlog records only DML and DDL statements, and LOAD TABLE FROM MASTER is not the one. For selective replication I would check startup options --replicate-do-* and --replicate-wild-*. See http://dev.mysql.com/doc/mysql/en/replication-options.html for details. Also you may need log-slave-update option on if you have daisy-chain replication. Regards, Artem -Original Message- From: Lutz Maibach [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 8:24 AM To: mysql Liste Subject: drop table is written to bin-log, load table is NOT - why Hello, I'm wondering why on a replication client (MySQL 4.0.23) a drop table XYZ is written to the mysql binlog while the following load table XYZ from master is not. This missing binlog-entry in the first repl. client causes a second replication client, which is replicating the first client, (sounds silly but this construction is necessary to filter the entries of a single database out of dozen hosted on the master and to replicate the logentries for this database only) to go out of sync cause it still holds the old values while the first client now is up to date. Help would be appreciated Lutz Maibach EasyCom GmbH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 07:00:34 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello MySQLers, I'm trying to define a structure for my database and I'm experience some problems, any comments would be appretiated. This is for a (web) application to handle information about music, lyrics and resources for playing songs. So, the data I'll be managing are: persons, groups, songs, albums and some song-related data that is not important right now. Let's start with the idea that I have a table for each of those kind of data I'm managing, my problem is when I try to relate songs to musicians (a musician can be a person or a group). The relationship happens thru a third table that can stablish if the person or group (the musician) is the author of the lyrics, of the music, an interpreter or whatever is needed. In short, for some relationships, persons and groups are the same sings, and for some others, they are very different things. I've found the following three possible solutions: 1) Have three totally separated tables: persons, groups, songs. Have another table that relates songs to persons or groups, with an enum field that tells if it's relating to one table or the other. The bad thing about this is that I'll have a lot of redundant information: because if I link to a band 1000, I'll be specifing 1000 that it is a band, only one should be enough. 2) Have the persons and the groups in one table: musicians, with a field that indetifies if it's a person or a group. The good thing is that the relationship from songs to to musicians is very simple and it's specified in only one place if it's a band or a person. The bad part is that it's hard to separate groups from persons, it's not as easy as if they were in two separate tables. Soem fields are valid for one type of musician and some fields for another, so, the forms (to submit, modify, show and delete) for each type need extra care. 3) Have master table, musicians, with only the common fields between groups and persons and then have two tables, one for persons and one for groups. This seems like a cleaner solution, but it requires two inserts for each insert of data (wich I would put in a transaction, but I'm stuck with MySQL 3.x) and it my have other problems. Now that I'm thinking about a fourth solution: Have two totally separate tables for groups and persons (this is what I really like) and then, one table to relate songs to persons, and another table to relate songs to groups. The problem with that is that, sometimes, I need to get all the musicians that are related to a song, including both, persons and groups, to just list them, BUT, with some identification if it's a person or a group, so in the listing I can do some exceptions. Any comment is very well appretiated. Thank you. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB94ZlfW48a9PWGkURAnoXAJwKJ/8pUWfWFrSvAUG+lzOWIjB6HQCePc/m bLKQuSNJE6ZsYrWEyPQAhw0= =fdbJ -END PGP SIGNATURE- If it were me, I would have separate tables for groups, songs, performances, recordings, and artists. I would define Group as one or more people that perform a version of a song. Each member of a Group will be an Artist (person). A Performance is a unique combination of GROUP - SONG - RECORDING. Some Groups remake or remix or rerecord the same song several times over their career so each perfomance should be treated uniquely. This will help to differentiate studio recordings from live recordings of the same song. Groups can form and reform over the course of several years but keep the same name so (especially if you are creating a database to deal with performance royalties) you should also keep up with group lineups by date ranges. You can do this either by creating a new Group record for each line up (each with the same name but for different date ranges) or you handle this on your Groupmembership table. Disjoint membership spans would get two or more records. For example Joe is part of insert band name here from 1988 to 1990 then leaves (for whatever reason) and eventually rejoins the band from 1995 until their breakup in 1996. That would be one Group record with two Groupmembership records that associates Joe to his band for two different date ranges. Artists are in general just people. Performers, producers, lyricists, arrangers, backup musicians, engineers, etc. This is your master table of Who's-who. There should be a table or tables that associates the construction of each Song to one or more Artists. You could create separate tables for lyricists, composers, and arrangers (the normalized approach) or one table and include a value for how that artist contributed to the song (sometimes faster to work with but takes
InnoDB, record locking question
Hi, I am trying to learn more about InnoDB to convert MyISAM to InnoDB. according to MySQL document, I can lock a record like this: SELECT * FROM mytable WHERE id = '100' LOCK IN SHARE MODE; I run this query and showed a message to stop the screen (waiting) and on the other computer I run the same query or even I updated the record (which is locked by the other computer), but I didn't get any error. How does this work? Did I miss anything here? My other question: if I lock a record with that command, then how I can release the lock? I could find any command to release the lock! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Je Merkredo Januaro 26 2005 15:34, Ian Sales (DBA) skribis:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* person_id
* group_id
* role (performer, lyricist, etc.)
What I don't like about that, is that half the person_id fields would be empty
and half the group_id fields would be empty. I was thinking about:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* musician_id
* type ('person', 'group')
* role (performer, lyricist, etc.)
But this is the solution that has so many redundant data. It's not very clear,
but it's there.
- --
Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com
Reading Science Fiction ? http://sfreaders.com.ar
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
iD8DBQFB95G5fW48a9PWGkURAkhJAJ41HNEEI9v1ccLIAIuAajxA/oL59wCffdLE
MNgYp2L6UrjgQcB3WWfCI10=
=yDWO
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno wrote:
What I don't like about that, is that half the person_id fields would
be empty
and half the group_id fields would be empty. I was thinking about:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* musician_id
* type ('person', 'group')
* role (performer, lyricist, etc.)
But this is the solution that has so many redundant data. It's not very clear,
but it's there.
- and conversely, I don't like the idea of column that can join to
either of two tables depending on the value of a switch (the type
column) :-)
- perhaps using separate columns in the link for each role might work...
performer_group_id, performer_person_id, lyricist_person_id, etc. There
will be one row per recording of a song, although some columns on that
row may be empty.
- ian
--
+---+
| Ian Sales Database Administrator |
| |
| If your DBA is busy all the time... |
| ...he's not doing his job properly |
| eBuyer http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in your SQL syntax
Thanks, works fines. I use access because i don´t know a GUI tool that make SQL querys more easy. Thanks all again, Daniel Sousa - Original Message - From: [EMAIL PROTECTED] To: Daniel Sousa Cc: mysql@lists.mysql.com Sent: Wednesday, 26 January, 2005 14:57 Subject: Re: error in your SQL syntax Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories. categories_id) ON specials.products_id = products_to_categories. products_id) INNER JOIN products ON specials.products_id = products. products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id =
RE: error in your SQL syntax
Try Query Browser ( http://dev.mysql.com/downloads/query-browser ) for building queries for MySQL. Regards, Artem -Original Message- From: Daniel Sousa [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 11:18 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: error in your SQL syntax Thanks, works fines. I use access because i don´t know a GUI tool that make SQL querys more easy. Thanks all again, Daniel Sousa - Original Message - From: [EMAIL PROTECTED] To: Daniel Sousa Cc: mysql@lists.mysql.com Sent: Wednesday, 26 January, 2005 14:57 Subject: Re: error in your SQL syntax Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price,
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Shawn Green, Je Merkredo Januaro 26 2005 15:42, [EMAIL PROTECTED] skribis: If it were me, I would have separate tables for groups, songs, performances, recordings, and artists. Well, the thing is that I was just doing a site for holding information for musicians (that is, lyrics with chords to play in the guitar or piano), but the thing started to grow as I am a very structured person I wanted to have a very nice structure. What you say makes sense, but the thing starts to be even bigger, I'm not sure that anyone would be able to introduce data if it's so complicated. Anyway, I'm already tempted to introduce your ideas and I think I can't be untempted. The thing is that this is art, and art is hard to structure. What is a song ? ok, Freddie Mercury composed a song, he wrote the music, he wrote the lyrics and he recorded with a band called Queen. Latter, someone else comes and record it, but changes the lyrics, is it still the same song ? What if the music is changed and the lyrics stay ? what if everything changes, but the title/name remains ? As I can't ensure when it is still the same song, I would take the asumption that all of them are different songs. But then, my asumption might be wrong. For example, the last song of the album Queen (the first album by the band Queen) has the same name as the last song of the album Queen II, the music is similar, slightly changed, and the second version has lyrics while the first one doesn't. Is it the same song or two separate songs ? the same songs and two different performances ? I would define Group as one or more people that perform a version of a song. Each member of a Group will be an Artist (person). Ok. A Performance is a unique combination of GROUP - SONG - RECORDING. Some A performance can be a unique combination of artist (person) - song - recording as well! Groups remake or remix or rerecord the same song several times over their career so each perfomance should be treated uniquely. Indeed. This will help to differentiate studio recordings from live recordings of the same song. Agreed. Groups can form and reform over the course of several years but keep the same name so (especially if you are creating a database to deal with performance royalties) you should also keep up with group lineups by date ranges. You can do this either by creating a new Group record for each line up (each with the same name but for different date ranges) or you handle this on your Groupmembership table. Disjoint membership spans would get two or more records. For example Joe is part of insert band name here from 1988 to 1990 then leaves (for whatever reason) and eventually rejoins the band from 1995 until their breakup in 1996. That would be one Group record with two Groupmembership records that associates Joe to his band for two different date ranges. This was in my mind... I was trying to convince myself that the system was good enough without this information, but again, I'm tempted to implement your solution. Artists are in general just people. Performers, producers, lyricists, arrangers, backup musicians, engineers, etc. This is your master table of Who's-who. There should be a table or tables that associates the construction of each Song to one or more Artists. You could create separate tables for lyricists, composers, and arrangers (the normalized approach) or one table and include a value for how that artist contributed to the song (sometimes faster to work with but takes up more room). Takes more room because of the extra field ? Can you tell me more about 'the normalized approach' ? I think you were on the right track but were just trying to merge too many objects into the same containers. Then I have albums, which are collections of songs, with a track number and have one or more musicians (which can be persons or groups) asociated. For the album It's a kind of magic, the main artists would be Queen, while a lot of other people participated in the album, even as musicians (that is, playing an instrument or doing something). I'm still stuck with the problem that groups and artists are interchangable things, how would you solve this ? Thank you for your comments, they were greatly appretiated. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB96F3fW48a9PWGkURAnZKAJ9y9+H/vhB+0lLPEQxw2LMDyWfNiwCfVaRz SzvKTgyHZ3YBovGfT3+GuPk= =Moax -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
On Wed, Jan 26, 2005 at 01:56:05PM +, Pupeno wrote: Takes more room because of the extra field ? Can you tell me more about 'the normalized approach' ? http://en.wikipedia.org/wiki/Database_normalization -Jason Martin -- If you cannot convince them, confuse them. This message is PGP/MIME signed. pgpGY7lV887VI.pgp Description: PGP signature
Checking max_allowed_packet from PHP during runtime?
Hi MySQL gurus, I'm using the LAMP stack and I'm currently creating a script that allows file uploading through a HTML form. The script itself is to be redistributed so I do not really know anything about the systems it runs on, except maybe that it's LAMP. I keep getting errors because I upload to large files, ie the files are larger than the MySQL servers max_allowed_packet parameter. I would like to check this value during runtime and take proper action. So; How can I check the value of MySQLs max_allowed_packet parameter from a PHP script during runtime? I tried this before: ?php connectToDatabase(); $result = mysql_query(SHOW VARIABLES); $row = mysql_fetch_assoc($result); echo VALUE= . $row['max_allowed_packet']; ? But this does not work, as SHOW VARIABLES seems to be console only; not a valid query. How can I proceed? regards, martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld restarts with no apparent reason
Hi, Im running MySQL 4.0.23 (build from source with skunkware gcc 2.95.2pl1 and a precompiled FSU-pthreads 3.5c) under SCO Openserver 5.0.4. It runs great by itself, the problem comes when I make a program using the C API, it restart mysqld the second (sometimes third) time I run my program, so my program hangs expecting the answer from the already finished mysqld. Here is the example (a.out is my program): # mysqld_safe --user=root --log 448 # Starting mysqld daemon with databases from /usr/local/mysql/var # ps -u root 448 tty01 00:00:00 mysqld_safe 468 tty01 00:00:01 mysqld # a.out # ps -u root 448 tty01 00:00:00 mysqld_safe 468 tty01 00:00:01 mysqld # a.out 050126 12:19:34 mysqld restarted //Here it hangs until I press Ctrl + Pause # ps -u root 448 tty01 00:00:00 mysqld_safe 480 tty01 00:00:01 mysqld The output in hostname.log is the following: /usr/local/mysql/libexec/mysqld, Version: 4.0.23-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock TimeId Command Argument 050126 12:19:29 1 Connect[EMAIL PROTECTED] on 1 Quit /usr/local/mysql/libexec/mysqld, Version: 4.0.23-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock TimeId Command Argument As you see, there is no message from the server that explains why does mysqld restarted. I've already googled for it, but there is no such case in the internet material. Can you help me? Thanks. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking max_allowed_packet from PHP during runtime?
Martin Olsson wrote: I tried this before: ?php connectToDatabase(); $result = mysql_query(SHOW VARIABLES); $row = mysql_fetch_assoc($result); echo VALUE= . $row['max_allowed_packet']; ? But this does not work, as SHOW VARIABLES seems to be console only; not a valid query. Try looking at the result of SHOW VARIABLES. It's not just one row. It's one row for each variable, with the column names Variable_name and Value. If you're just interested in max_allowed_packet, you can eliminate the part of the result set you won't be using by changing the query to SHOW VARIABLES LIKE 'max_allowed_packet'; and then looking at $row['Value'] for the one row that's returned. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 08:56:05 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Shawn Green, Je Merkredo Januaro 26 2005 15:42, [EMAIL PROTECTED] skribis: If it were me, I would have separate tables for groups, songs, performances, recordings, and artists. Well, the thing is that I was just doing a site for holding information for musicians (that is, lyrics with chords to play in the guitar or piano), but the thing started to grow as I am a very structured person I wanted to have a very nice structure. What you say makes sense, but the thing starts to be even bigger, I'm not sure that anyone would be able to introduce data if it's so complicated. Anyway, I'm already tempted to introduce your ideas and I think I can't be untempted. The thing is that this is art, and art is hard to structure. What isa song ? ok, Freddie Mercury composed a song, he wrote the music, he wrote the lyrics and he recorded with a band called Queen. Latter, someone else comes and record it, but changes the lyrics, is it still the same song ? What if the music is changed and the lyrics stay ? what if everything changes, but the title/name remains ? As I can't ensure when it is still the same song, I would take the asumption that all of them are different songs. But then, my asumption might be wrong. For example, the last song of the album Queen (the first album by the band Queen) has the same name as the last song of the album Queen II, themusic is similar, slightly changed, and the second version has lyrics while the first one doesn't. Is it the same song or two separate songs ? the same songs and two different performances ? Same composer + same title + roughly same melody = same song, different arrangement. This arrangement had words and a slightly different structure but I count them as same song I would define Group as one or more people that perform a version of a song. Each member of a Group will be an Artist (person). Ok. In answer to your question below. I believe you are confusing a Group consisting of a single person with the entity we are calling Artist. A Group can represent one or more people working together under a common name. We should relate the artists to the groups they are in through a separate table. The relationship is an entity itself and can have other information (like dates of join/leave, band position, etc.) Take a look at this sample data Group table -- Queen Wham George Michael Ziggy Stardust David Bowie Van Halen David Lee Roth Artists table Brian May Freddy Mercury George Michael David Bowie David Lee Roth Sammy Hagar Eddie Van Halen Steve Vai Groupmembership (Group - Artist - primary role) - Queen - Brian May - Lead Guitar Queen - Freddy Mercury - Lead Singer Wham - George Michael - Lead Singer Wham - Andrew Ridgely - Lead Singer George Michael - George Michael - Lead Singer Ziggy Stardust - David Bowie - Lead Singer David Bowie - David Bowie - Lead Singer Van Halen - Eddie Van Halen - Lead Guitar Van Halen - Sammy Hagar - Lead Singer Van Halen - David Lee Roth - Lead Singer David Lee Roth - David Lee Roth - Lead Singer David Lee Roth - Steve Vai - Guitar If you wanted to allow for multiple roles for a Groupmember then we would need two more tables to make that association. You could create a table with nothing in it but roles (lead singer, steel guitar, fiddle, rhythm guitar, saxophonist, keyboard, drummer, etc.) and a table to match Grouproles to Groupmembers. Each row in that matching table would be a combination of what that person did while a member of that group. If someone did 4 things, they would have 4 records in that matching table. Make sense? A Performance is a unique combination of GROUP - SONG - RECORDING. Some A performance can be a unique combination of artist (person) - song - recording as well! Not exactly, this is where I think you are still confusing the entity GROUP with the entity ARTIST. A group may be composed of only one person (person = artist) but the group is what makes a PERFORMANCE. An artist is part of the performance only by virtue of being a member of the GROUP that created it. For instance, when Eric Clapton did his unplugged version of Layla, he was the same ARTIST as recorded the original studio version but he was part of two different groups. The first time he was part of the group Derrick and the Dominoes and the second time he was part of the group Eric Clapton (GROUP does not equal ARTIST) Groups remake or remix or rerecord the same song several times over their career so each perfomance should be treated uniquely. Indeed. This will help to differentiate studio recordings from live recordings of the same song. Agreed. Groups can form and reform over the course of several years but keep the same name so (especially if you are
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There where two cases where I have roles: How an artist participated in a song (creating the lyrics, creating the musici, etc, etc) and how an artist participates in a group. Do you think that enums are good for this kind of things ? or another table ? If in another table, how would you manage translations (that is, the system is multilingual), having the translations in that table, or another table ? or what ? Thank you. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB98n+fW48a9PWGkURAjlAAJsEMwnQDJOqcLMFrSdm6FrLJym6wACdHQxp XRfhhyEP1ccRuPEqh5f6rqM= =CzC2 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Je Merkredo Januaro 26 2005 18:56, [EMAIL PROTECTED] skribis: For instance, when Eric Clapton did his unplugged version of Layla, he was the same ARTIST as recorded the original studio version but he was part of two different groups. The first time he was part of the group Derrick and the Dominoes and the second time he was part of the group Eric Clapton (GROUP does not equal ARTIST) If for example, someone played the drums for Eric Clapton on that performance, would he have 'joined' the 'Eric Clapton' group during that performance ? Or for example, when Paul McCartney goes doing a tour as Paul McCartney, do the musicians who go with them join the group Paul McCartney ? Thanks. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar PS: I'm not sure how to handle the interface for this, I mean, people won't really understand that Eric Clapton IS a group if I list him under Groups. Any recomendations ? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB98r0fW48a9PWGkURAn42AJ0XdACT/PYjbseVg7q9ftLgOB9JnACggtHK +3x3oiWxKpi6RLpOA6QjIis= =LRI1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 11:48:59 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There where two cases where I have roles: How an artist participated in a song (creating the lyrics, creating the musici, etc, etc) and how an artist participates in a group. Do you think that enums are good for this kind of things ? or another table ? I prefer 1 table per association. If I need to associate people (Artists table) to Recordings (song+group+...) as Engineers, I would create a table called Engineer to store that relationship. If in another table, how would you manage translations (that is, the system is multilingual), having the translations in that table, or another table ? or what ? Proper names (groups, artists, albums, etc.) do not need to be translated. Jimi Hendrix is Jimi Hendrix in Spanish, Thai, German, or Swahili. It's the other names (roles, relationships) that shift from language to language. It's rather simple to have a master table in one language and translation. Really, the topic of internationalization is something you need to research on your own as it really doesn't apply well to this list. Thank you. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB98n+fW48a9PWGkURAjlAAJsEMwnQDJOqcLMFrSdm6FrLJym6wACdHQxp XRfhhyEP1ccRuPEqh5f6rqM= =CzC2 -END PGP SIGNATURE-
slow connections with 4.1.9
Since upgrading my Solaris9 server to MySQL 4.1.9 (previously 4.1.3), remote connections to it from my WinXP laptop have become very slow. Remote connections using the mysql command-line tool (4.1.7 client), MySQL Query Browser (1.1.5) or JDBC connections with Connector/J (3.0.16) used to all connect immediately. Now they take anywhere from 10-30 seconds. The Query Browser has also become very frustrating because every time I execute a query it freezes for 10-30 seconds before finally executing each query. I have noticed that on the server during this long wait time, my new connection appears immediately, but mysqladmin processlist shows the user to be unauthenticated user until it finally completes the connection (and updates the user to the correct username). So, I'm not having network problems getting to the server or anything like that. Also, the server is not low on memory, low on connections, is not producing any errors, etc. Via Google I found a few occurrences of others with unauthenticated user issues, but they all seem to involve lots of connections in this state. In my case it's only one--the user very slowly connecting. This has been happening since a recent server update to 4.1.9 and never happened previously with 4.1.3. Also, it does not occur when making remote connections from the same PC to a 4.1.7 server. Any suggestions? -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
Pupeno [EMAIL PROTECTED] wrote on 01/26/2005 11:53:06 AM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Je Merkredo Januaro 26 2005 18:56, [EMAIL PROTECTED] skribis: For instance, when Eric Clapton did his unplugged version of Layla, he was the same ARTIST as recorded the original studio version but he was part of two different groups. The first time he was part of the group Derrick and the Dominoes and the second time he was part of the group Eric Clapton (GROUP does not equal ARTIST) If for example, someone played the drums for Eric Clapton on that performance, would he have 'joined' the 'Eric Clapton' group during that performance ? Or for example, when Paul McCartney goes doing a tour as Paul McCartney, do the musicians who go with them join the group Paul McCartney ? Thanks. I guess that all depends on the level of detail you want to keep. Legal membership (with contracts and all that) is one condition but acting as a studio or backup player is another. If you want to make that distinction then you would assign backing artists to the Recording and not make them members of the group. The group 'M' is actually only 1 person, Robin Scott. However, on his single 'Pop Muzik' he had two female backup singers. Legally, they weren't part of the group (because he was the only official member) but they do appear on the recording. What you need to decide, now and not later, is if you are even interested in this level of detail. If you are, I would associate those ladies with the Recording not the Group. Otherwise, just leave that information out of your database. - -- Pupeno: [EMAIL PROTECTED] - http://www.pupeno.com Reading Science Fiction ? http://sfreaders.com.ar PS: I'm not sure how to handle the interface for this, I mean, people won't really understand that Eric Clapton IS a group if I list him under Groups. Any recommendations ? I think they will so long as they understand that in the context of their search that the name Groups means who recorded the music. Off the top of my head Eric has recorded as himself, as a member of the group Cream, as a member of Derrick and the Dominoes, and as a supporting musician for a lot of other artists. If you are searching by group names, you only turn up Eric Clapton. If you are searching for recordings that have Eric on them, you will find them all. How you design your search page will make all the difference in what the user thinks they are looking for (perceptions are 90% of the battle). How you organize your database will make all the difference in how well your application responds. There is one VERY important thing to remember. What you call the entities in your database can be entirely different than what an end user thinks they are. Whatever label you put on your web site or in your application's GUI *DOES NOT* need to correspond to the actual name of the data you are presenting. If your users understand what you mean, you can call it anything you like on the back end so long as your back end name makes sense to you. In our case, we need to name a table to store data about an entity that creates a recording. A few possible names for that table would be Group, Band, Artist, or Orchestra. We also need to name a table to contain the names and other biographical information of the actual people that participate in the recording industry. Possible names are Person, People, Artists, Humans, Gente, Hombres, Mujeres, etc. However there are problems with most of those names. They are just not generic enough to fit our needs. If we create one table for just men and another for just women, then you would need to reference and coordinate between two different sets of IDs when looking up anyone's name (not fun). The name Artists appear in both lists of potential names. That means that we need to decide which entity we call Artist (if we even use that name) and which one gets an alternate name. How about we not use the table name of Artist and instead call it Person? That way you can't be confused between the name of the thing (BAND/GROUP/ORCHESTRA) that works together on a recording and the people that make up that thing. It's all a matter of _definition_. We _DEFINE_ that GROUP (or whatever) will the name of the table of those things that make recordings and People is the name of the table of those things that make up a GROUP. People can also be producers, engineers, etc. so that name fits rather well, I think. The basic rules of normalization says that you should only store 1 copy of any entity in your database. That means that we shouldn't have a list of people that were engineers and a separate list of people that were part of groups and another list of people that did arrangements. We need one list of people and whenever one of those people did something (they were a member of a group, the arranged a song, etc.) we create an entry in another table that shows that
oid or rowid equivalent
Hi, I was wondering if mysql supports rowid like oracle or oid like pgsql does for updates? If no, is there a work around to these? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Serious bug (or my foolishness) with alter table and InnoDB
Karam, - Original Message - From: Karam Chand [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 25, 2005 8:58 PM Subject: RE: Serious bug (or my foolishness) with alter table and InnoDB Hello, I just checked with the same version at home and it works. Dont know whats the problem at office. do the tables contain data? Does the data satisfy the FOREIGN KEY constraint you are trying to add? Will check again? It is best not to add any extra clauses to a plain: ALTER TABLE ... TYPE=InnoDB; Few people try to run such complex statements, and there may be bugs there. Karam Regards, Heikki --- Artem Koltsov [EMAIL PROTECTED] wrote: Works fine on WinXP 4.1.8. Only generates warning: mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead | +-+--+--+ And here is the `child` table after execution: mysql show create table child; +---+ - | Table | Create Table +---+ - | child | CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `master` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+ - -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 10:50 AM To: mysql@lists.mysql.com Subject: Serious bug (or my foolishness) with alter table and InnoDB Hello, I am running mysql 4.1.7 on Win2K. I have two tables: CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `master` ( `id` int(11) NOT NULL default '0', `name` char(10) NOT NULL default '', PRIMARY KEY (`id`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; If I execute the following statement: alter table child add foreign key (id) references master (id), type = innodb; the mysql server hangs and needs to be killed. After restarting the table child is also lost. Is this a known bug? Karam __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of tablespace when using innodb_file_per_table
Carsten, - Original Message - From: Grumm, Carsten [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 26, 2005 4:22 PM Subject: Out of tablespace when using innodb_file_per_table Hello, I'm using InnoDB with the innodb_file_per_table option on. But now my .MYD-file has reached the maximum filesize of my filesystem and i have to add a new tablespace. .MYD file? That is a MyISAM type table. Can someone tell me how? http://dev.mysql.com/doc/mysql/en/adding-and-removing.html Thanks in advance for your help Carsten Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: oid or rowid equivalent
Innodb has a oracle style rowid but it can't be accessed. You might want to use auto_increment a table option that updates with each inserted record. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Nupur Jain [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 2:27 PM To: mysql@lists.mysql.com Subject: oid or rowid equivalent Hi, I was wondering if mysql supports rowid like oracle or oid like pgsql does for updates? If no, is there a work around to these? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem of structure
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
After the good comments I've got on this mailing list, I think I have the
structure more or less complete. In some cases I follow the comments, in some
others, I've improvised (hehehe).
So, this is the (explained) structure, what do you think ? Anything I can
improve ?
Everything starts with persons, actual human beings (this is a very simple
table):
CREATE TABLE `persons` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`birthdate` date default NULL,
`deathdate` date default NULL,
`bio`,
PRIMARY KEY (`id`)
)
Then we have the groups of (one or more) people:
CREATE TABLE `groups` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
)
Since the groups or made of people, there's a table which says who belonged to
what group and for what period of time:
CREATE TABLE `memberships` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`person` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
`from` date default NULL,
`to` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `artistGroupFromTo` (`person`,`group`,`from`,`to`)
)
As some extra information, it can be specified what did this persons do in
that group (for that period of time) in the following table, since what a
person can do in a group is likely to change and grow (specially grow, I can
even say that in the future we'll invent more instruments so more roles will
be added as people perform those instruments in a group):
CREATE TABLE `membershipRoles` (
`membership` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `membership` (`membership`,`role`)
)
Now, another point of entry to the system. The songs... this table defines the
abstract concept of song (language is a three letter code of the language of
the song, to be matched agains another table[1]):
CREATE TABLE `songs` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`language` char(3) NOT NULL default 'eng',
`lyrics` longtext,
PRIMARY KEY (`id`)
)
I was tempted to add a field 'translationOf' to easily hold translations of
songs. What do you think about that ?
Now, a song can be performed, so, I have the following table for performances
(either live or studio):
CREATE TABLE `performances` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`song` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
And then who (which group) did what (which role) in a song, the role is the
same as for `membershipRoles`:
CREATE TABLE `performancesGroups` (
`performance` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0'
UNIQUE KEY `performanceGroupRole` (`performance`,`group`,`role`)
)
The third end of this whole thing, are albums:
CREATE TABLE `albums` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`cover` longblob,
`coverFormat` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
Now, the albums can have a lot of related information, like, who made them,
that is the main group, like Queen, The Beatles, Eric Clapton:
CREATE TABLE `albumsGroups` (
`album` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `album` (`album`,`group`)
)
The, to specify who did what in that album (for example, Freedie Mercury:
Vocals, John Lenon: Guitars, Whoever Knowshim: Producer, etc):
CREATE TABLE `albumsPersons` (
`album` bigint(20) unsigned NOT NULL default '0',
`person` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `album` (`album`,`person`,`role`)
)
Now, each album contains a set of performances (not songs), in a specific
order:
CREATE TABLE `albumsTracks` (
`album` bigint(20) unsigned NOT NULL default '0',
`performance` bigint(20) unsigned NOT NULL default '0',
`track` tinyint(3) unsigned NOT NULL default '0',
UNIQUE KEY `albumPerformanceTrack` (`album`,`performance`,`track`)
)
And at last, the table of roles (the scope fields specifies what can be done
for each kind of data, it's more of a helper than anything else, a helper for
the GUI):
CREATE TABLE `roles` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`scope` set('album','performance','song') NOT NULL default
'album,performance',
PRIMARY KEY (`id`)
)
which for example, can contain the following data:
(1, 'Music composer', 'song');
(2, 'Lyrics composer', 'song');
(3, 'Vocals', 'album,performance');
(4, 'Guitars', 'album,performance');
(5, 'Bass Guitar', 'album,performance');
(6, 'Percussion', 'album,performance');
(7, 'Piano', 'album,performance');
So, in
Weird join needs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm in need of doing the following. I have a table, let's say, with a record like this: 1, 'A', 'B'. 2, 'C', 'D' And then I have another table with records like this: 1, 'E' 1, 'F'. Currently I have a query that is able to return (doing a left join): 'A', 'B', 'E' 'A', 'B', 'F' 'C', 'D', NULL But I need this: 'A', 'B', 'E,F' 'C', 'D', NULL Can SQL help me in any way here ? or do I have to do it by programming ? Thanks. - -- Pupeno: [EMAIL PROTECTED] - http://pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB+G7ofW48a9PWGkURAssUAJwM5WJjlD6Q9cFR/JFhASIZWKZHzQCfavQa Tp5Ha1Ebk5qg5avaRGwzp14= =jScZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
