Wedge

Public area => Features => The Pub => Features: Forward thinking => Topic started by: Nao on May 6th, 2011, 05:10 PM

Title: MySQL only!
Post by: Nao on May 6th, 2011, 05:10 PM
Feature: MySQL only!
Developer: Arantor
Target: modders, themers, admins
Status: 100% (complete.)
Comment:

PostgreSQL and SQlite are great database systems. But they just aren't used that much, and they add more complexity to database development in SMF. Wedge continues abstracting database queries, but doesn't attempt to convert them to another SQL language.
From our experience, users with PGSQL or SQlite installed on their server always have MySQL installed as well. If you really don't, Wedge just isn't for you, but we won't go back to applying patches for systems that no one really ever uses. It makes development tedious, longer, and probably insecure.
Title: Re: MySQL only!
Post by: Artur on May 6th, 2011, 11:17 PM
After all I think SQLite isn't made for forums. It works well with the most sites, but forums are clearly the wrong scope, except then you are setting up a testboard.
Title: Re: MySQL only!
Post by: Nao on May 7th, 2011, 12:05 AM
I guess it's mostly a matter of saying, "look, we support everything and everyone". Or perhaps the dev in charge of SQlite integration was himself playing with SQlite at the time. Or something. It just doesn't add up...
Plus, the simple fact that SMF requires an *empty* parameter[1] at the beginning of query lines has always been preposterous to me. It doesn't help anyone. At all.
 1. Used only in a few internal queries...
Title: Re: MySQL only!
Post by: Arantor on May 7th, 2011, 12:22 AM
OK, let's back up before any FUD starts flying.

Cast your mind back to late 2005 when SMF 2.0 started development. PHP 5 was still relatively new, and due to PHP's licence change, classic client MySQL (the mysql_* functions) was no longer compliant. That's why the MySQLi interface even exists, in fact.

Alongside this, PHP 5 was bringing SQLite with it, enabled by default, so in theory any host with PHP 5 would have SQLite support enabled. And with MySQL being tenuously supported it seemed reasonably logical.

There's the reason for it.[1]


The empty parameter is not always empty. Yes, it's only used in a few queries but they're ones where MySQL's syntax isn't compatible with either PostgreSQL and/or SQLite, and there was no need to slow down *all* queries for the sake of the minority. For example, there's a RAND() reference in there; except on one or other, it's RANDOM() not RAND(). Since it's only used in one place, there's the logic of using specialist replacements.

Honestly, if they were going for proper abstraction, the best place to start would have been upending the schema and removing FIND_IN_SET which is the single biggest barrier to PostgreSQL use, not to mention shooting performance in the foot on anything that isn't MySQL.

It claims to support these other platforms, but it only really gives the impression of doing so; you'd have to up performance by an order of magnitude on the backend to see an improvement in the application as a whole because of the fact that FIND_IN_SET (which is so heavily used in MySQL/SMF) is not available in PostgreSQL and is implemented as a user defined function. Guess how much that hurts performance.
 1. I'm not defending it, because it's not defensible. But there it is.
Title: Re: MySQL only!
Post by: Nao on May 7th, 2011, 12:32 AM
Yeah, SMF's implementation of a user-defined find_in_set was pretty funny...

Had forgotten about the Sqlite stuff. How exactly did the PHP/MySQL duo manage to survive the PHP license change then? I don't remember ever using mysqli_* mysqlelf...
Title: Re: MySQL only!
Post by: Arantor on May 7th, 2011, 01:00 AM
No, I haven't used MySQLi either, nor the newer mysqlnd (native driver).

What happened was, prior to 5.0, it was bundled and thus part of the source. In the case of 5.0+, it wasn't bundled, nor built by default but because it's then a dynamic linked library it's sort of on a technicality - it's then calling it through a defined interface rather than direct linking the code.

http://uk3.php.net/manual/en/faq.databases.php#faq.databases.mysql.php5 has more on it no longer being bundled - specifically, the PHP licence isn't strictly BSD/Apache licensed, but for the general use it makes no real difference.

As of 5.3.0, it's been replaced by the native driver (mysqlnd) and as such there is no physical requirement at all, so that's the future-proofed answer.
Title: Re: MySQL only!
Post by: Nao on May 7th, 2011, 07:47 AM
Okay... Technicalities came to our help for once ;)
Title: Re: MySQL only!
Post by: CJ Jackson on May 7th, 2011, 01:26 PM
I have used MySQLi_* before, the biggest advantage over MySQL_* is prepared statement

Code: [Select]
$prepare = $mysqli->prepare('INSERT INTO people (name, address, postcode) VALUES (?, ?, ?)'); // '?' specifies param
$prepare->bind_param('sss', $name, $address, $postcode); // The first param has to be string, which specifies the data type, the letter 's' is obvious.
$prepare->execute();

The great thing about prepared statement is that you don't need to sensitize the data input that done automatically.  PDO_MYSQL has better support for prepared statement.

