Uploading DOCX (four lettered extension?)
Submitted by petergraves on Sun, 08/17/2008 - 22:34.
Apologies if this post is all wrong -- my first time here :) Had a look to see if this had been raised before, but couldn't find anything related.
Running ProjectPier 0.8.0.3 - when trying to upload a file with the extension docx I get the error on this page index.php?c=files&a=add_file&active_project=1:
"Query failed with message 'Data too long for column 'type_string' at row 1'"
Uploading anything with a 3 lettered extension (such as .doc, .xls, .png etc) works ok.
Is this a database field limitation or a misconfiguration on my part?
Any pointers would be appreciated.
Many thanks,
Peter
I think this should be based on the full name of the file, but I'll have to look at the code more closely to figure out if this is a self imposed restriction since I haven't had this problem yet. Try using a short named file with the longer extension and see if that is the problem.
I just checked the database and the 'extension' column in the pp_file_type table is 10 characters long.
The column 'type_string' in pp_project_file_revisions is 50 characters long. I'm not sure what the 'type_string' value is (I would need to look at the code), but it would be worth it to try with different name lengths. I don't have time to try that this morning though...
Tim
Tim,
'type_string' is the content mimetype. Or at least, that is what i use it for in RC.
I've done a search and I do believe the issue is with the "extreme" length of the Office 2007 mime-type "strings". I don't have a clue what would posses them to make them so verbose, but, here is what I've found them – listed at another site (with similar issues in another application).
.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltx application/vnd.openxmlformats-officedocument.spreadsheetml.template
.potx application/vnd.openxmlformats-officedocument.presentationml.template
.ppsx application/vnd.openxmlformats-officedocument.presentationml.slideshow
.pptx application/vnd.openxmlformats-officedocument.presentationml.presentation
.sldx application/vnd.openxmlformats-officedocument.presentationml.slide
.docx application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotx application/vnd.openxmlformats-officedocument.wordprocessingml.template
.xlam application/vnd.ms-excel.addin.macroEnabled.12
.xlsb application/vnd.ms-excel.sheet.binary.macroEnabled.12
I see in this forum posting that the "type_string" is used to hold the mime-type information, it is pp_project_file_revisons, and is designed as a STRING of 50 characters in length. Looks like the .docx mime-type of over 60 in length (as are most of the other Office 2007 types) and is certainly the problem!
Can you give me guidance as if I can simply increase this "type_string field to say 70" in that table of the database, or, if there might be source code issues (in the PHP code) of the system if I do so? I'll be glad to test trying to fix it and provide feedback (a resolution of the issue) if you can point me to potential "problem areas".
Are you sure this has nothing to do with server / php / other settings than PP?
I just uploaded a file called: "GameCampPressemeldingSeptember2008.docx" to a project and it went just fine...
It does not appear to be server or PHP related (as far as I can tell).
I'm using the "File System" for storage of uploaded files.
When I go to the file system, into the "upload" directory, I looked at the file "attributes.php" and in that file it shows the name of the uploaded file and has the correct "mime extension" identified in it.
This would tell me that PHP and the Server are working correctly (or I believe this is a good indication), as the correct filename and mime-type are getting deep enough in the code to put the file in the file system.
It appears to me to be something related to updating the record in the database that tracks the file in project pier.
I went ahead and tried to expand the above field in the database relation... It made absolutely no difference, problem still exists with a STRING(70) field type.
Any suggestions? Do you have an ER diagram of the system/database that you can share or a Data Flow Diagram of the system to help me troubleshoot?
The problem seem to occur also with ProjectPier's older brother:
http://www.activecollab.com/forums/topic/489/
OK,
I have the fix. Increasing the field to VarChar(70) did not work. I scratch my head and thought on it... then saw this post from ActiveCollab and was sure that must have been the problem.
So, I went back in and took a closer look. My Table is setup for UTF8 character set.... a "double byte" character set.
For MS Office Documents you need a little under 70 characters for the mime-strings(2-bytes for each character). Therefore, 70 x 2 = 140 bytes for the strings. I set the field to VarChar(140) and guess what, it works.
Again: I did this using MySQL Administrator, connected to the ProjectPier Schema(Database), opened the table 'pp_project_file_revisions' for modification and set the following new value.
The field I modified was: 'type_string'
I modified the 'type_string' field's datatype (from the "install default size" which was VARCHAR(50)) to be VARCHAR(140).
Everything seems to be working just fine!
Thanks to everyone for all of the good leads in solving this!
You might consider rolling this into the next install release? I don't think Office 2007 and beyond is going away real soon.
Still can't understand why they would put such an abusively long mime-string on the new MS Office files? What a waste of space!
I've entered your solution in the issue tracker, dsdoom.