RE: Missing Entries
-Original Message- From: Neil Aggarwal n...@jammconsulting.com Sent: 10 December 2009 01:19 To: mysql@lists.mysql.com Subject: RE: Missing Entries Carlos: I was checking my fresh install of MySQL and noticed I had empt spaces or missing entries in some sections when I did a search for users on the 'mysql' database. According to this page: http://dev.mysql.com/doc/mysql-security-excerpt/5.4/en/default-privileges.ht ml If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts. We I do a MySQL install, I always go into the mysql client as root and issue these commands: use mysql; delete from user where Password=''; flush privileges; quit; Be careful doing this on a fresh install, if you don't set passwords for root first you will delete all the initial root accounts too! You really should read the installation section of the manual. Your issue is simple because the installation has not been secured. It removes those accounts. Neil === John Daisley MySQL 5.0 Certified Database Administrator (CMDBA) MySQL 5.0 Certified Developer Cognos BI Developer Telephone: +44(0)1283 537111 Mobile: +44(0)7812 451238 Email: john.dais...@butterflysystems.co.uk === Sent via HP IPAQ mobile device -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Hi Eric, At least with Maatkit, you get transparency. We make a concerted effort to update the RISKS section of each tool with each release, so there is full disclosure. Fair enough, but I still found the warnings a little too scary. A more complete explanation of the exact nature of the bugs and the exact circumstances under which I should be concerned about triggering them would have increased my comfort level. I've made a note to review these, because the ones I checked have kind of drifted from their original purity. I updated the RISKS section for mk-table-sync the other day. I checked it and agreed with you -- it didn't distinguish between cases where there is actually a risk, or cases where the tool would just refuse to work (which isn't a risk IMO). And it sounded ambiguously scary in a don't-blame-us, we're-avoiding-your-eyes kind of way because of passive voice. You can see my changes here: http://code.google.com/p/maatkit/source/detail?r=5269 I think that's a pretty realistic balanced statement of risk: you are playing with a powerful tool, so learn how to use it first. Thanks for the feedback! BTW, there's also a Maatkit mailing list that I watch closely: http://groups.google.com/group/maatkit-discuss - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Multiple joins from same table?
Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. - Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint - teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) - SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no - Any help is appreciated. Best Regards, Terry
Re: Multiple joins from same table?
Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. select * from table as t1,table as t2,table as t3 where t1.column1='blah' and t2.column2='blah' and t3.column3='blah' does the same thing as select * from table where column1='blah' and column2='blah' and column3='blah' Does that do what you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Missing Entries
delete from user where Password=''; Be careful doing this on a fresh install, if you don't set passwords for root first you will delete all the initial root accounts too! That is a good point. I always set the root password first. I just assumed that he would do that firt, but it is good to be clear. Thanks, Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing Entries
I already set the root password for the MySQL root user. That is why in my 1st email you see the encrypted password listed for root in localhost, mail, 127.0.0.1. Thanks for all your info. I followed Neil's steps and it worked perfect...after I realised that those were two 's and not a single quote () at the end of the delete command. Thanks for your support! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join on a where clause.
On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi everybody! Neil Aggarwal wrote: Paul: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN 'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; I am surprised by the quotes you have around the start_ip and end_ip columns; to me, this makes that look like strings. That because I don't know what I am doing :). No quotes on integers; got it! From your posting, I see the result you hope to get but not the one you actually get. IMO, just dropping the single quotes around the two column names should produce the data you want to get. Or what is the result you receive? Removing the quotes does work. The query however took 1h15m to complete. Yuck. I am guessing this is because even though there is a limit, it is still doing the lookup on everything past 20. Also, if the first address has a count of say 2000, it would be doing the lookup 2000 times for a single address. Is that right? If it is I guess I will have to post process the results. Which is fine, I just like to keep as much in the queries as I can. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join on a where clause.
A couple of thoughts - it's not no quotes on integers, but no quotes around column references. When you use 'mappings.end_ip', you are saying the string mappings.end_ip, and not referring to a column in the mappings table. It just becomes a constant at that point. As for the performance, you should look at the indices on the tables involved. Try doing explain on the query: EXPLAIN SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event join mappings ON event.src_ip between mappings.start_ip and mappings.end_ip WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; That'll give you some more information on what mysql is doing with your data. andy Paul Halliday wrote: On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi everybody! Neil Aggarwal wrote: Paul: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN 'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; I am surprised by the quotes you have around the start_ip and end_ip columns; to me, this makes that look like strings. That because I don't know what I am doing :). No quotes on integers; got it! From your posting, I see the result you hope to get but not the one you actually get. IMO, just dropping the single quotes around the two column names should produce the data you want to get. Or what is the result you receive? Removing the quotes does work. The query however took 1h15m to complete. Yuck. I am guessing this is because even though there is a limit, it is still doing the lookup on everything past 20. Also, if the first address has a count of say 2000, it would be doing the lookup 2000 times for a single address. Is that right? If it is I guess I will have to post process the results. Which is fine, I just like to keep as much in the queries as I can. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
stored procedure and random table name - temp table, merge, prepared statement
All,
I have a stored procedure that I'm writing where I need to run a lot of
queries against a particular table. The name of the table will be a
parameter to the stored procedure ... example:
CALL normalize_data('name_of_table_here');
Since I want to run queries against this table, I don't want to have to use
prepared statements for all the queries because treating my queries as
strings gets ugly. Ideally I want to use the table name as a variable in
the stored procedure, but as a hack around that, I thought about trying this
trick instead: give the table name an alias.
-- remove our temporary table if it already exists
DROP TABLE IF EXISTS dante;
--
-- clone the table structure
CREATE TEMPORARY TABLE dante LIKE name_of_table_here;
--
-- change the temporary table to a merge table which references the named
table
ALTER TABLE dante ENGINE=MERGE UNION(name_of_table_here);
Once these 3 statements were run, the merge table would essentially just
be a view on the underlying table and all my following queries could
reference the dante table and not the strangely named random table.
Note, that queries above that use name_of_table_here would need to be
prepared and executed using the string concat approach.
The problem I am having is that this strategy is not working. After running
the statements above, I check my new dante table and it doesn't work:
DESC dante;
Error Code : 1168
Unable to open underlying table which is differently defined or of
non-MyISAM type or doesn't exist
So, how can I accomplish what I am trying to do? I just want to alias a
random table to a fixed name (preferably as a temporary table name so that
it won't conflict with other connections running similar code
simultaneously) so that I can avoid having to use prepared statements
through my whole stored procedure. I may potentially perform 20-30 queries
to the table which is passed in and want to keep this code looking clean.
I could avoid this problem altogether if I can assign an alias to a table:
ALIAS dante TO name_of_table_here;
or use a variable table name in a query inside a stored procedure:
SET @table_name = 'name_of_table_here';
INSERT INTO some_table (value)
SELECT something
FROM @table_name
WHERE ...;
Am using MySQL 5.1.36. Any pointers?
-- Dante
RE: stored procedure and random table name - temp table, merge, prepared statement
Creating a temporary merge table works fine for me on 5.0.
Your table isn't innodb is it? That will fail with an error like you're
getting.
Regards,
Gavin Towey
-Original Message-
From: Dante Lorenso [mailto:da...@lorenso.com]
Sent: Thursday, December 10, 2009 3:20 PM
To: mysql@lists.mysql.com
Subject: stored procedure and random table name - temp table, merge, prepared
statement
All,
I have a stored procedure that I'm writing where I need to run a lot of
queries against a particular table. The name of the table will be a
parameter to the stored procedure ... example:
CALL normalize_data('name_of_table_here');
Since I want to run queries against this table, I don't want to have to use
prepared statements for all the queries because treating my queries as
strings gets ugly. Ideally I want to use the table name as a variable in
the stored procedure, but as a hack around that, I thought about trying this
trick instead: give the table name an alias.
-- remove our temporary table if it already exists
DROP TABLE IF EXISTS dante;
--
-- clone the table structure
CREATE TEMPORARY TABLE dante LIKE name_of_table_here;
--
-- change the temporary table to a merge table which references the named
table
ALTER TABLE dante ENGINE=MERGE UNION(name_of_table_here);
Once these 3 statements were run, the merge table would essentially just
be a view on the underlying table and all my following queries could
reference the dante table and not the strangely named random table.
Note, that queries above that use name_of_table_here would need to be
prepared and executed using the string concat approach.
The problem I am having is that this strategy is not working. After running
the statements above, I check my new dante table and it doesn't work:
DESC dante;
Error Code : 1168
Unable to open underlying table which is differently defined or of
non-MyISAM type or doesn't exist
So, how can I accomplish what I am trying to do? I just want to alias a
random table to a fixed name (preferably as a temporary table name so that
it won't conflict with other connections running similar code
simultaneously) so that I can avoid having to use prepared statements
through my whole stored procedure. I may potentially perform 20-30 queries
to the table which is passed in and want to keep this code looking clean.
I could avoid this problem altogether if I can assign an alias to a table:
ALIAS dante TO name_of_table_here;
or use a variable table name in a query inside a stored procedure:
SET @table_name = 'name_of_table_here';
INSERT INTO some_table (value)
SELECT something
FROM @table_name
WHERE ...;
Am using MySQL 5.1.36. Any pointers?
-- Dante
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
Re: stored procedure and random table name - temp table, merge, prepared statement
Gavin Towey wrote:
Creating a temporary merge table works fine for me on 5.0.
Your table isn't innodb is it? That will fail with an error like you're
getting.
Strange. Now that I am on my home network and trying this again, it
seems to be working.
mysql SELECT version();
+--+
| version()|
+--+
| 5.1.41-community |
+--+
1 row in set (0.00 sec)
Here is my stored procedure:
8
CREATE PROCEDURE `test_massage_table`(IN in_table_name VARCHAR(64))
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
-- remove temp table
DROP TABLE IF EXISTS test_temp;
-- clone table structure from submitted table
SET @s = CONCAT('CREATE TABLE test_temp LIKE ', in_table_name);
PREPARE stmt FROM @s;
EXECUTE stmt;
-- convert table type to MERGE. Pass through to original table
SET @s = CONCAT('ALTER TABLE test_temp ENGINE=MERGE UNION(',
in_table_name, ')');
PREPARE stmt FROM @s;
EXECUTE stmt;
-- test query 1
UPDATE test_temp SET value = value * value;
-- test query 2
UPDATE test_temp SET modified = NOW();
-- test query 3
DELETE FROM test_temp WHERE value 10;
-- test query 4
SELECT *
FROM test_temp;
END;
8
Then, here is the code I used to test it:
8
-- destroy tables
DROP TABLE IF EXISTS test_table_odds;
DROP TABLE IF EXISTS test_table_evens;
DROP TABLE IF EXISTS test_temp;
--
-- create new tables
CREATE TABLE `test_table_odds` (
`value` int(11) unsigned NOT NULL,
`modified` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
--
-- check table
SELECT * FROM test_table_odds;
SELECT * FROM test_table_evens;
--
-- run new procedure stuff
CALL test_massage_table('test_table_odds');
CALL test_massage_table('test_table_evens');
8
And here is my output:
8
mysql -- destroy tables
mysql DROP TABLE IF EXISTS test_table_odds;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS test_table_evens;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS test_temp;
Query OK, 0 rows affected (0.00 sec)
mysql --
mysql -- create new tables
mysql CREATE TABLE `test_table_odds` (
- `value` int(11) unsigned NOT NULL,
- `modified` datetime DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 0 rows affected (0.00 sec)
mysql CREATE TABLE test_table_evens LIKE test_table_odds;
Query OK, 0 rows affected (0.00 sec)
mysql --
mysql -- add sample data
mysql INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql --
mysql -- check table
mysql SELECT * FROM test_table_odds;
+---+--+
| value | modified |
+---+--+
| 1 | NULL |
| 3 | NULL |
| 5 | NULL |
+---+--+
3 rows in set (0.00 sec)
mysql SELECT * FROM test_table_evens;
+---+--+
| value | modified |
+---+--+
| 2 | NULL |
| 4 | NULL |
| 6 | NULL |
+---+--+
3 rows in set (0.00 sec)
mysql --
mysql -- run new procedure stuff
mysql CALL test_massage_table('test_table_odds');
+---+-+
| value | modified|
+---+-+
| 1 | 2009-12-10 21:18:59 |
| 9 | 2009-12-10 21:18:59 |
+---+-+
2 rows in set (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql CALL test_massage_table('test_table_evens');
+---+-+
| value | modified|
+---+-+
| 4 | 2009-12-10 21:19:01 |
+---+-+
1 row in set (0.15 sec)
Query OK, 0 rows affected (0.15 sec)
8
So, thanks for the reply. I hope someone can review what I've done here
and let me know if there is a smarter way to accomplish what I'm trying
to do. Otherwise, I'll have to review what I was working on at the
office and figure out why that wasn't working for me. It might be a
mysql version difference, but I'm guessing it was something to do with
the original table being too complex for the Merge table to work. Maybe
indexes not matching?
Anyhow, let me know what you think of this strategy for sidestepping the
need for prepared statements when working with MyISAM tables passed to
Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onstat us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_detai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viability= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Uhhh... wow. Unless I'm very, very, very mistaken, I think you're missing something pretty obvious: I believe you can simply a) shut down the database b) mv the directory to a different directory name. *DONE* Your database now has a different name. Boy, that 30 seconds of hard labor was sure faster than waiting a week for SQL dumps. Granted, I can't swear that this is Officially Sanctioned And Approved(tm), but I've done it many times, myself (and, indeed, just verified it under 5.1 to be sure it still worked). Since you are talking such a significant volume of data, I would suggest either testing, or hearing from someone more knowledgeable than I, but I think this problem is substantially smaller than you've let yourself believe. -Ken On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote: How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onst at us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_det ai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viabilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
