[gentoo-server] [friendly reminder] sys-libs/timezone-data and MySQL

[gentoo-server] [friendly reminder] sys-libs/timezone-data and MySQL

[Francesco Riosa]

after an upgrade of sys-libs/timezone-data is good advice to update the
mysql timezones tables too:

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

for details refer to
http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] [friendly reminder] sys-libs/timezone-data and MySQL

[Christian Bricart]

> after an upgrade of sys-libs/timezone-data is good advice to update the
> mysql timezones tables too:
>
> # mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
>
> for details refer to
> http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

also - contrary to most Distro installation docs (which includes Gentoo
;-) ):

In those docs, /etc/localtime ought to be copied from
/usr/share/timezone/XX/YY
Personally, I rather set a symlink than copy the file...
Has the following advantages:
 - It's clearly visible what's the configured timezone (by simply reading
the link text ;-) )
 - updates to timezone-data update system's timezone automagically

Drawbacks:
  according to FHS /usr may be a remote filesystem, but /etc/localtime has
to exist during boot.

Christian

-- 
gentoo-server@gentoo.org mailing list

[gentoo-server] remote /usr

[Alex Efros]

Hi!

On Fri, Dec 01, 2006 at 01:53:05PM +0100, Christian Bricart wrote:
> Drawbacks:
>   according to FHS /usr may be a remote filesystem, but /etc/localtime has
> to exist during boot.

Can anybody in _this_ maillist confirm importance of "remote /usr" support?

In my experience remote /usr is very rare case nowadays and supporting it
"by default" result in few very uncomfortable things - in addition to
/etc/localtime example I can add grep ebuild which compile grep without
perl regexp support (only because libperl is in /usr while grep is in /).

-- 
			WBR, Alex.
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Ronan Mullally]

On Fri, 8 Dec 2006, Alex Efros wrote:

> On Fri, Dec 01, 2006 at 01:53:05PM +0100, Christian Bricart wrote:
> > Drawbacks:
> >   according to FHS /usr may be a remote filesystem, but /etc/localtime has
> > to exist during boot.
>
> Can anybody in _this_ maillist confirm importance of "remote /usr" support?
>
> In my experience remote /usr is very rare case nowadays and supporting it
> "by default" result in few very uncomfortable things - in addition to
> /etc/localtime example I can add grep ebuild which compile grep without
> perl regexp support (only because libperl is in /usr while grep is in /).

I use an NFS mounted /usr file system extensively, although not on gentoo
systems - I've not got any deployments running gentoo that are large
enough to make it worth the effort.


-Ronan
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Christian Bricart]

Alex Efros schrieb:
> Hi!
> 
> On Fri, Dec 01, 2006 at 01:53:05PM +0100, Christian Bricart wrote:
>> Drawbacks:
>>   according to FHS /usr may be a remote filesystem, but /etc/localtime has
>> to exist during boot.
> 
> Can anybody in _this_ maillist confirm importance of "remote /usr" support?

quoting from http://www.pathname.com/fhs/pub/fhs-2.3.html#THEUSRHIERARCHY
[..]
/usr is the second major section of the filesystem. /usr is shareable,
read-only data. That means that /usr should be shareable between various
FHS-compliant hosts and must not be written to. Any information that is
host-specific or varies with time is stored elsewhere.
[..]

Christian

-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Alex Efros]

Hi!

On Fri, Dec 08, 2006 at 08:12:51AM +0100, Christian Bricart wrote:
> > Can anybody in _this_ maillist confirm importance of "remote /usr" support?
> quoting from http://www.pathname.com/fhs/pub/fhs-2.3.html#THEUSRHIERARCHY

AFAIK "remote /usr" idea was born many years ago, when hard drive sizes
was too small. Nowadays this has no sense anymore.

And from management/administration view sharing /usr between many servers
isn't significantly help because most complexity in administration is
updating /etc, not /usr.

Probably I miss something important but I've no idea how "remote /usr" can
make administration easier nowadays...

-- 
			WBR, Alex.
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Ronan Mullally]

On Fri, 8 Dec 2006, Alex Efros wrote:

> AFAIK "remote /usr" idea was born many years ago, when hard drive sizes
> was too small. Nowadays this has no sense anymore.
>
> And from management/administration view sharing /usr between many
> servers isn't significantly help because most complexity in
> administration is updating /etc, not /usr.
>
> Probably I miss something important but I've no idea how "remote /usr"
> can make administration easier nowadays...

There are several very good reasons for using a remote-mounted /usr:

 - You can mount it read-only so it can't be modified
 - You can easily 're-task' a server by changing what it mounts
 - Your data is easier to update
 - Your data is easier to backup

