Databases

fixing a corrupted mysql database that does not want to start up

/ bonnu18 / Leave a comment

Just this week I was trying to load an old WordPress website I had lurking locally on my PC. I hadn’t touched it in months to the point where I forgot what stage of development I was on. My setup in this case was a Xampp server and through it I would load both Apache and MySql, then open up PhpMyAdmin and just fiddle in there. Straight away I was facing an issue when clicking on the Start button in Xampp to fire up MySql. Don’t know what was up with it so naturally I opened up the mysql_error.log file. There were a bunch of different ones but the predominant one was this one.

[ERROR] InnoDB: Page [page id: space=0, page number=307] log sequence number 45608603 is in the future! Current system log sequence number 45109372.
[ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

Without sounding like a MySql expert (because really and truly I’m not and the only times I ever used was for little WordPress projects) the solution that worked for me was stopping anything that was running on Xampp and open command prompt in admin mode. I would then simply execute the following command.

mysqld --skip-grant-tables

MySql would then show as running on Xampp, if Apache was not running then turn it back on, and hopefully you should be able to navigate to http://localhost/phpmyadmin/. Hope this was useful as it was to me, thanks for reading!

fiddling around with a date parameter in an SQL query

/ bonnu18 / Leave a comment

Hey guys!

Continuing on my previous blog post which was databases oriented, this week I ended up fiddling around with an SQL query in order to fetch the data set I was after. Diving straight into it, say we have a table in the database and this table has records of different API releases. Four columns. Unique ID, name, version number and release date, and it would look something like this.

tbl_api_version

At one point I was interested in getting which versions the API was running on from a particular date onward. If we had to look at the table above, and for instance pick the first row, API version Superman was active from 01/01/2015 till 24/05/2015, one day before the release date of API version GreenLantern. Therefore, for my query, say I was interested in getting active API versions from 01/08/2017, the query should return API versions Atom, GreenArrow and Hawkgirl. The problem I was facing was that table, in my opinion, could have been designed better and include a start date column and an end date column. Having that structure querying would turn out to be easier and in fact that is what I aimed for when building my query. The idea behind it was to create two columns, start and end, on the fly and then manipulate them to get the desired result.

The query I came up with would be the following;


DECLARE @param DATETIME
SET @param = '2017-01-01 00:00:00.000'
SELECT Version, Name, StartDate FROM(
SELECT Version,
Name,
MIN(Date) StartDate,
ISNULL(( SELECT DATEADD(day, -1, MIN(c.Date)) EndDate
FROM tbl_api_version c
WHERE c.Version = b.Version + 1 GROUP BY Version, Name ), GETDATE()) EndDate
FROM tbl_api_version b GROUP BY Version, Name) a
WHERE @param BETWEEN a.StartDate AND a.EndDate OR a.EndDate > @param
GROUP BY Version, Name, StartDate
ORDER BY Version

view raw

dateparametersqlquery.sql

hosted with ❤ by GitHub

I’ve also created an SQL fiddle which has the create and insert script, and the actual select statement. Is this the best solution? Probably not. I ran the query constantly several times and the SQL Server Management Studio was producing the results quite instantaneously so for the time being I was satisfied. I’m still not sure how efficient this would be if we had to perform this query on a table that has way more rows, for instance thousands. I have my doubts it would be efficient though since we have two nested select statements. Feel free to share your thoughts in the comments section if you would have tackled it differently or perhaps if you thought of a cleaner or faster solution to his problem.

Till my next post,
Bjorn