1. phpLiteAdmin Avatar phpLiteAdmin
  2. phpLiteAdmin
  3. public
  4. Issues

Cannot delete field/column in table

Issue #143 duplicate
Former user created an issue

Originally reported on Google Code with ID 143

What steps will reproduce the problem?

Cannot delete column in table (when going to table->structure->"column's delete link").
I get the following error when trying to do this:
Error: not an error.
This may be a bug that needs to be reported at code.google.com/p/phpliteadmin/issues/list


What version of the product are you using? On what operating system? 
phpLiteAdmin v1.9.3; Windows XP SP3

Which
Database Extension (PDO/SQLiteDatabase/SQLiteDatabase3 - see Database
structure-tab in phpLiteAdmin)?
SQLite version: 2.8.17
SQLite extension: SQLiteDatabase

Reported by jgjooste on 2012-11-13 14:24:44

Comments (26)

  1. Former user Account Deleted 
    Hey again

It seems that SqLite doesn't support: ALTER TABLE 'table_name' DROP 'columns_name'
: http://www.sqlite.org/faq.html#q11

So we need to change it the way its described on there site.

    Reported by teryaki1963 on 2012-11-13 15:32:30

  2. Christopher Kramer 
    Hey,

SQLite does not support ALTER TABLE DROP, but phpLiteAdmin does ;-)
See issue #12. We implement this ourselves...
But it's not completely debugged yet...

Could you please set $debug=true; and post all the output you get when trying to delete
the column?

Thanks in advance!

    Reported by crazy4chrissi on 2012-11-13 15:36:58

  3. Former user Account Deleted 
    I also get same Error. This is the result of $debug:

ALTER TABLE QUERY=(ALTER TABLE "notes" DROP "w", DROP "h"), tablename=(notes), alterdefs=(DROP
"w", DROP "h")ALTER TABLE: table=(notes), alterdefs=(DROP "w", DROP "h")SQL?
SQL?
createtemptableSQL=(CREATE TEMPORARY TABLE 't1352821949' ([id] INTEGER PRIMARY KEY
AUTOINCREMENT, [user] VARCHAR (50), [w] INTEGER (4), [h] INTEGER (4), [note] TEXT))SQL?
def=DROP "w"action=(drop), column=(w), column_escaped=(w)preg_column_definition=(\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[)
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+)preg_columns_before=((?:((?:\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[)
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+,\s*)*\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[)
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+),\s*)?)CREATE TEMPORARY TABLE
't1352821949' ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [user] VARCHAR (50), [w] INTEGER
(4), [h] INTEGER (4), [note] TEXT)CREATE TEMPORARY TABLE 't1352821949' ([id] INTEGER
PRIMARY KEY AUTOINCREMENT, [user] VARCHAR (50), [w] INTEGER (4), [h] INTEGER (4), [note]
TEXT)/^\s*(CREATE\s+TEMPORARY\s+TABLE\s+'?t1352821949'?\s*\()(?:((?:\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[)
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+,\s*)*\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[)
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+),\s*)?\s*(?:'w'|w|"w"|`w`|\[w\])\s+(?:(?:'(?:[^']|'')*'|[^,')"\[`]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+(,\s*([^)]+))?\s*\)\s*$/

    Reported by teryaki1963 on 2012-11-13 15:56:29

  4. Christopher Kramer 
    Okay, thanks. that's really helpful (although it might not look like it ^^)
I am going to debug this later.
It looks like the maximum bounds ("INTEGER (4)") do not match the regex correctly.
I'll fix that.

    Reported by crazy4chrissi on 2012-11-13 18:57:19 - Status changed: Accepted

  5. Christopher Kramer 
    Okay, so this should work now. I just committed a version that fixes this.
You can get the latest svn version here:
https://phpliteadmin.googlecode.com/svn/source/1.9.4/phpliteadmin.php

Can both of you confirm it works now?

If not, please post the debug output.

I also improved error handling. Now (even if not in debug mode) alterTable will display
meaningful error messages if it fails.

    Reported by crazy4chrissi on 2012-11-13 21:35:44

  6. Christopher Kramer 
    By the way: it might be the case that Ayman ran into a completely different problem
