[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I need to drop, then recreate a table, then merge that table with another existing table..

Posted on 2005-04-21
7
Medium Priority
?
277 Views
Last Modified: 2012-08-13
I am running MySQL 4.1.7 for windows.

This works for Linux, but not windows. help!!!!

DROP TABLE IF EXISTS `fin_mod_users`;
CREATE TABLE `fin_mod_users` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `username` varchar(20) binary NOT NULL default '',
  `password` varchar(32) binary NOT NULL default '',
  `email` varchar(50) default NULL,
  `admin_switch` smallint(6) NOT NULL default '0',
  `groups` text,
  `deity` smallint(6) NOT NULL default '0',
  `log_sess` int(10) unsigned NOT NULL default '0',
  `last_on` int(10) unsigned NOT NULL default '0',
  `cookie` varchar(32) default NULL,
  PRIMARY KEY  (`user_id`)
) TYPE=MERGE INSERT_METHOD=LAST UNION=(mod_users);
0
Comment
Question by:trf000
  • 4
6 Comments
 
LVL 6

Expert Comment

by:campbelc
ID: 13834427
Just did this on my Windows 2003 server 4.1.9

DROP TABLE IF EXISTS fin_mod_users;

CREATE TABLE fin_mod_users (
      user_id int(10) unsigned NOT NULL default '0',
      username varchar(20) binary NOT NULL default '',
      password varchar(32) binary NOT NULL default '',
      email varchar(50) default NULL,
      admin_switch smallint(6) NOT NULL default '0',
      groups text,
      deity smallint(6) NOT NULL default '0',
      log_sess int(10) unsigned NOT NULL default '0',
      last_on int(10) unsigned NOT NULL default '0',
      cookie varchar(32) default NULL,
      PRIMARY KEY  (user_id)
    ) TYPE=MERGE INSERT_METHOD=LAST UNION=(mod_users);


Works like a champ...
0
 

Author Comment

by:trf000
ID: 13834659
I get error 1017 can't find file: 'work_mod_users.MRG' (errno:2)

no data, no table structure. I have full rights to the db.
0
 

Author Comment

by:trf000
ID: 13834945
I'm upgrading to 4.1.9... I'll see if that has any effect.

campbelc, what client are you using to execute your queries? I'm running Navicat and MySQL Query Browser.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:trf000
ID: 13835764
DROP TABLE IF EXISTS fin_mod_users;

CREATE TABLE fin_mod_users (
      user_id int(10) unsigned NOT NULL default '0',
      username varchar(20) binary NOT NULL default '',
      password varchar(32) binary NOT NULL default '',
      email varchar(50) default NULL,
      admin_switch smallint(6) NOT NULL default '0',
      groups text,
      deity smallint(6) NOT NULL default '0',
      log_sess int(10) unsigned NOT NULL default '0',
      last_on int(10) unsigned NOT NULL default '0',
      cookie varchar(32) default NULL,
      PRIMARY KEY  (user_id)
    ) ENGINE=MyISAM  UNION=(mod_users) INSERT_METHOD=LAST;

This creates the table structure correctly, but does NOT populate the table with data.
0
 

Author Comment

by:trf000
ID: 13868148
Solved it myself.

MySQL defaulted the source tables to OnnoDB. they needed to be MyISAM. now it works.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 15710755
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question