Restoring a WordPress site from WAMP with only .ibd and .frm files, into a new WAMP with ... .myd and .myi files? Background: I uninstalled a WAMPserver (version 2.5) on a PC, which left over a WordPress installation with only .frm and .ibd and .opt files. That's it. I need to restore the site.

My latest attempt to restore the site is based on a method I read at this link, where:

I install a fresh installation of WAMP (version 3 this time)
I install a WAMP add-on of MySQL 5.6.12, which is the version of MySQL that my site was running on before my WAMP failure.
Install WordPress as a fresh new install.
Copy all the .ibd, .opt, and .frm files left over from the old setup, and replace the fresh .ibd, .opt, and .frm files in the new setup.
Go into PHPAdmin, access the database and run "alter table" comments for each wordpress table (ex.: alter table wp_posts import tablespace;)

I copied the left over .ibd, .opt, and .frm files from the old setup, and then went over to the new database folder and was surprised to find that there were not .ibd files in there to replace but .myd and .myi files instead!

I find this confusing because I learned that I had to have the same version of MySQL running to attempt this.

My questions are this:

1) why did my new installation create dual .myd and .myi files for each database table instead of single .ibd files?

2) am I able to replace the files anyway, with the hopes of having the new setup read the old .ibd files and working them into the new .myi and .myd files

3) is there no hope of restoring this site now? This would appear to be my last hope before I'd have to resort to copying the paragraphs out of the .ibd files using TextWrangler to convert them into a text file in order to at least get my content back in some form.

.myd and .myi files are the data and index files for MyISAM database engines. There is nothing wrong with them being there. They won't affect in any way your Innodb files that you are copying. They don't have to be overwritten if you copy the files into the folder.

In my installation of wampserver the MyISAM files (myd and myi) are one folder deeper than the ibdata1 file. That folder name should be the name of your database. If its not there then create the folder with the database name.

Copy the data that you have over. This probably won't worth without ibdata, but just in case, after copying the other files into the appropriately named folder, click the wamp icon in the bottom right of the screen and select my.ini. Add the line:

innodb_force_recovery = 1

Restart wamp server. Let me know if your database is able to actually get anything out of the files you copied over.


Query returns NULL - Oracle I've an Oracle query where I need to get some data on the basis of some records (strings). I am using IN clause and query is working fine in TOAD with hardcoded data. But when I run that query through code (Dapper) and pass those strings as Parameters, query always returns NULL/ Empty result.
Here is my query:
SELECT * FROM table WHERE id IN :lstIds

that's how I'm passing my data as parameter:
using (IDbConnection conn = new DBManager().connection)
        {
            conn.Open();

            lstData = conn.Query<ViewModelData>(sqlGetData, new
            {
                lstIds = lstIdsParams

            }).ToList();

                conn.Dispose();
        }

and this is my list of Ids, that I am passing as param

Even if I run this query with hardcoded data, it still does not work..
SELECT * FROM table WHERE id IN 'PL-0417-01301006-H01-D-00013#000013#18'

But if I run same query in TOAD with hardcoded data, it works fine and returns proper data. I don't know what I am doing wrong in code that is creating issue. Can anyone help me out here??

Your syntax is wrong you need  parenthesis around the value(s)
Please remember not to post Images for data, queries and errors

CREATE TABLE table1 (

  id VARCHAR(50)
);

INSERT INTO table1
VALUES
  ('PL4418.01301006-H01-D-03013O322241');

INSERT INTO table1
VALUES
  ( '04l801301006-I401•0013ccO27I0');

  INSERT INTO table1
VALUES
  ( 'P104I8.013016-H0l-D0l3c60O22l1');

  INSERT INTO table1
VALUES
  ( 'P104I8013016-I40l-D013c0c0224V');

  INSERT INTO table1
VALUES
  ( 'P10418-013016-HO1-D-010X2141');

  INSERT INTO table1
VALUES
  ('PL-0418-013016-H01-D-0132241');

  INSERT INTO table1
VALUES
  ( 'PL.5410.01301006-H0l-D.0201300022');

SELECT * FROM table1 WHERE id IN ('P104I8013016-I40l-D013c0c0224V');

| ID                             |
| :----------------------------- |
| P104I8013016-I40l-D013c0c0224V |

db<>fiddle here


Cloned production SQL Server now Windows Authentication doesn't work I needed to create a test environment for one of our applications that uses SQL Server as its data store. Our sysadmin cloned the existing production server to create the test server.

I can connect to SQL Server on the test server using Windows Authentication, but not the production server (I can connect to production using a SQL login). I could connect to the production server using Windows Authentication before it was cloned.

Here is the error message:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

What can I do to connect using Windows Authentication again?

SQL Server 2008 R2 version: 10.50.2550.0

Some more info...

All of our servers are virtual (using VMware)
This same situation has happened with another production SQL Server. Again, we made a clone of production for a test environment. As with this case, I'm able to connect to that SQL Server using Windows Auth.

Sean brings up a good point about invalid SPNs, but what I don't understand is why SQL Auth. would work if it was cloned improperly?

If the machine SID was not changed Active Directory will only allow the most recently booted machine to authenticate users.
Cloning a server and not giving it a new SID will definitely cause it to conflict with the current PROD server. I would advise doing this sort of thing in an environment separate from production.


How to evenly distribute a value over varying time periods with T-SQL? I am quite a newbie to MS SQL Server 2014 and in need for a sql skript that would retrieve evenly distributed budgets over given project durations.

The budget amounts need only to be equally distributed based on the total running months over the entire project duration.

Since durations widely vary between projects, end/start and entire years of project duration must be taken into account as well as monthly budget amounts need to be calculated at the same time, I can't come up with a concept of how to apply T-SQL to retrieve what I want.

Therefore, any ideas or sql snippets would be highly appreciated as well as a short explanation that a beginner could understand.

Data available within my project database:

|   id  |   budget  |   start    |      end        |
+-------+-----------+------------+-----------------+
|   1   |1000000,00 | 2001-06-01 |   2003-09-30    |
|   2   |3000000,00 | 2013-03-01 |   2016-06-30    |

For the budget distribution, I want my final results to look like
this:

|   id  |  Year_01  |  Year_02  |  Year_03   |  Year_04   |
+-------+-----------+-----------+------------+------------+
|   1   | 250000,00 | 428571,43 | 321428,57  |   NULL     |
|   2   | 750000,00 | 900000,00 | 900000,00  |  450000,00 |

To solve this problem you'd need to figure out how many days are recieving a budget in each relevant year. I don't have the time to create a perfectly coded answer but this should definitely get you started.

DECLARE @Budget int = 1000000
DECLARE @Startdate date = '2001-06-01'
DECLARE @Enddate date = '2003-09-30'
DECLARE @nYears int = 1 --1 is required to make sure the current year is taken into account
DECLARE @dailyBudget int = DATEDIFF(D, @startdate,@endDate)
SET @nYears += (YEAR(@endDate)-YEAR(@Startdate))

DECLARE @cnt int = 0;
DECLARE @yearBudget int;

WHILE @cnt < @nYears
BEGIN
    SET @yearBudget = @dailyBudget * (DATEDIFF(d, CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-01-01', CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-12-31') + 1 )

    IF @cnt = 0 --If this is the starting year, calculate remaining days
        SET @yearBudget = @dailyBudget * (DATEDIFF(d, @Startdate, CAST(YEAR(@Startdate) AS CHAR(4)) + '-12-31') +1)
    IF @cnt = @nYears-1 --If this is the ending year, calculate elapsed days
        SET @yearBudget = @dailyBudget * (DATEDIFF(d, --DailyBudget * days elapsed
            CAST(YEAR(@Enddate) AS CHAR(4)) + '-01-01', --get the first day of the last year
                @Enddate) +1) --Last day +1 so you get a budget for the last day as well

    PRINT @yearBudget
    SET @cnt += 1
END

The key element of this answer is the DATEDIFF function. This is used to calculate the amount of days between a startdate and enddate. The current solution will print the budget for each year. Please note that I did NOT fully test this so you'd have to manually calculate if the results are correct.


SSAS cube process time increased after making fact and dimension small I have a cube of 55 GB size which takes around 2 Hour to process full. As I have data for last 4 years but our business wants only 2 years of data.
For that, I have changed dimension and Fact views to have only last two years of data. Now, my cube size is reduced to 32 GB but processing time increased by 30 min (i.e. 2 hours 30 min). I was expecting it to be less as I have restricted good amount of data going into the cube.
Why the processing time has increased when it should actually decrease?
How can I reduce processing time now?

P.S.: I have already tried cube partition and due to large dimension size it is also increasing processing time.

I am using views where I have restricted the data via WHERE clause.

My view is basically like this:

SELECT V.Col1, V.Col2.... V.Col13
FROM DimeTable V
WHERE <my filter clause>

It is selecting almost all columns from the dimension table so I can't create non clustered index on all these columns as it may slow down insert operation and won't help me much

Processing a cube largely consists of 3 steps,

Getting the data
building indexes
calculating aggregations

Step 2 and 3 are the least expensive (during processing) in my opinion so let's start with that.

Building indexes does little more than calculating bitmap indexes for your attribute relationships. So depending on how many of those you have designed this could take longer or not, but usually shouldn't take half an hour. This does little more than saying "hey if this item group is filtered on, I already know what Items are in it, so I can add those up instead of doing a NONEMTPTYCROSSJOIN"

