1

I am building an app (JavaFx application) whose logic is completely dependent on the column names and the table names of the available database (which is in MySQL). Everything was fine as long as the column names were small enough. I encountered a major setback in a certain module which required the column names to be quite long. I came to know that MySQL does not allow column names to be larger than 64 characters.

I've tried doing: 

ALTER TABLE information_schema.`COLUMNS` MODIFY COLUMN COLUMN_NAME varchar(256);

But I'm getting the following error:

#1044 - Access denied for user 'root'@'localhost' to database 'information_schema'

root has the highest privilege but its still not allowing me to alter the information_schema.

Am I doing something wrong? Is there another way?

Or is there any other application (other than MySQL) that I can use (given that it gives the above facility of changing the maximum length of the column name).

UrsinusTheStrong
  • 1,239
  • 1
  • 16
  • 33
  • Maybe this: http://stackoverflow.com/questions/21714869/error-1044-42000-access-denied-for-root-with-all-privileges helps – apomene May 08 '15 at 09:15
  • @apomene Still not able to. 'root' had both fields (GRANT_PRIV and SUPER_PRIV) set as 'Y'. – UrsinusTheStrong May 08 '15 at 09:20
  • According to manual 64 characters seems to be the max length to the identifier: https://dev.mysql.com/doc/refman/5.0/en/identifiers.html – ZZ-bb May 08 '15 at 09:20
  • @ZZ-bb I know that. Is there a way to change it? – UrsinusTheStrong May 08 '15 at 09:21
  • 2
    Most likely not. More importantly: why would you even want a column name that exceeds 64 characters? Even 30 is annoying. – Mave May 08 '15 at 09:27
  • I don't know and I'm not sure if that's a good idea. 64 characters is a very long identifier and IMHO would look quite funny in a source code. (What if every variable name is almost one line long, I wouldn't like that.) I also think logic should not be completely dependant on the column and table names. (You choose the names and create the logic. Why are they tied together?) – ZZ-bb May 08 '15 at 09:27
  • 2
    The limit for column name exists for a reason. Since your app depends on column names, instead of trying to change inner workings of MySQL - change inner workings of your app. If your application depends on a long name identifier - hash that identifier with md5 or sha1 or some other short hashing algorithm. Use the hash for actual column name, and have your app depend on a lookup table that you store internally in the app. Technically you have 20 minutes of work to do that. Alternative is to completely waste weeks on stupid workarounds. – N.B. May 08 '15 at 09:28
  • @N.B. It seems to be a good idea. I would just need to have a lookup table. – UrsinusTheStrong May 08 '15 at 09:32
  • @Mave The user of the app would never know. The headache is for the coder (only). I am just thinking of a new way to do things, that's it. – UrsinusTheStrong May 08 '15 at 09:34
  • @ZZ-bb It doesn't matter if the code looks funny as long as I'm able to make the app work seamlessly. As far as, the logic being tied up to table name is concerned, I'm trying a new approach that can reduce my lines of code. – UrsinusTheStrong May 08 '15 at 09:39

0 Answers0