Beginnings
In the summer of 2008, I volunteered to help out a local political organization Get Out The Vote this election year. There is an old saying that goes something along the lines of, “No good deed goes unpunished”
I wish I’d remembered that before I started all this!
NOTE:
I’ve prepared a Programmer’s Cheat Sheet of
how I resolved my issues. If you just need the answers,
click here.
But, if you’d like to read a good story, carry on!
Original Plans
The project involved creating a .NET based website that was to leverage some data stored in a MS SQL Database.
“Easy enough,” I thought at first. After all, I’ve been creating websites using these tools for several years now.
.NET provides several tools for interacting with a MS SQL Server database. I opted for the ADO.NET since they work well, and I have lots of experience using them. I managed to create my web application and database and imported all the raw data that we would need for the project. So far, so good.
We then purchased our shared Windows hosting account with GoDaddy.com (the same
company that is hosting this site). I have another site site using this exact same setup. This setup is working well and I’ve experienced no problems whatsoever. I uploaded the application and prepared to create and populate the database.
This is where things started to unravel.
Database Size
My database was large. The table that held the bulk of the data has over 500 thousand records. MS SQL Databases on the GoDaddy system can import records via CSV files (CSV files are “comma separated value” files; plain text files that can be used to represent a lost of data).
I tried to upload the large CSV file, but kept getting time-out errors using the standard web-based file upload process.
I then made the first of many unsuccessful calls to GoDaddy Tech Support. The techie on the other end of the line instructed me to upload a database backup into a special folder in my account’s file system. I uploaded the backup (over 500MB in size!) and then tried to initiate a restore of the database per the instructions in GoDaddy’s help system.
Shortly, after I had started the restore, I went back to check the progress, only to find that the restore had failed. The reason given was that a MS SQL Restore can only be performed using backup files that are created by the GoDaddy system. It seems that there is a MS Security bulletin out on this. But I guess that the Tech Support staff did not know this.
The second call to Tech Support taught me about the Database Publishing Wizard produced by Microsoft. I downloaded this wizard and began to publish my data.
This, too, failed.
It seems that there is a limit on the physical size of database available to Windows shared hosting accounts. The limit, it seems is 200MB. My main data table is over 200MB by itself. The problem, is that I was not made aware of this limitation at any point during the account creating process. And this limit was not visible on any of the sign up screens that I came across when setting up the account.
Another call to tech support informed me of the 200 MB limit. The Techie’s only solution was to transfer to a dedicated hosting account. Exept that because of the database size, we would need a full MS SQL license fo our site.
Did I mention that this work is for a non-profit organization?
So the dedicated hosting with a full MS SQL Server license was out of the question.
The tech support person informed me that there is no limit on the size of a MySQL database (actually there is, but it is the size of the actual account space; mutiple GBs).
Conversion to MySQL
After much cursing and swearing, I got down to the business of downloading and installing MySQL on my development laptop (as if I needed anything else slowing things down on my old laptop). I ran the Database import/conversion wizard and copied all my data into my brand spankin’ new MySQL datbabase. But, it seems, none of the stored procedures were duplicated.
I then exported my MS SQL stored procedures into a text based SQL file. I opened the text/sql file and tried to execute the SQL in MySQL only to find that there are significant differences in how MySQL interprets SQL, when compared to MS SQL.
So I had to re-write approximately 50 stored procedures, using MySQL syntax. Did I mention that I had not used MySQL before?
After I finished re-writing the stored procs, I then had to convert my web app to make use of the new data source. Fortuntately, I had all my database functions in a single file. Even better, there were only four functions that needed to be modified.
So I downloaded the MySQL ASP.NET database connector/DLL and rewrote my application. Initial testing identified about 6 places where I needed to “tweak” some code. And then voila it worked!!
So, I then created a database backup and uploaded the backup file (~250MB) to GoDaddy and then initiated a Restore operation. It eventually ran successfully. I ran some test using the MyPHPAdmin application. (MyPHPAdmin runs slower than molasses in winter on the GoDaddy servers, of course).
ASP.NET Login Controls
Because this application has restrictions on who has access to what, I wrote the application using the .NET login controls. They use a database to track users,
permissions, etc. But…. you will recall the we moved our database over to
MySQL.
Fortunately, GoDaddy allows you to have both a MS SQL Database and multiple
MySQL databases. So I then set about learning how to use a SQL database as my
Authentication provider’s backing store. Another two days later, and I was able to get the accounts logging in, resetting passwords, etc.
I then uploaded my application and tried to perform some of the tasks that this application was designed to do.
No go.
MySQL .NET Connector
The .NET connector that MySQL makes available requires “full trust” in order to run. But the shared hosting environment is a “medium trust” environment so the connection would not work. I downloaded a patch and recompiled the .NET connector so that it allows a medium trust application to use the connector.
So I uploaded the new connector’s DLL and executed some AdHoc queries. Success! Or so I thought. I then tried to use the application’s pages which called on some of my stored procedures (which I discussed above).
No go. Again.
The .NET connector now worked using ad hoc queries, but required admin rights in order to execute stored procedures. Obviously, GoDaddy was not about to give my account admin rights. So, all seemed lost. Again.
But, I then realized that I had been able to use AdHoc queries to call my stored procedures. So, I re-wrote my database access class to create SQL statements that get executed as AdHoc queries, and FINALLY, I was able to get the application to work as designed.
Well, not as designed, but as originally intended.
Almost…..
iTextSharp Library
I had originally designed my application to use Crystal Reports to generate PDF outputs. But (surprise!) GoDaddy does not make Crystal Reports available on their servers. So Crystal Reports is out.
I then found the iTextSharp library which can generate PDF files from C# code. But, the reports has to be created in code, not in a designer, as are the Crystal Reports outputs.
So five days of coding later, and I was able to generate my reports using the iTextSharp library. Almost…
When I first tried to create some reports I got an error telling me that I did not have permissions to write to my report output folder. I then tried deleting the folder and having the application create the folder. Still no-go.
Finally, I found the “Permission” page on the GoDaddy site and gave my output folder “Write” permissions. Good to go? Well, not quite.
When I next tried to run a report, I got error messages stating:
System.Security.SecurityException: That assembly does not allow partially trusted callers
in that ugly yellow colored window that we’ve all come to hate. After some deft Googling, I determined that the iTextSharp library would need to be re-compiled so that Partially-Trusted callers would be able to use the iTextSharp library.
So I then downloaded the source code, and re-compiled the library to allow Partially-Trusted callers.
Specifically, I modified the AssemblyInfo.cs file by adding these references and attribute:
using System.Security;
using System.Security.Permissions;
[assembly: AllowPartiallyTrustedCallers]
Once that new library was in place (and some API calls re-written in the report), I was
finally able to generate some PDFs.
Retrospective
This is where things stand today (September, 2008): I am generating data using a MySQL database, managing user accounts using MS SQL, and producing reports using the iTextSharp library.
Would I do it again? No. There are other hosting providers that would allow me to host my large database and use Crystal Reports to produce my output. But they cost considerably more that what we are paying for GoDaddy hosting.
So… since this website is for a non-profit organization we’re here to stay.
I don’t know if anyone will read this. But if you have any questions just drop me a note.
-------------------------------------------------------------------------------------
4 Responses to GoDaddy, MySQL, iTextSharp and Shared Hosting Woes
Regan Rispoli says:
January 17, 2011 at 00:12
This blog seems to recieve a great deal of visitors. How do you promote it? It offers a nice individual spin on things. I guess having something real or substantial to talk about is the most important factor.
-------------------------------------------------------------------------------------
Reply
Kevin Olson says:
January 12, 2011 at 22:44
I was having this exact problem with GoDaddy and itextsharp. I downloaded the source code and made the modification to the AssemblyInfo.cs file.
I just installed the updated itextsharp.dll file and now my site works perfectly.
Thank you so much for this simple, yet completely effective, solution. I would have floundered-around for days searching for a solution – with no guarantee for success – were it not for you.
Well done!
-------------------------------------------------------------------------------------
Reply
darts says:
January 12, 2011 at 19:33
I don’t even know how I ended up here, but I thought this post was good. Cheers!
-------------------------------------------------------------------------------------
Reply
Touseef Ahmad Rehan says:
January 4, 2011 at 03:19
Excellent Article.. I spent almost two days to resolve the problem while writing pdf files. I was having same problem “System.Security.SecurityException: That assembly does not allow partially trusted callers”. I tried different ways but of no use. Later on i found your article while searching and tried the method you explained. Thankfully it worked…!
Touseef
Reply
-------------------------------------------------------------------------------------
Full Post is below ::
No comments:
Post a Comment