than jgjooste. It really depends a lot on how you originally created your table and
what you try to alter. So if jgjooste still has the problem, please post the debug
info.
(Ayman: You too if you still run into some alterTable problem...)

    Reported by crazy4chrissi on 2012-11-13 21:47:45

  8. Christopher Kramer 
    By the way: If anybody of you ever develops or debugs regex, try this page:
http://gskinner.com/RegExr/
Also works as an Adobe Air app without internet.

I dream of a tool even a lot better, but this is already quite a lot of help. If I
have too much time some day I might develop a real cool regex IDE. Well, maybe an eclipse
plugin or something would be cool.

    Reported by crazy4chrissi on 2012-11-13 23:09:19

  10. Former user Account Deleted 
    By the way. 
having a "Create new Table with same structure" is very simple cause phpliteadmin has
already the "Query used to create this table".
We need to change the table name (with the given name) and run the query.

    Reported by teryaki1963 on 2012-11-13 23:28:03

  11. Former user Account Deleted 
    I still get an error when trying to delete a column in a table. Here is the error message:

ERROR: Error: Altering of Table failed (drop) - Pattern did not match on your original
CREATE TABLE statement. Please post a bug report..
This may be a bug that needs to be reported at code.google.com/p/phpliteadmin/issues/list

And here is the debug output:

