How To Clean Old Table Data

--000e0cd248268e4967046e0aa9c5
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

I have an application that crates a users email info in MySQL every time
they login into the application. The only problem is once I remove their
account from the application/Linux, their user data remains in the MySQL
table and will eventually clutter the database. I don't know how I should or
can set something up that when I remove someone from the Linux system, I can
also remove their data from MySQL.

mysql> select * from identities;
+-------------+-----+----------+-----------------+---------- ----+-----------------------+----------+-----+------------+- ---------------+---------+
| identity_id | del | standard | name | organization |
email | reply-to | bcc | signature | html_signature |
user_id |
+-------------+-----+----------+-----------------+---------- ----+-----------------------+----------+-----+------------+- ---------------+---------+
| 1 | 0 | 1 | Carlos Williams | |
carlos@ideorlando.org | | | --
Carlos | 0 | 1 |
| 2 | 0 | 1 | carlos | |
carlos@ideorlando.org | | | NULL | 0 |
2 |
| 3 | 0 | 1 | Carlos Williams | |
carlos@iamunix.com | | | Carlos | 0
| 3 |
+-------------+-----+----------+-----------------+---------- ----+-----------------------+----------+-----+------------+- ---------------+---------+
3 rows in set (0.00 sec)


Above I have connected to the database and I can see 3 users that are no
longer present on the server however their user data is still stored in
MySQL. How can I delete the entire row of data in the table.

--000e0cd248268e4967046e0aa9c5--

RE: How To Clean Old Table Data

>-----Original Message-----
>From: Carlos Williams [mailto:carloswill@gmail.com]
>Sent: Monday, July 06, 2009 10:53 AM
>To: mysql@lists.mysql.com
>Subject: How To Clean Old Table Data
>
>I have an application that crates a users email info in MySQL every time
>they login into the application. The only problem is once I remove their
>account from the application/Linux, their user data remains in the MySQL
>table and will eventually clutter the database. I don't know how I
>should or
>can set something up that when I remove someone from the Linux system, I
>can
>also remove their data from MySQL.
>
>mysql> select * from identities;
>+-------------+-----+----------+-----------------+--------- -----+-------
>----------------+----------+-----+------------+------------ ----+--------
>-+
>| identity_id | del | standard | name | organization |
>email | reply-to | bcc | signature | html_signature |
>user_id |
>+-------------+-----+----------+-----------------+--------- -----+-------
>----------------+----------+-----+------------+------------ ----+--------
>-+
>| 1 | 0 | 1 | Carlos Williams | |
>carlos@ideorlando.org | | | --
>Carlos | 0 | 1 |
>| 2 | 0 | 1 | carlos |
>|
>carlos@ideorlando.org | | | NULL | 0 |
>2 |
>| 3 | 0 | 1 | Carlos Williams | |
>carlos@iamunix.com | | | Carlos | 0
>| 3 |
>+-------------+-----+----------+-----------------+--------- -----+-------
>----------------+----------+-----+------------+------------ ----+--------
>-+
>3 rows in set (0.00 sec)
>
>
>Above I have connected to the database and I can see 3 users that are no
>longer present on the server however their user data is still stored in
>MySQL. How can I delete the entire row of data in the table.
[JS] Ah, perhaps I misunderstood your question. The SQL command you want is

DELETE identities WHERE email = 'carlos@ideorlando.org';

You'll have to do that for each email address, of course.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: How To Clean Old Table Data

>-----Original Message-----
>From: OKAN ARI [mailto:okanari@aribem.com]
>Sent: Monday, July 06, 2009 11:39 AM
>To: Jerry Schwartz
>Subject: Re: How To Clean Old Table Data
>
>Thank you Jerry Schwartz and Blog Tieng Viet,
>
>To Viet: Mysql server is up alt lease 1 year, I have setup my.cnf and
>query_cache_size to a best value in this time, I think this setting is
>best
>as I can do.
>
>To [JS]: I understand that you say, memory limits can do this dramatic
>performance problem. In my.cnf there is no configuration about Max
>memory
>usage, this limit takes automatically or with any other setting or
>hardware.
>I am planning to increase the memory to 8GB of Ram, this will solve the
>problem if the reason is memory. I hope thet, I understant you
>correctly,
>for confirmation, what you thnik about memory increase plan for the
>first
>step of optimization.
>
[JS] As I said, I have no direct experience with tuning MySQL. All of the
databases I manage are quite small.

Adding memory is certainly the cheapest thing you can try.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com



>OKAN
>
>----- Original Message -----
>From: "Jerry Schwartz"
>To: "'Carlos Williams'" ;
>Sent: Monday, July 06, 2009 6:20 PM
>Subject: RE: How To Clean Old Table Data
>
>
>>
>>
>>>-----Original Message-----
>>>From: Carlos Williams [mailto:carloswill@gmail.com]
>>>Sent: Monday, July 06, 2009 10:53 AM
>>>To: mysql@lists.mysql.com
>>>Subject: How To Clean Old Table Data
>>>
>>>I have an application that crates a users email info in MySQL every
>time
>>>they login into the application. The only problem is once I remove
>their
>>>account from the application/Linux, their user data remains in the
>MySQL
>>>table and will eventually clutter the database. I don't know how I
>>>should or
>>>can set something up that when I remove someone from the Linux system,
>I
>>>can
>>>also remove their data from MySQL.
>>>
>>>mysql> select * from identities;
>>>+-------------+-----+----------+-----------------+------- -------+-----
>--
>>>----------------+----------+-----+------------+---------- ------+------
>--
>>>-+
>>>| identity_id | del | standard | name | organization |
>>>email | reply-to | bcc | signature | html_signature |
>>>user_id |
>>>+-------------+-----+----------+-----------------+------- -------+-----
>--
>>>----------------+----------+-----+------------+---------- ------+------
>--
>>>-+
>>>| 1 | 0 | 1 | Carlos Williams | |
>>>carlos@ideorlando.org | | | --
>>>Carlos | 0 | 1 |
>>>| 2 | 0 | 1 | carlos |
>>>|
>>>carlos@ideorlando.org | | | NULL | 0 |
>>>2 |
>>>| 3 | 0 | 1 | Carlos Williams | |
>>>carlos@iamunix.com | | | Carlos | 0
>>>| 3 |
>>>+-------------+-----+----------+-----------------+------- -------+-----
>--
>>>----------------+----------+-----+------------+---------- ------+------
>--
>>>-+
>>>3 rows in set (0.00 sec)
>>>
>>>
>>>Above I have connected to the database and I can see 3 users that are
>no
>>>longer present on the server however their user data is still stored
>in
>>>MySQL. How can I delete the entire row of data in the table.
>> [JS] Ah, perhaps I misunderstood your question. The SQL command you
>want
>> is
>>
>> DELETE identities WHERE email = 'carlos@ideorlando.org';
>>
>> You'll have to do that for each email address, of course.
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>http://lists.mysql.com/mysql?unsub=okanari@aribem.com
>>





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org