Can I just run this past you guys, to check I'm not losing my mind...
I use a hosted webserver for a number of my sites, that each use a MySql database. Most use StoredProcs to access data. Yesterday, all the SP-based ones stopped working, with a variety of error messages. No code had changed. Trying to connect to the databases using HeidiSQL or MySql Workbench threw up warnings that the tools didn't support v10.2.24... now I thought I was on MySql 5.7 or something...?
Digging deeper reveals that v10.2.24 is the current MariaDB version. Checking with my hosting provider, and asking why they'd not only upgraded version but changed from MySql to MariaDB without telling anyone, I got this reply:
MySQL 5.7 is the last version of MySQL to be called MySQL
MariaDB is the new name for what was MySQL, so thare is noting to move back to. [sic]
This is news to me!!!
I found the following (really useful) article from less than two weeks ago: MariaDB vs MySQL in 2019: Compatibility, Performance, and Syntax[^]
Please, tell me my hosting provider is talking rubbish!!
Oddly enough, after giving more detail on the issue, everything started working normally again (but still reporting potential compatibility issues with 10.2.24 when using Workbench or Heidi). I'm guessing they'd not configured MariaDB to support StoredProcs (is that even an option?) but whatever they've done seems to have done the trick. But my confidence in them is very severely shaken, when they change d/bs without informing customers, and seem to think MariaDB 10.2.24 is just the new name for MySql 5.7 (and they don't even seem to know about MySql 8.0!) ....
Please, tell me my hosting provider is talking rubbish!
Yes they are.
MariaDB is a clone taken from the MySQL code base when Oracle acquired it. So now there are two products. And more specifically two different development branches which are different. The branch wasn't a recent one either.
So upgrading from MySQL to MariaDB is a product change. And likely a disruptive one as well.
Compare this to AWS Aurora which is also a different product but one that promises binary equality.
Might also note, my impression (without any research) is that stored procedures might be impacted the most.
That said if you are not tied to MySQL for marketing reasons then my gut would say that MariaDB might, in general, be a better product. If for no other reason then Oracle absolutely will pour more money into their commercial MySQL offering than they will into the open source version.
Thanks, JSchell. I later found that although my apps were generally working OK, a couple of functions were failing. Digging deeper I found the issue was that on one StoredProc there was a "missing" parameter, and another was just missing altogether. Everything else was fine. I deduce from this that once alerted to the issue, they probably realised they'd forgotten to migrate the SPs and did a quick manual copy/paste. It's not a huge hosting company and although they have a few hundred databases, I suspect only a handful - or fewer - are using SPs so they may not even have been aware until I pointed out the issue.
They previously (last October) migrated to a new server op.system plus MySql upgrade. That also broke most of my sites since many call webservices on other domains, and the new server didn't support some of the older security protocols the sites were using. It was a one-line fix for each but took quite a while to identify. Also I couldn't send any emails via the SMTP server; I reported it and they claimed nothing had changed, but magically it started working about 10 minutes after I raised the ticket. Seriously p***ed off with them. They've given me a year's free hosting as compensation but when they continually break sites by screwing up upgrades (with no advance warning) I'd rather pay someone to do a proper job....
You might want to take a longer look at where you think your company/product is going.
Like I said I suspect MariaDb is better but, far as I can tell, there is more broad support for MySQL.
So if you think that you might want to move to a different host in three years then you should take care to continue to test and develop on MySQL. That would make your upgrade path easier then although harder now since you probably need to test on both. Although perhaps you could use that as a marketing claim ("runs on both!").
Fortunately it's a bespoke application for a specific client, so we'll host it wherever they want - and the choice of d/b will be part of that decision making process. In fact the whole data access layer is pretty much "pluggable" and switching DBMS is a non-event as far as the application itself is concerned - the only impact would be on stored procedures. So I'm not too fussed which d/b the host uses - what concerns me far more is that they think they can just "upgrade" one to another, and with no advance warning, no support, no awareness that the two are not 100% compatible, and not even knowing that they're different products, rather than just a later version. It's a real shame, because 15 years ago when I first started using them, they were small but outstanding. Expert support staff who really knew their product, and would go out of their way to assist and adapt as needed. They appreciated the importance of reliable and predictable service levels and the reputational damage outages could do for their clients. That ethos seems to have been abandoned...
It lets you replace [MyDB].[dbo].[MyTable] with a shorter name, such as a:
FROM [MyDB].[dbo].[MyTable] AS a
You can also use the aliasiing feature on column names, like so:
SELECT [MyReallyLongColumnName] AS ShorterName
A simple google search will give you all the nuances regarding the use of aliasing in SQL Server (for columns, sometimes you should, sometimes you must, and sometimes you don't have to use aliasing.
BTW, you generally alias table names when using join or merge.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
Just randomises the output and has nothing to do with selecting alternate records. Incidentally, you should avoid using reserved words (e.g. TABLE) as tablenames, but if you insist, then get into the habit of surrounding the reserved word with square brackets (i.e. [TABLE])
In your section 2 you have put Identity column in bold suggesting that it is someway relevant to identifying alternate records - it is not, as I stated earlier.
You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
Your final comment I think, is saying that the Rank function has an option to order columns - all Window functions have the potential for ORDER BY and/or PARTITION BY, that's why they are sometimes referred to as OVER functions. You can use Window functions on any table, not just derived tables or CTEs.
Here is an example of why Identity Column is not appropriate: Consider this sample data
createtable test (d varchar(10))
insertinto test (d) values
('Test 1'), ('Test 2'), ('Test 3'), ('Test 4'),
('Test 5'), ('Test 6'), ('Test 7'), ('Test 8')
DELETEfrom test WHERE Id = 3
The contents of the table are
1 Test 1
2 Test 2
4 Test 4
5 Test 5
6 Test 6
7 Test 7
8 Test 8
Note the missing Id 3.
So I would expect to return rows where Id = 1, 4, 6 and 8. But if I just use the Identity Column
SELECT * FROM test where id % 2 = 1
I only get rows where id = 1, 5 and 7. Incorrect.
An example where RANK is inappropriate. Consider the following test data
1 Test 1
1 Test 2
1 Test 3
2 Test 4
2 Test 5
2 Test 6
3 Test 7
3 Test 8
So I would expect to return the rows where d is Test... 1, 3, 5, 7.
If I try to use Rank like this
;with CTE AS
select *, ROW_NUMBER() OVER (ORDERBY d) as rn, RANK() OVER (ORDERBY d) as r
SELECT * FROM CTE WHERE r % 2 = 1
I get the correct answer. But I could just have easily used
SELECT * FROM CTE WHERErn % 2 = 1
as both RANK and ROW_NUMBER return the same value in this instance. I contend that using ROW_NUMBER is clearer and less prone to risk - what if someone changes it to use a PARTITION … RANK() OVER (PARTITION BY id ORDER BY d) as r … you're going to get the rows where Test is … 1, 3, 4, 6, 7. Incorrect again.
Even if partition is not used, RANK can fail depending on the data being returned. Try adding some more data to test2 e.g.
I was trying to make him aware that his post was as clear as mud.
I'd just had a session with a user group from whom I'm trying to get some requirements. They (the group and the requirements!) are woollier than a woolly mammoth.
I wasn't in the best of moods.
I used SQLSERVER 2012,today when I query data,I found a very strange thing. As when I used the sql:
select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t
it returns 22 items
but when I use statement query in parentheses:
select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx')
it returns 20 items
how can this possible?I'm confused.
select count(1) as num from(select * from View_paymentApproval v1 whereexists(select1from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0'and v1.approvalManCode='zhouyx') )t
it returns 22 items
As to the semantic used in your statement: it does NOT return 22 items. It returns a number which should be equal to the count of records returned by the view.
As to the strange issue... I can't reproduce your issue, but i can recommend to test this statement:
SELECT COUNT(*) --or COUNT(1), because both statements are equal!FROM View_paymentApproval v1
WHERE contractNo=v1.contractNo AND payNo=v1.payNo AND SendSAPStatus='0'AND v1.approvalManCode='zhouyx'