ALTER TABLE QUERY=(ALTER TABLE "forum_users" DROP "read_topics"), tablename=(forum_users),
alterdefs=(DROP "read_topics")ALTER TABLE: table=(forum_users), alterdefs=(DROP "read_topics")SQL?
SQL?
createtemptableSQL=(CREATE TEMPORARY TABLE 't1352879008' CREATE TABLE forum_users (
id INTEGER NOT NULL, group_id INTEGER NOT NULL DEFAULT 4, username VARCHAR(200) NOT
NULL DEFAULT '', password VARCHAR(40) NOT NULL DEFAULT '', email VARCHAR(50) NOT NULL
DEFAULT '', title VARCHAR(50), realname VARCHAR(40), url VARCHAR(100), jabber VARCHAR(75),
icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30), yahoo VARCHAR(30), location VARCHAR(30),
use_avatar INTEGER NOT NULL DEFAULT 0, signature TEXT, disp_topics INTEGER, disp_posts
INTEGER, email_setting INTEGER NOT NULL DEFAULT 1, save_pass INTEGER NOT NULL DEFAULT
1, notify_with_post INTEGER NOT NULL DEFAULT 0, show_smilies INTEGER NOT NULL DEFAULT
1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig INTEGER NOT NULL DEFAULT 1, show_avatars
INTEGER NOT NULL DEFAULT 1, show_sig INTEGER NOT NULL DEFAULT 1, timezone FLOAT NOT
NULL DEFAULT 0, language VARCHAR(25) NOT NULL DEFAULT 'English', style VARCHAR(25)
NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT NULL DEFAULT 0, last_post INTEGER,
registered INTEGER NOT NULL DEFAULT 0, registration_ip VARCHAR(15) NOT NULL DEFAULT
'0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics TEXT, admin_note VARCHAR(30),
activate_string VARCHAR(50), activate_key VARCHAR(8), PRIMARY KEY (id) ))SQL?
def=DROP "read_topics"action=(drop), column=(read_topics), column_escaped=(read_topics)preg_column_definition=(\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+)preg_columns_before=((?:((?:\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+,\s*)*\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+),\s*)?)CREATE TEMPORARY TABLE
't1352879008' CREATE TABLE forum_users ( id INTEGER NOT NULL, group_id INTEGER NOT
NULL DEFAULT 4, username VARCHAR(200) NOT NULL DEFAULT '', password VARCHAR(40) NOT
NULL DEFAULT '', email VARCHAR(50) NOT NULL DEFAULT '', title VARCHAR(50), realname
VARCHAR(40), url VARCHAR(100), jabber VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50),
aim VARCHAR(30), yahoo VARCHAR(30), location VARCHAR(30), use_avatar INTEGER NOT NULL
DEFAULT 0, signature TEXT, disp_topics INTEGER, disp_posts INTEGER, email_setting INTEGER
NOT NULL DEFAULT 1, save_pass INTEGER NOT NULL DEFAULT 1, notify_with_post INTEGER
NOT NULL DEFAULT 0, show_smilies INTEGER NOT NULL DEFAULT 1, show_img INTEGER NOT NULL
DEFAULT 1, show_img_sig INTEGER NOT NULL DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT
1, show_sig INTEGER NOT NULL DEFAULT 1, timezone FLOAT NOT NULL DEFAULT 0, language
VARCHAR(25) NOT NULL DEFAULT 'English', style VARCHAR(25) NOT NULL DEFAULT 'Oxygen',
num_posts INTEGER NOT NULL DEFAULT 0, last_post INTEGER, registered INTEGER NOT NULL
DEFAULT 0, registration_ip VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER
NOT NULL DEFAULT 0, read_topics TEXT, admin_note VARCHAR(30), activate_string VARCHAR(50),
activate_key VARCHAR(8), PRIMARY KEY (id) )CREATE TEMPORARY TABLE 't1352879008' CREATE
TABLE forum_users ( id INTEGER NOT NULL, group_id INTEGER NOT NULL DEFAULT 4, username
VARCHAR(200) NOT NULL DEFAULT '', password VARCHAR(40) NOT NULL DEFAULT '', email VARCHAR(50)
NOT NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40), url VARCHAR(100), jabber
VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30), yahoo VARCHAR(30),
location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature TEXT, disp_topics
INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT 1, save_pass INTEGER
NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT 0, show_smilies INTEGER
NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig INTEGER NOT NULL
DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER NOT NULL DEFAULT
1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL DEFAULT 'English',
style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT NULL DEFAULT 0,
last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip VARCHAR(15)
NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics TEXT,
admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8), PRIMARY
KEY (id) )/^\s*(CREATE\s+TEMPORARY\s+TABLE\s+'?t1352879008'?\s*\()(?:((?:\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+,\s*)*\s*(?:'(?:[^']|'')+'|[^
'"\[`]+|"(?:[^"]|"")+"|`(?:[^`]|``)+`|\[(?:[^\]]|\]\])+\])(?:\s+(?:'(?:[^']|'')*'|[^'",`\[
]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+),\s*)?\s*(?:'read_topics'|read_topics|"read_topics"|`read_topics`|\[read_topics\])\s+(?:(?:'(?:[^']|'')*'|[^,'"\[`]*|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[(?:[^\]]|\]\])*\]))+(,\s*(.+))?\s*\)\s*$/

    Reported by jgjooste on 2012-11-14 08:30:11

  12. Christopher Kramer 
    @ Ayman: Thanks for RegexBuddy. I'll have a look on it. But unfortunately it's not free
 and seems to be win-only.
I think I'll check out some alternatives soon (http://alternativeto.net/software/regexbuddy/).

"having a "Create new Table with same structure" is very simple cause phpliteadmin
has already the "Query used to create this table".
We need to change the table name (with the given name) and run the query."

Yeah, well. Works like this if we do a table-rename only and the table has no indexes
and no triggers. "Real" altering (i.e. adding/dropping/changing columns) is a lot more
complicated I'm afraid.

@jgjooste: Thanks a lot! I'll debug it this evening.

    Reported by crazy4chrissi on 2012-11-14 11:48:27

  13. Christopher Kramer 
    Hmm... strange things happening here.

It seems as phpLiteAdmin did not correctly replace "CREATE TABLE forum_users" with
"CREATE TEMPORARY TABLE 't1352879008'" here. This later causes the problem that the
drop-regex doesn't match.

I tried to reproduce this with your CREATE TABLE statement. The tablename gets replaced
correctly for me, but php crashes while trying to do the preg_replace. It does not
log or produce any error, it just crashes. Apache logs:
Parent: child process exited with status 255 -- Restarting

I guess it's the same as this problem:
http://stackoverflow.com/questions/7620910/regexp-in-preg-match-function-returning-browser-error

I haven't read the whole answer there yet. Let's see if it helps us.

    Reported by crazy4chrissi on 2012-11-16 22:09:08

  14. Christopher Kramer 
    Okay, I tuned the regex a bit using possessive quantifiers to reduce memory usage and
avoid the stack overflow. I was now able to alter a table created with the same CREATE
TABLE statement as your table.

I am not sure you had the same problem as I had when I wanted to alter this table.
But nevertheless, it would be very helpful if you could try the newest development
version.
You can get it here:
http://phpliteadmin.googlecode.com/svn/source/1.9.4/phpliteadmin.php

Please let me know whether you are now able to alter your table.
If not, please again provide debug output.

Thanks a lot!

    Reported by crazy4chrissi on 2012-11-17 00:02:13 - Status changed: Started

  15. Former user Account Deleted 
    Hi, I tried the latest development version, but still get an error. Here is the error
message:

ERROR: Error: Altering of Table failed - could not replace the tablename with the temporary
one!?.
This may be a bug that needs to be reported at code.google.com/p/phpliteadmin/issues/list

And here is the debug output:

ALTER TABLE QUERY=(ALTER TABLE "forum_users" DROP "read_topics"), tablename=(forum_users),
alterdefs=(DROP "read_topics")ALTER TABLE: table=(forum_users), alterdefs=(DROP "read_topics")SQL?
SQL?
origsql=(CREATE TABLE forum_users ( id INTEGER NOT NULL, group_id INTEGER NOT NULL
DEFAULT 4, username VARCHAR(200) NOT NULL DEFAULT '', password VARCHAR(40) NOT NULL
DEFAULT '', email VARCHAR(50) NOT NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40),
url VARCHAR(100), jabber VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30),
yahoo VARCHAR(30), location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature
TEXT, disp_topics INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT
1, save_pass INTEGER NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT
0, show_smilies INTEGER NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig
INTEGER NOT NULL DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER
NOT NULL DEFAULT 1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL
DEFAULT 'English', style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT
NULL DEFAULT 0, last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip
VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics
TEXT, admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8),
PRIMARY KEY (id) ))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'forum_users'|forum_users|"forum_users"|`forum_users`|\[forum_users\])\s*+(\(.*+)$/i)ERROR:
could not get rid of CREATE TABLE

    Reported by jgjooste on 2012-11-20 14:36:38

  16. Christopher Kramer 
    Thanks!

