Re: what is a schema? what is a database?
On Mon, 03 Mar 2008 14:20:58 -0500, Martin Gainty wrote: http://dev.mysql.com/doc/refman/5.0/en/schemata-table.html According to MYSQL doc: A schema is a database That contradicts the following claim (to my reading): A true fully (database, schema, and table) qualified query is exemplified as such: select * from database.schema.table http://en.wikipedia.org/wiki/ Comparison_of_relational_database_management_systems#Databases_vs_Schemas_.28terminology.29 What' I'm familiar with is: SELECT * FROM database.table; That's ok, that makes sense, this is how MySQL does it and is how I've been doing it. Some databases do it differently, apparently. Apparently MySQL lacks this feature, but what feature is it lacking? There's no equivalent to: SELECT * FROM database.schema.table; thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debugging mysql limits
On Thu, 28 Feb 2008 11:19:40 -0500, Phil wrote: I have 50 plus tables lets call them A_USER, B_USER, C_USER etc which I daily refresh with updated (and sometimes new) data. I insert the data into a temporary table using LOAD DATA INFILE. This works great and is very fast. May I ask why you have fifty plus tables with, apparently, the same schema? Why not have one table with an extra column user? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
password for system user
I understand that there's a configuration so that instead of typing: [EMAIL PROTECTED] ~ $ mysql -u root -ppassword that the password (of password) is stored so that whenever this user connects as root the password is automatically passed. Is this possible? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple revision of a record
Hi I am a beginner to mySQL so I hope this is not a too basic question I'd like to be able to track changes made to the attribute of one record, without wanting to duplicate the complete record each time . How an I achieve this? Thanks laurent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Mon, 3 Mar 2008, Daniel Brown wrote:
On Mon, Mar 3, 2008 at 2:46 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
mysql select user,host,password from mysql.user;
+--+--+--+
| user | host | password |
+--+--+--+
| root | localhost| |
| root | dev.jss.com | |
| | dev.jss.com | |
| | localhost| |
+--+--+--+
4 rows in set (0.00 sec)
Okay, I wasn't aware that it's all on the same server. Try this:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE
User='root' AND host='dev.jss.com' LIMIT 1;
FLUSH PRIVILEGES;
do i have to worry about those don't have user name? what are they use
for? should i delete them???
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debugging mysql limits
Just inheritance from an old design that has passed it's limits. I actually have a development version which does just that, but there is a lot of work to convert many php scripts and sql to include the new column. It's some way away from live though, so the problem I outlined still exists. Phil On Tue, Mar 4, 2008 at 4:03 AM, Thufir [EMAIL PROTECTED] wrote: On Thu, 28 Feb 2008 11:19:40 -0500, Phil wrote: I have 50 plus tables lets call them A_USER, B_USER, C_USER etc which I daily refresh with updated (and sometimes new) data. I insert the data into a temporary table using LOAD DATA INFILE. This works great and is very fast. May I ask why you have fifty plus tables with, apparently, the same schema? Why not have one table with an extra column user? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password for system user
You can have a file called .my.cnf in your home directory that stores it. This page outlines it pretty well: http://www.modwest.com/help/kb6-242.html In your case, you would just want to use the password = 'foo' part of it. -Dan On 3/4/08 4:10 AM, Thufir [EMAIL PROTECTED] wrote: I understand that there's a configuration so that instead of typing: [EMAIL PROTECTED] ~ $ mysql -u root -ppassword that the password (of password) is stored so that whenever this user connects as root the password is automatically passed. Is this possible? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
You should definitely consider getting rid of them, otherwise people can log
in to MySQL from any host with no credentials.
They are created during installation by the mysql_install_db script.
This tells you how to remove them:
http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html
-Dan
On 3/4/08 7:23 AM, Hiep Nguyen [EMAIL PROTECTED] wrote:
On Mon, 3 Mar 2008, Daniel Brown wrote:
On Mon, Mar 3, 2008 at 2:46 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
mysql select user,host,password from mysql.user;
+--+--+--+
| user | host | password |
+--+--+--+
| root | localhost| |
| root | dev.jss.com | |
| | dev.jss.com | |
| | localhost| |
+--+--+--+
4 rows in set (0.00 sec)
Okay, I wasn't aware that it's all on the same server. Try this:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE
User='root' AND host='dev.jss.com' LIMIT 1;
FLUSH PRIVILEGES;
do i have to worry about those don't have user name? what are they use
for? should i delete them???
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unnormalize db here is more efficient?
Hi, i hope this is the right place for this basic question. i have a table like this: TABLE elements `id_element` INT UNSIGNED NOT NULL , `name` VARCHAR(100), `date` DATE ... and other table with the comments of every element. TABLE elements_comments `id_element` INT UNSIGNED NOT NULL , `comment` TEXT each element can have a lot of comments, so, what's more efficient, unnormalize the database and keep track of the amount of comments (using a trigger fired when a comment is wrote or deleted) such as TABLE elements `id_element` INT UNSIGNED NOT NULL , `name` VARCHAR(100), `date` DATE, `comments` SMALLINT UNSIGNED ... or count the comments on elements_comments table. I guess the last option could be very slow with lot of rows because mysql has to scan all of them .. and the first option seems to be very fast. can someone help me with that? thanks a lot
Re: change pw
On Tue, 4 Mar 2008, Dan Rogart wrote: You should definitely consider getting rid of them, otherwise people can log in to MySQL from any host with no credentials. They are created during installation by the mysql_install_db script. This tells you how to remove them: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html i followed the instruction and typed: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' mysql DROP USER ''@'localhost'; Query OK, 0 rows affected (0.00 sec) and mysql DROP USER ''@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost' what's wrong here??? t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
That error occurs when the user has already been dropped - so it's good news :). You can check for users with blank user names and/or blank passwords by querying the mysql.user table: select user,host,password from mysql.user where user = '' or password = ''; Those are the users you should consider dropping or assigning passwords to. Hope that helps, Dan On 3/4/08 9:57 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: On Tue, 4 Mar 2008, Dan Rogart wrote: You should definitely consider getting rid of them, otherwise people can log in to MySQL from any host with no credentials. They are created during installation by the mysql_install_db script. This tells you how to remove them: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html i followed the instruction and typed: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' mysql DROP USER ''@'localhost'; Query OK, 0 rows affected (0.00 sec) and mysql DROP USER ''@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost' what's wrong here??? t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
got it. thanks. t. hiep On Tue, 4 Mar 2008, Dan Rogart wrote: That error occurs when the user has already been dropped - so it's good news :). You can check for users with blank user names and/or blank passwords by querying the mysql.user table: select user,host,password from mysql.user where user = '' or password = ''; Those are the users you should consider dropping or assigning passwords to. Hope that helps, Dan On 3/4/08 9:57 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: On Tue, 4 Mar 2008, Dan Rogart wrote: You should definitely consider getting rid of them, otherwise people can log in to MySQL from any host with no credentials. They are created during installation by the mysql_install_db script. This tells you how to remove them: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html i followed the instruction and typed: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' mysql DROP USER ''@'localhost'; Query OK, 0 rows affected (0.00 sec) and mysql DROP USER ''@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost' what's wrong here??? t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Re: what is a schema? what is a database?
My experience (Oracle, PostgreSQL, MySQL, SQL Server) is that every DBMS is different in this regard. Microsoft's SQL Server works like this: A SQL Server instance (server) can have many databases. A database can have many schemas, schema simply being a grouping for objects in a database. In a SQL Server 2005 database, there can be two tables named Product if one is in the schema Sales and the other is in the schema Manufacture. The two tables are Sales.Product and Manufacture.Product. A fully qualified SQL Server object name is server.database.schema.object. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Thufir Sent: Tuesday, March 04, 2008 12:58 AM To: mysql@lists.mysql.com Subject: Re: what is a schema? what is a database? On Mon, 03 Mar 2008 14:20:58 -0500, Martin Gainty wrote: http://dev.mysql.com/doc/refman/5.0/en/schemata-table.html According to MYSQL doc: A schema is a database That contradicts the following claim (to my reading): A true fully (database, schema, and table) qualified query is exemplified as such: select * from database.schema.table http://en.wikipedia.org/wiki/ Comparison_of_relational_database_management_systems#Databases_vs_Schema s_.28terminology.29 What' I'm familiar with is: SELECT * FROM database.table; That's ok, that makes sense, this is how MySQL does it and is how I've been doing it. Some databases do it differently, apparently. Apparently MySQL lacks this feature, but what feature is it lacking? There's no equivalent to: SELECT * FROM database.schema.table; thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on March 6
Hi, this Thursday, Alexander Barkov will give a MySQL University session: http://forge.mysql.com/wiki/How_to_Add_a_Collation Please register for this session by filling in your name on the session Wiki page. Registering is not required but appreciated. That Wiki page also contains a section to post questions. Please use it! MySQL University sessions normally start at 13:00 UTC (summer) or 14:00 UTC (winter); see: http://forge.mysql.com/wiki/MySQL_University for more time zone information. Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. Next MySQL University sessions: March 13: Checking Threading and Locking With Helgrind (Stewart Smith) March 20: Building MySQL Client Applications (Hartmut Holzgraefe) March 27: EC2 (Brian Aker) April 3: Checking Performance with Kchachegrind (Stewart Smith) See http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the complete list. -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
At 8:58 AM + 3/4/08, Thufir wrote: On Mon, 03 Mar 2008 14:20:58 -0500, Martin Gainty wrote: http://dev.mysql.com/doc/refman/5.0/en/schemata-table.html According to MYSQL doc: A schema is a database That contradicts the following claim (to my reading): A true fully (database, schema, and table) qualified query is exemplified as such: select * from database.schema.table http://en.wikipedia.org/wiki/ Comparison_of_relational_database_management_systems#Databases_vs_Schemas_.28terminology.29 What' I'm familiar with is: SELECT * FROM database.table; That's ok, that makes sense, this is how MySQL does it and is how I've been doing it. Some databases do it differently, apparently. Apparently MySQL lacks this feature, but what feature is it lacking? There's no equivalent to: SELECT * FROM database.schema.table; In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can be replaced with SCHEMA or SCHEMAS wherever it appears. Examples: CREATE DATABASE = CREATE SCHEMA SHOW DATABASES = SHOW SCHEMAS -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting the last item in a group by query?
There is likely a blindingly obvious solution to this, I need to do a group by expression in my query and get the latest row based on a date field in the same table. Is this even possible, and any tips on how to do that? Example of data and query: --- Table: =(pseudo table based on origional, trimmed)= 'id', 'bigint(20)', '', 'PRI', '', 'auto_increment' 'date', 'datetime', '', '', '-00-00 00:00:00', '' 'serial_number', 'varchar(25)', '', '', '', '' 'pass', 'varchar(6)', '', '', 'false', '' = SELECT t.pass, t.id FROM theTable t group by t.serial_number --- I have tried putting a sort into the query like so: SELECT t.pass, t.id FROM theTable t GROUP BY t.serial_number ORDER BY t.date desc Which naturally only sorts the resulting rows by date.
Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 10:57 AM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
[snip!]
SELECT
t.pass, t.id
FROM
theTable t
GROUP BY
t.serial_number
ORDER BY
t.date desc
Try adding the LIMIT keyword.
SELECT t.pass, t.id FROM theTable t GROUP BY t.serial_number ORDER
BY t.date DESC LIMIT 0,1;
That will give only the latest date.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple revision of a record
Laurent, I'd like to be able to track changes made to the attribute of one record, without wanting to duplicate the complete record each time . How an I achieve this? It often turns out that trying to make a SQL-level audit trail of such changes is more expensive in time and code than a simple implementation of point-in-time architecture (PITA) cf http://www.artfulsoftware.com/infotree/tip.php?id=530, http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch21.pdf). PB - Laurent Cerveau wrote: Hi I am a beginner to mySQL so I hope this is not a too basic question I'd like to be able to track changes made to the attribute of one record, without wanting to duplicate the complete record each time . How an I achieve this? Thanks laurent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 11:24 AM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
Thanks for the reply,
Sorry, should have been more specific on that :).
I need to access the last record by date for each serial_number in the
table (not just latest record)
Okay, this is untested, so I don't know if it will work
Out-Of-The-Box[tm] or not, but it should at least lead you in the
right direction. Plus, it's back on-list now, so that others can read
the results in the archives when they search on the web. ;-)
SELECT t.pass, t.id FROM theTable t WHERE t.serial_number IN
(SELECT DISTINCT serial_number,date FROM theTable ORDER BY date DESC
LIMIT 0,1);
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple revision of a record
The tack we take is to have a separate table that tracks changes. It does, of necessity, contain the same fields as the original record (including the ID of the records being modified). It also contains fields that specify whether the record was deleted or updated, when, and by whom. There is also a text field containing the names of the fields that were changed. We capture the before data, since the original record has the after data. (At least I think we do, this part of the system is rather murky.) If there is only one field that you need to track, that simplifies things a lot. You won't need the list of changed fields, etc. This is probably the only way to do it, even though it is expensive. Our transaction volume is very low, and our database is relatively small. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
RE: Getting the last item in a group by query?
Hmm didn't notice that replies are sent to personal emails :o!
I'll look down that avenue once I've completed the mysql version upgrade
(mysql 4 on my test bench, mysql5 upgrade in progress on the production
server)
Thanks again!
-Original Message-
From: Daniel Brown [mailto:[EMAIL PROTECTED]
Sent: 04 March 2008 16:56
To: Esbach, Brandon; MySQL User Group
Subject: Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 11:24 AM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
Thanks for the reply,
Sorry, should have been more specific on that :).
I need to access the last record by date for each serial_number in
the table (not just latest record)
Okay, this is untested, so I don't know if it will work
Out-Of-The-Box[tm] or not, but it should at least lead you in the right
direction. Plus, it's back on-list now, so that others can read the
results in the archives when they search on the web. ;-)
SELECT t.pass, t.id FROM theTable t WHERE t.serial_number IN (SELECT
DISTINCT serial_number,date FROM theTable ORDER BY date DESC LIMIT 0,1);
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 12:02 PM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
Hmm didn't notice that replies are sent to personal emails :o!
I'll look down that avenue once I've completed the mysql version upgrade
(mysql 4 on my test bench, mysql5 upgrade in progress on the production
server)
Thanks again!
Yeah, a lot of the lists now are configured so that you have to
hit Reply-All to post back to the list. This way, you can have
off-list discussions by simply clicking Reply.
One question are you building a PHP application with that? If
so, subscribe and send a message over to the PHP-DB list. It's
well-monitored by some very talented people, and you can get more
specific answers. http://www.php.net/mailinglists
If not, feel free to ignore this! ;-P
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the last item in a group by query?
Hi, On Tue, Mar 4, 2008 at 10:57 AM, Esbach, Brandon [EMAIL PROTECTED] wrote: There is likely a blindingly obvious solution to this, I need to do a group by expression in my query and get the latest row based on a date field in the same table. Is this even possible, and any tips on how to do that? Sure. Two posts that ought to cover your question: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ http://www.xaprb.com/blog/2007/08/21/how-to-select-the-first-or-last-row-per-group-in-sql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on March 6
Hi, sorry for a second mail on the same subject, but I forgot to mention that Alexander Barkov's MySQL University session http://forge.mysql.com/wiki/How_to_Add_a_Collation will be an IRC-only question-and-answer session. So please visit the session page, look at the presentation uploaded there, and come to the session with a bunch of questions! -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with timestamp and leap seconds?
Howdy -- new to the list.
BigCorp has a Bugzilla database that uses version 4.1.7-standard.
We've been taking backups using mysqldump. I thought to verify a
backup, in essence by
mysqldump bugzilla B
mysql test B
mysqldump test T
diff B T
Everything is the same, except that the timestamps I've looked at are
all 22 or 23 seconds earlier in the test database than in the original
database. E.g., from a line-by-line diff of the mysqldump output:
line 697609, characters 15 on:
...sions VALUES ('val1','PROD1','2005-09-14 15:21:03');
...sions VALUES ('val1','PROD1','2005-09-14 15:21:41');
Specifically, the discrepancy appears to be the number of leap seconds
that were in effect at the point of the timestamp. E.g.,
line 697611, characters 15 on:
...sions VALUES ('val2','PROD2','2006-07-31 11:30:57');
...sions VALUES ('val2','PROD2','2006-07-31 11:30:34');
Since the timestamps are from 2003 on, and
http://en.wikipedia.org/wiki/Leap_seconds says that there's been
only one leap second added (at the end of 2005), and I've looked at
only a few dozen lines of diff, I can't tell for sure that that's it,
but it seems quite likely.
It's not an artifact of mysqldump in particular: select shows the same
thing.
I've tried Googling and man mysqldump, but all I can find is
information on setting up timezone tables in MySQL.
In case it matters, SELECT COUNT(*) FROM mysql.time_zone_name;
returns 0, and
$ mysqladmin variables | grep zone
| system_time_zone| CST |
| time_zone | SYSTEM|
Is there some other configuration information I need to provide?
Any hints?
--
Tim McDaniel, [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with timestamp and leap seconds?
I had a bit of BFOTO and tried simple inserts.
mysql create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| f |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)
How very, very peculiar. Furthermore, the difference is 37 seconds,
but I don't see the significance of that particular value.
More data: Red Hat Linux 9 (as ancient as the Bugzilla). Kernel
2.4.20. I put new timezone info on the system in March of last year,
just before the new US Daylight Saving Time rules went into effect.
(I had wondered whether perhaps there hadn't been leap second info
before then, so timestamps stored in an internal format would have
jumped at the transition, but this example above doesn't have any sort
of transition involved that I can see.)
--
Tim McDaniel, [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
[snip]
I had a bit of BFOTO and tried simple inserts.
mysql create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| f |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)
[/snip]
The column type needs to be DATETIME.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
On Tue, 4 Mar 2008, Jay Blanchard [EMAIL PROTECTED] wrote:
[snip]
I had a bit of BFOTO and tried simple inserts.
mysql create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| f |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)
[/snip]
The column type needs to be DATETIME.
Thank you for pointing me at TIMESTAMP versus DATETIME. I'll read
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html
thoroughly when I can.
Can you give a little more detail as to why DATETIME is necessary?
Glancing briefly at
http://dev.mysql.com/doc/refman/4.1/en/timestamp.html, I see
(emphasis added):
Beginning with MySQL 4.1.3, the default current time zone for each
connection is the server's time. The time zone can be set on a
per-connection basis, as described in Section 9. but are converted
from the current time zone for storage, and converted back to the
current time zone for retrieval. As long as the time zone
setting remains constant, you get back the same value you
store. If you store a TIMESTAMP value, and then change the time
zone and retrieve the value, the retrieved value is different from
the value you stored. This occurs because the same time zone was
not used for conversion in both directions. The current time zone
is available as the value of the time_zone system variable.
In any event, the database has table definitions with columns like
`creation_ts` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
Also, it's a Bugzilla database, and I'm not free to change the
schema.
--
Tim McDaniel, [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
[snip] The column type needs to be DATETIME. Thank you for pointing me at TIMESTAMP versus DATETIME. I'll read http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html thoroughly when I can. Can you give a little more detail as to why DATETIME is necessary? [/snip] It was much too quick a reply on my part but it is my understanding that a TIMESTAMP field is updated according to server time and you cannot actually insert a value. I may be wrong as I have never tested this. On the other hand a DATETIME field accepts inserts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
On Tue, 4 Mar 2008, Jay Blanchard [EMAIL PROTECTED] wrote:
It was much too quick a reply on my part but it is my understanding
that a TIMESTAMP field is updated according to server time and you
cannot actually insert a value. I may be wrong as I have never
tested this.
Even in pre-4.1 versions, per
http://dev.mysql.com/doc/refman/4.1/en/timestamp-pre-4-1.html,
You can set any TIMESTAMP column to a value different from the
current date and time by setting it explicitly to the desired
value. This is true even for the first TIMESTAMP column.
though in those versions it took some work to keep it from updating
automatically. Those limitations have apparently been lifted starting
from 4.1.
Experimentally,
$ mysql ...
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 122433 to server version: 4.1.7-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql create table u (f timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql insert into u values ('2007-02-03 7:08:09');
Query OK, 1 row affected (0.00 sec)
mysql select * from u;
+-+
| f |
+-+
| 2007-02-03 07:08:46 |
+-+
1 row in set (0.00 sec)
TIMESTAMP columns *can* be used to update according to server time,
as explained at
http://dev.mysql.com/doc/refman/4.1/en/timestamp.html, and as shown
above with
default CURRENT_TIMESTAMP
-- but that does it when you set it to NULL via INSERT or UPDATE
on update CURRENT_TIMESTAMP
-- but that does it when you update some other column
without setting this TIMESTAMP column
or, of course, setting it to NOW() or one of its synonyms.
--
Tim McDaniel, [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Importing and exporting from MySQL, escape slash problem
Richard, Jed, Thank you for replying. Richard said: It's possible that there is an .htaccess file in phpMyAdmin that has Magic Quotes on that is messing you up... The .htaccess file for phpMyAdmin says php_flag magic_quotes_gpc Off, so I guess that means I'm okay there. Other than that, it's specific to phpMyAdmin, so maybe ask those guys what they did... I joined their list through Sourceforge, but I haven't seen any mail from it, and any mail I send gets bounced back to me. I'm not sure what the issue is. Jed said: If you're having trouble importing a mysql dump using phpMyAdmin, it might be simpler not to use it, and use mysqldump instead. I suppose I'll have to if I can't get phpMyAdmin to behave. It's too bad, though, as phpMyAdmin is so convenient otherwise. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to select total votes for each comment?
Hey all, I have comments(id,content) and votes(comment_id,vote). vote is a tinyint. I would like to select total votes for each comment, I tried: select content, sum(v.votes) from comments c left join votes v on c.id=v.comment_id but it only returns first result obviously, any idea how I could do this? Thanks in advance, Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL with its data files on a CD-R (recordable CD)
Hi, I have been researching to see if it's possible to have a MySQL database with it's data files on a cd-rom, but could use some help to determine if I have found out the full truth of what's possible. I would appreciate any additional info people have to offer. The situation I've been thinking about involves a MySQL database that is created and populated with data while the data files are in a directory on a regular hard drive. The database server is then shutdown, the data files are burned to a CD-R, and then the CD-R is distributed to another person. The person receiving the CD-R would have their installation of MySQL configured to access the data directly from the CD-R. The person receiving the CD-R would only have read access to the data, of course, unless they copy the data to their own hard drive, but since read access is what is wanted, this would not be a problem. An important factor is that the data is stored on a CD-R and the person receiving the data can use it directly from the CD-R without needing to copy it anywhere else. I have found that using MyISAM as the storage engine appears to work, but was wondering if other storage engines could be used as well. It appears from my attempts that InnoDB, Archive, and CSV cannot be used for the storage engines in this manner, but I thought that I may have overlooked something. Once again, any additional info people have to offer would definitely be appreciated. Thanks.
Only 3 weeks left for PostgreSQL conference
Hello, I know this is a *little* off topic but it is about Open Source databases :) There are only three weeks left to register for the PostgreSQL Community Conference: East! The conference is scheduled on March 29th and 30th (a Saturday and Sunday) at the University of Maryland. Come join us as Bruce Momjian and Joshua Drake have a round table open to any PostgreSQL question from the community. If a round table isn't your cup of tea, visit our talks page where you can pick to learn from over 20 experts in the field. http://www.postgresqlconference.org/talks/ To register just point that old fashion web browser over to: http://www.postgresqlconference.org/ All registrations and sponsorships are donations to PostgreSQL via Software in the Public Interest, Inc., a 501(c)3 non-profit corporation. Thanks again to our Community Conference Sponsors: Organizational Sponsor Command Prompt, Inc. http://www.commandprompt.com/ Silver Sponsor EnterpriseDB http://www.enterprisedb.com Talk Sponsors Afilias http://www.afilias.org/ Continuent http://www.continuent.com/ Sun http://www.sun.com/ Truviso http://www.truviso.com/ Xtuple http://www.xtuple.com/ Meal Sponsors OTG http://www.otg-nc.com/ General Sponsor Emma http://www.myemma.com/ Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit signature.asc Description: PGP signature
Re: Multiple revision of a record
Thanks to all for the answers. I have now some tries to do! Laurent Sent from my iPhone On Mar 4, 2008, at 5:58 PM, Jerry Schwartz [EMAIL PROTECTED] infoshop.com wrote: The tack we take is to have a separate table that tracks changes. It does, of necessity, contain the same fields as the original record (including the ID of the records being modified). It also contains fields that specify whether the record was deleted or updated, when, and by whom. There is also a text field containing the names of the fields that were changed. We capture the before data, since the original record has the after data. (At least I think we do, this part of the system is rather murky.) If there is only one field that you need to track, that simplifies things a lot. You won't need the list of changed fields, etc. This is probably the only way to do it, even though it is expensive. Our transaction volume is very low, and our database is relatively small. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