Calculating aggregations is the process in which the subtotals are calculated for every level where you defined them. If you haven't used any usage based optimization or attribute hierarchies and haven't defined any aggregations yourself those are only calculated on the leaf level of your dimensions hierarchies (aka the lowest level of every attribute). This is basically "hey if I need the sales for this item group I have precalculated it and don't have to add up these items"

A large part of processing time is used while fetching data. If you would trace your queries while processing you would notice that for every single dimension attribute a SELECT DISTINCT attributename FROM dimension_table is executed. If that dimension table is a view, and the query from the view is slower after adding a where, that dimension processing might become slower by time_the_view_is_slower * number_of_attributes.
How many columns are in the select list of your view is largely irrelevant, the number of attributes in your dimension is.

If you have a distinct count measure anywhere your extra where could have inserted a sort operation too because of a new execution plan.

So in your case my guess would be that your dimension views have gotten slower due to the fact that the valid date isn't indexed, or the queries aren't selective enough and the added processing time of the source queries exaggerates the reduced time of building indexes and aggregations.

You most likely have improved your cube browsing and MDX performance in the process by reducing the number of cells calculated with less data in the cube, but the processing time may just have grown by creating slower source queries, and that gets multiplied by the number of attributes.

Then again, I'm not sure what the problem is with larger processing times if you leave everything default. The performance of the SSAS solution should be more important than the processing time. If you run in to issues due to the processing time that might be another problem that needs fixing in another way.

So I guess, in the end, If you're worried about processing time, either load less data in your source database (which, depending on your setup, could reduce ETL loading time) or tune all those distinct queries by indexing your view accordingly.

If you want to know what exactly is slowing down your processing you could trace all the queries before and after the change and compare the execution times to see what queries are killing you.


Why doesn't first execution plan use RowCount Spool? Here is my repro that was tested on SQL Server 2008 R2, 2012, 2016.

The second and the third queries do use RowCount Spool, why the first does not?

create table dbo.t (id int identity primary key, v int);
--create statistics ST_t__v on dbo.t(v) with norecompute;

insert into dbo.t (v)
select top (10000) rand(checksum(newid())) * 5
from master.dbo.spt_values a cross join
     master.dbo.spt_values b;
go

declare @v int;

set statistics xml, io on;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4 having count(*) > 0);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 40);
set statistics xml, io off;
go

drop table dbo.t;
go

As the result,
IO statistics are:

Table 't'. Scan count 2, ***logical reads 20024***, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is just a cost based decision.

There are roughly 2,000 rows in the table for all v values from 0 to 4 inclusive and no other values of v.

The semi join can stop requesting rows after the first one is returned so SQL Server assumes it will only need to read 5 rows before it finds the first one matching v=4.

When you add having count(*) > 0 it now needs to read the whole table to get the COUNT(*) and can't stop after the first matching row is found. With v=40 this doesn't exist in the data at all but SQL server again needs to read the whole table to be sure of this.

The cost of reading the whole table for every outer row vs just reading a few rows from it is sufficient to make the plan with the row count spool optimisation attractive.

You can try the following with v=4 and v=40 and diff the results in a text diff tool.

SELECT v
FROM   dbo.t
WHERE  EXISTS (SELECT 1 FROM dbo.t WHERE v = 40)
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8615);

The results of the final memo are very comparable in terms of options explored but a different final result was chosen on cost grounds.

The plan with the spool chose PhyOp_LoopsJoinx_jtLeftSemi 6.2 13.1 and without it was PhyOp_LoopsJoinx_jtLeftSemi 6.2 8.2.

6.2 is the clustered index scan that is the same on both plans.

8.2 is the scan with filter executed 10,000 times.

13.1 is the spool built from executing the scan with filter once.

Those parts of the memo are copied below. When v=4 the cost of the spool is 1.0043 but the cost without the spool is lower (at 0.867567)

v=40

8.2 PhyOp_Filter 7.1 4.0  Cost(RowGoal 0,ReW 9999,ReB 0,Dist 0,Total 0)= 158.804 (Distance = 1)
13.1 PhyOp_Spool 8.4  Cost(RowGoal 0,ReW 9999,ReB 0,Dist 0,Total 0)= 1.03564 (Distance = 2)

v=4

8.2 PhyOp_Filter 7.1 4.0  Cost(RowGoal 1,ReW 9999,ReB 0,Dist 0,Total 0)= 0.867567 (Distance = 1)
13.1 PhyOp_Spool 8.4  Cost(RowGoal 1,ReW 9999,ReB 0,Dist 0,Total 0)= 1.0043 (Distance = 2)

The hint OPTION (NO_PERFORMANCE_SPOOL) removes the row count spool from the v=40 plan for comparison purposes.

I was expecting that USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') would do the opposite for the v=4 plan but despite group 8.2 now being costed in the memo at 158.804 it still gets selected and the overall plan is costed at only 0.939204 as the anti semi join costing itself still scales down the costs to reflect that it will only require one row from that input. Moreover with this hint in place there is no PhyOp_Spool option available in the final memo anyway.

Paul White points out in the comments that the performance spool can be achieved with the use of OPTION (QUERYTRACEON 8691).

If the following batch is run then the second query should show up as being more than 50% of the cost of the batch (i.e. more expensive according to SQL Server's cost model)

declare @v int;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4)
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4) OPTION (QUERYTRACEON 8691);


How to make a trigger for create_view I need to make a trigger about who and when created a view. I have some groundwork about it.. So, when some user is running a script for create view, trigger is crushing because this user doesn't have a permission to insert.  When I use trigger with EXECUTE AS OWNER, there is a problem with it. Problem - OWNER is not a valid parameter for EXECUTE AS in the context of server and database level triggers.

Use execute as self

create or alter trigger check_view
on database
with execute as self
for create_view
as . . .

or execute as 'dbo'

create or alter trigger check_view
on database
with execute as 'dbo'
for create_view
as . . .


phpmyadmin: know the import progress of sql file Im importing large size of sql file in PHPMyAdmin and it takes to long to import. is there any way to know its progress?.

like some kind of loading bar or percent of its progress?.

and i'm importing using the browser not the mysqldump command line.

Sort of; phpMyAdmin does have an upload progress bar, but there are some additional webserver dependencies:

  To be able to see a progress bar during your uploads, your server must
  have the APC extension [or] the uploadprogress one

  ...

  Moreover, the JSON extension has to be enabled in
  your PHP.

  If using APC, you must set apc.rfc1867 to on in your php.ini.

As some background information, this is a bit difficult to determine; since phpMyAdmin runs on the server that means it has difficulty knowing the size of the file before the upload is completed, which makes it inaccurate to show progress. I've never gone to the trouble to configure these additional requirements, so I'm not sure how well this works in practice.