Hmm... that's pretty strange. The regex that didn't match the string on your computer
matches on mine. And I cannot see a reason for it.

I attach the test-DB I used to test this. Please try whether it works for you with
this database. Thanks.

    Reported by crazy4chrissi on 2012-11-20 21:23:11

    <hr> * Attachment: test_issue_143

  17. Former user Account Deleted 
    The test-DB you attached works without error when I try to delete a column from it,
but I still get the same error in the DB I use. I have attached my test-DB so that
you can test it yourself.

    Reported by jgjooste on 2012-11-21 08:41:37

    <hr> * Attachment: test_db

  18. Christopher Kramer 
    Okay, thanks a lot.
The problem is caused by newlines in the CREATE TABLE statement. I could not see these
newlines from your debug-output because HTML shown in a browser does not display newlines
(unless they are converted to <br>).

The problem in the regex was that . does match any character _except newlines_. To
make it match newlines, the /s modifier is available. So enabling it where necessary
fixed the problem for me.

I noticed another problem with your database: It is not possible to add a column to
this table at the moment. The reason is the way PRIMARY KEY is defined in the CREATE
TABLE statement. Moreover, the error is not catched correctly in this case.

I will fix this soon as well.

Could you please try whether you can now drop columns successfully? You can get the
latest development version from svn as usual:
http://phpliteadmin.googlecode.com/svn/source/1.9.4/phpliteadmin.php

Thanks a lot for reporting this!

    Reported by crazy4chrissi on 2012-11-21 14:50:41

  19. Former user Account Deleted 
    Thanks, it now works perfectly! Just out of interest: when deleting a column in a table,