Additional complexity (to simplify things a bit) is about as complicated
changing your fstab to point at an NFS server rather than a local device.

If I've got more than 3 or 4 servers to manage in a deployment I typically
use remote-mounted root and /usr filesystems - it makes life an awful lot
easier.


-Ronan
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Alex Efros]

Hi!

On Fri, Dec 08, 2006 at 02:08:29PM +0000, Ronan Mullally wrote:
> There are several very good reasons for using a remote-mounted /usr:
> 
>  - You can mount it read-only so it can't be modified

Local /usr also can be mounted read-only.

>  - You can easily 're-task' a server by changing what it mounts

Re-task?! /usr usually contains binaries and data suitable for all tasks
so to re-task you should change what and how you execute on that server,
not /usr.

>  - Your data is easier to update

Yeah, I've already agreed with this point. But I don't think it's so
important just because in addition to /usr you should update /etc which is
different on different servers and which is much more painful to update.

>  - Your data is easier to backup

Backuping /usr is senseless operation in many cases. But in other cases if
you mount /usr read-only and sure it's same on all servers you can backup
single /usr from _any_ server, just like in case with single remote /usr.

> If I've got more than 3 or 4 servers to manage in a deployment I typically
> use remote-mounted root and /usr filesystems - it makes life an awful lot
> easier.

If you remote-mount root (using network boot?), /usr and everything else ;-)
than that's really can make life much easier, but this setup has nothing
with current topic. I'm asking about configuration where you may boot with
root but without /usr - that's why /etc/localtime symlink replaced by copy
of timezone file, grep compiled without perl regex support, etc.

-- 
			WBR, Alex.
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Ronan Mullally]

On Fri, 8 Dec 2006, Alex Efros wrote:

> >  - You can mount it read-only so it can't be modified
>
> Local /usr also can be mounted read-only.

And then re-mounted r/w if the box is rooted.  You can enforce read-only
access on the file server independantly of the application server.  In
hind-sight, this would read better as "you can export it r/o...".

> >  - You can easily 're-task' a server by changing what it mounts
>
> Re-task?! /usr usually contains binaries and data suitable for all tasks
> so to re-task you should change what and how you execute on that server,
> not /usr.

I only install the applications necessary for a server to do the job it is
tasked with.  The fewer the applications installed the less the
maintenance required.


-Ronan
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] remote /usr

[Christian Bricart]

Alex Efros schrieb:
> [..]
> 
> If you remote-mount root (using network boot?), /usr and everything else ;-)
> than that's really can make life much easier, but this setup has nothing
> with current topic. I'm asking about configuration where you may boot with
> root but without /usr - that's why /etc/localtime symlink replaced by copy
> of timezone file, grep compiled without perl regex support, etc.
> 
I think the point is (and that's why it's written in the FHS), that you
need a sane system to e.g. boot into single user mode.
First, imagine a setup that has different mount points (not necessarily
mounted from a remote system) for the different directories in he tree -
this setup is quite common and endorsed by many people - to have maybe
the files on different filesystems (e.g. ext2, ext3, reiser, XFS, JFS)
as different filesystems have deviant behavior regarding specific
performance issues, or provide e.g. ACL that you don't need on other
parts in your tree, or are to be located on different disks/IO systems,
etc..
In spite of that, you'll need a sane system when going into single user
mode (maybe to fix a broken partition - that might be /usr) or even
networkless boot.
This is not limited to /usr, but any path below the root-FS defined by
FHS in its first chapter.

Finally I think that asking for "real life scenarios" or "is this still
practicable nowadays" is not the point. The behavior is a principle
written down in FHS - that stands to regulate for standards.
You may say that your setup(s) does/do not need those premises - but
that's ok. There my be others that rely on them.. for various reasons..

Christian (who once thought /opt was a needless addition to the tree and
changed his mind about it - substituting it with averseness to /srv ;-) )

-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] [friendly reminder] sys-libs/timezone-data and MySQL

[Alex Efros]

Hi!

On Fri, Dec 01, 2006 at 10:30:15AM +0100, Francesco Riosa wrote:
> after an upgrade of sys-libs/timezone-data is good advice to update the
> mysql timezones tables too:

Hmm... What can be affected by this? How MySQL use timezone information,
which part of timezone information may change (and so require updates of
sys-libs/timezone-data) and why these changes may be important for MySQL?

-- 
			WBR, Alex.
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] [friendly reminder] sys-libs/timezone-data and MySQL

[Francesco Riosa]

>Hi!
>
>On Fri, Dec 01, 2006 at 10:30:15AM +0100, Francesco Riosa wrote:
>> > after an upgrade of sys-libs/timezone-data is good advice to update the
>> > mysql timezones tables too:
>
>Hmm... What can be affected by this? How MySQL use timezone information,
>which part of timezone information may change (and so require updates of
>sys-libs/timezone-data) and why these changes may be important for MySQL?