Code: [Select]
$prepare = $pdo->prepare('INSERT INTO people (name, address, postcode) VALUES (:name, :address, :postcode)');
$param = array(
':name' => $name,
':address' => $address,
':postcode' => $postcode
);
$prepare->execute($param);

With PDO you can use named parameters and you don't need to specifies data types that done automatically.  Prepared statement is the way forward.  I like to see someone try to pull off a SQL injection with prepared statement.

I would stick with the MySQL software, mainly because of dialect different in SQL in other SQL software.  I used Oracle at Uni, it wouldn't let me insert multiple rows via 'INSERT' statement only one row is allowed per 'INSERT'.
Title: Re: MySQL only!
Post by: Nao on May 7th, 2011, 01:49 PM
Prepared statements are interesting, but they're already available in Wedge/SMF2:

wesql::query('SELECT id FROM table WHERE name IN {array_string:names}', array('names' => (array) $names));[/tt]

That makes injections completely impossible if they're properly written, of course.
Title: Re: MySQL only!
Post by: Arantor on May 7th, 2011, 01:54 PM
Plus SMF/Wedge's version doesn't require two separate dependencies as it only uses the stock mysql client, as opposed to needing the PDO core plus the PDO-MySQL specific library.
Title: Re: MySQL only!
Post by: Nao on May 7th, 2011, 01:59 PM
(Pete is the MySQL pro here. Just pretend you understood and nod with a smile. :ph34r:)
Title: Re: MySQL only!
Post by: CJ Jackson on May 7th, 2011, 02:27 PM
I understood   :thanks:  :lol:
Title: Re: MySQL only!
Post by: Artur on May 7th, 2011, 10:15 PM
Well as I remember it right SQLite (2) is a core feature since some time in php and SQLite3 is a core feature since 5.3. So you can use both. If you implement SQLite then you should stick with SQLite3 as SQLite(2) is just to slow for it.

To the PDO-Topic:
With the PDO interface it shouldn't be that hard to allow additional databases. You have here and there some differences but that's nothing that couldn't be fixed (or something like that).

Title: Re: MySQL only!
Post by: Arantor on May 7th, 2011, 10:27 PM
Except that you still have the issue of platform specific queries, because PDO doesn't rewrite queries.
Title: Re: MySQL only!
Post by: chilly on May 7th, 2011, 11:39 PM
I might not get the point but AFAIK real prepared statements as CJ Jackson posted em are not only there to avoid sql injection but to improve performance.
is that what is used or are there just checks for the data to avoid injections?
Title: Re: MySQL only!
Post by: Arantor on May 7th, 2011, 11:50 PM
Real prepared statements have a truly marginal performance improvement because the parser doesn't have to parse the query. For us, Amdahl's Law applies.
Title: Re: MySQL only!
Post by: Nao on May 7th, 2011, 11:52 PM
Just to be clear -- we are not going to offer support for anything else than MySQL, and we never will. At least I won't, and I think Pete won't...
Title: Re: MySQL only!
Post by: CJ Jackson on May 8th, 2011, 01:01 AM
Quote from Arantor on May 7th, 2011, 10:27 PM
Except that you still have the issue of platform specific queries, because PDO doesn't rewrite queries.
I agree, it's does not rewrite queries, only a full blown database abstraction layer can do that, such as doctrine (it's built on top of PDO).  PDO is only a database access layer, not an abstraction layer.

My intention was only to post the main different between MySQL and MySQLi, as Nao and Arantor never used MySQLi.
Title: Re: MySQL only!
Post by: chilly on May 8th, 2011, 01:13 PM
mysqli can be used OOP which can be real nice ;)

