Google Cloud SQL driver change for mysql and mysqli

[garyamort]

While working on installing the Joomla! CMS under Google App Engine, I ran into an odd interaction with using Google Cloud SQL: https://developers.google.com/appengine/docs/php/cloud-sql/

There are 2 methods to connect to Google Cloud SQL server:

1) From everyplace EXCEPT a Google App Engine application running ON GAE, you use the standard IP address/port number method.

2) IF running under GAE, you instead set the host to localhost and use a socket.  Unfortunately, the socket's name contains a colon.  For example:

/cloudsql/overnumerousness-site:joomla

This causes 2 problems in the connect method:

1) The connect method lacks the ability to determine if your connecting from GAE or remotely.  Therefore you need to maintain 2 configurations, one for GAE and one for everywhere else.

2) More importantly, the current set of regular expression matching cannot handle a colon in the socket name.  No matter how the host string is arranged, either there is no socket name at all, or the socket name ends up as either /cloudsql/overnumerousness-site or joomla

Rather than try to make the Mysql and Mysqli drivers be aware of a non-standard definition, my solution is to have 2 new drivers, Gcloudsql and Gcloudsqli which extend Mysql and Mysqli and replace the current expectation for host name with one that is specific to Google App Engine/Cloud SQL.

So my config string for hostname becomes  <ipaddress>:<port>|<socket>

This way I can split the string on the | charector, and then either check to see if the application is running from a GAE instance, in which case I use the socket, or anywhere else - in which case I can pass the options on to be handled normally.

I think the appropriate place to make such a split is in the construct method since it can be easily handled there, though doing it in the connect method was my initial hack.

I figured I'd ask for feedback to see if anyone had a good reason not to use a bar as a seperator specifically for a Google Cloud SQL driver.

[garyamort]

Initial pass is https://github.com/joomla/joomla-framework/pull/313

I decided not to do MySQL since I can't think of any reason one would want to use MySQL when you know for sure MySQLi is available.

I need to review it some more but wanted to submit the code for feedback sooner rather than later.

[WooDzu]

Nice one. Since php team deprecated the old mysql driver I don't think anyone is using it.

Nice to see you are working on getting Joomla ready for GAE too. You seem to be a few steps further than me

[garyamort]

On Friday, December 13, 2013 8:08:50 PM UTC-5, WooDzu wrote:

Nice one. Since php team deprecated the old mysql driver I don't think anyone is using it.

Nice to see you are working on getting Joomla ready for GAE too. You seem to be a few steps further than me

If your looking for installing the CMS on GAE, then I have it almost completed in my repo here:

https://github.com/garyamort/joomla-gae

Some issues I've had with it:

1) When installing locally, Joomla Installer failed to detect the memcached driver[because there is no memcached extension - GAE stubs it to a python implementation for the SDK].  

To get around this,  I created my GAE Memcached engine and use a the regular expression matching features of GAE so that for installation, instead of loading installation/index.php my own php script, gae/joomla/install.php gets executed.  My install routine then is able to add a mapping to the loader to look for Joomla classes in my own folder first - so I am able to override session, cache, and storage drivers with my own custom drivers as needed.

2) After installing Joomla I ran into issues with the database driver - ie trying to use the socket name for the cloud sql server failed miserably, hence my creation of this cloud sql driver. :-)

3)  Making changes to configuration.php through the admin interface ran into a number of issues..mostly related to cache cleaning - and the xml for the config form is hardcoded to only support a fixed set of database, session, and caching drivers.  I ended up writing a system plugin which modifies the config form to add in the extra options as needed.   Once that was working, I was able to go ahead and save my changes, then deploy them to my GAE instance[and with the code to check for if it executes in the SDK or the instance for the database driver, the same configuration.php file is used in both places].

I still have some issues with the url mappings which I need to work out.  Also ran into an issue with the log and tmp directory - there are some cases where the CMS simply insists on creating a file in one of those directories - which you can't do when running on a GAE instance.  

My next step is to see if I can setup a couple of Google Cloud Storage buckets for tmp and logs, and save them in the overall configuration.  If that works, then I can deploy and it works in both places.

Assuming all that works, then it's down to tedious testing of all the admin screens to make sure they work and then figuring out my url mappings so I can get the frontend working alongside the backend.

One of the "neat" items in GAE is that I can restrict access by url pattern to ensure that it is HTTPS, ensure that there is a valid Google App Logon, and even insist that the Google App user is set as an admin for my program - so I can ensure that admin page logons are always secured.   There was also some feature to specify an "admin" url that will somehow appear in the Google App Admin interface as an iframe, so I'm curious to play with it.

[WooDzu]

Thanks. I will definitely take a look.

One thing that wonders me is how joomla/extenion updates would be handled.

When updates add/update files and folder it's not a problem, one can do it on his local machine and upload with the SDK.
But what about changes in database schema. Am I right in thinking there is no other way than do it manually by connecting the Cloud SQL and executing the same set of queries which were executed during update.