the order of the table's indexes are reversed every time.

    Reported by jgjooste on 2012-11-21 15:56:38

  20. Christopher Kramer 
    Thanks for confirming that it works.

Regarding indexes: What do you mean by "reversed"? Are they shown in another order
afterwards? I did not even think about whether indexes have an order at all ;-)

If you alter a table, phpLiteAdmin will create an altered copy of your table and replace
your table with this copy. It also needs to recreate all indexes and triggers for the
new altered table. Maybe it regenerates the indexes in the reversed order than they
were originally created. Shouldn't make a difference though.

The problem is that SQLite does not support ALTER TABLE out of the box so we need to
code this workaround.

    Reported by crazy4chrissi on 2012-11-21 16:14:59

  21. Former user Account Deleted 
    If the indexes were listed as:
Index1
Index2
Index3
After deleting a column, they are listed as:
Index3
Index2
Index1

So as you said, it looks like the indexes are regenerated in the reversed order.

It's just nice when visually comparing tables etc. to keep them in their original order.

    Reported by jgjooste on 2012-11-21 16:31:49

  22. Christopher Kramer 
    Okay. Please try the current development version. It should not reorder indexes anymore.

    Reported by crazy4chrissi on 2012-11-21 18:38:24

  23. Former user Account Deleted 
    Thanks, the indexes are not reordered anymore when deleting a column.

But I have now noticed another issue: you cannot delete a column which has an index
added to it. E.g. if you try to delete the 'username' column in the test db I attached
in comment 17, you get the following warning:

Warning: SQLiteDatabase::queryExec() [sqlitedatabase.queryexec]: table forum_users
has no column named username in C:\wamp\www\phpadminlite\phpliteadmin.php on line 1298

And here is the debug output:


ALTER TABLE QUERY=(ALTER TABLE "forum_users" DROP "username"), tablename=(forum_users),
alterdefs=(DROP "username")ALTER TABLE: table=(forum_users), alterdefs=(DROP "username")SQL?
SQL?
origsql=(CREATE TABLE forum_users ( id INTEGER NOT NULL, group_id INTEGER NOT NULL
DEFAULT 4, username VARCHAR(200) NOT NULL DEFAULT '', password VARCHAR(40) NOT NULL
DEFAULT '', email VARCHAR(50) NOT NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40),
url VARCHAR(100), jabber VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30),
yahoo VARCHAR(30), location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature
TEXT, disp_topics INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT
1, save_pass INTEGER NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT
0, show_smilies INTEGER NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig
INTEGER NOT NULL DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER
NOT NULL DEFAULT 1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL
DEFAULT 'English', style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT
NULL DEFAULT 0, last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip
VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics
TEXT, admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8),
PRIMARY KEY (id) ))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'forum_users'|forum_users|"forum_users"|`forum_users`|\[forum_users\])\s*+(\(.*+)$/is)createtemptableSQL=(CREATE
TEMPORARY TABLE 't1354013723' ( id INTEGER NOT NULL, group_id INTEGER NOT NULL DEFAULT
4, username VARCHAR(200) NOT NULL DEFAULT '', password VARCHAR(40) NOT NULL DEFAULT
'', email VARCHAR(50) NOT NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40),
url VARCHAR(100), jabber VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30),
yahoo VARCHAR(30), location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature
TEXT, disp_topics INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT
1, save_pass INTEGER NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT
0, show_smilies INTEGER NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig
INTEGER NOT NULL DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER
NOT NULL DEFAULT 1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL
DEFAULT 'English', style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT
NULL DEFAULT 0, last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip
VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics
TEXT, admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8),
PRIMARY KEY (id) ))SQL?
def=DROP "username"action=(drop), column=(username), column_escaped=(username)preg_column_definition=(\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)preg_columns_before=((?:((?:\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)CREATE TEMPORARY
TABLE 't1354013723' ( id INTEGER NOT NULL, group_id INTEGER NOT NULL DEFAULT 4, username
VARCHAR(200) NOT NULL DEFAULT '', password VARCHAR(40) NOT NULL DEFAULT '', email VARCHAR(50)
NOT NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40), url VARCHAR(100), jabber
VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30), yahoo VARCHAR(30),
location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature TEXT, disp_topics
INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT 1, save_pass INTEGER
NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT 0, show_smilies INTEGER
NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig INTEGER NOT NULL
DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER NOT NULL DEFAULT
1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL DEFAULT 'English',
style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT NULL DEFAULT 0,
last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip VARCHAR(15)
NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics TEXT,
admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8), PRIMARY
KEY (id) )CREATE TEMPORARY TABLE 't1354013723' ( id INTEGER NOT NULL, group_id INTEGER
NOT NULL DEFAULT 4, password VARCHAR(40) NOT NULL DEFAULT '', email VARCHAR(50) NOT
NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40), url VARCHAR(100), jabber
VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30), yahoo VARCHAR(30),
location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature TEXT, disp_topics
INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT 1, save_pass INTEGER
NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT 0, show_smilies INTEGER
NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig INTEGER NOT NULL
DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER NOT NULL DEFAULT
1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL DEFAULT 'English',
style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT NULL DEFAULT 0,
last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip VARCHAR(15)
NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics TEXT,
admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8), PRIMARY
KEY (id) )/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1354013723'\s*+\()(?:((?:\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'username'|username|"username"|`username`|\[username\])\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"\[`]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+(,\s*(.+))?\s*\)\s*$/srecreate=(CREATE
INDEX forum_users_registered_idx ON forum_users(registered);)recreate=(CREATE INDEX
forum_users_username_idx ON forum_users(username);)BEGIN; CREATE TEMPORARY TABLE 't1354013723'
( id INTEGER NOT NULL, group_id INTEGER NOT NULL DEFAULT 4, username VARCHAR(200) NOT
NULL DEFAULT '', password VARCHAR(40) NOT NULL DEFAULT '', email VARCHAR(50) NOT NULL
DEFAULT '', title VARCHAR(50), realname VARCHAR(40), url VARCHAR(100), jabber VARCHAR(75),
icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30), yahoo VARCHAR(30), location VARCHAR(30),
use_avatar INTEGER NOT NULL DEFAULT 0, signature TEXT, disp_topics INTEGER, disp_posts
INTEGER, email_setting INTEGER NOT NULL DEFAULT 1, save_pass INTEGER NOT NULL DEFAULT
1, notify_with_post INTEGER NOT NULL DEFAULT 0, show_smilies INTEGER NOT NULL DEFAULT
1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig INTEGER NOT NULL DEFAULT 1, show_avatars
INTEGER NOT NULL DEFAULT 1, show_sig INTEGER NOT NULL DEFAULT 1, timezone FLOAT NOT
NULL DEFAULT 0, language VARCHAR(25) NOT NULL DEFAULT 'English', style VARCHAR(25)
NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT NULL DEFAULT 0, last_post INTEGER,
registered INTEGER NOT NULL DEFAULT 0, registration_ip VARCHAR(15) NOT NULL DEFAULT
'0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics TEXT, admin_note VARCHAR(30),
activate_string VARCHAR(50), activate_key VARCHAR(8), PRIMARY KEY (id) ); INSERT INTO
"t1354013723"("id", "group_id", "username", "password", "email", "title", "realname",
"url", "jabber", "icq", "msn", "aim", "yahoo", "location", "use_avatar", "signature",
"disp_topics", "disp_posts", "email_setting", "save_pass", "notify_with_post", "show_smilies",
"show_img", "show_img_sig", "show_avatars", "show_sig", "timezone", "language", "style",
"num_posts", "last_post", "registered", "registration_ip", "last_visit", "read_topics",
"admin_note", "activate_string", "activate_key") SELECT "id", "group_id", "username",
"password", "email", "title", "realname", "url", "jabber", "icq", "msn", "aim", "yahoo",
"location", "use_avatar", "signature", "disp_topics", "disp_posts", "email_setting",
"save_pass", "notify_with_post", "show_smilies", "show_img", "show_img_sig", "show_avatars",
"show_sig", "timezone", "language", "style", "num_posts", "last_post", "registered",
"registration_ip", "last_visit", "read_topics", "admin_note", "activate_string", "activate_key"
FROM "forum_users"; DROP TABLE "forum_users"; CREATE TABLE 'forum_users' ( id INTEGER
NOT NULL, group_id INTEGER NOT NULL DEFAULT 4, password VARCHAR(40) NOT NULL DEFAULT
'', email VARCHAR(50) NOT NULL DEFAULT '', title VARCHAR(50), realname VARCHAR(40),
url VARCHAR(100), jabber VARCHAR(75), icq VARCHAR(12), msn VARCHAR(50), aim VARCHAR(30),
yahoo VARCHAR(30), location VARCHAR(30), use_avatar INTEGER NOT NULL DEFAULT 0, signature
TEXT, disp_topics INTEGER, disp_posts INTEGER, email_setting INTEGER NOT NULL DEFAULT
1, save_pass INTEGER NOT NULL DEFAULT 1, notify_with_post INTEGER NOT NULL DEFAULT
0, show_smilies INTEGER NOT NULL DEFAULT 1, show_img INTEGER NOT NULL DEFAULT 1, show_img_sig
INTEGER NOT NULL DEFAULT 1, show_avatars INTEGER NOT NULL DEFAULT 1, show_sig INTEGER
NOT NULL DEFAULT 1, timezone FLOAT NOT NULL DEFAULT 0, language VARCHAR(25) NOT NULL
DEFAULT 'English', style VARCHAR(25) NOT NULL DEFAULT 'Oxygen', num_posts INTEGER NOT
NULL DEFAULT 0, last_post INTEGER, registered INTEGER NOT NULL DEFAULT 0, registration_ip
VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', last_visit INTEGER NOT NULL DEFAULT 0, read_topics
TEXT, admin_note VARCHAR(30), activate_string VARCHAR(50), activate_key VARCHAR(8),
PRIMARY KEY (id) ); INSERT INTO "forum_users"("id", "group_id", "password", "email",
"title", "realname", "url", "jabber", "icq", "msn", "aim", "yahoo", "location", "use_avatar",
"signature", "disp_topics", "disp_posts", "email_setting", "save_pass", "notify_with_post",
"show_smilies", "show_img", "show_img_sig", "show_avatars", "show_sig", "timezone",
"language", "style", "num_posts", "last_post", "registered", "registration_ip", "last_visit",
"read_topics", "admin_note", "activate_string", "activate_key") SELECT "id", "group_id",
"password", "email", "title", "realname", "url", "jabber", "icq", "msn", "aim", "yahoo",
"location", "use_avatar", "signature", "disp_topics", "disp_posts", "email_setting",
"save_pass", "notify_with_post", "show_smilies", "show_img", "show_img_sig", "show_avatars",
"show_sig", "timezone", "language", "style", "num_posts", "last_post", "registered",
"registration_ip", "last_visit", "read_topics", "admin_note", "activate_string", "activate_key"
FROM "t1354013723"; DROP TABLE "t1354013723"; CREATE INDEX forum_users_registered_idx
ON forum_users(registered); CREATE INDEX forum_users_username_idx ON forum_users(username);
COMMIT;

    Reported by jgjooste on 2012-11-27 11:01:46

  24. Christopher Kramer 
    Thanks. I know this is still to be resolved. I only didn't find the time yet. From issue
#12:

-- 4a. -- TODO
I got another idea: When dropping or renaming a column with an index assigned, this
won't work at the moment. I guess one would expect (or at least "hope"...) that the
index is automatically dropped/adjusted as well in this case.
I'll implement this as well.

    Reported by crazy4chrissi on 2012-11-27 13:28:01

  26. Christopher Kramer 
    Issue 159 has been merged into this issue.

    Reported by crazy4chrissi on 2013-03-01 00:14:08

  27. Log in to comment
Assignee
–
Type
bug
Priority
minor
Status
duplicate
Component
–
Milestone
–
Version
–
Votes
0
Watchers
0
Jira: the preferred issue tracker for Bitbucket. Join the team!