well... I don't know that law, yet. looks like I have to ask google
Title: Re: MySQL only!
Post by: groundup on June 22nd, 2011, 01:04 AM
Arantor, you've seen my posts about prepared statements on SMF as well as many of my other SQL related posts. One of the big things with having a query name is that you can then do caching or break it down even more. You can use the name as a hook. There's a bunch of things you can do.
Title: Re: MySQL only!
Post by: Arantor on June 22nd, 2011, 01:16 AM
Yes, I saw. I'm not entirely sure what the benefits would be of using prepared statements vs what we're already doing (and how that's only going to change going forward anyway), and looking at the documentation it seems that we'd have to use the SQL syntax rather than the more elegant method (or converting everything to use MySQLi) which really seems to eradicate any benefits we could get from it.

Most hosts still provide ext/mysql and that's fine for now. Changing to async queries and the OOP style doesn't really achieve all that much so we don't really stand to gain much from changing to MySQLi; in any case the biggest DB overhead is one that MySQLi can't optimise for and that schema changes need to come first.
Title: Re: MySQL only!
Post by: groundup on June 22nd, 2011, 02:35 AM
That's one of the benefits of the abstraction layer though: you can support MySQLi and MySQL and the title of this topic still holds true ;)
Title: Re: MySQL only!
Post by: Arantor on June 22nd, 2011, 10:36 AM
Except that we removed half of the abstraction layer because it added complexity and a series of kludgy workarounds for things.
Title: Re: MySQL only!
Post by: Snape on August 15th, 2011, 06:44 PM
Quote from Nao/Gilles on May 7th, 2011, 11:52 PM
Just to be clear -- we are not going to offer support for anything else than MySQL, and we never will. At least I won't, and I think Pete won't...
Not to "poke the bear", but have you considered/played with Percona at all?

http://www.percona.com/software/percona-server/faq/

I haven't had a chance to dig into it too much personally yet, but the online export features could be interesting from a backup perspective...
Title: Re: MySQL only!
Post by: Nao on August 15th, 2011, 06:47 PM
Never heard of it personally.
Title: Re: MySQL only!
Post by: Dragooon on August 15th, 2011, 07:10 PM
Quote from Snape on August 15th, 2011, 06:44 PM
Not to "poke the bear", but have you considered/played with Percona at all?

http://www.percona.com/software/percona-server/faq/

I haven't had a chance to dig into it too much personally yet, but the online export features could be interesting from a backup perspective...
Since it is already compatible with standard MySQL syntax, it shouldn't take long to provide support for it.
Title: Re: MySQL only!
Post by: Arantor on August 15th, 2011, 07:21 PM
I actually had an interview with them, and as for their MySQL knowledge, half of the Percona core team actually wrote the best (IMHO) book on High Performance MySQL.

It should be pretty much a drop in replacement for MySQL.
Title: Re: MySQL only!
Post by: Eros on August 16th, 2011, 08:07 AM
Percona is nice (its close enough to a drop in replacement for MySQL for most things) but I've never tried to use it in a production environment. In theory, its a performance optimised version of MySQL. However, I've never been close enough to maxing the capabilities of MySQL to decide to replace it. (e.g. My solution to bottlenecks has always been to modify the web application or acquiring more server resources for the application)


P.S.
I wish we could report posts (see the post above with airport parking services SEO linking. Probably a bot :P)
Title: Re: MySQL only!
Post by: Pandos on August 16th, 2011, 08:47 AM
Sine changing from MySQL to Percona we recognize a huge performance benefit. Fully compatible to MySQL but much faster and really stable. 
Title: Re: MySQL only!
Post by: Nao on August 16th, 2011, 09:05 AM
Quote from Eros on August 16th, 2011, 08:07 AM
P.S.
I wish we could report posts (see the post above with airport parking services SEO linking. Probably a bot :P)
You can simply reply like you did ;)
I do read all of the posts on these boards so don't worry, I eventually see and delete these posts & accounts (I might miss posts if they're edited later though...)

Also, I'm a bit concerned that we're getting our first spammer since I enabled the Arantor Captcha and a custom question. Both of these together should have stopped them all...
Title: Re: MySQL only!
Post by: Arantor on August 16th, 2011, 09:16 AM
Experience tells me that SEO services spam is actually human driven, and no amount of CAPTCHAs or questions can defeat that.
Title: Re: MySQL only!
Post by: Nao on August 16th, 2011, 01:29 PM
And they get paid what for every link...? Ten cents? Pathetic...
Title: Re: MySQL only!
Post by: Arantor on August 16th, 2011, 03:56 PM
It might even be more like a dollar per 50 or something ridiculous. Wage equality is a long way away.
Title: Re: MySQL only!
Post by: Nao on August 16th, 2011, 04:46 PM
Poor people.

Hey Pete, I think there are a couple of topics waiting for your feedback if you have two minutes ;)
Title: Re: MySQL only!
Post by: Arantor on August 16th, 2011, 04:47 PM
I have quite a few topics unread right now :/ Had to go back to bed, feeling so ill this morning.
Title: Re: MySQL only!
Post by: Nao on August 16th, 2011, 04:51 PM
Take your time then.
Health is more important. Says Nao who's killing himself on the job :lol:
Title: Re: MySQL only!
Post by: Arantor on August 16th, 2011, 04:52 PM
Then perhaps you should take a break too :P
Title: Re: MySQL only!
Post by: Nao on August 16th, 2011, 07:04 PM
I... I really need to have Wedge ready and out by the end of the year. It's becoming an obsession to me.
I don't know what I'll do AFTER that (I'm certainly not going to provide support for it, but I'll do my best to keep developing & debugging it), but I know I want phase one to be over...
Title: Re: MySQL only!
Post by: Dismal Shadow on August 17th, 2011, 03:23 AM
It might be unrelated...but funnily in OS X Lion Server, they dropped MySQL support...

http://www.theregister.co.uk/2011/08/02/apple_dumps_mysql_from_mac_os_x_server/
Title: Re: MySQL only!
Post by: Nao on August 17th, 2011, 08:54 AM
Interesting.
But Mysql is here to stay. If not, then it's forks will.
We write for real use. If the paradigm shifts to pgsql or whatever, then Wedge will offer such versions.
Right now we just don't think any server with pgsql installed would not already have Mysql installed as well. Focusing on one db system only allows us to save precious time for other features and for speed improvements.
Title: Re: MySQL only!
Post by: Arantor on August 17th, 2011, 10:58 AM
Apple Server is not our typical deployment scenario anyway.