Hi, at the current state of your database, you have to use one of the alternatives I told you with the last mail. If the application is working in parallel, then during this work the application will have no chance to work with the table. But with lock table old_name in exclusive mode create table new_name as select * from old_name + privileges + index-creation rename table old_name to dummy_name (or drop table) rename table new_name to old_name + re-creation of views the time-interval for impossibility of normal work should be very small.
But for the future you should use a different way of handling your synchronization, otherwise you will have to repeat the work needed now from time to time. Why don't leave the column where it is? Fill it for those records you will delete afterwards. As I understood for the remaining records this column has the NULL-value as noone updated it. And then this column will not cause trouble except for select * where it will be output, too. But this could be handled with rename table old_name to new_name create view old_name as select ...,...,.. (all columns except for this special one) from new_name. set privileges for the view as you did for the table. Then normal work can be done as usual. Drop privileges for the table except for the user with the application using the special column and change the tablename in the sync-application to the new_name (knowing this additional column). May be that this whole stuff with additional view is not needed as with your current behaviour there is some time where the table has this additional column and other times it does not have this. And this does not cause trouble for the normal work, either. This in / Out of the column is only bad for the normal work as with this kind of DDL for a table, statements prepared for this table become invalid and have to be re-prepared at next time of execution. --> please think of the NEED to drop the column or just your WISH to get rid of this part-time superfluous column. Elke SAP Labs Berlin Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269 Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank. This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation. > -----Original Message----- > From: Chris Jölly [mailto:[EMAIL PROTECTED] > Sent: Freitag, 23. März 2007 11:19 > To: Zabach, Elke > Cc: [email protected] > Subject: AW: General error;-1002 POS(1) Too many columns > > Hi Elke! > > thanks for your help. unfortunately we use this column to > filter out data which should > be deleted during synchronization of an maxdb based database > with an oracle based > database. and therefore we add this marker column at the > beginning of the process > of synchronization and then drop it afterwards as we don't > need the column during > normal operation of our applications. > > are there a possibility to recover this tables without a need > to completely drop and > rebuild the tables so that the application doesn't go down? > > Chris > > > > -----Ursprüngliche Nachricht----- > > Von: Zabach, Elke [mailto:[EMAIL PROTECTED] > > Gesendet: Freitag, 23. März 2007 08:28 > > An: Chris Jölly; [email protected] > > Betreff: RE: General error;-1002 POS(1) Too many columns > > > > > > Hi, > > > > one reason may be that you altered your table tooooooo often > > with alter table add / alter table drop. > > This may happen in case of application development, I know. > > As with normal alter table drop only the description of the > > table is changed and the data remains > > unchanged, the old, 'dropped' columns remain even in the > > description as invisible columns to describe > > the used, but not selectable part of the records. > > In case of millions of records this is the only chance to > > finish the alter table within a short time. > > And no distinction is made if there are many or few/none > > records in the table. The behaviour is the same. > > And after x alter tables there are few columns visible, but > > many columns stored in the description > > and the only help is alter table with the option RELEASE > > SPACE or an alter table modify causing > > the table to be restructured, for example if a varchar-column > > of definition length <= 254 (127 in case of UNICODE) > > is changed to one with a higher maximum length or vice versa > > or making 1-n column(s) the primary key > > or the like or rename table and create table <old_name> as > > select * from <new_name>, thus making the > > stuff explicit. Then do not forget privileges, views, > > triggers, indexes for the new table with the old name. > > (I prefer one of the alter tables because of all that stuff.) > > > > Good luck > > > > Elke > > SAP Labs Berlin > > > > Sitz der Gesellschaft/Registered Office: Walldorf, Germany > > > > Vorstand/SAP Executive Board: Henning Kagermann > > (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, > > Claus Heinrich, Gerhard Oswald, Peter Zencke > > > > Vorsitzender des Aufsichtsrats/Chairperson of the SAP > > Supervisory Board: Hasso Plattner > > > > Registergericht/Commercial Register Mannheim No HRB 350269 > > > > Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder > > sonstige vertrauliche Informationen enthalten. Sollten Sie > > diese E-Mail irrtümlich erhalten haben, ist Ihnen eine > > Kenntnisnahme des Inhalts, eine Vervielfältigung oder > > Weitergabe der E-Mail ausdrücklich untersagt. > > > > Bitte benachrichtigen Sie uns und vernichten Sie die > > empfangene E-Mail. Vielen Dank. > > > > This e-mail may contain trade secrets or privileged, > > undisclosed, or otherwise confidential information. If you > > have received this e-mail in error, you are hereby notified > > that any review, copying, or distribution of it is strictly > > prohibited. Please inform us immediately and destroy the > > original transmittal. Thank you for your cooperation. > > > > > > > > > -----Original Message----- > > > From: Chris Jölly [mailto:[EMAIL PROTECTED] > > > Sent: Donnerstag, 22. März 2007 18:40 > > > To: [email protected] > > > Subject: General error;-1002 POS(1) Too many columns > > > > > > Hello! > > > > > > i have a small table with one pk integer column and 4 > > varchar columns > > > and want to issue the following alter statement to add a > new column: > > > > > > alter table my_table add ("delete_record" smallint) > > > > > > if the table both is filled or empty i can not add the > > > column. i always > > > get the error message: > > > > > > > > > ---- Error ------------------------------- > > > Auto Commit: On, SQL Mode: Internal, Isolation Level: Not > Committed > > > General error;-1002 POS(1) Too many columns > > > alter table my_table add ( "delete_record" smallint ) > > > > > > whats wrong with that statement? > > > > > > thx, chris > > > > > > -- > > > the version of the database is 7.5 running at suse linux... > > > > > > > > > > > > -- > > > MaxDB Discussion Mailing List > > > For list archives: http://lists.mysql.com/maxdb > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