Storing and querying rolling data in PostgreSQL I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a different variety of weather data (temp, dew point, wind, etc.). Each table will have 6-7 columns: latitude, longitude, point geometry, elevation, date-time the model is relevant for, and 1-2 data values of interest. The data will be primarily queried for a bounding box by time and elevation. There will be approximately 145,757,360 rows per table (data older than now is no longer relevant will be deleted). I roughly estimate the size of the tables to be about 10 GB each without indexes. (That's 52 bytes of data plus 23 bytes of overhead per row). The data will be regularly updated/inserted as new model data becomes available. Note: new data will include overwriting existing values in the table.

So I'm looking at these two plans:

Simply index and cluster by (datetime, elevation) with an additional index for the point geometry. Run a regular cron job that deletes old rows, runs vacuum/analyze, and re-clusters.
Partition by datetime and then cluster and index by elevation per table with an index on geometry. Run a regular cron job to add new tables going forward and drops old tables.

Further,

So, I know that dropping a table is much more efficient and deleting and vacuuming. But would I see a performance boost otherwise?
Are partitions appropriate when the all of the tables will be evenly updated and selected on until deleted as irrelevant (the documentation indicated that partitions worked best when only a few of them would be selected on)?

When delivering data will the selects be any faster than the clustered index? Does the answer change if multiple requests are being made at once?

Thank you. I hope I put up all the needed data. If not let me know and I'll add it.

All things considered, I would go with option 2. Dates will be evenly selected on, but I'm going to guess that for a given query only one or two date partitions will be involved. It's a shame you can't cluster on geolocation and partition on date, which would be ideal. Elevation tends to correlate with geolocation anyway, if the bounding boxes are sufficiently small.

Given the choices available, cleaner data operations and avoiding a daily vacuum is a good thing to have.

Delivering selects may be faster with option 1, though I suspect it will probably be a wash. With option 1, records with the same date and elevation are placed near each other in one big clustered index. With option 2, records with the same date and elevation are placed near each other in many smaller clustered indexes.


MySQL query - how to remove variable data I wonder if someone can help me please? I recently migrated from PHPBB forum software to MyBB software, but when the conversion finished I was left with lots of weird characters in many posts, such as
, </e>, etc.

I used the below query to remove things like this, and it has worked well and removed lots of these characters:

UPDATE mybb_posts SET message = REPLACE (message, '
', '') WHERE message LIKE '%
%';

My problem now is that the stuff I need to remove varies from post to post - here's a few examples:

">https://www.ebay.co.uk/itm/SUZUKI-DR-75 ... Sw7k9eIFHa

">http://www.hagon-shocks.co.uk/catalog/c ... x?TypeID=F
">https://www.moorespeedracing.co.uk/indi ... gKQjPD_BwE

As you can see, each line that I need to remove starts and ends with the same group of characters.
I modified the above query using the % as the wildcard, so the query became:

UPDATE mybb_posts SET message = REPLACE (message, '">htt%m -->', '') WHERE message LIKE '%">htt%m -->%';

When I click "Simulate Query" I get a message saying 1643 rows affected, but when I click "Go" to run the query it comes back and tells me 0 rows affected.

Is there something wrong with my query? Can anyone suggest how to fix it or suggest another query to achieve the same outcome?
TIA

Thanks for your replies.
The examples of text are shown in my original post, but I had to put the "code" formatting around them as the forum would not let me post without doing that. Here are the examples again:

">https://www.ebay.co.uk/itm/SUZUKI-DR-75 ... Sw7k9eIFHa

">http://www.hagon-shocks.co.uk/catalog/c ... x?TypeID=F
">https://www.moorespeedracing.co.uk/indi ... gKQjPD_BwE

I want to remove this whole section of text, but as you can see the centre section (i.e. the www part) varies in each line.
It is MySQL 5.5.65
Sorry, I have no idea how to use "fiddle".


Installing "SQL Server 2000 Client" and "SQL Server 2000 service pack 4" Installing "SQL Server 2000 Client" and "SQL Server 2000 service pack 4".

I am new to SQL server and want to know if both are different and the exact steps that i need to follow for installation. Please advice.

The SQL Server 2000 Client is the driver, admin and query tools.  The service pack is an update for the tools and the server.

I'd recommend looking at a newer version.  Your options are SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and the soon to be released SQL Server 2012.


Meaning of FETCH_CURSOR jconnect_implicit_1 in Sybase Situation
I am trying to troubleshoot performance when connecting to Sybase ASE Version 15.7 from Sqoop over JDBC.
Though I cannot check things myself it appears to be 10x slower to extract data this way then with another tool (which uses ODBC).
Observation
The DB team has observed the following:

Question:
What is the meaning of FETCH_SIZE jconnect_implicit_1:?
Of course other hints on what may be causing the performance issue are welcome as well.

The DB team believes it means a cursor is made for each row, perhaps because of this link which contains something vaguely similar, but I suspect it means something else as I have not found any reference of Oozie causing this over JDBC (we enforce a larger fetch size in the oozie command).

Even if fetch size is set to 1, it doesn't mean a cursor will be created for each row; it simply means the driver will request rows of an open cursor be sent to the client one by one instead of in batches. Still inefficient, but not that inefficient. However, there's no indication there that fetch size is set to 1; jconnect_implicit_1: seems to be simply an automatically generated name of a  bind variable. I believe Sqoop fetch size can be controlled with the --fetch-size command line argument.


Data modelling for specific units of measurement I am currently adding a feature in our software where we save a timeout in the database to initialize cronjobs which get active after that timeout.

To model this I see different ways but can't say which of them is the cleanest:

Create 2 columns "timeout" and "timeout_measure", where timeout_measure is a MySQL Enum with seconds, minutes, hours, etc.
Create 1 column "timeout" where the timeout is always stored in seconds

Even though I tended to the 1. way at first I saw the problem that additionally to this second column a MySQL trigger would be needed to check that if a timeout is set, the timeout_measure cannot be NULL , which would be an unnecessary performance hit as far as I am concerned, because we can implement the same functionality with only one column where we save the seconds of the timeout.

So I am now tending to the 2. way but I don't like the readability of this. Having a column like so with entries in seconds, one might not understand but only guess what this means without reading a documentation of this table.

| timeout |
| 3600    |
| 25200   |
| 86400   |

So I thought about saving the unit of measurement in the column name like timeout_in_secs. But then I am curious if this wouldn't be kind of boilerplate because if there is a documentation this additional info would not be needed in each query made and whenever the column is referenced at in the code. So whenever one wants to reference to the column of this entity in the sourcecode (we use NodeJS and Sequelize as ORM) he would need to write obj.timeoutInSecs instead of just obj.timeout.

What to do? I don't want to change the whole table structure once this feature is productive, since this could end up in a big mess. Do you think it suffices to have this documented in the database documentation, or would you still go to choose timeout_in_secs as the column name?

EDIT: I know this might be opinion based but in this case different opinions and experiences might help a lot. I am on my own in this decision and simply can't decide which solution is the best/cleanest.

I would go with one unit (seconds).  Then I would have the input/output convert as needed.

Controlling cron from a table??  Turn it around.  Have the job continually running, but sleeping a computed length of time.  If necessary, use a table for communicating the "when to run next".  Be sure to watch out for edge cases -- such as being told that the next run time is yesterday.  It should probably be interpreted as means "run immediately".


Convert to Self JOIN I have a correlated sub query, which is taking 16 Minutes to execute.  How can I convert it to join or optimize it.

SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM  Stock AS SSF
    WHERE SSF.SM_StockCode = (
        SELECT SM_StockCode
        FROM  Stock AS SSFAI
        WHERE SSFAI.ST_StockCode = SSF.ST_StockCode
        ORDER BY SSFAI.ST_ItemSize ASC
        LIMIT 1
    ) GROUP BY SSF.SM_StockCode

UPDATE : Added Explain Plan

+----+--------------------+-------+-------+-------------------+------------------+---------+------+-------+----------------------------------------------+
| id | select_type        | table | type  | possible_keys     | key              | key_len | ref  | rows  | Extra                                        |
+----+--------------------+-------+-------+-------------------+------------------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY            | SSF   | ALL   | NULL              | NULL             | NULL    | NULL | 45180 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | SSFAI | index | ST_StockCode_Indx | ST_ItemSize_Indx | 9       | NULL |     1 | Using where                                  |
+----+--------------------+-------+-------+-------------------+------------------+---------+------+-------+----------------------------------------------+

UPDATE : Table Structure

CREATE TABLE `Stock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ST_StockCode` varchar(20) NOT NULL,
 `SM_StockCode` varchar(20) NOT NULL,
 `ST_ItemSize` decimal(18,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ST_StockCode_Indx` (`ST_StockCode`),
 KEY `SM_StockCode_Indx` (`SM_StockCode`),
 KEY `ST_ItemSize_Indx` (`ST_ItemSize`)
) ENGINE=MyISAM;

You have SELECT SSF.ST_ItemSize after GROUP BY SSF.SM_StockCode, so you are using a column without aggregation in the SELECT list, a column that is not in the GROUP BY list.

This is not valid SQL by the 1992 standard.

Since ST_ItemSize is neither the primary key nor it has a unique constraint, the ST_ItemSize is not functionally dependent on it. This makes the SQL code invalid by the ISO/ANSI standards even in the 2003+ versions and will give indeterminate results (in the ST_ItemSize column).

MySQL unfortunately allows such queries (does no dependency check) and is the duty of the developer to ensure that a query will give determinate results.

So I would advise to remove this query from your code, discuss with the developers that wrote it or the ones that use it and find out what results it is supposed to return. Then you can rewrite it.

If you still want to have it running until you do that, the following will produce similar results - and will gain efficiency by an index on (ST_StockCode, ST_ItemSize, SM_StockCode):

SELECT ssf.SM_StockCode, MIN(ssf.ST_ItemSize) AS ST_ItemSize
FROM  Stock AS ssf
  JOIN
  ( SELECT ST_StockCode, MIN(ST_ItemSize) AS ST_ItemSize
    FROM  Stock
    GROUP BY ST_StockCode
  ) AS g
  ON  g.ST_StockCode = ssf.ST_StockCode
  AND g.ST_ItemSize = ssf.ST_ItemSize
GROUP BY ssf.SM_StockCode ;


When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance reasons I bulk insert the data before creating any primary keys or indexes. This is my current order of operations:

create the tables
bulk COPY all of the data
create primary keys
create additional indexes

Creating the primary keys and the indexes are the slowest parts of this workflow. Is there a point in this workflow when recalculating statistics via ANALYZE/VACUUM/REINDEX would be helpful or even necessary?

To speed up index creation, increase maintenance_work_mem.

Additionally you can raise max_wal_size to reduce the number of checkpoints.
If you don't need crash recovery at all (i.e., you are happy to recreate the database in case of a crash), you can use unlogged tables which would boost performance.


mismatch between server schema content and exported sql rows I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data.

I am trying to figure out, when I export my table from phpMyAdmin the exported sql file structure when opened does not match the schema seen in the admin panel. By that I mean the row sequence in the sql file are jumbled up.

I don't have any foreign key constraints set up or any other operations on the table.

Any steering and knowledge as to why this might be the case would be appreciated.

The issue disappeared when I changed the engine from MyISAM to InnoDB. I understand that InnoDB has row locking.


Snowflake/S3 Pipeline: ETL architecture Questions I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data Engineering, so I would love to hear from the community what the recommended path is. The pipeline requirements are the following:

I am looking to schedule a monthly job. Do I specify such in AWS or on the Snowflake side?
The monthly pulls should get the last full month (since this should be a monthly pipeline).
All monthly data pulls should be stored in own S3 subfolder like this query_01012020,query_01022020,query_01032020 etc.
The data load from S3 (query_01012020,query_01022020,query_01032020) back to a specified Snowflake table should be triggered after the ML model has successfully scored the data in Sagemaker.
I want to monitor the performance of the ML model in production overtime to catch if the model is decreasing its accuracy (some calibration-like graph perhaps).
I want to get any error notifications in real-time when issues in the pipeline occur.

I hope you are able to guide me on what components the pipeline should include. Any relevant documentation/tutorials for this effort are truly appreciated.

Thank you very much.

see: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html

Depends... you can use SNOWFLAKE TASKS to copy data from table to external location (s3/azure..). You can also use any other system which can run commands in snowflake

Same as 1. I think you need use (js)UDF to make your dynamic SQL (filename part) and run it if you use snowflake only solution. Ie. Js UDF, which executes your SQL

see: https://stackoverflow.com/questions/62403809/how-to-write-dynamic-queries-in-snowflake-user-defined-functions

You could use snowpipe and listen events, or run command from ML or just have task that tries to stage new files from ML output s3 folder, which to my knowledge does not cost compute to try and fail to find new files

I don't know about that, maybe report after you have imported it into snowflake

reporting from task history could be one way, not my expertise

So in practise 1 ,2 ,3 can be done using snowflake task only or tasks and snowpipe using files into s3 as trigger. Data can be also enriched using external function calls in snowflake query which uses udf to make some call to some api.
Assuming that your team is small and you do not yet have company way of doing things, i would recommend to start using tasks + snowpipe to handle simple data staging. I personally use Matillion because i have one, but if you do not have one, it's cheaper just to start learn snowflake way and do things there. Learning curve is not that bad, it has it own problems, but those are quite easy to handle. Most of them were communication to ML data producers to keep output schema stable
Only thing which changes if you have company way of doing thing is that those programs orchestrate copy into s3 command and copy from s3 commands in snowflake and run ML in sagemaker ( i do not know about that one to say anything useful)


After mysql server restarts I cannot see my own database By mistake I restarted MySQL server in Linux system. But MySQL server was unable to start.

I renamed my.cnf to my_bak.cnf. After that MySQL server started. But I can not see my project database.

I tried below command:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

But I can not see prj_db. But the prj_db folder is available in my Linux system.
I want to restore the database from prj_db.

The steps that I have done :

I made a change in max_allowed_packet=500M.
MySQL server is not starting after changing the max_allowed_packet.
I reverted back to my old my.cnf.
After that also server was not started.
Then I renamed the my.cnf to my_bak.cnf.Then server started.But i can not able to see my prj_db database.(Here i thought a new my.cnf will be created.But i can not see any my.cnf file under /etc folder.)
I moved my_bak.cnf file from /etc folder to another location. mysql service started, but I can not see any my.cnf file under /etc folder as well as my prj_db database also.
Please suggest me  how can i reload my prj_db database.

AND I HAVE .MYD,.frm,.MYI Files in my prj_db folder.

----------------------------------my.cnf-----------------------

[client]
port = 3306
socket = /DB/mysql/mysql.sock

#This is to handle unicode
default-character-set=utf8

[mysqld]
datadir=/DB/mysql
socket=/DB/mysql/mysql.sock
port = 3306
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

#skip-networking
#skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = localhost

#Added by JV to store and retrive unicode (native language) data
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
#character_set_client=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

User=mysql
max_allowed_packet=500M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

But after that I reverted back to old my.cnf and then I moved the .cnf file from /etc folder to other backup place.

Make the sure the datadir in new cnf file is the same as the old one then restart the service and try again.


Safely changing innodb_buffer_pool_size on MySQL replication master? We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown.

What is the "safest" method for us to do this? We have 2 slave servers which will need to continue replication after this is done.

The plan so far is:

Stop replication on the 2 slave servers with STOP SLAVE.
Stop MySQL on all 3 servers with service mysql stop.
Make the changes in the my.cnf file on the master server.
Start MySQL on the master again to get the live database back online ASAP with service mysql start.
Make the changes in the my.cnf file in both the slave servers.
Start MySQL again on both slave servers with service mysql start.

The settings are:

innodb_buffer_pool_size = 54G
innodb_flush_method=O_DIRECT

The server has 64GB of memory and is a dedicated MySQL server, the server doesn't really do anything else at all. It's a Dell PowerEdge R720 running Ubuntu 12.04 Server.

Can anyone foresee any problems with this method? Is there anything else we need to be aware of?

What you have seems fine. I would add the following

Run this on the Master.

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_master_info = 1;

This will cause everything that has been uncommitted to be committed on shutdown.

Then, it flushes the binlogs to disk.

On the Slaves, run this

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_relay_log = 1;
SET GLOBAL sync_relay_log-info = 1;
SET GLOBAL sync_master_info = 1;

This will flush all the binlogs and relay logs. If the Slaves do not have binary logging enabled, then skip SET GLOBAL sync_binlog = 1;.

Your steps look like this now:

Stop replication on the 2 slave servers with STOP SLAVE.
Run SET GLOBAL innodb_fast_shutdown = 0; on all three servers
Run SET GLOBAL sync_binlog = 1; on the Master
Run SET GLOBAL sync_master_info = 1; SET GLOBAL sync_relay_log = 1; SET GLOBAL sync_relay_log-info = 1; SET GLOBAL sync_master_info = 1; on the Slave
Stop MySQL on all 3 servers with service mysql stop.
Make the changes in the my.cnf file on the master server.
Start MySQL on the master again to get the live database back online ASAP with service mysql start.
Make the changes in the my.cnf file in both the slave servers.
Start MySQL again on both slave servers with service mysql start.


Ms-access defining additional Index Fields I am developing an access database that eventually will grow in size. For that reason scalability has to be taken in account.

My background: no official studies about databases.

I have read Access Database Design & Programming (O'Reilly) and I have taken multiple courses regarding databases (I keep studying and reading)
So far I've been working on developing a database from scratch with tables up to 50-60k rows (but will get bigger).
Advanced VBA user and comfortable with C#

I would like to confirm/deny some assumptions that might jeopardize the project.

Let say tblA has minimal superkey uniqueID. uniqueID is a text string of 7+ char. Is It necessary(recommended) to define a new field type numeric(integer) that will be used as the FK in other tables to generate the relationship?
Let's say, in the future will be migrated to a SQL server. Will it help?
Applying the prior principle.
When connecting the database with Excel, user needs to see information attribute that helps him to identify a record but when I send it back to the database I need to pass the numeric value that connects both records.
Let's say we need to update a record in tblInventory that has relationships with tblEmpl/tblClients/tblPrices.

How should I handle the conversion of empl_name="Carlos" to emplID=1?

Maintaining a dictionary key/item?
Generating recorset to lookup the value in tblEmpl?
Is there any SQL {INSERT INTO;UPDATE} syntax to create INNER JOIN so it automatically converts it? (I haven't found anything that applies to SQL Access)

By using numeric ID to lookup values in table (when the table has 3/4/+ relationships), SELECT statement results in a nested INNER JOINs that look really ugly:

(ie: want to find values on tblC where the emplName is "MyName" and clientName is "MyClient". Need nesting tables to find those attributes)

SELECT tblC.ID,tblA.Name,tblB.Client
FROM tblC INNER JOIN (tblA INNER JOIN tblC ON tblA.ID = tblC.FK_A)
ON tblB.ID = tblC.FK_B
WHERE (((tblA.Name)="MyName",(tblB.Client)="MyClient"));

If I have 4 different relationships, It gets scary.

As I self-taught, I am never quite sure if I just read the wrong post in stackoverflow or this is the way it is and I just should bear with it.

Any light upon those questions will be greatly appreciated.

Your question is really: What are the benefits of surrogate keys?

A surrogate key is a (generally) integer identifier for a record in a database which is used as the primary key (generally) and used as a link in foreign key relationships.

The answers are found all over the place, Wikipedia has a great summary including the advantages and disadvantages, some of which you allude to: https://en.wikipedia.org/wiki/Surrogate_key

Addressing each of your points:

"Will it help?"
Yes. From a performance perspective it will make a huge difference if your database grows to a large size because computers naturally use integers and comparing them is easy for them to do. As soon as you have to compare natural/business keys, computer processing time ramps up considerably. But this is only one advantage, immutability being probably the biggest. Read the above Wikipedia article to understand this.

"How should I handle the conversion of empl_name="Carlos" to emplID=1?"
You shouldn't. You have a couple of options:

Hide the key field from the user, they don't need to know about it.
Let the user see the field, just tell them don't touch it (preferably lock it).

Users don't need to know there is stuff going on behind the scenes. They usually don't care either. There is no need to do the conversion if you hide the column or show it. Secondly the user should not be updating details on excel sheets offline unless you have a process for automatically reintegrating these into your application. Instead, build a data entry form for them to use with a search function and tell them to make updates to the database directly.

"SELECT statements look ugly"
This is because MS Access makes SELECT statements look ugly NOT because they are ugly. A properly formatted SELECT statement looks fine:

SELECT
    tblC.ID
    ,tblA.Name
    ,tblB.Client
FROM tblC
INNER JOIN tblA ON tblA.ID = tblC.FK_A
INNER JOIN tblB ON tblB.ID = tblC.FK_B
WHERE tblA.Name="MyName"
AND tblB.Client="MyClient";

Unfortunately if you are stuck with Access, you are stuck with ugly looking SELECTs. Having worked with MS Access a lot, you are better off just using the query designer in Access and forgetting about SQL Statements unless you are doing them in VBA code. In VBA code you are in control of how it looks so can design it as you wish.

Your book appears to include a chapter about database theory. This is important and you should re-read the parts about data theory (Chapter 4) as these should have answered your questions. If not, look for another book that hopefully will.


How to update scheduler job credentials in oracle enterprise manager? I have a job scheduled in oracle 11g which runs on daily basis. Job is running with User "user_dba" (unix server user). The job is failing from the day I changed the password for the user "user_dba". I navigated to scheduler jobs in OEM and found the credential name "user_dba_credential". The job is failing because this named credential still has old password where as I have changed the password. So can anyone please tell me how to update the password in the "user_dba_credential" to my new password via OEM?

Thanks in advance.

In Oracle Enterprise Manager Cloud Control 12c you can edit the named credentials by navigating to Setup > Security > Named Credentials

In Oracle Enterprise Manager 11g Database Control you should look at Preferences > Preferred Credentials, here is the documentation.


Grouping/sorting performance choice between bigint and nvarchar I want to store a hash-code for a variable-length text field (max 1000 chars) in a database table. The hash-code will be computed and assigned once on insert, and new rows will be inserted very often.
The hash-code will be used mainly for filtering (WHERE), grouping (GROUP BY), and sorting (ORDER BY) in a couple of queries. The database table will hold a few million rows over time, with the probability of identical hash-codes (for identical text) being around 30% (rest being unique).
I have the choice of making the hash-code data type NVARCHAR (SHA1 of text) or BIGINT (converted bytes of SHA1 of text). I think BIGINT will be better in terms of storage space (less pages).
Generally speaking, which of these two data types will be better in terms of performance, considering the operations mentioned above?

You want to store the hash using the densest datatype possible.
A bigint is 8 bytes, or 64-bits of data.
An nvarchar storing 8 bytes of data would take 10 bytes on disk.
But how long are your hashes? SHA-1 hashes are 20 bytes long (160 bits) so won't fit in a bigint. Storing it as an nvarchar will take 42 bytes of storage if stored naively.
According to https://stackoverflow.com/a/16680423/391445 you best option is to use binary(20) so you are storing exactly 20 bytes (160 bits) of data.
Are you intending to do the hashing in the database or in client code? According to the documentation, as of SQL Server 2016 all hash algorithms other than SHA2_256 and SHA2_512 are deprecated.
But a cryptographic hash function is probably not the best choice of hash function for you if all you want is a random distribution of rows. There are many hash functions available, producing whatever length key you desire!


Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read? I have to find the total time taken for query to execute in SQL-SERVER 2016 when all the data pages should be in Disk only.
To accomplish that , I cleared all buffers in the SQL-SERVER using the following commands :

DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
DBCC FLUSHPROCINDB (@dbId)
GO

After this I again found that logical reads were there, then I got to know about Read-Ahead , so I disabled read-ahead by the trace flag

DBCC TRACEON(652,-1)
GO

and also the prefetch flag

DBCC TRACEON(8744,-1)
GO

Now still I have logical reads , but Physical reads are significantly higher than just clearing the buffer.
Is there any way to achieve 0 logical reads, or my understanding of logical read is wrong?

Please guide me through, I'am new to SQL-SERVER world

A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query execution.

A physical read is counted when the storage engine reads one or more pages from storage because the requested page is not already in cache. This excludes physical IO done by read-ahead scans.

A read-ahead read is counted when the storage engine reads one or more full extents (each extent being 8 contiguous pages) from storage during sequential scans. Read-ahead reads prefetches data into memory aggressively so that the pages are likely to be already cached when needed by the query. The number of extents read by read-ahead scans varies by SQL Server version and edition as well as fragmentation. I've seen read-ahead scans read as much as 4MB at once using a single scatter-gather IO during scans in Enterprise Edition using the latest SQL Server version.

Note that the SQL Server storage engine behaves differently with a cold cache (e.g. after restart or DBCC DROPCLEANBUFFERS). When the cache is not yet warmed up (Buffer Manager's Target Pages met), the storage engine performs full 64K extent reads instead of single 8K page reads to read pages into cache. This warms the cache much more quickly than would otherwise occur.

I prefer to focus on logical reads rather than physical for the purpose of query and index tuning because that's a better measure of the work performed by the query. It is only by happenstance whether data is cached or not. Testing with a cold cache is more of a measure of storage performance than query performance, IMHO, and isn't representative of what will occur under a production workload with a warm cache.


Social network DB model in MongoDB I am building a story site for pre school

The basic functions are students will be posting stories and others will be able to like/comment on that story

Below is the model of the DB

Stories
Story_Id
StoryTitle
StoryContent
StoryAuthor
StoryLikes {
LikerId,LikedTime
}
StoryComments{
AuthorId,AuthorComment
}

Is this model OK? How else can this be remodelled?

The problem I see here is, if I store it in this way, will be it faster when I query for all comments by a particular user and so on?

According to above mentioned description it seems that story comments is a separate collection which will encapsulate comments related to several stories along with story Id.But that approach will cause a performance overhead since it will require multiple queries for retrieval of information.As an alternate solution comments can be stored into stories collection as embedded documents which will consume less time duration of retrieval of comments
Embedded documents are alternative to joins in MongoDB
For more detailed description regarding embedded documents please refer the documentation as mentioned in following URL

https://docs.mongodb.org/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/


View in SQL Server 2008 I am in need of getting data from multiple select queries. So I thought of creating two views and to inner join them to get my data.

My questions is: which option is better

creating a view,
creating temporary table

Using two VIEWs in your SELECT statement will always be better because it can combine both of the views query plans into a single optimised query. Very neat solution which is also fast.

BTW. Using an in-memory temp table > 100 rows generally poor performance because the query plan optimiser assumes there is only 1 row in the temp table.


UNIQUE constraint on large VARCHARs - PostgreSQL I have a column defined like:

data_url character varying(32768) NOT NULL

and UNIQUE constraint on that column:

CONSTRAINT unique_data_url UNIQUE (data_url)

When a large object is being inserted into the table, the following error message appears:

ERROR:  index row requires 32584 bytes, maximum size is 8191

How can PostgreSQL be set in order to be able to index object larger that 8191 chars? Space and speed are not a problem. It is a rarely changed table of at most hundreds of rows.

Environment: PostgreSQL 9.3.6, Fedora 20 x64

As suggested by @Josh Kupershmidt and @JoeNahmias the solution is to use UNIQUE on md5 hash of the long value. However PostgreSQL 9.3 doesn't support expressions in UNIQUE constraints so an index, that supports expressions, has to be used:

create unique index unique_data_url_index on mytable (md5(data_url));


SQL Server - register Service Principle Name without service restart? I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required.  But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

Quick answer

Question:

  I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

  Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

  A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

  If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

  When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the  value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

  To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

  Read servicePrincipalName
  Write servicePrincipalName

Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)


How different are the top DB vendor's SQL (DML-) dialects from each other? Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from each other. Since I come from an Oracle background, I am especially interested in

analytical functions
model clauses
hierachical queries (start with .. connect by comes to mind)
any other feature that is more than the usual (select x, y from a, b where...)

Probably, the question boils down to if and to what extent these features are regulated by an ansi standard.

Practically, I'd like to know if there are "rule of thumbs" that would indicate if (and how) I can take an SQL DML-statement that runs on one database and let it run on an other database.

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything.  Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL.  Of course as each company seeks to differentiate their products, they will develop additional functionality beyond the standards.
From w3schools:

Although SQL is an ANSI (American
National Standards Institute)
standard, there are many different
versions of the SQL language.
However,
to be compliant with the ANSI
standard, they all support at least
the major commands (such as SELECT,
UPDATE, DELETE, INSERT, WHERE) in a
similar manner.
Note: Most of the SQL
database programs also have their own
proprietary extensions in addition to
the SQL standard!

The differences between the SQL dialects is too great in the more specialized areas you listed to simplify the problem down to a “rule of thumb”.  In these situations you probably should convert the entire statement for each database.  By doing this each statement can be optimized for each platform taking advantage of the special features each database can provide.  Writing SQL to lowest common denominator (ANSI) may simplify migration, but at the cost of performance.
SQL Dialects Reference gives some useful comparisons of the SQL variants.  For more information I recommend searching for the specific dialects you are switching between like this:
   differences between mysql and oracle sql


Can I get similar Full Text Search functionality without using Full-Text Search? Normally, I wouldn't post this kind of question here, but I can't seem to find anyone who has actually used SQL Server Full Text Search, so I'm posting here.

I'm creating a solution for a client that involves searching an Item catalog.  The number of items is currently around 56k items.  This number could reach 200k in a few years, so I'm going to safely assume 500k max items.

Here are my requirements?

Searching needs to be fast.

Need to be able to do a keyword search that will search multiple columns (i.e. Material, Feature, VendorItemName, ItemName, etc...)

An item may have a value of 'Aluminum' in all 4 of the columns mentioned above.  Multiple matches for an item would 'rank' higher than an item matching on only one column.

Word Inflection: i.e. Search for PE should return Polyethelene material items, or search for RND (industry standard) should return items with Shape of Round.

Results will be weighted bases upon matches, number of matches, and other criteria that may be handled in code.

It seems Full-Text search will handle a lot of this, but I'm not sure it's the right approach.  I don't have any single columns with a lot of text (like a description field), which from what I understand, Full-Text search is really designed for.  I've considered creating a table that contains a concatenation of all the columns data combined into a single column.  I would use this column for searching only, and then use the returned index keys to join on my actual Item table.

When I start to consider these kinds of solutions, I begin to wonder if perhaps I'm better off not using Full-Text search.

On one hand, it seems like overkill. But on the other hand, it has a lot of features that I could use (thesaurus comes to mind).

So, should I continue to look into using SQL Server's Full-Text Search, or try and come up with my own solution.

Native full-text can handle all of the features you've mentioned.

Ranking/relevance, weighting
Word inflection and expansion
Customisable noise words and thesaurus

Whether or not it will be the best solution for your particular domain is a tough call. Rather than attempt to hand-roll an alternative, spike a proof of concept using SQL FTS and repeat the exercise with one or more open source alternatives.

Lucene is the obvious contender to compare and comes with the "Stack Overflow search is now 81% less crappy" endorsement of the SO team.


Differential backup same size as of Full Backup I have a SQL Server database of almost 2 TB size, it has tables with image datatypes. Now the size of differential backups are same as that of full backup. Does image datatype makes any difference to this? Or am i missing something.

This was originally left in a comment by Dan Guzman:

  The differential backup includes all extents modified since the last full backup. If you reorganize or rebuild after the full backup, the diff backup will include all those modified extents. Note when you reorg with LOB_COMPACTION ON, the differential backup size will increase significantly if LOB columns are a large portion of the database space.

If you've performed a big maintenance action (rebuild, or LOB compaction) on the tables with images in them, you'll likely see the differential approach or reach the size of the full database backup, as is documented on the MS Docs page Differential Backups (SQL Server):

  A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and the when differential backup is created. This means that the size of a given differential backup depends on the amount of data that has changed since the base.

Another possibility, mentioned by Midhun C N, is that the "differential base" (the last full backup) is very old.

As far as actions you can take:

verify that full backups are completing successfully, so that the differential base is getting updated
consider avoiding large rebuilds or reorgs on the tables with image data in them unless there is a compelling reason to do so
if maintenance actions are necessary, try to space them out across multiple full backup iterations, to limit the size increase of differentials
if splitting them up is not possible, make sure to plan for the increased size of the differentials


How can I setup and use a PostgreSQL database without installing a driver? I have a portable PostgreSQL database that runs perfectly on my computer as a database server. This is a work computer that I use for a lot of work in Excel with non-sensitive data (that is, data that is visible to anyone on the LAN).

Having access to a fast database server would make my life much easier, however, I need to be able to write to it using either VBA/VBS/Powershell or .NET. I can't install the ODBC driver for the database as that requires administrator privileges and I don't have a great relationship with the IT staff, so they would reject any request without even reading it.

Is there a way of inserting data quickly into PostgreSQL (or any other fast database server) without installing the driver? I'm thinking of something that functions as a portable driver. Since the database is run with only my privileges and I can insert into it directly form the command line, I am hoping there might some way of doing this from VBA.

postgres can be "installed" from a ZIP file by simply extracting it.

psql is part of the Postgres "installation" can be used without any further steps.

Any JDBC based query tool can also be used without "installing" a driver, as you can put the JDBC driver's .jar file anywhere you want.

It seems you can download the ODBC driver binaries directly without an installer. But I don't know if it's possible to properly register that with any tool that needs it.


C API hangs in mysql_query() I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours only for this table:

CREATE TABLE `ReferenceWaves` (
  `ReferenceWave` datetime NOT NULL COMMENT 'DateTime of the Reference Wave',
  `InstrumentId` int(11) NOT NULL,
  `Triggered` datetime DEFAULT NULL COMMENT 'When it was triggered'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Assures FAFWE doesn''t use this reference wave when restarted';

ALTER TABLE `ReferenceWaves`
  ADD UNIQUE KEY `ReferenceWave` (`ReferenceWave`,`InstrumentId`);

Then running this command

SELECT COUNT(*) FROM MyISAM_ElliottWavesCore.ReferenceWaves
      WHERE ReferenceWave = '2018-04-02 17:06:00' AND InstrumentId = 73

does not return from the mysql_query() C function. This occurs more or less once in 12 hours - in the meantime around 10'000 queries like this have been executed without any problem.

Running SELECT * FROM information_schema.PROCESSLIST WHERE ID=6981; - where ID is the thread id for the query - returns this:

ID      USER    HOST                                      DB    COMMAND TIME STATE  INFO
6981    FAEWE   aaeb-app206ly.aaeb-holding.local:52492  (null)  Sleep   1836        (null)

After around 5 hours this thread apparently is killed automatically by MySQL and not visible anymore in the information_schema.PROCESSLIST.

The table MyISAM_ElliottWavesCore.ReferenceWaves has ~4'800 rows and the total size - including the index - is <150KB. I have tables which have million of rows and have size > 1GB but there is never this issue - it is only in this table and very unpredictable. You can imagine how frustrating it is.

Any idea what is going wrong?

Technical data:

SLES 12.3 running on XenServer 7.4 as VM
compiled with gcc 7.3 using -std=c++17 and almost every warning option.
using glibc 2.27 which is separately linked into the project because SLES 12.3 uses 2.22
MySql Community Server 5.7.21
C API client 5.7.19

Are you doing a backup every 12 hours?  MyISAM is blocked when dump, ALTER, OPTIMIZE, and several other operations are done.

Switch to InnoDB to eliminate most of such locks.

You can probably see the offending SQL by getting a full SHOW PROCESSLIST;.


Transfer logins to another server I moved a database from SQL Server 2005 to SQL Server 2012. Is there any way of restoring all the logins on the new server?

You will want to use the 'transfer logins' script provided by MS.  Pinal had a write up of it here.  I do not remember if I used this to move logins TO 2012, I think I did to setup a QA environment but not sure.  You might need to make minor adjustments but I think it'll work fine.

Keep in mind if you use AD logins and changed domains with no trusts, those users will need to be recreated with the correct security permissions which this script does not do.  This post references SQLSoldier (A SQL guru) and has a handy script.


How to get all distinct words in a column I have a table with a text column that can contain a single word, more words separated by a space or a NULL value. I need to get all the distinct words in that column (words non column entries).

For getting all the distinct column entries I use

SELECT DISTINCT(col_name) AS col_name FROM table_name ORDER BY col_name ASC

but I don't know how to get all the distinct words.

SELECT DISTINCT
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.col_name, ' ', numbers.n), ' ', -1) col_name
FROM
  (SELECT 1 n UNION ALL SELECT 2
   UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN table_name
  ON CHAR_LENGTH(table_name.col_name)
     -CHAR_LENGTH(REPLACE(table_name.col_name, ' ', ''))>=numbers.n-1
ORDER BY
  col_name;

You can see it here: http://sqlfiddle.com/#!2/b5be5/2/0

reference: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows


Moving SQL Server 2012 Database to SQL Server 2008 R2 I follow the answer of Mr. Kin in this post

How to move a database from SQL Server 2012 to SQL Server 2005

I run the query without errors, but when I check my blank database created on SQL Server 2008 R2, there's no change or new added data on my database.

Please help.

Sorry for duplicate post, I don't have enough reputation to comment on the link that I put here. Sorry.

Script out schema using SSMS GUI. And using this script create the database on the destination server.
Run below script on the source Server in SSMS with Text output CTRL+T:

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
---and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema
order by schema_name(schema_id)

In above script change the bcp.exe path to match. I have commented out the schema part in above script as you want to migrate all data from all schemas.

Once you have the script output in TEXT format, open a notepad and paste it in and save it as .BAT file.

Once saved, run the .BAT file by launching the CMD prompt. This will produce all the .dat files in the folder D:\BCP_OUT\ <-- change this as per your location.
Now you will have all the .dat files. These are the BCP OUT files with data. So run the below script on the destination server. You can copy all the bcp files to the destination server or just use SSMS from the source server to generate the scripts.Make sure you change the bcp.exe path and the destination_database_Name.

--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        +'.dat''
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',
        TABLOCK
        )'  + char(10)
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go'
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
--and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema
order by schema_name(schema_id)

Above will generate scripts for bulk insert using SSMS set in TEXT MODE as output. Copy the generated script and run it in another window using SSMS.

Hope that helps.


Dynamic auditting trigger without temporary tables We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows.  In order to use the INSERTED and DELETED tables in the dynamic SQL they have to be inserted into temporary tables.

We are currently trying to limit our tempdb usage as it has been causing issues and this table is udpated frequently, and is often causing tempdb contention as the trigger is creating and dropping so many temporary tables.

Is there a way to audit data changes to this table without using temporary tables?  Obviously I can hard code in the columns myself into the trigger and audit tables, but I'd rather have a solution which doesn't need updating every time there is a schema change on the table.

I have been looking around and I expect the answer to be 'No you can't do this' but I thought it was worth an ask in case I was missing some obscure method (or newer feature as we are on SQL Server 2016). As you can guess the solution needs to have good performance, otherwise it will cause issues somewhere else!

Obviously I can hard code in the columns myself into the trigger and audit tables, but I'd rather have a solution which doesn't need updating every time there is a schema change on the table.

That is nearly the correct approach.  Write a stored procedure to generate your triggers and run it any time you change the schema.


Nothing changed; always get connection timeout on alter table in MariaDB I have a MariaDB 10.1.21 database running in Fedora 25. I successfully ran alter table statements on auto_incrementcolumns in the past. After updating MariaDB to 10.1.21, but without changing any settings, I now get this with any alter table, even as root:

  ERROR 2006 (HY000): MySQL server has gone away
  No connection. Trying to reconnect...
  Connection id:    2

I temporarily increased every timeout variable, even the ones that shouldn't matter.

MariaDB [valar]> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 8000     |
| deadlock_timeout_long       | 50000000 |
| deadlock_timeout_short      | 10000    |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 31536000 |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 31536000 |
+-----------------------------+----------+

What's going on?

Which version of MariaDB are you upgrading from? It could be that there has been a table or index format change between the versions. Or it could be that the newer version is using the ONLINE alter table and the previous version was not, resulting in the newer version using more of a particular resource. What's the server doing during the time before the timeout? (swapping, writing to disk, etc)

You can try executing the command with the '--old-alter-table' argument.

If you still don't have any luck, the first thing I would try is to rebuild the table. Dump it (good to have a backup in this situation anyway) into an sql file and then reload it into a table with a new, different name. If all goes well drop the old one and rename the new one. Now try the alter statement again.


What is the infile SAS equivalent in SQL Server? I am trying to read a csv file in my SQL Server Script. In SAS, I can read it using the infile option. (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm)

I know that I can achieve the same thing in SQL Server using BULK INSERT. (https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017) What I can't figure out is some of the equivalent option in SQL.

Specifically, in SQL I want to achieve the functionality of Missover, DSD, FirstObs and LRECL provided by SAS. I am confident that FirstObs equivalent is Firstrow, I am not sure about Missover and LRECL and I cannot figure out DSD all together.

Any help/suggestion much appreciated.

As per MSSQLTIPS documentation Transferring data from SAS to SQL Server and back SQL Server data typing is much richer than SAS, translating SQL Server values for use with SAS can be challenging. Even the topic of representing and processing missing data values is substantially different between SQL Server and SAS. SQL Server represents missing data values with NULL values, but SAS has a minimum of two ways of representing missing values: a period (.) for numeric missing values and a blank space (' ') for character missing values.

As DSD (delimiter-sensitive data) tells SAS to treats two
consecutive delimiters as a missing value and removes quotation marks
from character values.
Whereas SQL Server represents missing data
values with NULL values.

For further your ref here & here


Get one column table when doing localization I have got only one column for a table when i create two localized tables. Code as bellow.

-- Month
CREATE TABLE `month` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
);

-- Month Localized
CREATE TABLE `month_loc` (
  `month_id'    INT NOT NULL,
  `name`        VARCHAR(200)    NOT NULL,
  `description` VARCHAR(500)    NOT NULL,
  `lang_id`     INT NOT NULL
);

month_loc.month_id is the foreign key.

Month table holds only the primary key. Other all fields should be localized. Is this table structure correct ?

Thanks.

Assuming that each month is universally identified by number (1 is January, 2 is February, etc.), it is useless to have a table that store the first twelve numbers, so you could have a single table, with the following attributes:

CREATE TABLE month_loc (
  month_number    INT NOT NULL,
  name            VARCHAR(200)    NOT NULL,
  description     VARCHAR(500)    NOT NULL,
  lang_id         INT NOT NULL,
  PRIMARY KEY(lang_id,month_number)
);

You could use this table to find efficiently a certain month in a certain language.


View with OPENQUERY won't build in SSDT project I have an existing database that I'm trying to set up as a database project.  I'm not able to get the following statement to build:

CREATE view [dbo].[VW_SOMEVIEW] as
SELECT * FROM OPENQUERY ([SERVER2],'
SELECT   SESSION_NAME,
         SESSION_ROWS,
         ACTUAL_START
FROM  [DATABASE2].[dbo].[TABLE2] (NOLOCK)
WHERE  SUBJECT_AREA = ''XYZ''
order by ACTUAL_START '
)

I'm getting the error:

  SQL71501: View [dbo].[VW_SOMEVIEW] has an unresolved reference to object [SERVER2].

When I use the variable name for the server instead of the actual server name, I get:

  SQL46010: Incorrect syntax near $(LinkedServer).

Both the server containing the view and the server being referenced are on SQL Server 2016, and I'm using Visual Studio 2017.

I've added the .dacpac file from SERVER2 in References, and I've tried using variables for the server and database name, but nothing works. What am I missing here?

Have you tried this?
CREATE VIEW dbo.VW_SOMEVIEW AS
SELECT *
FROM OPENQUERY (
    [$(LinkedServer)] /*we need the [] brachets to make this parse ok in VS*/
    ,'SELECT SESSION_NAME,SESSION_ROWS,ACTUAL_START
    FROM DATABASE2.dbo.TABLE2 (NOLOCK)
    WHERE SUBJECT_AREA = ''XYZ''
    ORDER BY ACTUAL_START'
    )


How to query/monitor index fragmentation? I have been a DBA for 3 months now at my first job out of college and I am having a significant amount of difficulty trying to figure out how to accurately query and report on index fragmentation across 2 of our production servers (2 separate clusters, but that doesn't matter for this specific question... let's just pretend I have 1 server).

We use Ola Hallengren's scripts for index maintenance already, but I need to query all of our databases on our server and get the fragmentation % along with the table name, index name, and database name.

Here is my current query that doesn't seem to be correct:

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,dbd.name AS [database_name]
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,page_count
 FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id =   i.index_id)
INNER JOIN sys.databases AS dbd on dbd.database_id = ips.database_id
WHERE i.type IN (1,2) -- Include ONLY Clustered & Non-Clustered Indexes
AND s.name <> 'Audit' -- Exclude Audit Schemas
AND ips.page_count > 100 --Exclude small page counts
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0 -- Current level of the index. 0 for index leaf
levels... Greater than 0 for nonleaf index levels.

Even when I remove the WHERE clauses it still seems my data is incorrect.  One of my records/tuples shows a table in a database with an index that has 1700 pages, but when I manually go to that index it only has 1?  I have been working on this for hours and can't seem to figure out what is going on.  I have read all of the documentation for this DMV.

Since you are using Ola's script - he has provided a script to check Index Fragmentation - see FAQ

In addition, fragmentation on very small tables does not affect performance. Microsoft has a white paper about this:
“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”
By default, IndexOptimize does not reorganize or rebuild indexes with less than 1000 pages. If you want to change this setting, you can use the @MinNumberOfPages parameter.


PostgreSQL server integrity after losing a tablespace I'm running a PostgreSQL server with 2 tablespaces: 'ssd' and 'hdd'. The first contains databases that need to be queried quickly, and is located on a single SSD that is not redundant in any way (no RAID). I don't care if I lose these databases to a disk failure. By contrast, the 'hdd' tablespace is located on a RAID array and contains critical databases that need to survive a disk failure.

Question: if my SSD fails and PostgreSQL loses the 'ssd' tablespace completely (or it becomes corrupt/unstable), will databases in 'hdd' continue functioning normally? Or will the entire server experience issues?

I can't describe this better than the manual does

  Warning

  Even though located outside the main PostgreSQL data directory, tablespaces are an integral part of the database cluster and cannot be treated as an autonomous collection of data files. They are dependent on metadata contained in the main data directory, and therefore cannot be attached to a different database cluster or backed up individually. Similarly, if you lose a tablespace (file deletion, disk failure, etc), the database cluster might become unreadable or unable to start. Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster.

Or this blog post

  You can’t just throw a tablespace away and have the rest of the database system keep on working.

  Most importantly, the write-ahead log (WAL) that provides PostgreSQL’s crash safety is stored in pg_xlog, and is shared across all tablespaces. PostgreSQL expects to be able to replay this log in order and without errors after a crash or shutdown. Until the log has replayed, the database is assumed to be in an unsafe state and connections will be refused


System DSN for SQL Server to connect through ODBC with for a specific port Earlier we were using default port number on the SQL Server and import of registry was working fine when configuring the clients to connect through ODBC. Recently we moved to new server where the SQL Server port is not the default one. We have around 300 client where this change needs to be made. Due to this we are looking for the best way to reconfigure the SYSTEM DSN's. The registry file content is as below,

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"SQLtest"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLtest]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="SQLtest"
"Server"="BLR002\\MAINT"
"Database"="Maint"
"LastUser"="sa"