I did prepared a (possibly) decent answer then my MUA decided to crash and
destroy it, must be something karmik so here there is the shortened version:

sys-libs/timezone-data can change daylight saving switching day, also leap
seconds, time mangling/conversion stuff in general.
Strictly speaking you need to update timezone-data to have a sane system,
think also at "cron"ed scripts.

[TMZ] leave make suppose that most of [DTM] functions are affected. At least
if your server timezone is changed in the latest timezone-data release.

Running replication? Then [REP] will tell you subtly things that can happen
to timestamps and UPDATE/INSERT using date functions if the servers are not
in sync i.e. using the same timezone data.

Clustering? No idea and I don't even want to try.

Regards,
Francesco

[TMZ] http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
[REP] http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
[DTM] http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

-- 
gentoo-server@gentoo.org mailing list

[gentoo-server] MySQL upgrade guide

[Alex Efros]

Hi!

I notice you are one of authors for Gentoo's MySQL upgrade guide
    http://www.gentoo.org/doc/en/mysql-upgrading.xml
and I've few questions about it.

    # MYSQL --execute="FLUSH TABLES WITH READ LOCK;"
    ...
    # MYSQL --execute="UNLOCK TABLES;"

AFAIK MySQL drop any locks when connection closes, so you probably need to
open MySQL client in another console and run FLUSH TABLES/UNLOCK TABLES
there while executing tar in first console between these commands.

    # mysql_upgrade_shell --user=root --password='your_password' \
	 --protocol=tcp --datadir="${DATADIR}"

In original MySQL upgrade documentation they recommend to run mysql_upgrade
program and there is nothing about mysql_upgrade_shell script.
AFAIK mysql_upgrade_shell is older version of this program (mysql_upgrade
program was added because there no bash to execute mysql_upgrade_shell
script on Win32).
So, what's better to run - mysql_upgrade_shell or mysql_upgrade? Or there
no difference between them now?

-- 
			WBR, Alex.
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] MySQL upgrade guide

[Alex Efros]

Hi!

On Fri, Dec 08, 2006 at 01:45:31AM +0200, Alex Efros wrote:
>     # mysql_upgrade_shell --user=root --password='your_password' \
> 	 --protocol=tcp --datadir="${DATADIR}"
> 
> In original MySQL upgrade documentation they recommend to run mysql_upgrade
> program and there is nothing about mysql_upgrade_shell script.
> AFAIK mysql_upgrade_shell is older version of this program (mysql_upgrade
> program was added because there no bash to execute mysql_upgrade_shell
> script on Win32).
> So, what's better to run - mysql_upgrade_shell or mysql_upgrade? Or there
> no difference between them now?

BTW, I got error while running mysql_upgrade_shell:

---cut---
# mysql_upgrade_shell --user=root --password='MYPASS' --protocol=tcp --datadir="${DATADIR}"
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

Got a failure from command:
cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --password=MYPASS --database=mysql
Please check the above output and try again.

Running the script with the --verbose option may give you some information
of what went wrong.

If you get an 'Access denied' error, you should run this script again and
give the MySQL root user password as an argument with the --password= option
---cut---

As you see, there no "above output", so I've tried to run failed command
manually. And got strange output with some errors:

---cut---
ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
@hadGrantPriv:=1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type'
ERROR 1146 (42S02) at line 67: Table 'mysql.procs_priv' doesn't exist
ERROR 1146 (42S02) at line 68: Table 'mysql.procs_priv' doesn't exist
ERROR 1146 (42S02) at line 70: Table 'mysql.procs_priv' doesn't exist
ERROR 1146 (42S02) at line 72: Table 'mysql.procs_priv' doesn't exist
ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 100: Duplicate column name 'type'
@hadShowDbPriv:=1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 137: Duplicate column name 'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 140: Duplicate column name 'Create_tmp_table_priv'
ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor'
ERROR 1054 (42S22) at line 247: Unknown column 'Create_view_priv' in 'where clause'
ERROR 1054 (42S22) at line 277: Unknown column 'Create_routine_priv' in 'where clause'
ERROR 1054 (42S22) at line 313: Unknown column 'Create_user_priv' in 'where clause'
---cut---

Running mysql_upgrade result in similar errors but looks like it stop on
first error:

---cut---
# mysql_upgrade -p -v --basedir=/usr
Enter password: 
mysql_upgrade already done for this version
ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
---cut---

-- 
			WBR, Alex.
-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] MySQL upgrade guide

[Francesco Riosa]

