Avoiding TIMESTAMP
Hello list I need to solve a little problem but don't mind how, maybe you could suggest something. I have a database which registers payments, records have a AUTO_INCREMENT field to assign a unique consecutive number associated to every payment, some other fields that store the amount, type of payment, etc. and another TIMESTAMP field that stores the date and time. One of the fields contains the username for who received the payment and it is stored automatically by the software, so I can know who processed the payment, when, and all related information... In the first stage of the system it worked only for 'localhost' and a unique test user, so all my current payments have registered that user. Now the system is able to select a host and a user, so I want to update the test user username for a real user, the database has already some records and would not like to reenter them by hand logging in as a real user. My problem is this... if I update the username field, the TIMESTAMP updates the date and time of the payment to the current values and the payment date/time does not coincide then... Is there a way to update only that one field avoiding the TIMESTAMP update? One way is to update both fields, specifying the user and the same date/time by hand, but comes a new problem, payments have different date and time, so I would need to create a small routine to update records one by one with its particular date/time, but maybe there is another way to do it with a single command... Thanks for any comment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Avoiding TIMESTAMP
-Original Message- From: Miguel Cardenas [mailto:[EMAIL PROTECTED] Sent: Saturday, May 26, 2007 8:21 AM To: mysql@lists.mysql.com Subject: Avoiding TIMESTAMP Hello list I need to solve a little problem but don't mind how, maybe you could suggest something. I have a database which registers payments, records have a AUTO_INCREMENT field to assign a unique consecutive number associated to every payment, some other fields that store the amount, type of payment, etc. and another TIMESTAMP field that stores the date and time. One of the fields contains the username for who received the payment and it is stored automatically by the software, so I can know who processed the payment, when, and all related information... In the first stage of the system it worked only for 'localhost' and a unique test user, so all my current payments have registered that user. Now the system is able to select a host and a user, so I want to update the test user username for a real user, the database has already some records and would not like to reenter them by hand logging in as a real user. My problem is this... if I update the username field, the TIMESTAMP updates the date and time of the payment to the current values and the payment date/time does not coincide then... Are you inserting null into the timestamp column, either explicitly or implicitly (like with a trigger)? If I don't specify the timestamp column, as shown below, it leaves it alone. Using MyISAM on 5.0.24-NT, it works as you want. Assuming I've inferred your table definition, that is. drop table if exists payment; create table payment ( id integer not null AUTO_INCREMENT primary key, username varchar(255) not null, amount FIXED(10,2), txtime timestamp not null ); insert into payment (username, amount) values ('tjl', 1234567.89); select * from payment; -- note time -- wait a little while update payment set username='aal'; select * from payment; -- same time... update payment set username='tjl', txtime=null; select * from payment; -- updated time Tim Is there a way to update only that one field avoiding the TIMESTAMP update? One way is to update both fields, specifying the user and the same date/time by hand, but comes a new problem, payments have different date and time, so I would need to create a small routine to update records one by one with its particular date/time, but maybe there is another way to do it with a single command... Thanks for any comment -- 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]
LAST_INSERT_ID LAST_UPDATE_ID
How can I select (retrieve) the last updated cell (field). Basically I need to pull the new information only. I'm using for updating my database: UPDATE table SET column = CONCAT_WS ('column,' . $column.') WHERE column= value; I need to select data: SELECT * FROM table WHERE column=Whatever I found LAST_INSERT_ID but i doesn't work What I want to do is: 1-user has 10 columns (fields). 2- User updated one of these fields using CONCAT_WS(adding new data to previous data). 3- When user views any of his information he sees only the last updated part of the data. So, timestamp/datetime field is not going to work. - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
restore one database.
Hi All, I have take mysqldump of all the databases. If somebody accidently drops one database, can i restore just that database from mysqldump and apply the binary logs to restore all the data to that database. If yes, can you please let me know how that can be done. I am using version 5.0.40 regards anandkl
RE: restore one database.
Hi Ananda, I don't know how to solve this, but if you have space in another storage, try restoring your all-databases dump there and then create a new onlyone-database dump to restore in your server. Regards. Pelle.- -Mensaje original- De: Ananda Kumar [mailto:[EMAIL PROTECTED] Enviado el: Sábado, 26 de Mayo de 2007 10:23 a.m. Para: MySQL General Asunto: restore one database. Hi All, I have take mysqldump of all the databases. If somebody accidently drops one database, can i restore just that database from mysqldump and apply the binary logs to restore all the data to that database. If yes, can you please let me know how that can be done. I am using version 5.0.40 regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.0/819 - Release Date: 26/05/2007 10:47 a.m. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.0/819 - Release Date: 26/05/2007 10:47 a.m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Questions About Indexes
I have a few questions about indexes. I understand (1) what an index is, and (2) why indexes are useful, but I don't have even a rough idea about HOW they work. The internet resources I've been able to find don't answer the questions I'm asking. I also tried cat /var/lib/mysql/srms07/staff.MYI to see if I could glean some information directly from an index file, but the MYI file wasn't human-readable. Q1. What good does it do to store the primary key or a unique key if you're normally SELECTing columns that don't use that primary or unique key? Q2. Does a SELECT statement look at an index before it looks at a table? Q3. Are JOINs where the real timesaving occurs and SELECTs just a peripheral issue muddying the water? Q4. What about non-unique indexes? Is the structure of a non-unique index file similar to the index in the back of a book, the phrase you're searching for plus a list of row numbers (page numbers for a book) where that phrase is found? Q5. Is an item in an index tied to a memory address (like a pointer in C++) where the indexed data appears inside the larger memory area staked out by the table? Q6. As for memory, when you choose a database inside the mysql client, are all the tables within that database read into memory from the hard drive, or just the indexes? Thanks in advance for taking the time to read this, and even more thanks if you take the time to respond to my questions with either an explanatory URL or your words explaining the matter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tellico and MySQL
I'm still searching online, but does anybody know of a script that will input a tellico database into MySQL? -- The NCP Revue -- http://www.ncprevue.com/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]