[geeks] MySQL question

[Kurt Huhn]

On May 19, 2004, at 9:20 PM, Bill Bradford wrote:

> On Wed, May 19, 2004 at 07:20:28PM -0400, Kurt Huhn wrote:
>> Even on same hardware/OS I always dump and reload.  It's just my
>> comfort level is much higher doing that way.
>
> Okay, then tell me how to get around this:
>
> root at habu:/usr/local/mysql/data> ../bin/mysql -p mysql < mysql.sql
> Enter password:
> ERROR 1050 at line 11: Table 'columns_priv' already exists
>
> mysql.sql is a dump of the "mysql" privs database from the other box.
>
> I dont think you can start up mysql *without* already having a
> "mysql" database..
>

For your mysql privs database, you can remove the statements to create 
tables where those tables already exist, and keep the ones to insert 
the values.  Just make sure that the table structure is the same 
between the two installations.

For instance, in your mysql.sql file:

--
-- Table structure for table 'columns_priv'
--

** comment out this block**

CREATE TABLE columns_priv (
   Host char(60) binary NOT NULL default '',
   Db char(64) binary NOT NULL default '',
   User char(16) binary NOT NULL default '',
   Table_name char(64) binary NOT NULL default '',
   Column_name char(64) binary NOT NULL default '',
   Timestamp timestamp(14) NOT NULL,
   Column_priv set('Select','Insert','Update','References') NOT NULL 
default '',
   PRIMARY KEY  (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM COMMENT='Column privileges';

**stop commenting things out here**

--
-- Dumping data for table 'columns_priv'
--
**this is where the data lives, keep this stuff**

Compare the structure of the stuff your commenting out, if it's the 
same, you're good to go.






--
Kurt Huhn
kurt at k-huhn.com