My approach here is a bit different. I'm trying to setup everything in Cloud Storage (joomla code) and keep some php interface in AppEngine to access the cloud storage. Hope one day google allows mounting buckets for read/write in AppEngine instances like Amazon did with S3 on EC2

[garyamort]

On Sunday, December 15, 2013 2:39:36 PM UTC-5, WooDzu wrote:

Thanks. I will definitely take a look.

One thing that wonders me is how joomla/extenion updates would be handled.

When updates add/update files and folder it's not a problem, one can do it on his local machine and upload with the SDK.
But what about changes in database schema. Am I right in thinking there is no other way than do it manually by connecting the Cloud SQL and executing the same set of queries which were executed during update.

My process is to install extensions/plugins on the local sdk using the cloud sql database.  IE I assign an ipaddress to my cloud sql server.  That way db updates are done in the "Joomla" server while the file changes are done on my local system and then deployed up.

That was one of the reasons I decided to use a different seperator charectory[the |] so that the same configuration.php file can be used locally and from the GAE instance.  When run on GAE it will use the socket, when run locally it will use the ip address and port.

 

My approach here is a bit different. I'm trying to setup everything in Cloud Storage (joomla code) and keep some php interface in AppEngine to access the cloud storage.

I considered that route but to me, one of the extremely nice features of GAE is the fact that it is impossible to change the PHP files unless you use the SDK or some other access to the GAE admin api.

Almost every hacked Joomla website I've been called to look at has had either a file modified[either .htaccess or index.php] to execute the crackers code - or has had an extra php file stuck someplace.  And since they all had very short log retention times, I have no clue how those files got there - was it a bug in Joomla?  Was it someone logging on via FTP?  Was it some other script on the server?  No clue...very frustrating.

By keeping all my executable code in GAE and not using their "deploy via git" system - I can't ensure someone won't add hacked files - but I can ensure that there will be a log of every single file change made to the system so I can figure out how it was done.  And since the log is maintained seperately from the system, they can't delete the logfiles even if they gain access.

 

Hope one day google allows mounting buckets for read/write in AppEngine instances like Amazon did with S3 on EC2

They do - if you use Google Compute Engine[the Google version of EC2] then you can configure your web server execute PHP directly from a gs:// path.

Even without Compute Engine, you can easily access the files from a php script running on GAE:

If you use a filepath of gs://bucketname/filename and you can access them just like local files:

https://developers.google.com/appengine/docs/php/googlestorage/

You can even include and exclude PHP scripts from those folders if you set

 google_app_engine.allow_include_gs_bucket




 I know right now their experimenting with "Compute Engine backed GAE instances" - which might allow you to combine the functionality...but since that's all python based at the moment it doesn't have a lot of interest to me.

[WooDzu]

As far as I'm aware the "Fix" button in Extension manager > Database should handle Joomla SQL updates once files have been uploaded to AppEngine. So the real question mark are the extensions... will the button fix them as well if they are using versioning properly.
Maybe we could keep a list of Joomla components known to be working in AppEngine environment.

Maybe we could add something to the documentation to instruct developers how to write components that are GAE compatible.
An example is to keep all dynamic assets in /media folder. That way we could store assets in the storage and make them accessible via media manager after some tweaking in JFolder and JFile.

[WooDzu]

Computing engine look very much like EC2 instances where you get control of entire virtual machine. I'm also not interesting in this as we do same with EC2+mounting shared S3 storage on www.redsharknews.com but it's not working ideally atm on heavy load.

I'm aware of google_app_engine.allow_include_gs_bucket but in the ideal world we would mount whole bucket as a folder.
The biggest disadvantages is that each time you require/include a file it counts against your GCS requests which will get expensive and it may have the same issues like I'm facing with EC2/S3.

I appreciate your point about security and keeping control of the sites. I think I'll drop my approach and help you getting your fork tested / improved.

[WooDzu]

FYI there's already a nice pool request in the CMS repo for some Google Storage classes you might be interested in to use for writing to /tmp, /log and potentially /media.

https://github.com/joomla/joomla-cms/pull/1925

[garyamort]

On Sunday, December 15, 2013 5:49:17 PM UTC-5, WooDzu wrote:

FYI there's already a nice pool request in the CMS repo for some Google Storage classes you might be interested in to use for writing to /tmp, /log and potentially /media.

https://github.com/joomla/joomla-cms/pull/1925

Thanks, I'll take a look when I get a chance.

Actually, for logging itself I do not WANT to use Google Cloud Storage...Google already provides an automatically connected syslog function and will take all log messages to syslog and add them to the application request logs.

The problem is that for the CMS, if you switch on debug mode, for some reason there is one class that will always try to generate a log file instead of using Joomla logging...  of course I can and will disable it when I get to that point, but I figure I'd rather having a potentially working log configuration in case anything else tries to write a logfile - then have the app break when it can't write a file.