Please let me know how to include the specific port number as well into the registry or what is the alternative method that can be used.

To specify a non-default port number, add a comma and the port number after the server name. For example, with a port number of 12345:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLtest]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="SQLtest"
"Server"="BLR002,12345"
"Database"="Maint"
"LastUser"="sa"

If the SQL instance is a named instance (named MAINT) like the DSN in your question shows, you should be able to connect using with your original DSN specification instead of port as long as the SQL Server Browser service is running on the server, UDP port 1434 allowed through the firewall on the server, and the instance port allowed as well.


Specify INSERT columns with query I'd like to INSERT data from one table to another, where the destination table has all the source columns with a few others in between. The tables have a large number of columns (plus this can get repeated now and then), so I'd rather not explicitly write the column names.

Along the lines of (doesn't work):

INSERT INTO dest_table
(SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'source_table')
SELECT * from source_table

The context is that I'm trying to alter a column's position.

Altering a columns position is going to require a full table rewrite. My suggestion is not to alter column positions. However, there is an abundance of people doing this and there are numerous ways to do it. In my experience,

one way is far better and easier, if you have the privileges, and
most DBAs have those privileges.

My suggestion is to use pg_dump.

Dump with --column-inserts

  Dump data as INSERT commands with explicit column names (INSERT
  INTO table (column, ...) VALUES ...). This will make restoration
  very slow; it is mainly useful for making dumps that can be loaded
  into non-PostgreSQL databases. However, since this option generates
  a separate command for each row, an error in reloading a row causes
  only that row to be lost rather than the entire table contents.

Remove from dump what you don't need.
Modify table definition or ordering.
In a transaction,

Drop the old tables
Execute the script creating the new tables.
Commit


Couchbase GUI for exploring data Is there GUI for Couchbase to explore data (especially views), similar to PGAdmin 3 or MySQL Workbench or RoboMongo

EDIT: ah, I found the button mentioned by this link

Just in case someone else comes across this needing help... the Couchbase UI is both command line and web-based, the latter of which is typically running on port 8091.

So, for example, if you've installed it on your computer, it would be http://localhost:8091/

If Couchbase is installed elsewhere, say IP 192.168.1.101, it would be http://192.168.1.101:8091

This is covered in the Couchbase 3.x post-install tasks as well as the post-install tasks for Couchbase 4.x.


How to download or place a copy of original tables with locked-permissions via remote connection in Oracle SQL Developer? N00b question here: I'm still learning Oracle SQL Developer.  Although I understand how to query databases/tables remotely, I'm unable to manipulate the data due to permissions-basically I would like to download/export copies of the databases/tables so I can manipulate or adjust the data within the table.

I'm running Oracle SQL Developer on Mac OS X Yosemite and I'm unable to place a copy of the original table in my table space.  I've consulted Oracle dox, but I believe these are dated because the GUI isn't as described.  I'd appreciate any assistance.  Thank you.

Evidently, this is how you copy tables from databases with permissions via remote connection to a local file-folder on Oracle SQL Developer (Mac OS X Yosemite):

Target database and expand to view tables.
Select desired table and right-click.
Hover-over 'Table' and select 'Copy'
A pop-up window will prompt to save copy table-name.

I've attached a screenshot below for clarification.  Thanks!


Queries are taking long time to execute I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine.

Problem started 3 days back, there is long list of queries in mysql status showing table level lock, it's taking long time to open the application. To keep the application running I set wait timeout = 8. Application is running but not executing long queries.

Kill    22  DELAYED localhost   eximstats   Delayed insert  3   Waiting for INSERT  ---
Kill    155 DELAYED localhost   eximstats   Delayed insert  58  Waiting for INSERT  ---
Kill    23123   office_eoffice  localhost   office_nw   Query   0   Sending data
select data_type, fund from transaction where ID = '539329' AND productCode = 'RC11' AND folioNumber
Kill    23124   common  localhost   common  Sleep   0   --- ---
Kill    31879   office_eoffice  localhost   office_nw   Query   10  Sending data
SELECT * FROM transaction WHERE transDate = (SELECT MIN(transDate) FROM transaction WHERE ID = '4473
Kill    32289   eximstats   localhost   eximstats   Sleep   3   --- ---
Kill    32635   office_eoffice  localhost   office_nw   Query   0   statistics
SELECT * FROM clients_map WHERE map_from = '434301' AND arn_id = '396'
Kill    32643   common  localhost   common  Sleep   0   --- ---
Kill    32644   root    localhost   None    Sleep   0   --- ---
Kill    32645   root    localhost   mysql   Query   0   ---
SHOW PROCESSLIST
Kill    32651   common  localhost   common  Query   0   statistics
select nav, nav_date from nav_new where productCode='CB87' and nav_date ='2004-10-11' order by nav_

What is the permanent and long term solution?

Your PROCESSLIST

Look back the processlist in your post. I see you are using INSERT DELAYED. I have always felt it was invented as a band-aid to squeeze some write performance out of MyISAM. Note that the first line of the INSERT DELAYED Documentation says

  The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

For those using MySQL 5.6, INSERT DELAYED IS DEPRECATED:

  As of MySQL 5.6.6, INSERT DELAYED is deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead.

You should consider using INSERT instead of INSERT DELAYED. Why? From the Documentation:

  INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it.

What is the permanent and long term solution?

You need to get away from MyISAM. Any kind of writing you do against a MyISAM table will always require a full table lock. Don't get me wrong. There are rare occasions when MyISAM is better than, and could even outperform, InnoDB :

Sep 20, 2011 :Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?

At times, MyISAM could also have a slight edge in read performance over InnoDB. Why? Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT=Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR fields are treated as CHAR. This will reduce access time for data retrieval.

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

In the short term, just ditch INSERT DELAYED. It seems that your write load has increased in overhead beyond what MyISAM can handle.

After doing that, give it some time. If you still see perform issues, it may be time to switch all MyISAM tables to InnoDB (See my old post When to switch from MyISAM to InnoDB?)


Query Plan Different When Using Compiled Values I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different even though they run against the same database.

Can anybody please explain why this occurred?

Most likely, either:

The state of the database (e.g. statistics) changed since the cached plan was first compiled, causing a recompilation; or
Your SSMS connection uses different settings (e.g. ANSI_NULLS) from those used by the connection that cached the plan (so it could not be reused); or
It was an ad-hoc batch and the text of your query did not exactly match the cached version
You used literal values where the original plan was parameterized


helping hand for sql server resources I'm just getting into SQL-SERVER, so forgive me if this is a basic question.

I need help, recently work on database programming, For my query development, to understand complex query, to can optimized syntax, I need guideline/good resources. Give me a helping hand to increase my knowledge in sql field.

Thanks in advanced

Here is my favourite article for beginners: http://www.sqlmag.com/article/tsql3/t-sql-101-lesson-1

10 articles in total, you will be a pro in no time.


Problems with generating Audit SQL Im currently working on a project that uses Oracle 10g where every action from the user is recorded in a auditlog table. The current table holds the following values:

AuditID
AuditDate
Username
Page Name
Type (Insert, Delete, Update, Login etc...)

Whenever a user does one of the above Types an audit log is saved.

What I would like to do is to get a count of each page for a particular time scale. For example Day, Month, Year and Quarter.

I have came up with some SQL to get the results for all of the pages together but i am having some trouble with getting the values for each individual page. Below i have given one of the SQL queries that i am using (Year).

SELECT (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-1100, 'yy')) As Y_2009,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-732, 'yy')) As Y_2010,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-368, 'yy')) As Y_2011,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate), 'yy')) As Y_2012,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)+368, 'yy')) As Y_2013
From Dual;

