MYSQL C
Hi all I have a little question, have you ever work C MYSQL??? all about that is new for me, if anybody has some info, help me!!! __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! Regístrate ya - http://correo.yahoo.com.mx/
Re: MYSQL C
Vicente Moreno schrieb: Hi all I have a little question, have you ever work C MYSQL??? all about that is new for me, if anybody has some info, help me!!! yes, re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
Hi Steffan, all !
Steffan A. Cline wrote:
[[...]]
I am hoping that by using FK based relationships I can just do one massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
AFAIK, this isn't possible.
Foreign keys (aka referential integrity) has a different purpose:
Cross-table (or inter-table) consistency.
If one table refers to (the primary key of) another table, it should be
guaranteed that this reference is an existing value (does point to an
existing record).
If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Using your example:
The purpose of referential integrity is to avoid the case where a
homes record contains a person_id for which there is no people
record, IOW the case of a home whose owner isn't known.
The relation is asymmetric, as indicated by parent and child:
You cannot have a child record without a parent,
but you can have a parent type record without an actual child.
The typical example is customer and order:
You cannot have an order without customer (so you must insert the
customer first, and you must not delete a customer record from your data
while there is an order associated with it),
but you can easily enter a (prospective) customer into your system who
hasn't yet placed an order.
Then I could do something like:
insert into people (firstname, lastname, address) values ('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field and
insert the data into the matching table and auto populate the person_id in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
It isn't possible by using foreign keys, AFAIK.
You could do that by creating an updateable join view: a view which
creates a 1:1 relationship between people and homes on the
person_id column.
Drawbacks:
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each people_id value there
could be only *one* homes record (IOW, you couldn't define both a
town and a summer house).
What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a homes record when you delete the owner's people record
(on delete cascade), or
- prevent you from deleting a people record referencing a homes
record (on delete restrict).
I did not follow how far this is already implemented, depending on the
MySQL version and the table handler.
Regards and HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection failed!!
I have just loaded MySQL 5 and MySQL Front on to a new computer, and I am now getting a MySQL-Error which is: Connection failed: 1045 - Access denied for user 'root'@'localhost' (using password: NO) My Old pc has exactly the same settings but that uses MySQL 4 I have done a system restore to the point before I installed MySQL 5, and then installed MySQL 4 and that went on with no problem, and I am able to make a connection with MySQL Front! So, what's different in the processes of installation between v4 and v5 that stops me making a connection with v5? What have I done wrong. In both instances, I have used 'localhost' as my Hostname and 'root' as my username, and 'root' as a password for v4 and 'NO' password for v5 (because I didn't see anywhere where it asked for it - I think). I am using MySQL Front because I'm not Neo and I don't see the world in zero's and one's ;) Mat
List of Publicly Accessible MySQL Databases?
Is there a listing of public MySQL Databases anywhere? Just if someone would be new to databases (not me...other people at my office) and they would want to get a look at an existing working database to learn SQL on? I've found one such database: Genome Bioinformatics db.host=genome-mysql.cse.ucsc.edu db.user=genomep db.password=password But I really don't think the people I'm trying to teach here know much about Genome Bioinformatics (and ah consequently I don't know anything about that either...) Thank you, Andrew J. Leer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Failed!!
Have you tried setting the password with mysql admin? Example: mysqladmin -u Root yourpassword 'yournewpassword' - Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
-Original Message- From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Sunday, August 24, 2008 12:00 PM To: Deniss Hennesy Cc: mysql Subject: Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists correction, the command that's failing evidently is the 'create table columns_priv', not a drop table. Sorry for the typo. [JS] Are you running as root, or whichever user has full privileges? It's easy to make that mistake, and you'll get a lot of barking and growling. 2008/8/24 Jim Lyons [EMAIL PROTECTED] The command that's failing evidently is the drop table columns_priv which is why you're getting the error message: *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* I assume you're trying to restore the mysql database from a backup and the script is trying to re-create the privileges tables. You cannot create a table that already exists, so you must drop it first. When you installed a mysql instance on the new server of course it put in a mysql database, that's required. You're now trying to replace it with the mysql database from your previous instance. You have to replace the old mysql database with the new mysql database. You do understand the difference between the mysql *database* and the mysql *instance*, right? 2008/8/24 Deniss Hennesy [EMAIL PROTECTED] I dont understand what you mean by drop table... why i must droped my table? cýrrent server is running properly.. but either OS version or HW is very old. We took new powerfull server, anc we want to migrate our data to new server... onlt this i want to do it. 2008/8/22 Jim Lyons [EMAIL PROTECTED] As I said, it appears your mysqldump output is not doing a DROP TABLE before each create. You need to correct that. Do the drop table commands yourself, then load the tables. Afterwards, do a FLUSH PRIVILEGES to activate the new permissions.l 2008/8/22 Deniss Hennesy [EMAIL PROTECTED] Ý am migrating database from mysql-client-4.0.20 running server(current) to mysql-client-5.0.51a running server(new) ..i ve took mysqldump and transfered to new server and restored but process broken up with this error message. On Fri, Aug 22, 2008 at 6:33 PM, Jim Lyons [EMAIL PROTECTED]wrote: You're restoring the mysql database itself, do you mean to do this? You probably do if it's a new server but there already exists a mysql database so, unless your restore program does DROP TABLE before each create, you'll keep getting this error. On Fri, Aug 22, 2008 at 10:22 AM, Deniss Hennesy [EMAIL PROTECTED] wrote: Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List of Publicly Accessible MySQL Databases?
Hi! Check out db4free.net. :) Cheers, Jay Andrew J. Leer wrote: Is there a listing of public MySQL Databases anywhere? Just if someone would be new to databases (not me...other people at my office) and they would want to get a look at an existing working database to learn SQL on? I've found one such database: Genome Bioinformatics db.host=genome-mysql.cse.ucsc.edu db.user=genomep db.password=password But I really don't think the people I'm trying to teach here know much about Genome Bioinformatics (and ah consequently I don't know anything about that either...) Thank you, Andrew J. Leer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection failed!!
There's probably a password set. Look up how to reset a password: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html On 08/25/2008 03:25 AM, Matthew Stuart wrote: I have just loaded MySQL 5 and MySQL Front on to a new computer, and I am now getting a MySQL-Error which is: Connection failed: 1045 - Access denied for user 'root'@'localhost' (using password: NO) My Old pc has exactly the same settings but that uses MySQL 4 I have done a system restore to the point before I installed MySQL 5, and then installed MySQL 4 and that went on with no problem, and I am able to make a connection with MySQL Front! So, what's different in the processes of installation between v4 and v5 that stops me making a connection with v5? What have I done wrong. In both instances, I have used 'localhost' as my Hostname and 'root' as my username, and 'root' as a password for v4 and 'NO' password for v5 (because I didn't see anywhere where it asked for it - I think). I am using MySQL Front because I'm not Neo and I don't see the world in zero's and one's ;) Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
Joerg- Your comments below are excellent and my comments are just building
off what you have so clearly stated.
I think Stefan's best bet would be a stored procedure. Pass all the data,
insert the parent record (or make sure the parent record exists ... perhaps
it already is present) and then do an insert into the child table. The PK's
and FK's should be good.
Thanks and have a great day!
Tom
On Mon, Aug 25, 2008 at 2:37 AM, Joerg Bruehe [EMAIL PROTECTED] wrote:
Hi Steffan, all !
Steffan A. Cline wrote:
[[...]]
I am hoping that by using FK based relationships I can just do one massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
AFAIK, this isn't possible.
Foreign keys (aka referential integrity) has a different purpose:
Cross-table (or inter-table) consistency.
If one table refers to (the primary key of) another table, it should be
guaranteed that this reference is an existing value (does point to an
existing record).
If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Using your example:
The purpose of referential integrity is to avoid the case where a homes
record contains a person_id for which there is no people record, IOW the
case of a home whose owner isn't known.
The relation is asymmetric, as indicated by parent and child:
You cannot have a child record without a parent,
but you can have a parent type record without an actual child.
The typical example is customer and order:
You cannot have an order without customer (so you must insert the customer
first, and you must not delete a customer record from your data while there
is an order associated with it),
but you can easily enter a (prospective) customer into your system who
hasn't yet placed an order.
Then I could do something like:
insert into people (firstname, lastname, address) values
('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field
and
insert the data into the matching table and auto populate the person_id in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
It isn't possible by using foreign keys, AFAIK.
You could do that by creating an updateable join view: a view which
creates a 1:1 relationship between people and homes on the person_id
column.
Drawbacks:
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each people_id value there
could be only *one* homes record (IOW, you couldn't define both a
town and a summer house).
What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a homes record when you delete the owner's people record
(on delete cascade), or
- prevent you from deleting a people record referencing a homes
record (on delete restrict).
I did not follow how far this is already implemented, depending on the
MySQL version and the table handler.
Regards and HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error stops replication
I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
I forgot to mention that the only benefit of a stored procedure would be
minimizing code in your application. you'd have a single call to the stored
procedure... though some people may prefer having the back-to-back insert
statements in their code.
On Mon, Aug 25, 2008 at 11:28 AM, Tom Nugent [EMAIL PROTECTED] wrote:
Joerg- Your comments below are excellent and my comments are just building
off what you have so clearly stated.
I think Stefan's best bet would be a stored procedure. Pass all the data,
insert the parent record (or make sure the parent record exists ... perhaps
it already is present) and then do an insert into the child table. The PK's
and FK's should be good.
Thanks and have a great day!
Tom
On Mon, Aug 25, 2008 at 2:37 AM, Joerg Bruehe [EMAIL PROTECTED] wrote:
Hi Steffan, all !
Steffan A. Cline wrote:
[[...]]
I am hoping that by using FK based relationships I can just do one
massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
AFAIK, this isn't possible.
Foreign keys (aka referential integrity) has a different purpose:
Cross-table (or inter-table) consistency.
If one table refers to (the primary key of) another table, it should be
guaranteed that this reference is an existing value (does point to an
existing record).
If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Using your example:
The purpose of referential integrity is to avoid the case where a homes
record contains a person_id for which there is no people record, IOW the
case of a home whose owner isn't known.
The relation is asymmetric, as indicated by parent and child:
You cannot have a child record without a parent,
but you can have a parent type record without an actual child.
The typical example is customer and order:
You cannot have an order without customer (so you must insert the customer
first, and you must not delete a customer record from your data while there
is an order associated with it),
but you can easily enter a (prospective) customer into your system who
hasn't yet placed an order.
Then I could do something like:
insert into people (firstname, lastname, address) values
('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field
and
insert the data into the matching table and auto populate the person_id
in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
It isn't possible by using foreign keys, AFAIK.
You could do that by creating an updateable join view: a view which
creates a 1:1 relationship between people and homes on the person_id
column.
Drawbacks:
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each people_id value there
could be only *one* homes record (IOW, you couldn't define both a
town and a summer house).
What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a homes record when you delete the owner's people record
(on delete cascade), or
- prevent you from deleting a people record referencing a homes
record (on delete restrict).
I did not follow how far this is already implemented, depending on the
MySQL version and the table handler.
Regards and HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
All Dear friends İ am mentioning that 2 different server one is running on mysql 4.1 (Current) (dumped) the other is 5.1..xxx (New) (restored) İ solved my problem in such a way that i droped in my new server mysql and test databases mysql show databases; +---+ | Database | ++ | information_schema | | mysql | | test| ++ Than restore my backup finally no any error. thank for all you, for your help , time... regards
Huge temporary file
Awhile back I was having trouble with an INSERT ... SELECT taking up an enormous amount of temporary file space. I've narrowed down, in fact eliminated, my problem by making a minor change. Here is my original INSERT command: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(giiexpr_db.topic.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id LEFT JOIN giiexpr_db.topic ON cust_topics.topic_id = giiexpr_db.topic.topic_id GROUP BY customers.customer_id; When I removed the field `stage_name` from both the query and the `consolidated_customer_data` table, the operation stopped using temporary files altogether! I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. The `stage` table is very small, it has only 9 rows. CREATE TABLE `stage` ( `stage_id` int(11) NOT NULL auto_increment, `stage_name` varchar(15) default NULL, PRIMARY KEY (`stage_id`) ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 I can supply the structures of the other tables, but I wanted to keep this post reasonably short. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge temporary file
what is the value for tmpdir parameter in you my.cnf. regards anandkl On 8/26/08, Jerry Schwartz [EMAIL PROTECTED] wrote: Awhile back I was having trouble with an INSERT ... SELECT taking up an enormous amount of temporary file space. I've narrowed down, in fact eliminated, my problem by making a minor change. Here is my original INSERT command: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(giiexpr_db.topic.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id LEFT JOIN giiexpr_db.topic ON cust_topics.topic_id = giiexpr_db.topic.topic_id GROUP BY customers.customer_id; When I removed the field `stage_name` from both the query and the `consolidated_customer_data` table, the operation stopped using temporary files altogether! I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. The `stage` table is very small, it has only 9 rows. CREATE TABLE `stage` ( `stage_id` int(11) NOT NULL auto_increment, `stage_name` varchar(15) default NULL, PRIMARY KEY (`stage_id`) ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 I can supply the structures of the other tables, but I wanted to keep this post reasonably short. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error stops replication
does tsa table exists on both master and slave. If you have enabled general log, then u can see most of the errors. I am suspecting that this table is not present in slave On 8/26/08, Jesse [EMAIL PROTECTED] wrote: I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
