RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
> -Original Message- > From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au] > Sent: Friday, October 15, 2010 5:54 PM > To: MY SQL Mailing list > Subject: Re: How do I GRANT SELECT to mysql.time_zone_name > for ANYONE?! > > > Any user can get into mysql, it's what they can do after that's the > > interesting part. > > Sorry, I'm not sure what you mean. Unless a username and host > combination matches a record in the mysql.user table, then the user > cannot log into the server. > > $ mysql -u random_user -p > Enter password: > ERROR 1045 (28000): Access denied for user > 'random_user'@'localhost' (using password: NO) > > $ mysql -u random_user -p > Enter password: > ERROR 1045 (28000): Access denied for user > 'random_user'@'localhost' (using password: YES) Don't use the -p password prompt and you get into mysql... develo...@mypse:~$ mysql -u random_user Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2393 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu) (random_u...@localhost) [(none)]> show databases; ++ | Database | ++ | information_schema | | mysql | ++ 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
Hi, On 16/10/2010, at 8:50 AM, Daevid Vincent wrote: Thanks for the reply Jesper, but either there isn't a solution in your response, or I'm missing it? What I mean is that you have to explicitly give the grant to each user that should be allowed to query the table. You can't run one grant that automatically will apply to all users. So if you have three users use...@localhost, use...@192.168.1.1, and use...@localhost you have to run: GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_a'@localhost; GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_a'@192.168.1.1; GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_b'@localhost; Any user can get into mysql, it's what they can do after that's the interesting part. Sorry, I'm not sure what you mean. Unless a username and host combination matches a record in the mysql.user table, then the user cannot log into the server. $ mysql -u random_user -p Enter password: ERROR 1045 (28000): Access denied for user 'random_user'@'localhost' (using password: NO) $ mysql -u random_user -p Enter password: ERROR 1045 (28000): Access denied for user 'random_user'@'localhost' (using password: YES) I used your GRANT example above and get this... develo...@mypse:~$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2275 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. (develo...@localhost) [(none)]> SELECT * FROM mysql.time_zone_name; ++--+ | Name | Time_zone_id | ++--+ | Africa/Abidjan |1 | | Africa/Accra |2 | | Africa/Addis_Ababa |3 | | Africa/Algiers |4 | ... But then when I try an existing user that I use for all my PHP/DB connections: develo...@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core (omt_mas...@mypse) [agis_core]> SELECT * FROM mysql.time_zone_name; ERROR 1142 (42000): SELECT command denied to user 'OMT_Master'@'mydomain.com' for table 'time_zone_name' That is because the GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; statement only gives the privilege to query the time_zone_name table to users who use the '%'@'%' account when logging in. Here's the current user's I have in my VM so far: SELECT `User`, `Host` FROM mysql.user; User Host -- % % % OMT_Master% OMT_Web % View_ReadOnly % developer % diagnostics % diagnostics 10.10.10.% root 127.0.0.1 localhost debian-sys-maint localhost root localhost I will recommend you to drop all the users that can log in from arbitrary hosts or with arbitrary usernames. E.g. the diagnost...@10.10.10.% account is a much better way to create a user rather than the diagnost...@% account. The latter will allow the diagnostics user to login from anywhere, whereas diagnost...@10.10.10.% restricts the login to a small subnet. Hope that helps. Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Primary key not unique on InnoDB table
You obviously know best how your application will query your database, and you may have already thought through your indexing strategy. If so, please disregard my comments. In my experience, it is not often you need separate indexes on most or all the columns in a table (excepting very narrow tables, perhaps), so I would think about how you anticipate the database might use each of these indexes. Even though you may have multiple indexes available, most of the time a database query optimizer will only choose one when deciding how to retrieve data for a query. So, if you have a column like first_name that is indexed, your database engine may never use this index unless you have a query like "select * from players_master where first_name = 'xyz'". If a column is part of your select list, but is not used as your WHERE clause expression or as part of a table join, indexing that column may not be a benefit. Running EXPLAIN will tell you whether or not the index you anticipate is actually being used for your query. -Travis From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, October 15, 2010 3:43 AM To: [MySQL]; Travis Ard Subject: Fwd: Primary key not unique on InnoDB table Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard Cc: "[MySQL]" Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard wrote: I couldn't help but notice you have individual indexes on nearly all the fields of your table. If you won't be using these fields exclusively as a join or filter condition in a query, you are unlikely to benefit from the extra indexes and, in fact, they could slow down your inserts and add to your storage requirements. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, October 13, 2010 8:37 AM To: [MySQL] Subject: Primary key not unique on InnoDB table I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
RE: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
> -Original Message- > From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au] > Sent: Friday, October 15, 2010 2:33 PM > To: MY SQL Mailing list > Subject: Re: How do I GRANT SELECT to mysql.time_zone_name > for ANYONE?! > > Hi > > > On 16/10/2010, at 1:47 AM, Suresh Kuna wrote: > > > Hey Daevid, > > > > As this time zone table won't change once it is set up. Do a copy > > of the > > table data into another database and give grants to it. > > Copy the data is not a good solution. First of all, time zone data > does change. Secondly if you need to use functions such as CONVERT_TZ > () I believe you need access to the time zone tables in the mysql > database. > > > > > > On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers > > wrote: > > > >> I think this is one of those times you would update the > mysql.user > >> table > >> directly, then flush privileges. > > You can grant access to the time zone tables just as you would do to > any other table. > > >>> > >>> GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; > >>> GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; > >>> GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; > >>> GRANT SELECT ON `mysql`.`time_zone_name` TO ''; > >>> GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) > > As mentioned above, granting access to the time zone tables works > exactly as it does for all other tables, so. e.g. granting SELECT to > '%' will not allow everybody to do a SELECT on the table, but rather > allow users logging in as the '%'@'%' user to select from the > mysql.time_zone_name table. If the users used in the above GRANT > statements don't exist, they will also end up being created. This > means that you suddenly might have opened access to the database for > a user called '%' from everywhere (although they only can > select from > the time_zone_name table). Note that the new user can login without > using a password. > > (none)> SELECT User, Host FROM mysql.user; > +--+---+ > | User | Host | > +--+---+ > | root | localhost | > | testuser | localhost | > +--+---+ > 2 rows in set (0.37 sec) > > (none)> GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; > Query OK, 0 rows affected (0.18 sec) > > (none)> SELECT User, Host FROM mysql.user; > +--+---+ > | User | Host | > +--+---+ > | %| % | > | root | localhost | > | testuser | localhost | > +--+---+ > 3 rows in set (0.00 sec) > > $ mysql -u % -p > Enter password: > Welcome to the MySQL monitor. Commands end with ; or \g. > ... > > > Jesper Thanks for the reply Jesper, but either there isn't a solution in your response, or I'm missing it? Any user can get into mysql, it's what they can do after that's the interesting part. I used your GRANT example above and get this... develo...@mypse:~$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2275 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. (develo...@localhost) [(none)]> SELECT * FROM mysql.time_zone_name; ++--+ | Name | Time_zone_id | ++--+ | Africa/Abidjan |1 | | Africa/Accra |2 | | Africa/Addis_Ababa |3 | | Africa/Algiers |4 | ... But then when I try an existing user that I use for all my PHP/DB connections: develo...@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core (omt_mas...@mypse) [agis_core]> SELECT * FROM mysql.time_zone_name; ERROR 1142 (42000): SELECT command denied to user 'OMT_Master'@'mydomain.com' for table 'time_zone_name' Here's the current user's I have in my VM so far: SELECT `User`, `Host` FROM mysql.user; User Host -- % % % OMT_Master% OMT_Web % View_ReadOnly % developer % diagnostics % diagnostics 10.10.10.% root 127.0.0.1 localhost debian-sys-maint localhost root localhost At this point of frustration, unless someone has a way to do this -- which seems like it should be a pretty straight forward thing to do -- I'll just add this particular OMT_Master user to have this particular table's SELECT GRANT. Or am I missing something? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
Hi On 16/10/2010, at 1:47 AM, Suresh Kuna wrote: Hey Daevid, As this time zone table won't change once it is set up. Do a copy of the table data into another database and give grants to it. Copy the data is not a good solution. First of all, time zone data does change. Secondly if you need to use functions such as CONVERT_TZ () I believe you need access to the time zone tables in the mysql database. On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers wrote: I think this is one of those times you would update the mysql.user table directly, then flush privileges. You can grant access to the time zone tables just as you would do to any other table. GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''; GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) As mentioned above, granting access to the time zone tables works exactly as it does for all other tables, so. e.g. granting SELECT to '%' will not allow everybody to do a SELECT on the table, but rather allow users logging in as the '%'@'%' user to select from the mysql.time_zone_name table. If the users used in the above GRANT statements don't exist, they will also end up being created. This means that you suddenly might have opened access to the database for a user called '%' from everywhere (although they only can select from the time_zone_name table). Note that the new user can login without using a password. (none)> SELECT User, Host FROM mysql.user; +--+---+ | User | Host | +--+---+ | root | localhost | | testuser | localhost | +--+---+ 2 rows in set (0.37 sec) (none)> GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; Query OK, 0 rows affected (0.18 sec) (none)> SELECT User, Host FROM mysql.user; +--+---+ | User | Host | +--+---+ | %| % | | root | localhost | | testuser | localhost | +--+---+ 3 rows in set (0.00 sec) $ mysql -u % -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. ... Jesper
RE: Are Ubuntu 64-bit /var/lib/mysql/ibdata (etc) compatible with 32-bit ??
> We have a PSE05 "Master" and PSE06 "Slave" (PRODUCTION servers) both > are > Ubuntu 32-bit. > We have a third slave PSE07 which is Ubuntu 64-bit. This is our 'live > backup' so to speak. We take mysqld down daily on there and tarball the > /var/lib/mysql and /var/log/mysql as snapshots (since mysqldump would > take > a week literally to re-import). > Our data is about 100GB and nearly 1 Billion records and growing by > several > hundred thousand per day. A medium sized installation, but still too big to do a quick dump-n-restore that the mysql docs always suggest. > We had some replication hose-up where someone accidentally wrote to the > PSE06 slave. This wasn't caught right away and so it cascaded and > queued up > about 130 rows to be written. Obviously going through this whole > business: > > mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; > show > slave status\G > > Would take WAY too long and painful. Painful for 130 rows? Script it ... check "show slave status", and if replication is off execute "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;" > So is this even possible. Are the ibdata files binary compatible > between > "bit" versions (or even different OS's for that matter) Its safe, but this is not the way to fix your problem. You should use Maatkit mk-table-checksum and mk-table-sync: http://www.maatkit.org/doc/mk-table-checksum.html http://www.maatkit.org/doc/mk-table-sync.html > We are getting errors: > > 101013 23:56:22 [Warning] Neither --relay-log nor --relay-log-index > were > used; so replication may break when this MySQL server acts as a slave > and > has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to > avoid this problem.^M Probably you didn't set "log-bin" in my.cnf with a value? HTH, ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple mysql installations - Install mysql from source with a prefix
you can use mysqld_multi On Fri, Oct 15, 2010 at 8:24 PM, Claudio Nanni wrote: > did you remove /etc/my.cnf? > > 2010/10/15 ml ml > > > Hello, > > > > i installed mysql with: > > ./configure --prefix=/usr/local/myprefix/mysql/ && make && make install > > > > (at the point mysql is not running yet) > > > > Next i would like to initialize the DB but get the follwoing error: > > -- > > #:/usr/local/myprefix/mysql# bin/mysql_install_db --user=mysql > > --basedir=/usr/local/myprefix/mysql/ > > --datadir=/usr/local/myprefix/mysql/data > > Installing MySQL system tables... > > 101015 15:13:17 [Warning] option 'thread_stack': unsigned value 65536 > > adjusted to 131072 > > /usr/local/myprefix/mysql//libexec/mysqld: File > > '/var/log/mysql/mysql-bin.index' not found (Errcode: 2) > > 101015 15:13:17 [ERROR] Aborting > > > > 101015 15:13:17 [Note] /usr/local/myprefix/mysql//libexec/mysqld: > > Shutdown complete > > > > > > Installation of system tables failed! Examine the logs in > > /usr/local/myprefix/mysql/data for more information. > > > > > > > > > > > > find /usr/local/myprefix/mysql/data/ > > /usr/local/myprefix/mysql/data/ > > /usr/local/myprefix/mysql/data/mysql > > /usr/local/myprefix/mysql/data/test > > > > --- > > > > And the folder /var/log/mysql/ does not exist. I dont want it to be > > there anyway. > > > > > > Cheers, > > Mario > > > > > > > > On Fri, Oct 15, 2010 at 2:12 PM, Himanshu Raina > > wrote: > > > Hi Mario, > > > > > > While installing a new instance you don't exactly need to mention > > > anything except for the --prefix option. While starting the new > instance > > > you can provide all config parameters like config file, datadir etc. > > > > > > > > > On Fri, 2010-10-15 at 16:47 +0530, ml ml wrote: > > >> Hello List, > > >> > > >> how do i install mysql COMPLETLY in a diffrent directory? > > >> > > >> Right now i am using: > > >> ./configure --prefix=/usr/local/mysql-5.1.42 > > >> --sysconfdir=/usr/local/mysql-5.1.42/etc > > >> > > >> BUT, mysql still looks for /etc/my.cnf and for > > >> /var/log/mysql/mysql-bin.index ... > > >> > > >> So what configure options do i need if i want to install mysql > > >> seperatly in a folder? > > >> Do i also need --datadir? If yes, where sould my data dir be? In > > >> /usr/local/mysql-5.1.42/var? > > >> > > >> Thanks a lot, > > >> Mario > > >> > > > -- > > > Regards, > > > > > > Himanshu Raina > > > -- > > > Guillotine, n.: > > >A French chopping center. > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > > > > > > -- > Claudio > -- Thanks Suresh Kuna MySQL DBA
Re: multiple mysql installations - Install mysql from source with a prefix
did you remove /etc/my.cnf? 2010/10/15 ml ml > Hello, > > i installed mysql with: > ./configure --prefix=/usr/local/myprefix/mysql/ && make && make install > > (at the point mysql is not running yet) > > Next i would like to initialize the DB but get the follwoing error: > -- > #:/usr/local/myprefix/mysql# bin/mysql_install_db --user=mysql > --basedir=/usr/local/myprefix/mysql/ > --datadir=/usr/local/myprefix/mysql/data > Installing MySQL system tables... > 101015 15:13:17 [Warning] option 'thread_stack': unsigned value 65536 > adjusted to 131072 > /usr/local/myprefix/mysql//libexec/mysqld: File > '/var/log/mysql/mysql-bin.index' not found (Errcode: 2) > 101015 15:13:17 [ERROR] Aborting > > 101015 15:13:17 [Note] /usr/local/myprefix/mysql//libexec/mysqld: > Shutdown complete > > > Installation of system tables failed! Examine the logs in > /usr/local/myprefix/mysql/data for more information. > > > > > > find /usr/local/myprefix/mysql/data/ > /usr/local/myprefix/mysql/data/ > /usr/local/myprefix/mysql/data/mysql > /usr/local/myprefix/mysql/data/test > > --- > > And the folder /var/log/mysql/ does not exist. I dont want it to be > there anyway. > > > Cheers, > Mario > > > > On Fri, Oct 15, 2010 at 2:12 PM, Himanshu Raina > wrote: > > Hi Mario, > > > > While installing a new instance you don't exactly need to mention > > anything except for the --prefix option. While starting the new instance > > you can provide all config parameters like config file, datadir etc. > > > > > > On Fri, 2010-10-15 at 16:47 +0530, ml ml wrote: > >> Hello List, > >> > >> how do i install mysql COMPLETLY in a diffrent directory? > >> > >> Right now i am using: > >> ./configure --prefix=/usr/local/mysql-5.1.42 > >> --sysconfdir=/usr/local/mysql-5.1.42/etc > >> > >> BUT, mysql still looks for /etc/my.cnf and for > >> /var/log/mysql/mysql-bin.index ... > >> > >> So what configure options do i need if i want to install mysql > >> seperatly in a folder? > >> Do i also need --datadir? If yes, where sould my data dir be? In > >> /usr/local/mysql-5.1.42/var? > >> > >> Thanks a lot, > >> Mario > >> > > -- > > Regards, > > > > Himanshu Raina > > -- > > Guillotine, n.: > >A French chopping center. > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > -- Claudio
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
Hey Daevid, As this time zone table won't change once it is set up. Do a copy of the table data into another database and give grants to it. On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers wrote: > I think this is one of those times you would update the mysql.user table > directly, then flush privileges. > > JW > > > On Thu, Oct 14, 2010 at 6:46 PM, Daevid Vincent wrote: > > > I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER > for > > the very specific mysql.time_zone_name table?? I don't want to GRANT it > to > > every individual user manually, I want one single GRANT that encompasses > > every user simultaneously. > > > > I've tried all of these, and they all are valid in mySQL but none of them > > actually have the desired result. > > > > GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; > > GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; > > GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; > > GRANT SELECT ON `mysql`.`time_zone_name` TO ''; > > GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) > > > > Here are the results: > > > > SELECT * FROM mysql.time_zone_name LIMIT 0, 5000 > > > > Error Code : 1142 > > SELECT command denied to user 'daevid'@'mycompany.com' for table > > 'time_zone_name' > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > > > > > > -- > - > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- Thanks Suresh Kuna MySQL DBA
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
I think this is one of those times you would update the mysql.user table directly, then flush privileges. JW On Thu, Oct 14, 2010 at 6:46 PM, Daevid Vincent wrote: > I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER for > the very specific mysql.time_zone_name table?? I don't want to GRANT it to > every individual user manually, I want one single GRANT that encompasses > every user simultaneously. > > I've tried all of these, and they all are valid in mySQL but none of them > actually have the desired result. > > GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; > GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; > GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; > GRANT SELECT ON `mysql`.`time_zone_name` TO ''; > GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) > > Here are the results: > > SELECT * FROM mysql.time_zone_name LIMIT 0, 5000 > > Error Code : 1142 > SELECT command denied to user 'daevid'@'mycompany.com' for table > 'time_zone_name' > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: multiple mysql installations - Install mysql from source with a prefix
You can pass the following variables as well --defaults-extra-file= --socket= --port= On Fri, 2010-10-15 at 18:50 +0530, ml ml wrote: > Hello, > > i installed mysql with: > ./configure --prefix=/usr/local/myprefix/mysql/ && make && make install > > (at the point mysql is not running yet) > > Next i would like to initialize the DB but get the follwoing error: > -- > #:/usr/local/myprefix/mysql# bin/mysql_install_db --user=mysql > --basedir=/usr/local/myprefix/mysql/ > --datadir=/usr/local/myprefix/mysql/data > Installing MySQL system tables... > 101015 15:13:17 [Warning] option 'thread_stack': unsigned value 65536 > adjusted to 131072 > /usr/local/myprefix/mysql//libexec/mysqld: File > '/var/log/mysql/mysql-bin.index' not found (Errcode: 2) > 101015 15:13:17 [ERROR] Aborting > > 101015 15:13:17 [Note] /usr/local/myprefix/mysql//libexec/mysqld: > Shutdown complete > > > Installation of system tables failed! Examine the logs in > /usr/local/myprefix/mysql/data for more information. > > > > > > find /usr/local/myprefix/mysql/data/ > /usr/local/myprefix/mysql/data/ > /usr/local/myprefix/mysql/data/mysql > /usr/local/myprefix/mysql/data/test > > --- > > And the folder /var/log/mysql/ does not exist. I dont want it to be > there anyway. > > > Cheers, > Mario > > > > On Fri, Oct 15, 2010 at 2:12 PM, Himanshu Raina > wrote: > > Hi Mario, > > > > While installing a new instance you don't exactly need to mention > > anything except for the --prefix option. While starting the new instance > > you can provide all config parameters like config file, datadir etc. > > > > > > On Fri, 2010-10-15 at 16:47 +0530, ml ml wrote: > >> Hello List, > >> > >> how do i install mysql COMPLETLY in a diffrent directory? > >> > >> Right now i am using: > >> ./configure --prefix=/usr/local/mysql-5.1.42 > >> --sysconfdir=/usr/local/mysql-5.1.42/etc > >> > >> BUT, mysql still looks for /etc/my.cnf and for > >> /var/log/mysql/mysql-bin.index ... > >> > >> So what configure options do i need if i want to install mysql > >> seperatly in a folder? > >> Do i also need --datadir? If yes, where sould my data dir be? In > >> /usr/local/mysql-5.1.42/var? > >> > >> Thanks a lot, > >> Mario > >> > > -- > > Regards, > > > > Himanshu Raina > > -- > > Guillotine, n.: > >A French chopping center. > > > > -- Regards, Himanshu Raina -- I'm successful because I'm lucky. The harder I work, the luckier I get. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple mysql installations - Install mysql from source with a prefix
Hello, i installed mysql with: ./configure --prefix=/usr/local/myprefix/mysql/ && make && make install (at the point mysql is not running yet) Next i would like to initialize the DB but get the follwoing error: -- #:/usr/local/myprefix/mysql# bin/mysql_install_db --user=mysql --basedir=/usr/local/myprefix/mysql/ --datadir=/usr/local/myprefix/mysql/data Installing MySQL system tables... 101015 15:13:17 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 /usr/local/myprefix/mysql//libexec/mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2) 101015 15:13:17 [ERROR] Aborting 101015 15:13:17 [Note] /usr/local/myprefix/mysql//libexec/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /usr/local/myprefix/mysql/data for more information. find /usr/local/myprefix/mysql/data/ /usr/local/myprefix/mysql/data/ /usr/local/myprefix/mysql/data/mysql /usr/local/myprefix/mysql/data/test --- And the folder /var/log/mysql/ does not exist. I dont want it to be there anyway. Cheers, Mario On Fri, Oct 15, 2010 at 2:12 PM, Himanshu Raina wrote: > Hi Mario, > > While installing a new instance you don't exactly need to mention > anything except for the --prefix option. While starting the new instance > you can provide all config parameters like config file, datadir etc. > > > On Fri, 2010-10-15 at 16:47 +0530, ml ml wrote: >> Hello List, >> >> how do i install mysql COMPLETLY in a diffrent directory? >> >> Right now i am using: >> ./configure --prefix=/usr/local/mysql-5.1.42 >> --sysconfdir=/usr/local/mysql-5.1.42/etc >> >> BUT, mysql still looks for /etc/my.cnf and for >> /var/log/mysql/mysql-bin.index ... >> >> So what configure options do i need if i want to install mysql >> seperatly in a folder? >> Do i also need --datadir? If yes, where sould my data dir be? In >> /usr/local/mysql-5.1.42/var? >> >> Thanks a lot, >> Mario >> > -- > Regards, > > Himanshu Raina > -- > Guillotine, n.: > A French chopping center. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple mysql installations - Install mysql from source with a prefix
Hi Mario, MySQL search my.cnf in this order(if I remember well): 1. /etc/my.cnf 2. /basedir/my.cnf 3. /datadir/my.cnf 4. $userhome/my.cnf you have to make sure that you REMOVE ate least /etc/my.cnf from the system try and let me know Claudio 2010/10/15 ml ml > Hi Claudio, > > i would like to compile it from source. > > Mario > > > On Fri, Oct 15, 2010 at 2:08 PM, Claudio Nanni > wrote: > > hi, > > do you want to compile or you can use binaries? > > > > Claudio > > > > 2010/10/15 ml ml > >> > >> Hello List, > >> > >> how do i install mysql COMPLETLY in a diffrent directory? > >> > >> Right now i am using: > >> ./configure --prefix=/usr/local/mysql-5.1.42 > >> --sysconfdir=/usr/local/mysql-5.1.42/etc > >> > >> BUT, mysql still looks for /etc/my.cnf and for > >> /var/log/mysql/mysql-bin.index ... > >> > >> So what configure options do i need if i want to install mysql > >> seperatly in a folder? > >> Do i also need --datadir? If yes, where sould my data dir be? In > >> /usr/local/mysql-5.1.42/var? > >> > >> Thanks a lot, > >> Mario > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > >> > > > > > > > > -- > > Claudio > > > -- Claudio
Re: multiple mysql installations - Install mysql from source with a prefix
Hi Mario, While installing a new instance you don't exactly need to mention anything except for the --prefix option. While starting the new instance you can provide all config parameters like config file, datadir etc. On Fri, 2010-10-15 at 16:47 +0530, ml ml wrote: > Hello List, > > how do i install mysql COMPLETLY in a diffrent directory? > > Right now i am using: > ./configure --prefix=/usr/local/mysql-5.1.42 > --sysconfdir=/usr/local/mysql-5.1.42/etc > > BUT, mysql still looks for /etc/my.cnf and for > /var/log/mysql/mysql-bin.index ... > > So what configure options do i need if i want to install mysql > seperatly in a folder? > Do i also need --datadir? If yes, where sould my data dir be? In > /usr/local/mysql-5.1.42/var? > > Thanks a lot, > Mario > -- Regards, Himanshu Raina -- Guillotine, n.: A French chopping center. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple mysql installations - Install mysql from source with a prefix
Hi Claudio, i would like to compile it from source. Mario On Fri, Oct 15, 2010 at 2:08 PM, Claudio Nanni wrote: > hi, > do you want to compile or you can use binaries? > > Claudio > > 2010/10/15 ml ml >> >> Hello List, >> >> how do i install mysql COMPLETLY in a diffrent directory? >> >> Right now i am using: >> ./configure --prefix=/usr/local/mysql-5.1.42 >> --sysconfdir=/usr/local/mysql-5.1.42/etc >> >> BUT, mysql still looks for /etc/my.cnf and for >> /var/log/mysql/mysql-bin.index ... >> >> So what configure options do i need if i want to install mysql >> seperatly in a folder? >> Do i also need --datadir? If yes, where sould my data dir be? In >> /usr/local/mysql-5.1.42/var? >> >> Thanks a lot, >> Mario >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com >> > > > > -- > Claudio > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple mysql installations - Install mysql from source with a prefix
hi, do you want to compile or you can use binaries? Claudio 2010/10/15 ml ml > Hello List, > > how do i install mysql COMPLETLY in a diffrent directory? > > Right now i am using: > ./configure --prefix=/usr/local/mysql-5.1.42 > --sysconfdir=/usr/local/mysql-5.1.42/etc > > BUT, mysql still looks for /etc/my.cnf and for > /var/log/mysql/mysql-bin.index ... > > So what configure options do i need if i want to install mysql > seperatly in a folder? > Do i also need --datadir? If yes, where sould my data dir be? In > /usr/local/mysql-5.1.42/var? > > Thanks a lot, > Mario > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > -- Claudio
multiple mysql installations - Install mysql from source with a prefix
Hello List, how do i install mysql COMPLETLY in a diffrent directory? Right now i am using: ./configure --prefix=/usr/local/mysql-5.1.42 --sysconfdir=/usr/local/mysql-5.1.42/etc BUT, mysql still looks for /etc/my.cnf and for /var/log/mysql/mysql-bin.index ... So what configure options do i need if i want to install mysql seperatly in a folder? Do i also need --datadir? If yes, where sould my data dir be? In /usr/local/mysql-5.1.42/var? Thanks a lot, Mario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backing up MySQL using PHPMyAdmin problem with UTF-8
I managed to use the older version of MySQL Administrator 1.2.15 and it appears to back up find using InnoDB online backup. On Thu, Oct 14, 2010 at 6:56 PM, Michael Dykman wrote: > I have had this problem with PHPMyAdmin many times, and the only way I > know around it, is to go in and do your dump at the console. PHP does > not deal with UTF very well. > > - michael dykman > > > On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil > wrote: > > Hi, > > > > I'm using PHP MyAdmin to backup my MySQL database. The database is of > > type InnoDB > > and encoding used is utf8_unicode_ci. The variables are set as follows : > > > > *MySQL connection collation: **utf8_unicode_ci* > > *MySQL charset: **UTF-8 Unicode (utf8)* > > *character set client: utf8* > > *character set connection: utf8* > > *character set results: utf8* > > *collation connection: **utf8_unicode_ci* > > > > The problem I have is that the foreign characters like ăÿć etc are being > > backed up as scrambled non-readable characters. Any ideas why this is > > happening ?? Are there any other variables I need to check/set ? > > > > Cheers > > Neil > > > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. >
Fwd: Primary key not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard Cc: "[MySQL]" Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard wrote: > I couldn't help but notice you have individual indexes on nearly all the > fields of your table. If you won't be using these fields exclusively as a > join or filter condition in a query, you are unlikely to benefit from the > extra indexes and, in fact, they could slow down your inserts and add to > your storage requirements. > > -Travis > > -Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, October 13, 2010 8:37 AM > To: [MySQL] > Subject: Primary key not unique on InnoDB table > > I've the following table. But why isn't the primary key unique, e.g. > preventing duplicates if entered ? > > CREATE TABLE `players_master` ( > > `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > `default_teams_id` bigint(20) NOT NULL, > `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > `dob` date NOT NULL, > `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > `retirement_date` date DEFAULT NULL, > `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > `estimated_value` double NOT NULL DEFAULT '0', > `contract_wage` double NOT NULL DEFAULT '0', > `rating` int(11) NOT NULL, > PRIMARY KEY (`players_id`,`default_teams_id`), > KEY `FK_players_master_countries_id` (`countries_id`), > KEY `FK_players_master_positions_id` (`positions_id`), > KEY `IDX_first_name` (`first_name`), > KEY `IDX_known_as` (`known_as`), > KEY `IDX_second_name` (`second_name`), > KEY `IDX_dob` (`dob`), > KEY `IDX_estimated_value` (`estimated_value`), > KEY `IDX_contract_wage` (`contract_wage`), > KEY `IDX_rating` (`rating`), > KEY `FK_players_master_teams_id` (`default_teams_id`), > CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) > REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) > REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) > REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION > ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > COLLATE=utf8_unicode_ci > > I'm confused, I thought primary keys were always unique ? > > Cheers > Neil > >
Re: Backing up MySQL using PHPMyAdmin problem with UTF-8
Oh this isn't good. Because the database in on a shared server and I don't think I've got console access ? On Thu, Oct 14, 2010 at 6:56 PM, Michael Dykman wrote: > I have had this problem with PHPMyAdmin many times, and the only way I > know around it, is to go in and do your dump at the console. PHP does > not deal with UTF very well. > > - michael dykman > > > On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil > wrote: > > Hi, > > > > I'm using PHP MyAdmin to backup my MySQL database. The database is of > > type InnoDB > > and encoding used is utf8_unicode_ci. The variables are set as follows : > > > > *MySQL connection collation: **utf8_unicode_ci* > > *MySQL charset: **UTF-8 Unicode (utf8)* > > *character set client: utf8* > > *character set connection: utf8* > > *character set results: utf8* > > *collation connection: **utf8_unicode_ci* > > > > The problem I have is that the foreign characters like ăÿć etc are being > > backed up as scrambled non-readable characters. Any ideas why this is > > happening ?? Are there any other variables I need to check/set ? > > > > Cheers > > Neil > > > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. >
