RE: Confused about syntax for specific join with 3 tables
Date: Sat, 16 May 2009 15:39:56 -0700
From: davidmichaelk...@gmail.com
To: mysql@lists.mysql.com
Subject: Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
Hi David,
Well I could say it's probably because grade_event is a parent table while
score is a child table. And the parent joined first (you know, the deserved
respect) :)). Cheers.
Alugo Abdulazeez
www.frangeovic.com
_
Windows Live™: Keep your life in sync. Check it out!
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sexENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Confused About Rows and Skipping On Import with MySQL Migration Toolkit 1.1
It's not skipping any rows. When you select records from a database, it gets them in the order that is quickest to retrieve them, not the order they were entered. The natural order is how they are stored on disk. As your database is updated over time, this order may change. If you have an auto increment column, order it by that value. That field will have the order the records were imported in. Brent Baisley On Mar 6, 2009, at 9:10 PM, revDAVE wrote: Hi folks, I'm trying to use MySQL Migration Toolkit 1.1 with MS SQL server 2005 http://dev.mysql.com/downloads/gui-tools/5.0.html - basically all is working great - some tables import no problem - except... I'm trying to import an address table and in the summary it says that there's a few problems like: incorrect string value for column 'street' at row 655 0 rows transferred The problem is when I try to import just 650 or so, then I go to phpmyadim to look - well the rows are not in the original order - they start with addressID 1-2-3-4 etc but randomly skips some so there's no way I can match the imported row 655 to the addressID (655) (they were in order - no deletions) - to find the bad field to fix it... Q: why is it importing rows and seemingly skipping many? Q: how can I figure out which is the REAL row 655 (without counting by hand) to fix the field text string error? Thanks in advance - dave -- Thanks - RevDave Cool @ hosting4days . com [db-lists 09] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Confused About Rows and Skipping On Import with MySQL Migration Toolkit 1.1
Hi folks, I'm trying to use MySQL Migration Toolkit 1.1 with MS SQL server 2005 http://dev.mysql.com/downloads/gui-tools/5.0.html - basically all is working great - some tables import no problem - except... I'm trying to import an address table and in the summary it says that there's a few problems like: incorrect string value for column 'street' at row 655 0 rows transferred The problem is when I try to import just 650 or so, then I go to phpmyadim to look - well the rows are not in the original order - they start with addressID 1-2-3-4 etc but randomly skips some so there's no way I can match the imported row 655 to the addressID (655) (they were in order - no deletions) - to find the bad field to fix it... Q: why is it importing rows and seemingly skipping many? Q: how can I figure out which is the REAL row 655 (without counting by hand) to fix the field text string error? Thanks in advance - dave -- Thanks - RevDave Cool @ hosting4days . com [db-lists 09] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
confused about logging
My web server is running drupal. Yesterday it started giving error messages instead of displaying the hom page. The error says, The MySQL error was: Host 'web1.math.wisc.edu' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'. I want to find out why that happened. I'm running the mysql debian package for etch (stable). It installs mysql 5.0. The default my.cnf says this: # Be aware that this log type is a performance killer. #log = /var/log/mysql/mysql.log # Error logging goes to syslog. This is a Debian improvement :) Two questions: 1. Is it a performance killer? 2. There was nothing in the system log. The only messages about mysql were from restarting the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default, Nullable and NULL : confused
Hello, I do not understand the behavior of a simple table : from what I red, in the following exemple the Null column tells the value can be set to NULL, and the Default value is NULL. It doesn't seem to work that way. Some one could explain it ? I run on a linux debian/etch 5.0.32 MySQL release. I have a table named t like : mysql describe t; +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | n | double | YES | | NULL| | | c | varchar(5) | YES | | NULL| | +---++--+-+-+---+ Now I load data infile like this : load data infile'/data/foo' into table t fields terminated by';'; with /data/foo containing : 0.12345;qwer 1.2345; ;asdf I get Records: 3 Deleted: 0 Skipped: 0 Warnings: 1 mysql show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1265 | Data truncated for column 'n' at row 3 | +-+--++ from now, I expect to have NULL where the fields are empty, but instead I get '' in the 2nd row, columb 'c' '0' in the last row, column 'n' mysql select * from t; +-+--+ | n | c| +-+--+ | 0.12345 | qwer | | 1.2345 | | | 0 | asdf | +-+--+ mysql select * from t where c is null or n is null; Empty set (0.00 sec) For my purpose, '0' , '' and NULL Thank you for any help. regards, _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default, Nullable and NULL : confused
in your data file use this for inserting null values '\N' 0.12345;qwer 1.2345;\N \N;asdf On 7/17/08, Gilles MISSONNIER [EMAIL PROTECTED] wrote: Hello, I do not understand the behavior of a simple table : from what I red, in the following exemple the Null column tells the value can be set to NULL, and the Default value is NULL. It doesn't seem to work that way. Some one could explain it ? I run on a linux debian/etch 5.0.32 MySQL release. I have a table named t like : mysql describe t; +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | n | double | YES | | NULL| | | c | varchar(5) | YES | | NULL| | +---++--+-+-+---+ Now I load data infile like this : load data infile'/data/foo' into table t fields terminated by';'; with /data/foo containing : 0.12345;qwer 1.2345; ;asdf I get Records: 3 Deleted: 0 Skipped: 0 Warnings: 1 mysql show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1265 | Data truncated for column 'n' at row 3 | +-+--++ from now, I expect to have NULL where the fields are empty, but instead I get '' in the 2nd row, columb 'c' '0' in the last row, column 'n' mysql select * from t; +-+--+ | n | c| +-+--+ | 0.12345 | qwer | | 1.2345 | | | 0 | asdf | +-+--+ mysql select * from t where c is null or n is null; Empty set (0.00 sec) For my purpose, '0' , '' and NULL Thank you for any help. regards, _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused on Query's
lets say u have a table called Parts and another called Projects ,,, how can u associate the Parts Table with the Projects table so lets say u wanna add a Specific Part to a project maybe even add three parts from the parts table and associate it with a specific project ??? so maybe u would have projectid = partsid ? so your project id lets say would be 1222007BB but u wanna add 7 parts associated with that Projectid hmm maybe whatever partnumber u add would be associated with the projectid ??? such as Partnum = projectid ?? with a left join ? can someone Give me an example of how i would do this ? thanks alot i wanna run a query and see the parts associated with each project ! thats my ultimate goal ,, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused on Query's
I assume each part can be associated with multiple projects, which is a many to many relation. In that case you need to create a join table that holds the relation. Table like that are typically just 2 fields, one for the project id and one for the part id. You may want to add other fields like a timestamp so you know when the part was added to the project. Your query on the three tables (projects, projparts, parts) would look something like this: SELECT projects.*, parts.* FROM projects JOIN projparts ON projects.projectid=projparts.projectid JOIN parts ON projparts.partsid=parts.partsid WHERE projects.projectid=1222007BB You would change those to left joins if you are not sure whether a project has any parts. If there are no parts, that query would not return anything. On Aug 22, 2007, at 5:05 PM, Brian E Boothe wrote: lets say u have a table called Parts and another called Projects ,,, how can u associate the Parts Table with the Projects table so lets say u wanna add a Specific Part to a project maybe even add three parts from the parts table and associate it with a specific project ??? so maybe u would have projectid = partsid ? so your project id lets say would be 1222007BB but u wanna add 7 parts associated with that Projectid hmm maybe whatever partnumber u add would be associated with the projectid ??? such as Partnum = projectid ?? with a left join ? can someone Give me an example of how i would do this ? thanks alot i wanna run a query and see the parts associated with each project ! thats my ultimate goal ,, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dazed confused. Less is more?
A puzzler for you guys.. all plausible explanations (and suggestions for proving them) gratefully received. We run several MySQL database servers in the traditional master-slave configuration and attempt (rather poorly) to spread select queries between them. Normally the slave gets 1/3 of the master load. Both machines have identical configurations, hardware specifications and network connectivity. The main clients of these databases are PHP websites without persistent connections. A fail-over pair of machines in a separate building replicates from the master. Today (as a result of replication failure) we directed all the traffic normally sent to the reporting server back to the master server adding a 1/3 to its load. Several areas of the websites got FASTER afterwards and I'm currenlty at a loss to explain why. Nigel Wood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dazed confused. Less is more?
I would say caching, on multiple levels (CPU, DB, File System). By splitting at least some of the load, it's possible for parts of the cache to become old and get flushed. When everything is on one machine, the box has a complete picture of the traffic patterns and can optimize better. On Jul 31, 2007, at 8:17 AM, nigel wood wrote: A puzzler for you guys.. all plausible explanations (and suggestions for proving them) gratefully received. We run several MySQL database servers in the traditional master- slave configuration and attempt (rather poorly) to spread select queries between them. Normally the slave gets 1/3 of the master load. Both machines have identical configurations, hardware specifications and network connectivity. The main clients of these databases are PHP websites without persistent connections. A fail- over pair of machines in a separate building replicates from the master. Today (as a result of replication failure) we directed all the traffic normally sent to the reporting server back to the master server adding a 1/3 to its load. Several areas of the websites got FASTER afterwards and I'm currenlty at a loss to explain why. Nigel Wood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character set for French... confused :\
Hi all, I need a database that is able to handle french characters. I read the Mysql Reference Manual and have done the following in a table to test the different results but I wasn't successful which leads me to believe I am doing something wrong... and yes, I'm a noob. ;) CREATE TABLE companies ( c1 VARCHAR(30) CHARACTER SET utf8, c2 VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci, c3 VARCHAR(30) CHARACTER SET latin1, c4 VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_general_ci, c5 VARCHAR(30) CHARACTER SET dec8, c6 VARCHAR(30) CHARACTER SET dec8 COLLATE dec8_bin, c7 VARCHAR(30) CHARACTER SET hp8, c8 VARCHAR(30) CHARACTER SET hp8 COLLATE hp8_bin, c9 VARCHAR(30) CHARACTER SET cp850, c10 CHAR(30)NOT NULL, c11 TEXTCHARACTER SET latin1 COLLATE latin1_general_ci ); When I insert 'Récré, Vive La Fête! the best I get is R?cr?, Vive La F?te! from c11. Can anyone advise me on what I need done to get these accents show up? Pointers to more documentation are also welcome. Many thanks, -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character set for French... confused :\
On Thursday 07 December 2006 10:09, Ed wrote: Hi all, I need a database that is able to handle french characters. Sorry about that, it's probably due to my OS rather than MySQL. $ echo Fête Fête $ touch Fête $ ls -l -rw-r--r-- 1 me me 0 Dec 7 14:20 F?te $ rm Fête Hmmm, back to OS ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
confused...
Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Do I get the following? mysql select request_id from requestid; Empty set (0.01 sec) When I do a show create table I see: mysql show create table requestid; +---+--+ | Table | Create Table | +---+--+ | requestid | CREATE TABLE `requestid` ( `request_id` int(11) NOT NULL default '1', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+--+ 1 row in set (0.00 sec) Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? What am I not understanding? How do I create a table with a starting value of '1' or '0' for an int??? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confused...
On Tue, 21 Feb 2006, Patrick Duda wrote: Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Defines the properties of an empty table The request id field for an inserted object will default to one if not supplied. But the object must be supplied. Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confused...
Patrick Duda [EMAIL PROTECTED] wrote on 02/21/2006 02:39:47 PM:
Why, when I create a table as follows:
mysql create table requestid ( request_id int not null default
1, constraint requestid_innodb_pk_cons primary key(request_id) )
ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
Do I get the following?
mysql select request_id from requestid;
Empty set (0.01 sec)
When I do a show create table I see:
mysql show create table requestid;
+---
+--
+
| Table | Create
Table
|
+---
+--
+
| requestid | CREATE TABLE `requestid` (
`request_id` int(11) NOT NULL default '1',
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---
+--
+
1 row in set (0.00 sec)
Shouldn't I be getting back a '1' when I do my select??? Why am I
getting
an empty set? What am I not understanding? How do I create a table
with a
starting value of '1' or '0' for an int???
Thanks
You haven't created any rows yet. That's why you get nothing back from
your SELECT query. With a single-column table like this, it will be
impossible to add a row to the table without providing a value for ID
(because it's the only column). You will never see the default value
because you must always supply one.
The term starting value in your original post implies that you intended
some sort of sequence. Did you want the server to automatically increment
the request_id value for you each time you add a record to this table? If
so, you have to do two things:
1) add more columns to this table
2) change the definition of your ID column to be an auto_increment column.
Here is an example of what your `request` table may look like
CREATE TABLE `request` (
id int not null auto_increment,
details varchar(50) not null,
tsModified timestamp,
PRIMARY KEY(id)
);
and you could add reqests to it like this:
INSERT `request`(`details`) VALUES ('details of your first
request'),('details of a second request'), ('details of a third request');
Is it making any better sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: confused...
Patrick, Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? First, creating a table doesn't add any rows. Show Create Table ... returns a row of data definition info, not a row of table data. Second, Defining the column as NOT NULL will require numeric input for the column, so DEFAULT 1 will have no effect. What are you trying to accomplish with DEFAULT 1? PB - Patrick Duda wrote: Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Do I get the following? mysql select request_id from requestid; Empty set (0.01 sec) When I do a show create table I see: mysql show create table requestid; +---+--+ | Table | Create Table | +---+--+ | requestid | CREATE TABLE `requestid` ( `request_id` int(11) NOT NULL default '1', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+--+ 1 row in set (0.00 sec) Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? What am I not understanding? How do I create a table with a starting value of '1' or '0' for an int??? Thanks -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused about privileges
I have created a table, sfyc and as root I issued the following: grant all on * to todd identified by 'my_password' with grant option; However, todd cannot access sfyc with mysql -u todd -p sfyc And the mysql db contains the following: user table +---+--+ | host | user | +---+--+ | % | todd | | linux | root | | localhost | root | +---+--+ db table +--+-+--+ | host | db | user | +--+-+--+ | %| mysql | todd | | %| test| | | %| test\_% | | +--+-+--+ tables_priv; +--+---+--++ | host | db| user | table_name | +--+---+--++ | %| mysql | todd | localhost | +--+---+--++ If I issue the following command, no changes take place in the above tables: grant all on sfyc to todd identified by 'my_password' with grant option; Do I need to do an insert SQL command to specifically enter the information? Todd -- Ariste Software 200 D Street Ext Petaluma, CA 94952 (707) 773-4523 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about privileges
Did you run the statement witht he mysql database as the current database? If so , you're statement probably got converted to this: grant all on mysql.* to todd identified by 'my_password' with grant option; It seems like a logical thing The grant statement applying to all databases/tables should be: *.* Chris Todd Cary wrote: I have created a table, sfyc and as root I issued the following: grant all on * to todd identified by 'my_password' with grant option; However, todd cannot access sfyc with mysql -u todd -p sfyc And the mysql db contains the following: user table +---+--+ | host | user | +---+--+ | % | todd | | linux | root | | localhost | root | +---+--+ db table +--+-+--+ | host | db | user | +--+-+--+ | %| mysql | todd | | %| test| | | %| test\_% | | +--+-+--+ tables_priv; +--+---+--++ | host | db| user | table_name | +--+---+--++ | %| mysql | todd | localhost | +--+---+--++ If I issue the following command, no changes take place in the above tables: grant all on sfyc to todd identified by 'my_password' with grant option; Do I need to do an insert SQL command to specifically enter the information? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about privileges
Hello.
grant all on * to todd identified by 'my_password' with grant option;
May be you wanted '*.*' instead of '*'? See:
grant all on *.* to todd identified by 'my_password' with grant option;
mysql -u todd -p sfyc
You should specify the database name not a table name ('sfyc' is a table
name). See:
http://dev.mysql.com/doc/mysql/en/mysql.html
Todd Cary [EMAIL PROTECTED] wrote:
I have created a table, sfyc and as root I issued the following:
grant all on * to todd identified by 'my_password' with grant option;
However, todd cannot access sfyc with
mysql -u todd -p sfyc
And the mysql db contains the following:
user table
+---+--+
| host | user |
+---+--+
| % | todd |
| linux | root |
| localhost | root |
+---+--+
db table
+--+-+--+
| host | db | user |
+--+-+--+
| %| mysql | todd |
| %| test| |
| %| test\_% | |
+--+-+--+
tables_priv;
+--+---+--++
| host | db| user | table_name |
+--+---+--++
| %| mysql | todd | localhost |
+--+---+--++
If I issue the following command, no changes take place in the above tables:
grant all on sfyc to todd identified by 'my_password' with grant option;
Do I need to do an insert SQL command to specifically enter the
information?
Todd
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about privileges
Chris et al - The MySQL online manual does show *.* to be used for global priviledges, but my MySQL book only used the *. My mistake! However, the online manual does not indicate (or I am missing it) what the use of * grants. Thank you for the help...it is now working and my DB, sfyc does show in the table, db. Todd Chris wrote: Did you run the statement witht he mysql database as the current database? If so , you're statement probably got converted to this: grant all on mysql.* to todd identified by 'my_password' with grant option; It seems like a logical thing The grant statement applying to all databases/tables should be: *.* Chris Todd Cary wrote: I have created a table, sfyc and as root I issued the following: grant all on * to todd identified by 'my_password' with grant option; However, todd cannot access sfyc with mysql -u todd -p sfyc And the mysql db contains the following: user table +---+--+ | host | user | +---+--+ | % | todd | | linux | root | | localhost | root | +---+--+ db table +--+-+--+ | host | db | user | +--+-+--+ | %| mysql | todd | | %| test| | | %| test\_% | | +--+-+--+ tables_priv; +--+---+--++ | host | db| user | table_name | +--+---+--++ | %| mysql | todd | localhost | +--+---+--++ If I issue the following command, no changes take place in the above tables: grant all on sfyc to todd identified by 'my_password' with grant option; Do I need to do an insert SQL command to specifically enter the information? Todd -- Ariste Software 200 D Street Ext Petaluma, CA 94952 (707) 773-4523 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
now i am getting confused (i could not think of a better title)
Hi folks I have several small data bases running on mysql 4.1.13 no problem brought a book read it yes well i have a data base consisting of 3 tables , radios , users , events radios contains 4 fields csgn , setid , sernum cmmnts. this is populated and is more a lookup table for the radio info then there is events this contains 5 fieldsevnt , eloc , edate , cllct_date , rtn_date and lastly users this contains 6 fields sur_nme , fr_name , ph_w , ph_h , ph_m , addrss Using knoda (part of kde) i have created an form that looks all very nice (to me ) the selection of the radio works enters the right info into the form i can enter users data and event data but when i try to save the data and insert a new row i am unable to my question is How do i go about saving data to multiple tables from one entry form or should this happen automatically The book i purchased Beggining SQL i did not find any help at all i could not find anything on mysql in my local store or a bit further out any help pointers much appreaceted Thanks Pete . -- If Bill Gates had gotten LAID at High School do YOU think there would be a Microsoft ? Of course NOT ! You gotta spend a lot of time at your school Locker stuffing underware up your ass to think , I am going to take on the worlds Computer Industry ---:heard on Cyber Radio.:--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: now i am getting confused (i could not think of a better title)
Peter How do i go about saving data to multiple tables from one entry form or should this happen automatically If the model is that a user can have radios which in turn can have events, you need a userid column in users and radios, and a radioid column in radios and events. You need to read a bit about relational design (eg http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.html). To add a user, a radio and an event, save the user info, get the user id from that insert, save the radio info, get back the radioid from that insert, then save the event info. To add a radio, retrieve the userid from the user's input and offer the user a blank radio form. And so on. PB - Peter Nikolic wrote: Hi folks I have several small data bases running on mysql 4.1.13 no problem brought a book read it yes well i have a data base consisting of 3 tables , radios , users , events radios contains 4 fields csgn , setid , sernum cmmnts. this is populated and is more a lookup table for the radio info then there is events this contains 5 fieldsevnt , eloc , edate , cllct_date , rtn_date and lastly users this contains 6 fields sur_nme , fr_name , ph_w , ph_h , ph_m , addrss Using knoda (part of kde) i have created an form that looks all very nice (to me ) the selection of the radio works enters the right info into the form i can enter users data and event data but when i try to save the data and insert a new row i am unable to my question is How do i go about saving data to multiple tables from one entry form or should this happen automatically The book i purchased "Beggining SQL" i did not find any help at all i could not find anything on mysql in my local store or a bit further out any help pointers much appreaceted Thanks Pete . No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.9/72 - Release Date: 8/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what WHERE how... i am confused: extra instances of foreign keys are being problematic
I am having trouble with this small bit of sql I am using for a homepage. I need to select information on the next two events from two separate tables; tblevents (which holds event related info), and tbleventdate (which holds info related to each date, including times and information) herein lies the problem: By using two separate tables (something that is required) I need to use a foreign key in tbleventdate. This foreign key is the key of tblevents, so when multiple dates occur for the same event (the reason for two tables), this foreign key is duplicated. When an event occurs on consecutive days, it will appear twice, being the only event seen (LIMIT 2). What I want is the next event which does not have the same eventid. And, just to clarify, it will not work if I say WHERE… =varDate, tblevents.eventid != tbleventdate.eventid (as this would return nothing) SELECT tblevents.eventid, tblevents.eventdescr, tblevents.eventname, tbleventdate.eventdate, tbleventdate.timeinfo, tbleventdate.eventid, tbleventdate.dateid, left(tblevents.eventdescr, 150) FROM tbleventdate, tblevents WHERE tblevents.eventid = tbleventdate.eventid AND tbleventdate.eventdate = varDate ORDER BY tbleventdate.eventdate DESC LIMIT 2 ***Note: varDate is defined as ?php date(Y-m-d) ? *** which returns -MM-DD Thank you in advance -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005
FW: what WHERE how... i am confused: extra instances of foreign keys are being problematic
I am having trouble with this small bit of sql I am using for a homepage. I need to select information on the next two events from two separate tables; tblevents (which holds event related info), and tbleventdate (which holds info related to each date, including times and information) herein lies the problem: By using two separate tables (something that is required) I need to use a foreign key in tbleventdate. This foreign key is the key of tblevents, so when multiple dates occur for the same event (the reason for two tables), this foreign key is duplicated. When an event occurs on consecutive days, it will appear twice, being the only event seen (LIMIT 2). What I want is the next event which does not have the same eventid. And, just to clarify, it will not work if I say WHERE… =varDate, tblevents.eventid != tbleventdate.eventid (as this would return nothing) SELECT tblevents.eventid, tblevents.eventdescr, tblevents.eventname, tbleventdate.eventdate, tbleventdate.timeinfo, tbleventdate.eventid, tbleventdate.dateid, left(tblevents.eventdescr, 150) FROM tbleventdate, tblevents WHERE tblevents.eventid = tbleventdate.eventid AND tbleventdate.eventdate = varDate ORDER BY tbleventdate.eventdate DESC LIMIT 2 ***Note: varDate is defined as ?php date(Y-m-d) ? *** which returns -MM-DD Thank you in advance -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005
Re: LOAD DATA INFILE - still confused
Wrong path, you are referring to an uri, not a path. Way off topic to starting explaining basic file system stuff here. You should be the same path you used when your uploaded the file. Something like: /home/chris/datafile.txt Frank At 10:06 PM 6/7/05, Chris wrote: Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- 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]
LOAD DATA INFILE - still confused
I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) The simple php script and the file, datafile.txt are both in the same directory. Where is MySQL looking for the file, datafile.txt? NOTE: the error reported indicates that MySQL is searching in for datafile.txt in a directory called mydatbasename. However, I am running the php script in a shared hosting environment and I am not able to view the directory mydatbasename (if it is actually a directory). Apparently the directory is outside my root? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
yes he is right i am doing the same. and giving full path. i have other problems with load data infile :( - Original Message - From: Frank Bax [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 08, 2005 1:39 AM Subject: Re: LOAD DATA INFILE - still confused At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query cache confused when using different client protocols
Hello. Yes. See a long discussion at: http://bugs.mysql.com/bug.php?id=6511 Thomas van Gulick [EMAIL PROTECTED] wrote: Try this: Setup a database server, with MySQL 4.1, with query cache turned on Setup a client machine with MySQL 4.0 Setup a client machine with MySQL 4.1 (libmysqlclient14) Create very simple table : CREATE TABLE woepwoep (CNT int NOT NULL); Insert single row : INSERT INTO woepwoep SET CNT=10; Now, do select on client machine with MySQL 4.0 : SELECT CNT FROM woepwoep; Query gets cached in format suitable for old protocol Now, do select on client machine with MySQL 4.1 : SELECT CNT FROM woepwoep; This returns undesirable results. The other way around (initial query done on MySQL 4.1 client, subsequent query done on MySQL 4.0 client) fails too, but then it at least say 'Malformed packet'. Is this a bug? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache confused when using different client protocols
Try this: Setup a database server, with MySQL 4.1, with query cache turned on Setup a client machine with MySQL 4.0 Setup a client machine with MySQL 4.1 (libmysqlclient14) Create very simple table : CREATE TABLE woepwoep (CNT int NOT NULL); Insert single row : INSERT INTO woepwoep SET CNT=10; Now, do select on client machine with MySQL 4.0 : SELECT CNT FROM woepwoep; Query gets cached in format suitable for old protocol Now, do select on client machine with MySQL 4.1 : SELECT CNT FROM woepwoep; This returns undesirable results. The other way around (initial query done on MySQL 4.1 client, subsequent query done on MySQL 4.0 client) fails too, but then it at least say 'Malformed packet'. Is this a bug? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
confused about character sets in mysql 4.1.3b
Hello list, I have already installed mysql 4.1.3 beta to my windows xp. I set default-caharacter-set to latin5 and default-collation to latin5_turkish_ci in my.ini configuration file. Because I want latin5 my default character set. Then I restarted mysql service and looked up character set and collation variables. And get cofused. - In WinMySQLAdmin1.4 variables tab I see the following results: character_set_client = latin1 character_set_connection = latin1 character_set_database = latin5 character_set_results = latin1 character_set_server = latin5 character_set_system = utf8 collation_connection = latin1_swedish_ci collation_database = latin5_turkish_ci collation_server = latin5_turkish_ci - When i execute show variables like '%character%' and show variables like '%collation%' queries from the command line i get the same results. - When i execute the same queries from phpMyAdmin 2.5.7-pl1 i get different result set. character set client = latin5 character set connection = latin5 character set database = latin5 character set results = latin5 character set server = latin5 character set system = utf8 collation connection = latin5_turkish_ci collation database = latin5_turkish_ci collation server = latin5_turkish_ci First off all I could not understand why the results are different. And second, does not all my variables have to be latin5 and latin5_turkish_ci because of the configuration I have done in my.ini file? Thanks in advace. Harun __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused by max and group by
I'm having trouble with max() and group by. It seems pretty simple. I hope someone can point out my mistake. I want to select the max index of a group. In other words, I want to find the last record added for each group. The problem I'm having is that the columns of the resulting rows are mixed with different records. I get the expected indexes returned, but the fields appear to be from another record and not the fields associated with the index. I ran this query: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat; and I get the following results: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| one | B | +--+---+---+ But I was expecting this: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| three | B | +--+---+---+ This is my test data. CREATE TABLE `mytest`( `myindex` int(11) NOT NULL default '0', `myval` varchar(40) NOT NULL default '', `mycat` varchar(40) NOT NULL default '', PRIMARY KEY (`myindex`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (1, 'one', 'A'); INSERT INTO `mytest` VALUES (2, 'two', 'A'); INSERT INTO `mytest` VALUES (3, 'three', 'A'); INSERT INTO `mytest` VALUES (4, 'one', 'B'); INSERT INTO `mytest` VALUES (5, 'two', 'B'); INSERT INTO `mytest` VALUES (6, 'three', 'B'); INSERT INTO `mytest` VALUES (7, 'one', 'B'); INSERT INTO `mytest` VALUES (8, 'two', 'B'); INSERT INTO `mytest` VALUES (9, 'three', 'B'); Yours, Noah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Confused by max and group by
You aren't making any mistakes, it's just not possible to do. You can't rely on which row MySQL will return when using a GROUP BY clause. The standard method would be to do something like this: CREATE TEMPORARY TABLE mytemptable SELECT max(myindex) as myindex, mycat FROM `mytest` GROUP BY mycat; then SELECT myval, myotherrows, mycat FROM `mytemptable` LEFT JOIN mytest USING(myindex,mycat) note: I haven't tested the above code, it's just an example of the theory Chris -Original Message- From: Noah Spurrier [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 10:35 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Confused by max and group by I'm having trouble with max() and group by. It seems pretty simple. I hope someone can point out my mistake. I want to select the max index of a group. In other words, I want to find the last record added for each group. The problem I'm having is that the columns of the resulting rows are mixed with different records. I get the expected indexes returned, but the fields appear to be from another record and not the fields associated with the index. I ran this query: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat; and I get the following results: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| one | B | +--+---+---+ But I was expecting this: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| three | B | +--+---+---+ This is my test data. CREATE TABLE `mytest`( `myindex` int(11) NOT NULL default '0', `myval` varchar(40) NOT NULL default '', `mycat` varchar(40) NOT NULL default '', PRIMARY KEY (`myindex`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (1, 'one', 'A'); INSERT INTO `mytest` VALUES (2, 'two', 'A'); INSERT INTO `mytest` VALUES (3, 'three', 'A'); INSERT INTO `mytest` VALUES (4, 'one', 'B'); INSERT INTO `mytest` VALUES (5, 'two', 'B'); INSERT INTO `mytest` VALUES (6, 'three', 'B'); INSERT INTO `mytest` VALUES (7, 'one', 'B'); INSERT INTO `mytest` VALUES (8, 'two', 'B'); INSERT INTO `mytest` VALUES (9, 'three', 'B'); Yours, Noah -- 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: Confused by max and group by
I think what is happening is that you are getting the max value for one field, but the first values for the other fields. Try ordering you group by: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat DESC; On Apr 21, 2004, at 1:35 PM, Noah Spurrier wrote: I'm having trouble with max() and group by. It seems pretty simple. I hope someone can point out my mistake. I want to select the max index of a group. In other words, I want to find the last record added for each group. The problem I'm having is that the columns of the resulting rows are mixed with different records. I get the expected indexes returned, but the fields appear to be from another record and not the fields associated with the index. I ran this query: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat; and I get the following results: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| one | B | +--+---+---+ But I was expecting this: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| three | B | +--+---+---+ This is my test data. CREATE TABLE `mytest`( `myindex` int(11) NOT NULL default '0', `myval` varchar(40) NOT NULL default '', `mycat` varchar(40) NOT NULL default '', PRIMARY KEY (`myindex`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (1, 'one', 'A'); INSERT INTO `mytest` VALUES (2, 'two', 'A'); INSERT INTO `mytest` VALUES (3, 'three', 'A'); INSERT INTO `mytest` VALUES (4, 'one', 'B'); INSERT INTO `mytest` VALUES (5, 'two', 'B'); INSERT INTO `mytest` VALUES (6, 'three', 'B'); INSERT INTO `mytest` VALUES (7, 'one', 'B'); INSERT INTO `mytest` VALUES (8, 'two', 'B'); INSERT INTO `mytest` VALUES (9, 'three', 'B'); Yours, Noah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused by max and group by
The problem you are running into is that you are getting the max of one field and grouping by another. But then you want to get a third field that changes within the grouping. Perhaps this might work SELECT myindex, myval, mycat FROM `mytest` GROUP BY mycat ORDER BY myindex DESC; On Apr 21, 2004, at 4:47 PM, Noah Spurrier wrote: Unfortuantely, that didn't do it. I tried both DESC and ASC. I got the same incorrect result. This is too bad. It SEEMS like it should work... -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Confused by max and group by
Well, in traditional SQL you'd use a sub-query for this type of operation. In MySQL 4.1+ you could do this: SELECT t1.myindex, t1.myval, t1.mycat FROM mytest t1 WHERE myindex = (SELECT max(t2.myindex) FROM mytest t2 WHERE t2.mycat=t1.mycat); once again, just illustrating the theory, it may not work as is. SQL is really *meant* to have sub-queries, so the temp table solution is just a work around until sub-queries make it into the production version of MySQL. At least, that's how I understand it anyway. Chris -Original Message- From: Noah Spurrier [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 1:46 PM To: Chris Cc: [EMAIL PROTECTED] Subject: Re: Confused by max and group by This seems bizarre. Although I am the SQL neophyte and it is perhaps not my right to whine about the mysteries of SQL, but this seem very surprising and nonintuitive. In general, it seems like there is no reason to select multiple fields if one of the fields uses the max() function because the other resulting fields are meaningless (in that they are unrelated to the field returned by max()). Your temp table solution makes sense. I'll use that. Yours, Noah On Wednesday 21 April 2004 11:07 am, Chris wrote: You aren't making any mistakes, it's just not possible to do. You can't rely on which row MySQL will return when using a GROUP BY clause. The standard method would be to do something like this: CREATE TEMPORARY TABLE mytemptable SELECT max(myindex) as myindex, mycat FROM `mytest` GROUP BY mycat; then SELECT myval, myotherrows, mycat FROM `mytemptable` LEFT JOIN mytest USING(myindex,mycat) note: I haven't tested the above code, it's just an example of the theory Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql timed actions... Confused
Judging from the times on the clock (03:00 to 07:00) I would guess that the server on which MySQL is running is doing some scheduled activity. Cron jobs for backup, slocate updates, security checks, etc. MySQL doesn't do scheduled maintenance, especially not for four hours. j- k- On Tuesday 09 March 2004 01:49 pm, Scott Haneda wrote: Here is a log of query times I made when a certain page is loaded that uses php and mysql, does mysql 4 do some sort of scheduled maintenance I am not aware of? SNIP 2004/03/09 02:00:00OK, 77585 bytes1 seconds 2004/03/09 02:05:01OK, 77591 bytes24 seconds 2004/03/09 02:10:00OK, 77591 bytes23 seconds 2004/03/09 02:15:00OK, 77585 bytes32 seconds 2004/03/09 02:20:00OK, 77591 bytes26 seconds 2004/03/09 02:25:00OK, 77591 bytes25 seconds 2004/03/09 02:30:00OK, 77579 bytes27 seconds 2004/03/09 02:35:00OK, 77585 bytes26 seconds 2004/03/09 02:40:00OK, 77585 bytes27 seconds 2004/03/09 02:45:00OK, 77572 bytes28 seconds 2004/03/09 02:50:00OK, 77578 bytes26 seconds 2004/03/09 02:55:00OK, 77578 bytes26 seconds 2004/03/09 03:00:00OK, 77572 bytes26 seconds 2004/03/09 03:05:00OK, 77578 bytes26 seconds 2004/03/09 03:10:00OK, 77572 bytes28 seconds 2004/03/09 03:15:00OK, 77572 bytes29 seconds 2004/03/09 03:20:00OK, 77572 bytes33 seconds 2004/03/09 03:25:00OK, 77572 bytes64 seconds 2004/03/09 03:30:01OK, 77532 bytes27 seconds 2004/03/09 03:35:01OK, 77526 bytes25 seconds 2004/03/09 03:40:01OK, 77532 bytes114 seconds 2004/03/09 03:45:01OK, 77532 bytes27 seconds 2004/03/09 03:50:00OK, 77526 bytes79 seconds 2004/03/09 03:55:00OK, 77532 bytes26 seconds 2004/03/09 04:00:01OK, 77532 bytes27 seconds 2004/03/09 04:05:00OK, 77526 bytes26 seconds 2004/03/09 04:10:00OK, 77526 bytes26 seconds 2004/03/09 04:15:00OK, 77526 bytes26 seconds 2004/03/09 04:20:00OK, 77532 bytes33 seconds 2004/03/09 04:25:00OK, 77532 bytes26 seconds 2004/03/09 04:30:01OK, 77526 bytes167 seconds 2004/03/09 04:35:01OK, 77532 bytes26 seconds 2004/03/09 04:40:00OK, 77526 bytes29 seconds 2004/03/09 04:45:00OK, 77532 bytes28 seconds 2004/03/09 04:50:00OK, 77526 bytes26 seconds 2004/03/09 04:55:01OK, 77526 bytes25 seconds 2004/03/09 05:00:00OK, 77532 bytes28 seconds 2004/03/09 05:05:00OK, 77526 bytes28 seconds 2004/03/09 05:10:00OK, 77526 bytes27 seconds 2004/03/09 05:15:00OK, 77526 bytes28 seconds 2004/03/09 05:20:00OK, 77532 bytes26 seconds 2004/03/09 05:25:01OK, 77526 bytes27 seconds 2004/03/09 05:30:00OK, 77532 bytes27 seconds 2004/03/09 05:35:01OK, 77526 bytes25 seconds 2004/03/09 05:40:00OK, 77526 bytes26 seconds 2004/03/09 05:45:01OK, 77526 bytes26 seconds 2004/03/09 05:50:01OK, 77526 bytes26 seconds 2004/03/09 05:55:00OK, 77526 bytes28 seconds 2004/03/09 06:00:01OK, 77532 bytes27 seconds 2004/03/09 06:05:01OK, 77526 bytes28 seconds 2004/03/09 06:10:00OK, 77532 bytes30 seconds 2004/03/09 06:15:00OK, 77532 bytes30 seconds 2004/03/09 06:20:01OK, 77532 bytes27 seconds 2004/03/09 06:25:00OK, 77526 bytes27 seconds 2004/03/09 06:30:00OK, 77526 bytes27 seconds 2004/03/09 06:35:00OK, 77526 bytes31 seconds 2004/03/09 06:40:00OK, 77532 bytes27 seconds 2004/03/09 06:45:00OK, 77526 bytes26 seconds 2004/03/09 06:50:01OK, 77526 bytes29 seconds 2004/03/09 06:55:00OK, 77526 bytes28 seconds 2004/03/09 07:00:00OK, 77532 bytes28 seconds 2004/03/09 07:05:00OK, 77526 bytes1 seconds -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql timed actions... Confused
Here is a log of query times I made when a certain page is loaded that uses php and mysql, does mysql 4 do some sort of scheduled maintenance I am not aware of? 2004/03/08 16:11:27OK, 77569 bytes0 seconds 2004/03/08 16:11:37OK, 77575 bytes0 seconds 2004/03/08 16:13:31OK, 77569 bytes1 seconds 2004/03/08 16:13:49OK, 77575 bytes1 seconds 2004/03/08 16:14:51OK, 77575 bytes1 seconds 2004/03/08 16:17:00OK, 77569 bytes1 seconds 2004/03/08 16:20:01OK, 77575 bytes0 seconds 2004/03/08 16:25:01OK, 77569 bytes1 seconds 2004/03/08 16:30:00OK, 77569 bytes1 seconds 2004/03/08 16:35:00OK, 77575 bytes1 seconds 2004/03/08 16:40:00OK, 77575 bytes0 seconds 2004/03/08 16:45:01OK, 77569 bytes0 seconds 2004/03/08 16:50:01OK, 77569 bytes0 seconds 2004/03/08 16:55:01OK, 77569 bytes1 seconds 2004/03/08 17:00:00OK, 77588 bytes1 seconds 2004/03/08 17:05:01OK, 77601 bytes3 seconds 2004/03/08 17:10:00OK, 77601 bytes1 seconds 2004/03/08 17:15:00OK, 77601 bytes1 seconds 2004/03/08 17:20:01OK, 77595 bytes0 seconds 2004/03/08 17:25:01OK, 77595 bytes1 seconds 2004/03/08 17:30:01OK, 77577 bytes1 seconds 2004/03/08 17:35:00OK, 77577 bytes1 seconds 2004/03/08 17:40:00OK, 77577 bytes1 seconds 2004/03/08 17:45:00OK, 77577 bytes1 seconds 2004/03/08 17:50:00OK, 77565 bytes1 seconds 2004/03/08 17:55:00OK, 77577 bytes1 seconds 2004/03/08 18:00:00OK, 77577 bytes1 seconds 2004/03/08 18:05:00OK, 77577 bytes1 seconds 2004/03/08 18:10:00OK, 77571 bytes1 seconds 2004/03/08 18:15:00OK, 77571 bytes1 seconds 2004/03/08 18:20:00OK, 77577 bytes2 seconds 2004/03/08 18:25:00OK, 77571 bytes1 seconds 2004/03/08 18:30:00OK, 77571 bytes1 seconds 2004/03/08 18:35:00OK, 77589 bytes1 seconds 2004/03/08 18:40:00OK, 77601 bytes1 seconds 2004/03/08 18:45:00OK, 77595 bytes2 seconds 2004/03/08 18:50:00OK, 77595 bytes1 seconds 2004/03/08 18:55:00OK, 77583 bytes1 seconds 2004/03/08 19:00:01OK, 77601 bytes1 seconds 2004/03/08 19:05:00OK, 77595 bytes1 seconds 2004/03/08 19:10:01OK, 77601 bytes1 seconds 2004/03/08 19:15:00OK, 77595 bytes1 seconds 2004/03/08 19:20:00OK, 77583 bytes1 seconds 2004/03/08 19:25:00OK, 77589 bytes1 seconds 2004/03/08 19:30:00OK, 77595 bytes1 seconds 2004/03/08 19:35:00OK, 77595 bytes1 seconds 2004/03/08 19:40:00OK, 77601 bytes1 seconds 2004/03/08 19:45:00OK, 77589 bytes1 seconds 2004/03/08 19:50:01OK, 77601 bytes1 seconds 2004/03/08 19:55:00OK, 77583 bytes2 seconds 2004/03/08 20:00:00OK, 77601 bytes1 seconds 2004/03/08 20:05:00OK, 77595 bytes1 seconds 2004/03/08 20:10:00OK, 77595 bytes1 seconds 2004/03/08 20:15:00OK, 77595 bytes1 seconds 2004/03/08 20:20:00OK, 77595 bytes0 seconds 2004/03/08 20:25:01OK, 77601 bytes0 seconds 2004/03/08 20:30:01OK, 77601 bytes1 seconds 2004/03/08 20:35:00OK, 77601 bytes1 seconds 2004/03/08 20:40:00OK, 77601 bytes1 seconds 2004/03/08 20:45:00OK, 77601 bytes1 seconds 2004/03/08 20:50:01OK, 77601 bytes0 seconds 2004/03/08 20:55:01OK, 77601 bytes0 seconds 2004/03/08 21:00:00OK, 77601 bytes1 seconds 2004/03/08 21:05:00OK, 77595 bytes1 seconds 2004/03/08 21:10:00OK, 77601 bytes1 seconds 2004/03/08 21:15:00OK, 77601 bytes1 seconds 2004/03/08 21:20:00OK, 77595 bytes1 seconds 2004/03/08 21:25:00OK, 77595 bytes1 seconds 2004/03/08 21:30:00OK, 77601 bytes2 seconds 2004/03/08 21:35:00OK, 77595 bytes1 seconds 2004/03/08 21:40:00OK, 77601 bytes1 seconds 2004/03/08 21:45:00OK, 77601 bytes0 seconds 2004/03/08 21:50:00OK, 77601 bytes1 seconds 2004/03/08 21:55:00OK, 77595 bytes1 seconds 2004/03/08 22:00:00OK, 77595 bytes1 seconds 2004/03/08 22:05:00OK, 77601 bytes1 seconds 2004/03/08 22:10:00OK, 77595 bytes1 seconds 2004/03/08 22:15:00OK, 77595 bytes1 seconds 2004/03/08 22:20:00OK, 77601 bytes1 seconds 2004/03/08 22:25:00OK, 77601 bytes1 seconds 2004/03/08 22:30:01OK, 77589 bytes2 seconds 2004/03/08 22:35:00OK, 77601 bytes1 seconds 2004/03/08 22:40:00OK, 77601 bytes1 seconds 2004/03/08 22:45:00OK, 77601 bytes1 seconds 2004/03/08 22:50:00OK, 77601 bytes1 seconds 2004/03/08 22:55:00OK, 77595 bytes1 seconds 2004/03/08 23:00:00OK, 77601 bytes1 seconds 2004/03/08 23:05:00OK, 77595 bytes1 seconds 2004/03/08 23:10:00OK, 77595 bytes1 seconds 2004/03/08 23:15:00OK, 77589 bytes0 seconds 2004/03/08 23:20:01OK, 77601 bytes0 seconds 2004/03/08 23:25:01OK, 77601 bytes0 seconds 2004/03/08 23:30:00OK, 77589 bytes1 seconds
Newbie Confused with Update involving Multiple Tables
I am truly frustrated with what should be a simple update. Any help to enlighten this newbie is appreciated. [TABLE A] tdmr_dmr dmr_customer_code dmr_job_number_code [TABLE B] tjl_job_list jl_customer_code jl_jobnumber In Table A, the dmr_customer_code field is empty. So I need to populate with jl_customer_code data from Table B.into dmr_customer_code in Table Afor matching job numbers. I thought the command would simply be as follows: UPDATE tdmr_dmr, tjl_job_list SET tdmr_dmr.dmr_customer_code = tjl_job_list.jl_customer_code WHERE tdmr_dmr.dmr_job_number_code = tjl_job_list.jl_jobnumber But I keep getting this error from my admin tool, although I can't figure out what is wrong. You have an error in your SQL syntax near ' tjl_job_list SET tdmr_dmr.dmr_customer_code = tjl_job_list.jl_customer_cod' at line 1 Any help is deeply appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Newbie Confused with Update involving Multiple Tables
http://www.mysql.com/doc/en/UPDATE.html I believe multi-table updates weren't supported until MySQL version 4.04. Are you using a version earlier than this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Newbie Confused with Update involving Multiple Tables
Thanks for the reply!! IndeedI am using version 3.23.58. I can stop pounding my head against the wall now. Thanks again!! *** REPLY SEPARATOR *** On 2/19/2004 at 2:10 AM Jeremy March wrote: http://www.mysql.com/doc/en/UPDATE.html I believe multi-table updates weren't supported until MySQL version 4.04. Are you using a version earlier than this? -- 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]
confused newbie -- Open Office 1.1 as front end
Hi guys n gals OK MySQL is nice and robust, i'm stiil new to it and have an M$ Access mentality when it come to buiding and working with DBs. Can i build an example_client table and an example_appointment table in MySQL and then use M$ Access(odbc) or data sources in Open Office to create the relationships and forms and things to make the DBs usable to the average person? I haven't found a straight answer yet or even a a clear cut guide. All advice is GREATLY APPRECIATED(including what i can't do with this method)!! Thanks folks --- Warren Stanley Information Technology Support Officer Bidgerdii Community Health Rockhampton Q 4700 ---
RE: confused newbie -- Open Office 1.1 as front end
Warren: Yes and no. You can use Access as a front end to MySQL, including creating forms and queries. If you want relational integrity you'll need to handle that directly in MySQL (with raw SQL) or with a dedicated MySQL designer tool (there are several available, both freeware and commercial - google will find them for you or check the archives). Access can only manage relationships in actual Access databases. I'm not familiar with Open Office, but based on things I've read on the list (again, search the archives) I think you can do at least some of what you want with it. It's important to understand that working with MySQL and Access is *not* like working with Access alone. It's much more like working with Access and MS-SQL Server without .ADP files. You'll use either linked tables (low effort, low performance) or pass-through queries (more effort, better performance). Someday soon (hopefully ;-) you'll be able to use stored procedures. Hope this helps, John Hopkins Hopkins IT -Original Message- From: Warren Stanley [mailto:[EMAIL PROTECTED] Sent: Sunday, October 05, 2003 4:48 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: confused newbie -- Open Office 1.1 as front end Hi guys n gals OK MySQL is nice and robust, i'm stiil new to it and have an M$ Access mentality when it come to buiding and working with DBs. Can i build an example_client table and an example_appointment table in MySQL and then use M$ Access(odbc) or data sources in Open Office to create the relationships and forms and things to make the DBs usable to the average person? I haven't found a straight answer yet or even a a clear cut guide. All advice is GREATLY APPRECIATED(including what i can't do with this method)!! Thanks folks --- Warren Stanley Information Technology Support Officer Bidgerdii Community Health Rockhampton Q 4700 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL] Confused about DATETIME compare/subtraction
Hello All, I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds). Any help? FPM
Re: [MYSQL] Confused about DATETIME compare/subtraction
PAUL MENARD wrote: Hello All, I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds). Any help? FPM I don't really know if timestamps can be added or substracted. But I still found a way to help you. SELECT UNIX_TIMESTAMP(first_stamp)-UNIX_TIMESTAMP(second_stamp) AS difference FROM table_name WHERE your_join_here HAVING difference=900; I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds) I'm assuming, you were trying to mean that you DON'T values less than 900 seconds. If you meant the opposite, please switch the greater than sign. -- No, but he says that all Gods are good :w _ Meet the guy at http://www.meetRajesh.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? yes, you use a WHERE if you want all, dont use this WHERE! with your WEHRE you get only this news_category which have a news with the id 9 or no news at all -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
You have not shown us anything that would indicate that your output is not correct. If you think something is missing you have to show us what is missing, and why you think it should not be. John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? Cheers John Wards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problem, Confused by Left Join.
I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? Cheers John Wards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused about MyISAM vs InnoDB tabel types
Can anyone either summarize for me a comparison between the MyISAM and InnoDB MySQL table type? I am getting ready to upgrade from MySQL 3.23.42 to 4.0.13 in the coming week and started reading the upgrade documents on the www.mysql.com site. Never had even thought about using another table type since my current database seems to work fine. But thought I would ask.
Confused about network traffic on mysql port
I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms s 1460,sackOK,timestamp 420171046 7876889,nop,wscale 0 (DF) 14:33:46.156126 eth1 herzegbol.mysql shelbyville.2311: S 703613196:703613196(0) ack 1969309172 win 5792 ms s 1460,sackOK,timestamp 420171073 7876916,nop,wscale 0 (DF) 14:33:47.010646 eth1 herzegbol.mysql shelbyville.2345: S 697677373:697677373(0) ack 2546308254 win 5792 ms s 1460,sackOK,timestamp 420171158 7877001,nop,wscale 0 (DF) 14:33:47.246107 eth1 herzegbol.mysql shelbyville.2304: S 705352284:705352284(0) ack 1841862906 win 5792 ms s 1460,sackOK,timestamp 420171182 7877025,nop,wscale 0 (DF) This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 herzegbol.mysql shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 herzegbol.mysql shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 herzegbol.mysql shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 herzegbol.mysql shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 herzegbol.mysql shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about network traffic on mysql port
On Thu, Mar 27, 2003 at 02:28:37PM -0500, Gary Huntress wrote: I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville [snip] This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 herzegbol.mysql shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 herzegbol.mysql shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 herzegbol.mysql shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 herzegbol.mysql shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 herzegbol.mysql shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server. As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. That's really odd. Are you *sure* it actually shuts down? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 52 days, processed 1,799,891,638 queries (397/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Confused about network traffic on mysql port
Somebody got control of mysql, or your rooted? -Original Message- From: Gary Huntress [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:29 PM To: [EMAIL PROTECTED] Subject: Confused about network traffic on mysql port I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms s 1460,sackOK,timestamp 420171046 7876889,nop,wscale 0 (DF) 14:33:46.156126 eth1 herzegbol.mysql shelbyville.2311: S 703613196:703613196(0) ack 1969309172 win 5792 ms s 1460,sackOK,timestamp 420171073 7876916,nop,wscale 0 (DF) 14:33:47.010646 eth1 herzegbol.mysql shelbyville.2345: S 697677373:697677373(0) ack 2546308254 win 5792 ms s 1460,sackOK,timestamp 420171158 7877001,nop,wscale 0 (DF) 14:33:47.246107 eth1 herzegbol.mysql shelbyville.2304: S 705352284:705352284(0) ack 1841862906 win 5792 ms s 1460,sackOK,timestamp 420171182 7877025,nop,wscale 0 (DF) This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 herzegbol.mysql shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 herzegbol.mysql shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 herzegbol.mysql shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 herzegbol.mysql shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 herzegbol.mysql shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- 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: Confused about network traffic on mysql port
I've considered that. But there are no indications that is the case, I sniff traffic to that box from my firewall (that could be compromised too of course) and I see nothing suspicious. The only traffic on that box is on the mysql port. Since I see this traffic on the mysql port when the server is running I must assume that it is mysqld who owns the port. I am currently investigating the other comments that I have received. Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: GERST, MICHAEL (SBCSI) [EMAIL PROTECTED] To: 'Gary Huntress' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:44 PM Subject: RE: Confused about network traffic on mysql port Somebody got control of mysql, or your rooted? -Original Message- From: Gary Huntress [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:29 PM To: [EMAIL PROTECTED] Subject: Confused about network traffic on mysql port I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms s 1460,sackOK,timestamp 420171046 7876889,nop,wscale 0 (DF) 14:33:46.156126 eth1 herzegbol.mysql shelbyville.2311: S 703613196:703613196(0) ack 1969309172 win 5792 ms s 1460,sackOK,timestamp 420171073 7876916,nop,wscale 0 (DF) 14:33:47.010646 eth1 herzegbol.mysql shelbyville.2345: S 697677373:697677373(0) ack 2546308254 win 5792 ms s 1460,sackOK,timestamp 420171158 7877001,nop,wscale 0 (DF) 14:33:47.246107 eth1 herzegbol.mysql shelbyville.2304: S 705352284:705352284(0) ack 1841862906 win 5792 ms s 1460,sackOK,timestamp 420171182 7877025,nop,wscale 0 (DF) This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 herzegbol.mysql shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 herzegbol.mysql shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 herzegbol.mysql shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 herzegbol.mysql shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 herzegbol.mysql shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- 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: Confused about network traffic on mysql port
- Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Gary Huntress [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:33 PM Subject: Re: Confused about network traffic on mysql port I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms [snip] This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) [snip] The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. The shelbyville box (192.168.0.2) never ever connects to the Herzegbol (192.168.0.32) MySQL server, shelbyville does not even have a mysql ODBC driver installed. All connections are either from external users (port forwarded through firewall to herzegbol) or from the apache/php web pages (on 192.168.0.1). To add to the confusion, I just checked the host table on herzegbol and there isn't even an entry there for shelbyville so I don't even know how herzegbol even knows there is a box to TRY and connect to! (for those wondering why you then see the host shelbyville show up in the tcpdump above, it is done from a different host) Unless I discover something else, I'm going to assume this is not a mysql problem. Thanks for the help. Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org Do you experience a lot of connection aborts on the client end? Maybe the server's periodically trying to ascertain whether the client end of connections are still alive after a timeout period or something. (Just a guess, probably a poor one.) -- 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: Confused about network traffic on mysql port
Hi, On Thu, Mar 27, 2003 at 02:28:37PM -0500, Gary Huntress wrote: I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms s 1460,sackOK,timestamp 420171046 7876889,nop,wscale 0 (DF) 14:33:46.156126 eth1 herzegbol.mysql shelbyville.2311: S 703613196:703613196(0) ack 1969309172 win 5792 ms s 1460,sackOK,timestamp 420171073 7876916,nop,wscale 0 (DF) 14:33:47.010646 eth1 herzegbol.mysql shelbyville.2345: S 697677373:697677373(0) ack 2546308254 win 5792 ms s 1460,sackOK,timestamp 420171158 7877001,nop,wscale 0 (DF) 14:33:47.246107 eth1 herzegbol.mysql shelbyville.2304: S 705352284:705352284(0) ack 1841862906 win 5792 ms s 1460,sackOK,timestamp 420171182 7877025,nop,wscale 0 (DF) These look like reply packets (SYN-ACK) to a port open request (SYN) sent from shelbyville. In this case a confirmation that the port was opened succesfully. This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 herzegbol.mysql shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 herzegbol.mysql shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 herzegbol.mysql shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 herzegbol.mysql shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 herzegbol.mysql shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) These look like reply packets (RST) to a port open request (SYN) sent from shelbyville. In this case a notification that nothing is listening on the port. The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. My guess is that you're showing only packets sent by herzegbol and not packets received by herzegbol and that shelbyville is really trying to connect. What is your tcpdump line and what do you see when you connect manually from shelbyville to herzegbol? (i.e. telnet herzegbol 3306) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about network traffic on mysql port
Hi, On Thu, Mar 27, 2003 at 05:35:22PM -0500, Gary Huntress wrote: I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms [snip] This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) [snip] The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. [snip] :) Unless I discover something else, I'm going to assume this is not a mysql problem. This all happens at the level of the TCP stack, so MySQL couldn't be involved I guess. Could you try running tcpdump -n to make sure the DNS or host tables aren't goofed up? TCP stacks don't just reply to unsent packets. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused of using the Correct DataBase
Hi There, In my ongoing project one of my application is building reports and Printing them from a Remote access Machine.. Actually i have done the whole project in ASP, IIS, Access-2000. but the problem is IIS has Restricted number of users(10) on WIN 2000 prof. So, instead of buying an MS Server I have chosen to shift my whole project to LINUX, PHP, MYSQL. I am planing to transform each .ASP file into .PHP file. But I am very much worried about building an application for designing Reports for my database . Is there no way that i can do this without using 3rd party report Building Tool (Crystal Reports) or cann we get the Free Report BUilding Tools?? Or else if i use Oracle in stead of MYSQL will it be easy to Generate Reports and Print them from the remote machine(i.e using out of process components)... Which database is easy for doing this??? Thanks for all your Sujjestions?? I really appreciate your help. varun. __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Confused, discombobulated, weirded out, upset! --Windows standalone user
I seem to have a version problem. I'm using v. 3.23.51 on a Windows standalone system -- there is no network here. *ALL I want* is to run MySQL standalone on this machine. So do I need to start the server? MySQL seems to mostly work fine if I just go and do: C:\mysql\bin mysql without starting the server. The manual's Windows directions seem to assume a network situation, which is not my case. Also of concern is that when I inquire about version as follows I get for Win95/Win98 although I'm using Win XP (Home). C:\mysql\binmysql --version mysql Ver 11.18 Distrib 3.23.51, for Win95/Win98 (i32) C:\mysql\binmysqld --version mysqld Ver 3.23.51-max-debug for Win95/Win98 on i32 One thing that's currently NOT working is the following command: LOAD DATA LOCAL INFILE C:\\MO10.txt INTO TABLE stud1 fields terminated by / lines terminated by '\r\n'; That produces: ERROR 1148 at line 1: The used command is not allowed with this MySQL version. That's what's making me wonder about version issues. I've checked the manual and see no other reason for that message. Also, per advice here, I tried putting local-infile=1 in the [mysqld] and [mysql] sections of my.cnf(my.ini), but that does not change the behavior. Help would be appreciated! Uriel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused, discombobulated, weirded out, upset! --Windowsstandalone user
At 23:37 +0800 9/10/02, Uriel Wittenberg wrote: I seem to have a version problem. I'm using v. 3.23.51 on a Windows standalone system -- there is no network here. *ALL I want* is to run MySQL standalone on this machine. So do I need to start the server? MySQL seems to mostly work fine if I just go and do: C:\mysql\bin mysql without starting the server. If mysql works fine, then you've already started the server. Yes, you need a server. It can run on the same machine as the client programs, though, which is your situation. The manual's Windows directions seem to assume a network situation, which is not my case. Also of concern is that when I inquire about version as follows I get for Win95/Win98 although I'm using Win XP (Home). C:\mysql\binmysql --version mysql Ver 11.18 Distrib 3.23.51, for Win95/Win98 (i32) C:\mysql\binmysqld --version mysqld Ver 3.23.51-max-debug for Win95/Win98 on i32 One thing that's currently NOT working is the following command: LOAD DATA LOCAL INFILE C:\\MO10.txt INTO TABLE stud1 fields terminated by / lines terminated by '\r\n'; That produces: ERROR 1148 at line 1: The used command is not allowed with this MySQL version. That's what's making me wonder about version issues. I've checked the manual and see no other reason for that message. Also, per advice here, I tried putting local-infile=1 in the [mysqld] and [mysql] sections of my.cnf(my.ini), but that does not change the behavior. You have to restart the server before it will notice the [mysqld] option group change. Help would be appreciated! Uriel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused, discombobulated, weirded out, upset! --Windows standalone user
Sorry! I made a mistake here. I still have the questions below but my problem with LOAD DATA is SOLVED! I made an editing mistake when updating the my.cnf(my.ini) files. - Original Message - From: Uriel Wittenberg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 10, 2002 11:37 PM Subject: Confused, discombobulated, weirded out, upset! --Windows standalone user I seem to have a version problem. I'm using v. 3.23.51 on a Windows standalone system -- there is no network here. *ALL I want* is to run MySQL standalone on this machine. So do I need to start the server? MySQL seems to mostly work fine if I just go and do: C:\mysql\bin mysql without starting the server. The manual's Windows directions seem to assume a network situation, which is not my case. Also of concern is that when I inquire about version as follows I get for Win95/Win98 although I'm using Win XP (Home). C:\mysql\binmysql --version mysql Ver 11.18 Distrib 3.23.51, for Win95/Win98 (i32) C:\mysql\binmysqld --version mysqld Ver 3.23.51-max-debug for Win95/Win98 on i32 [.] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused, discombobulated, weirded out, upset! --Windows standalone user
If mysql works fine, then you've already started the server. Then does a normal Windows installation set it up so the server autostarts whenever you boot up? I did not manually start the server. You have to restart the server before it will notice the [mysqld] option group change. After I added local-infile=1 in the [mysqld] and [mysql] sections, the LOAD DATA LOCAL INFILE command started working. I did not do anything to shut down and restart the server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused, discombobulated, weirded out, upset! --Windowsstandalone user
At 23:52 +0800 9/10/02, Uriel Wittenberg wrote: If mysql works fine, then you've already started the server. Then does a normal Windows installation set it up so the server autostarts whenever you boot up? I did not manually start the server. It might be installed as a service. You have to restart the server before it will notice the [mysqld] option group change. After I added local-infile=1 in the [mysqld] and [mysql] sections, the LOAD DATA LOCAL INFILE command started working. I did not do anything to shut down and restart the server. I don't understand. In your previous message, you said: At 23:37 +0800 9/10/02, Uriel Wittenberg wrote: Also, per advice here, I tried putting local-infile=1 in the [mysqld] and [mysql] sections of my.cnf(my.ini), but that does not change the behavior. Which seems to contradict what you're saying now. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transaction Question CONFUSED
I am confused. (innodb table type) Client 1. starts transaction selects balance from table where id=1 for update update table set balance=100 #At this point from what i have read the balance could be selected by anther user. committ I have read that a select balance from table where id=1 lock in share mode will wait for the committ statement, but client 2 would be wanting to update the balance the same way client 1 does sO i do not see how client 2 could use the lock in share mode because the script is the same for client 2 as it is in client one. so how would i ensure that client 2 waits for client 1 to committ before processing their select and update? Randy sql,quary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Transaction Question CONFUSED
On Tuesday 20 August 2002 01:26 pm, Randy Johnson wrote: I am confused. (innodb table type) I'm really not so sure about the 'lock in share mode' thing, but to the best of my knowledge if you do a SET TRANSACTION_ISOLATION_LEVEL=SERIALIZABLE and then start a transaction where you read data from a row and then update it, there is an absolute guarantee (if the database properly honors the isolation level) that no two transactions can act in such a fashion that either one interferes with the other. In practical terms that means that the same code run from client 2 will block as soon as it attempts the read until transaction started in client 1 is 100% complete. Now, there may be more efficient ways to get this result. InnoDB uses multi-versioning and that has some subtle effects on transactions and concurrency. A larger question however is this, why do you care about reading the old balance? If you aren't going to use it to calculate the new one, then its irrelevant... In other words the scenario you outline reduces (as far as the db is concerned) to just update table set balance=100 and since that is an atomic operation it requires no transaction. In fact in theory ACID never requires a transaction for any operation involving only one single row. For instance if you were incrementing the balance by 100 it would STILL be an atomic operation update table set balance=balance+100 It is in fact only when you get to multi-row or multi-table situations where transactions are required. Consider again your example, since no matter what order the 2 operations are performed in the resut is the same (balance is 100) there is no point in caring what sequence occurs, esp since script 1 cannot care if script 2 ever runs or not, and vice versa (or else they'd be one script...). You can satisfy yourself that the same is true for increment, decrement, or ANY other single-row scenario that can possibly be invented. This is in fact a theorem of transactions... Why then were transactions invented? Suppose you had THREE rows you needed to update with a single update statement update table set balance = balance=1 where id =1 or id = 2 or id = 3 NOW you might need a transaction, because it might be a really bad idea for script 2 to come along and do select balance from table where id =1 or id = 2 or id = 3 and end up with the incremented balance for row 1, and the unincremented balances for rows 2 and 3, which is quite possible. In that case running the 1st query in a transaction would in fact be quite necessary. Now you know what keeps db design guys up late at night Client 1. starts transaction selects balance from table where id=1 for update update table set balance=100 #At this point from what i have read the balance could be selected by anther user. committ I have read that a select balance from table where id=1 lock in share mode will wait for the committ statement, but client 2 would be wanting to update the balance the same way client 1 does sO i do not see how client 2 could use the lock in share mode because the script is the same for client 2 as it is in client one. so how would i ensure that client 2 waits for client 1 to committ before processing their select and update? Randy sql,quary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused About JDBC Driver
Shankar Unni wrote: That's right: Do the following in a temp directory: jar xvf mm.mysql-2.0.10-you-must-unjar-me.jar This will create a directory called mm.mysql-2.0.10. Inside that, you'll find a mm.mysql-2.0.10.jar file, which is what you need to put in your classpath (you can move that file wherever you like it). However, I recommend using version 2.0.8 for now, if you use BLOBs a lot - 2.0.10 has a subtle BLOB reading bug that causes queries to throw IOExceptions with certain values. You can get all the old back-versions from http://sourceforge.net/project/showfiles.php?group_id=15923 Or download version 2.0.11 released today which fixes that bug (as far as I can tell): http://prdownloads.sourceforge.net/mmmysql/mm.mysql-2.0.11-you-must-unjar-me .jar -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused About JDBC Driver
[database,sql,query,table] Mark Matthews wrote: Or download version 2.0.11 released today which fixes that bug (as far as I can tell): Absolutely. Quick work, indeed. I've already switched over to 2.0.11 and it's been smooth. Thanks for the fantastic support! -- Shankar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Confused About JDBC Driver
I'm a bit confused here. can some please tell me what is the difference between this two files -- mm.mysql.jdbc-1.2c.tar.gz ( Includes mysql_comp.jar and mysql_uncomp.jar) -- mm.mysql-2.0.10-you-must-unjar-me.jar For JDBC driver to run with mysql which one do I need? I thought you only need .jar file. then whats the mm.mysql.jdbc-1.2c.tar.gz file for. Are they the same files??? thanks so much. --rahad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused About JDBC Driver
At 16:59 -0500 1/25/02, Rahadul Kabir wrote: I'm a bit confused here. can some please tell me what is the difference between this two files -- mm.mysql.jdbc-1.2c.tar.gz ( Includes mysql_comp.jar and mysql_uncomp.jar) -- mm.mysql-2.0.10-you-must-unjar-me.jar For JDBC driver to run with mysql which one do I need? I thought you only need .jar file. then whats the mm.mysql.jdbc-1.2c.tar.gz file for. Are they the same files??? thanks so much. I think that MM.MySQL used to be packaged as a tar file, but not is distributed as a JAR. Use the newer one, you'll be better off. And do as the filename indicates: un-jar it. You'll end up with a directory that contains the actual driver file plus a bunch of other stuff like the source code. Put the driver JAR file (mm.mysql-2.0.10) in your CLASSPATH. If you're using it with Tomcat, put it in the appropriate directory, depending on whether you want your applications, Tomcat, or both to be able to access it. --rahad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused About JDBC Driver
At 16:11 -0600 1/25/02, Paul DuBois wrote: At 16:59 -0500 1/25/02, Rahadul Kabir wrote: I'm a bit confused here. can some please tell me what is the difference between this two files -- mm.mysql.jdbc-1.2c.tar.gz ( Includes mysql_comp.jar and mysql_uncomp.jar) -- mm.mysql-2.0.10-you-must-unjar-me.jar For JDBC driver to run with mysql which one do I need? I thought you only need .jar file. then whats the mm.mysql.jdbc-1.2c.tar.gz file for. Are they the same files??? thanks so much. I think that MM.MySQL used to be packaged as a tar file, but not is Oops: should be ... but NOW is distributed... distributed as a JAR. Use the newer one, you'll be better off. And do as the filename indicates: un-jar it. You'll end up with a directory that contains the actual driver file plus a bunch of other stuff like the source code. Put the driver JAR file (mm.mysql-2.0.10) in your CLASSPATH. If you're using it with Tomcat, put it in the appropriate directory, depending on whether you want your applications, Tomcat, or both to be able to access it. --rahad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused About JDBC Driver
Paul DuBois wrote: I think that MM.MySQL used to be packaged as a tar file, but not is distributed as a JAR. Use the newer one, you'll be better off. And do as the filename indicates: un-jar it. You'll end up with a directory that contains the actual driver file plus a bunch of other stuff like the source code. Put the driver JAR file (mm.mysql-2.0.10) in your CLASSPATH. That's right: Do the following in a temp directory: jar xvf mm.mysql-2.0.10-you-must-unjar-me.jar This will create a directory called mm.mysql-2.0.10. Inside that, you'll find a mm.mysql-2.0.10.jar file, which is what you need to put in your classpath (you can move that file wherever you like it). However, I recommend using version 2.0.8 for now, if you use BLOBs a lot - 2.0.10 has a subtle BLOB reading bug that causes queries to throw IOExceptions with certain values. You can get all the old back-versions from http://sourceforge.net/project/showfiles.php?group_id=15923 -- Shankar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL newbie and confused
Hi, I've just downloaded and install mySQL on my server but only seem able to connect to the server across my network if the server is connected to the internet. Any ideas why and how to stop it. btw I'm running windows. TIA Alex - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Setting User Privilges ? Confused !!
I am pretty new to MySQL, in fact that also covers Linux as well.
I have Mandrake 8.0 which I have installed MySQL using the RPM format.
I plan to write code (python) to access the database to manage what
data it may hold.
The server automatically starts on boot - which is fine.
To be able to do anything with MySQL I have to be logged on as 'root'
If I try to access the database I have created under my normal logon
('pmoscatt') I get the following error message:
ERROR 1044: Access denied for user '@localhost' to database 'MCMaint'
I have read the manual to see how I can get around this but find it
confusing.
Can anyone help me to allow me to access databases under my normal user
account.
Pete
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Setting User Privilges ? Confused !!
By default you have record for anonimous user with any name and no pass in
your access tables, thats lock access any other user from localhost with
password to database. Just delete this anonimous user from mysql access tables
On Wed 29 Aug 2001 13:08, Peter Moscatt wrote:
I am pretty new to MySQL, in fact that also covers Linux as well.
I have Mandrake 8.0 which I have installed MySQL using the RPM format.
I plan to write code (python) to access the database to manage what
data it may hold.
The server automatically starts on boot - which is fine.
To be able to do anything with MySQL I have to be logged on as 'root'
If I try to access the database I have created under my normal logon
('pmoscatt') I get the following error message:
ERROR 1044: Access denied for user '@localhost' to database 'MCMaint'
I have read the manual to see how I can get around this but find it
confusing.
Can anyone help me to allow me to access databases under my normal user
account.
Pete
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Roman Festchook
Network Engineer
ISP ORTA Polesye
http://www.polesye.net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Setting User Privileges ? Confused !!
I have the same problem and have looked all over for the solution. You have
to delete the anonymous user from which table? I would assume the users
table?
-Original Message-
From: Roman Festchook [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 10:05
To: Peter Moscatt; MySQL List
Subject: Re: Setting User Privilges ? Confused !!
By default you have record for anonimous user with any name and no pass in
your access tables, thats lock access any other user from localhost with
password to database. Just delete this anonimous user from mysql access
tables
On Wed 29 Aug 2001 13:08, Peter Moscatt wrote:
I am pretty new to MySQL, in fact that also covers Linux as well.
I have Mandrake 8.0 which I have installed MySQL using the RPM format.
I plan to write code (python) to access the database to manage what
data it may hold.
The server automatically starts on boot - which is fine.
To be able to do anything with MySQL I have to be logged on as 'root'
If I try to access the database I have created under my normal logon
('pmoscatt') I get the following error message:
ERROR 1044: Access denied for user '@localhost' to database 'MCMaint'
I have read the manual to see how I can get around this but find it
confusing.
Can anyone help me to allow me to access databases under my normal user
account.
Pete
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Roman Festchook
Network Engineer
ISP ORTA Polesye
http://www.polesye.net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Confused with GCC
I notice some of you compile with gcc as you C-compiler (CC) and C++-compiler (CXX). Configure generates an error message for me when I try to do this. Can anyone help me with this? Thanks, Terry -- __ Terry Babbey Technical Support Specialist Lambton College, Sarnia, Ontario, Canada __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused with GCC
g++ works in the configure stage, but then when I do the gnumake using g++ I get the following error: g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="\"/usr/local/mysql \"" -DDATADIR="\"/usr/local/mysql/var\"" - DSHAREDIR="\"/usr/local/mysql/share/mysql\""-DHAVE_CONFIG_H -I./../include -I./../regex-I. -I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates -c sql_acl.cc In file included from ../include/global.h:186, from mysql_priv.h:20, from sql_acl.cc:28: /usr/include/alloca.h:71: warning: declaration of `void * alloca(int)' /usr/include/alloca.h:71: warning: conflicts with built-in declaration `void * a lloca(long unsigned int)' sql_acl.cc: In function `int replace_column_table(GRANT_TABLE *, TABLE *, const LEX_USER , ListLEX_COLUMN , const char *, const char *, unsigned int, bool)' : sql_acl.cc:1459: Internal compiler error in `scan_region', at except.c:2566 Please submit a full bug report. See URL:http://www.gnu.org/software/gcc/faq.html#bugreport for instructions. gnumake[3]: *** [sql_acl.o] Error 1 gnumake[3]: Leaving directory `/usr/local/mysql/mysql-3.23.33/sql' gnumake[2]: *** [all-recursive] Error 1 gnumake[2]: Leaving directory `/usr/local/mysql/mysql-3.23.33/sql' gnumake[1]: *** [all-recursive] Error 1 Peter Pentchev wrote: On Fri, Mar 02, 2001 at 11:30:31AM -0500, Terry Babbey wrote: Here is my configure statement and the generated error message ( I am using GCC2.95.2): CC="gcc" CXX="gcc" ./configure --prefix=/usr/local/mysql Have you tried CXX="g++"? The C++ compiler of the GNU Compiler Suite is g++, you know.. G'luck, Peter -- This sentence would be seven words long if it were six words shorter. -- __ Terry Babbey Technical Support Specialist Lambton College, Sarnia, Ontario, Canada __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Confused with GCC
# find /usr -name gcc /usr/local/bin/gcc # echo $PATH /sbin:/usr/sbin:/usr/bin:/usr/ccs/bin:/usr/bin/X11:/usr/local:/usr/local/bin It seems to find gcc for the CC compiler though, and that part of the configure works. "Matthew P. Marino" wrote: OK. Much better. The configure can't find gcc. If it is installed, find it with "find /usr -name gcc -print". This will tell you where gcc is installed, something like /usr/local/bin or /usr/ccs/bin. If you find "gcc" it is probably not in your search list. Meaning, the path to the directory that contains gcc isn't in the $PATH environment variable. Check it by entering "echo $PATH". So, let's say that it's in /usr/local/bin and your $PATH variable doen't contain that path. type in something like; PATH=$PATH:/usr/local/bin export PATH Now; "echo $PATH" to make sure it worked. That should help. !! I strongly suggest you use gnu "make" and use the "--with-low-memory" option. I have a sun UltraSparcII with dual CPU's and 512MB RAM that still couldn't manage to compile the sql_yacc.cc Good luck!! Terry Babbey wrote: Here is my configure statement and the generated error message ( I am using GCC2.95.2): CC="gcc" CXX="gcc" ./configure --prefix=/usr/local/mysql checking whether the C++ compiler (gcc ) works... no configure: error: installation or configuration problem: C++ compiler cannot cre ate executables. "Matthew P. Marino" wrote: We really need to know what the error message text is. Could be anything from a.) you don't actualy have gcc loaded" to z.) sql_yacc.cc which hardly ever compiles right and has nothing to do with gcc. Terry Babbey wrote: I notice some of you compile with gcc as you C-compiler (CC) and C++-compiler (CXX). Configure generates an error message for me when I try to do this. Can anyone help me with this? Thanks, Terry -- __ Terry Babbey Technical Support Specialist Lambton College, Sarnia, Ontario, Canada __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- __ Terry Babbey Technical Support Specialist Lambton College, Sarnia, Ontario, Canada __ -- __ Terry Babbey Technical Support Specialist Lambton College, Sarnia, Ontario, Canada __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