This SQL would give me the results of every single page for each year. However I need to find out the results for each page for each year. Is there anyway of doing this without creating an SQL statement like the one above for each page?

If you would like more information please ask.

Edit

The results i need would need to be in the following format:

PageTitle|   Oct_1    Oct_2     Oct_3    Oct_4    Etc...
----------------------------------------------------------
Page1    |   12       23        28       43        .....
Page2    |   23       64        21       18        .....

Looks like you need a GROUP BY.  Your query above done with a GROUP BY would look like this:

SELECT to_char(auditdate,'YYYY'), count(*)
FROM AuditLog GROUP BY to_char(auditdate,'YYYY');

A similar query can be used for other groupings.  For example, here would be a count for each day:

SELECT to_char(auditdate,'MM/DD/YYYY'), count(*)
FROM AuditLog GROUP BY to_char(auditdate,'MM/DD/YYYY');

To do this for each PageTitle, simply add PageTitle like this:

SELECT PageTitle , to_char(auditdate,'MM/DD/YYYY'), count(*)
FROM AuditLog GROUP BY PageTitle, to_char(auditdate,'MM/DD/YYYY');

To pivot the data as you have shown gets a bit more difficult particularly on 10g where there is no PIVOT. Here is one way to do it on 10g:

SELECT PageTitle
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/22/2012',1,NULL)) Oct_22
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/23/2012',1,NULL)) Oct_23
FROM AuditLog GROUP BY PageTitle;

Of course this requires entering all the dates.  You can make this a bit easier by generating the query using a query such as this:

SELECT 'SELECT PageTitle ' SQL FROM dual
UNION ALL
SELECT DISTINCT '    , Sum(DECODE(to_char(AuditDate,''MM/DD/YYYY''),'''
   || to_char(AuditDate,'MM/DD/YYYY') || ''',1,NULL)) ' || to_char(AuditDate,'Mon_DD')
FROM AuditLog
UNION ALL
SELECT 'FROM AuditLog GROUP BY PageTitle;' FROM dual;

Here is the sample data I used:

drop table auditlog;
create table AuditLog as (select sysdate auditdate, 'Page 1' PageTitle from dual);
insert into auditlog (select sysdate, 'Page 1' from dual);
insert into auditlog (select sysdate, 'Page 2' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 3' from dual);
commit;