Alex Efros ha scritto:
> Hi!
>
> On Fri, Dec 08, 2006 at 01:45:31AM +0200, Alex Efros wrote:
>   
>>     # mysql_upgrade_shell --user=root --password='your_password' \
>> 	 --protocol=tcp --datadir="${DATADIR}"
>>
>> In original MySQL upgrade documentation they recommend to run mysql_upgrade
>> program and there is nothing about mysql_upgrade_shell script.
>> AFAIK mysql_upgrade_shell is older version of this program (mysql_upgrade
>> program was added because there no bash to execute mysql_upgrade_shell
>> script on Win32).
>> So, what's better to run - mysql_upgrade_shell or mysql_upgrade? Or there
>> no difference between them now?
>>     
>
> BTW, I got error while running mysql_upgrade_shell:
>
> ---cut---
> # mysql_upgrade_shell --user=root --password='MYPASS' --protocol=tcp --datadir="${DATADIR}"
> This script updates all the mysql privilege tables to be usable by
> MySQL 4.0 and above.
>
> This is needed if you want to use the new GRANT functions,
> CREATE AGGREGATE FUNCTION, stored procedures, or
> more secure passwords in 4.1
>
> Got a failure from command:
> cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --password=MYPASS --database=mysql
> Please check the above output and try again.
>   

> Running the script with the --verbose option may give you some information
> of what went wrong.
>
> If you get an 'Access denied' error, you should run this script again and
> give the MySQL root user password as an argument with the --password= option
> ---cut---
>
> As you see, there no "above output", so I've tried to run failed command
> manually. And got strange output with some errors:
>
> ---cut---
> ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
> @hadGrantPriv:=1
>   
^^^ these are good, the colum is already in place and don't need to be
created === the database is up to date
> ERROR 1054 (42S22) at line 277: Unknown column 'Create_routine_priv' in 'where clause'
> ERROR 1054 (42S22) at line 313: Unknown column 'Create_user_priv' in 'where clause'
> ---cut---
>   

> Running mysql_upgrade result in similar errors but looks like it stop on
> first error:
>
> ---cut---
> # mysql_upgrade -p -v --basedir=/usr
> Enter password: 
> mysql_upgrade already done for this version
> ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
> ---cut---
>
>   
This is what I get with an already updated db:

---cut---


/usr/bin/mysql_fix_privilege_tables --verbose  -uroot  --password=${PASS}

This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!

ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type'
ERROR 1060 (42S21) at line 70: Duplicate column name 'Routine_type'
ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 100: Duplicate column name 'type'
ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 137: Duplicate column name
'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 140: Duplicate column name
'Create_tmp_table_priv'
ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor'
ERROR 1060 (42S21) at line 252: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 253: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 254: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 259: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 260: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 261: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 282: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 283: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 284: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 289: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 290: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 291: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 293: Duplicate column name 'Execute_priv'
ERROR 1060 (42S21) at line 294: Duplicate column name 'Execute_priv'
ERROR 1060 (42S21) at line 306: Duplicate column name 'max_user_connections'
ERROR 1060 (42S21) at line 315: Duplicate column name 'Create_user_priv'
done

---cut---

/usr/share/mysql/mysql_fix_privilege_tables.sql
is plain sql you can try single statments from there to see what's the
result

good night CET time remember ? ;)


-- 
gentoo-server@gentoo.org mailing list

Re: [gentoo-server] MySQL upgrade guide

[Francesco Riosa]

Alex Efros ha scritto:
> Hi!
>
> I notice you are one of authors for Gentoo's MySQL upgrade guide
>     http://www.gentoo.org/doc/en/mysql-upgrading.xml
> and I've few questions about it.
>
>     # MYSQL --execute="FLUSH TABLES WITH READ LOCK;"
>     ...
>     # MYSQL --execute="UNLOCK TABLES;"
>
> AFAIK MySQL drop any locks when connection closes, so you probably need to
> open MySQL client in another console and run FLUSH TABLES/UNLOCK TABLES
> there while executing tar in first console between these commands.
>
>     # mysql_upgrade_shell --user=root --password='your_password' \
> 	 --protocol=tcp --datadir="${DATADIR}"
>   
Verified, docs need to be updated, I'm planning to do a bunch of things
next three days on mysql this will be one.
> In original MySQL upgrade documentation they recommend to run mysql_upgrade
> program and there is nothing about mysql_upgrade_shell script.
> AFAIK mysql_upgrade_shell is older version of this program (mysql_upgrade
> program was added because there no bash to execute mysql_upgrade_shell
> script on Win32).
> So, what's better to run - mysql_upgrade_shell or mysql_upgrade? Or there
> no difference between them now?
>   
mysql_upgrade_shell  has survived better to my test but I should redo
those now probably mysql_upgrade is better maintained.

-- 
gentoo-server@gentoo.org mailing list