Postgresql subquery speed much slower than individual queries In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal code.  Here are the two queries that I need to do in order to get the data I need:

SELECT coord FROM public.postal_code WHERE postal_code = 'T1K0T4' LIMIT 1

query time: 6ms

This gives me the coordinate of the center of the postal_code area.  Then I use this coordinate to find which timezone it intersects:

SELECT *
FROM public.timezones as tz
WHERE ST_Intersects(ST_GeomFromText('POINT(-112 49)',4326),geom)

query time: 36ms

Now when I combine the 2 queries, the query time jumps to 7-8 seconds.  This is my query:

SELECT *
FROM public.timezones as tz
WHERE ST_Intersects((SELECT coord FROM taduler.postal_code
                     WHERE postal_code = 'T1K0T4' LIMIT 1),geom)

I have a spatial index on the coord column in the postal_code table, and also on the geom column in the timezone table, but it seems like it isn't being used for the subquery.

Does anyone know of a better way to optimize this query?  I have tried several variations of this query, like joining the tables and such, but they have all resulted in the same query speed.

Output from EXPLAIN ANALYZE:

Seq Scan on timezones tz  (cost=8.37..167.47 rows=136 width=335547) (actual time=4606.136..7274.428 rows=1 loops=1)
  Filter: st_intersects($0, (geom)::geography)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..8.37 rows=1 width=128) (actual time=0.011..0.011 rows=1 loops=1)
          ->  Index Scan using postal_code_idx on postal_code  (cost=0.00..8.37 rows=1 width=128) (actual time=0.010..0.010 rows=1 loops=1)
                Index Cond: ((postal_code)::text = 'T1K0T4'::text)
Total runtime: 7274.448 ms

EXPLAIN ANALYZE for the following query:

SELECT *
FROM public.timezones as tz
JOIN taduler.postal_code as pc on ST_Intersects(pc.coord, tz.geom)
WHERE pc.postal_code = 'T1K0T4'

Output:

Nested Loop  (cost=0.00..174.61 rows=1 width=335714) (actual time=4870.908..7572.723 rows=1 loops=1)
  Join Filter: ((pc.coord && (tz.geom)::geography) AND (_st_distance(pc.coord, (tz.geom)::geography, 0::double precision, false) < 1e-05::double precision))
  ->  Index Scan using postal_code_idx on postal_code pc  (cost=0.00..8.37 rows=1 width=167) (actual time=0.012..0.019 rows=1 loops=1)
        Index Cond: ((postal_code)::text = 'T1K0T4'::text)
  ->  Seq Scan on timezones tz  (cost=0.00..56.08 rows=408 width=335547) (actual time=0.002..2.795 rows=408 loops=1)
Total runtime: 7572.787 ms

Seems you are running in a weakness of the query planner: The best index is sometimes not used for joining tables. Had a similar problem here:
Algorithm for finding the longest prefix (Chapter "Failed attempt with text_pattern_ops")

In Postgres 9.3 You could try this version with LEFT JOIN LATERAL:

SELECT *
FROM  (
    SELECT coord
    FROM   taduler.postal_code
    WHERE  postal_code = 'T1K0T4'
    LIMIT  1
    ) pc
LEFT JOIN LATERAL (
    SELECT *
    FROM   public.timezones tz
    WHERE  ST_Intersects(pc.coord, tz.geom)
   ) tz ON TRUE;

Something similar Worked for @ypercube's solution in this related answer.
LATERAL requires Postgres 9.3+, though.

In PostgreSQL 9.1, it might help to encapsulate the first query in a CTE, but I doubt it. (Don't have a PostGis installation here to test.):

WITH pc AS (
    SELECT coord
    FROM   taduler.postal_code
    WHERE  postal_code = 'T1K0T4'
    LIMIT  1
    )
SELECT *
FROM   pc
JOIN   public.timezones tz ON ST_Intersects(pc.coord, tz.geom);

A plpgsql function to encapsulate two separate queries should certainly do the trick:

CREATE OR REPLACE FUNCTION f_get_tz(_pc text)
  RETURNS SETOF public.timezones AS
$func$
DECLARE
   _coord geom;
BEGIN

SELECT coord
INTO  _coord
FROM   taduler.postal_code
WHERE  postal_code = _pc
LIMIT  1;

RETURN QUERY
SELECT *
FROM   public.timezones tz
WHERE  ST_Intersects(_coord, tz.geom);

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_get_tz('T1K0T4');


SQL Server replication over internet error I have 2 SQL Servers connected over internet, I can connect (with no problem) from each one to other using SQL Server Management Studio by using publicIP,1433\server as server address.

So, I created a publication on server1 called PUB1 but if I try to create a subscription (from server1) to server 2, I get this error message

  TITLE: Connect to Server

  Can not connect to MyPublicIP,1433 .

  ADDITIONAL INFORMATION :

  SQL Server replication requires the real name of the server to establish a connection. Connections via alias, IP address or other reference are not supported. Enter the real name of the server, " SERVEUR\SERVER ". ( Replication.Utilities )

So, I resolved the problem by creating an alias whith the (expected) name: SERVEUR\SERVER, protocol: TCP/IP, port: 1433, server: MyPublicIP\SERVER

sql replication over internet is working very well.


Question regarding flow control of SQL based jobs in SSIS Question: I have pertaining to SQL and SSIS :

How do I make an SSIS package work according to a logical flow that I mapped out for it?

Say there are six steps, I have the logic for how they should all run.

So for example, if A fails, do b, regardless of failure of b, do C, etc. But it is becoming a huge pain in the back trying to implement this in SSIS.

The conventional precedence constraints are too simple and 'linear' for this sort of thing. So, I thought maybe I could create a separate table that stores the values of 'flags' that I will set to 1 or 0 and these flags will correspond to the outcome of each step.

I will keep two sets of 6 values in a table, one for last run of my package and one for current run, then make comparisons to ensure consistency of data across multiple tables in multiple runs (accounting for any failure of any step this way) but I have no clue how to implement this right now.

This actually is pretty easy to do within SSIS.  Taking your situation, I have 3 dataflows named A, B and C.

You can change whether it is on Success, Failure or Completion by right clicking on the lines and setting that value

I believe this gets you what you are looking for, at least for the scenario you have presented here.


Is Oracle 11g database supported in windows 10? When I tried to install oracle 11g on Windows 10 Home. I got the below error after the wizard started the installation:
ORA-03113: end-of-file on communication channel.

I tried in another PC also windows 10 but the installation failed with the same error.

Windows 10 is not certified officially.

Home edition Windows versions are not supported at all. That information you can find in the installation guide:

Windows x64 Software Requirements


Confused about the "Maximum Failures in the Specified Period" setting on SQL Server Cluster The failover properties of the SQL Server role on my two-node SQL cluster are as follows:

Maximum Failures in the Specified Period: 2

Time period: 6 (hrs)

I would expect that forcing failover (either by moving the services manually or rebooting the owner node) more than twice would cause the resources to stay 'Offline', but this isn't the case. I can freely failover a seemingly infinite amount of times with no impact.

What is the purpose of that property and how do I simulate things correctly to test behaviour?

I would expect that forcing failover (either by moving the services manually or rebooting the owner node) more than twice would cause the resources to stay 'Offline', but this isn't the case.

Correct, that isn't the case. Moving the services manually isn't a failure - you've told it to go ahead and change the owner, this isn't a failure. Rebooting also isn't viewed as a failure as long as it was controlled - for example, I run the shutdown -r command or click start->restart. You're telling the server,  "Hey, have a nice controlled reboot - no worries."

  What is the purpose of that property and how do I simulate things correctly to test behaviour?

AFAIK the purpose of the property is so you don't ping-pong around servers indefinitely, potentially causing more issues. If you exhaust the number of retries that implicitly assumes that each node was attempted to host the role without success - so why keep flopping around causing more logs to be spewed for no reason?

To simulate - have an actual failure... force shutdown (not cleanly) a machine (blue screen it on purpose - something like NotMyFault). Rip the disks out from under it, something to cause an ACTUAL failure.


Group by function not working correctly in SQL server CTE This is SQL Server CTE,

;with grp (Sdate,TransactionType,tot)
as

(select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type],
    (count ([Transaction_type])) as [Total Count]
    from TBL_AGENT_TRANSACTIONS
    where cast(CallStartTime as DATE)>='2015-04-27' and cast(CallEndTime as Date)<='2015-04-27'  and Transaction_type in ('Debit_Card')
    group by CallStartTime, Transaction_type)

select grp.Sdate,grp.TransactionType,sum(grp.tot) as totalCount from grp group by TransactionType,grp.Sdate,grp.tot

Here I want to group totalcount based on Sdate and Transaction Type.
But it is not working correctly. I have 3 records with same Sdate, Transaction Type and I need to show totalcount as 3 as a single row. But i'm getting two rows as below.

Can any one help me to fix this minor issue?

Update:

I got the exact problem,

In my table 'TBL_AGENT_TRANSACTIONS', data type of the column 'Transaction_type' is varchar but I need to find the sum of that Transaction_type based on the Sdate and Transaction_type. If I put sum(Transaction_type) in the first select query, it's showing datatype covertion failed error so I'm finding the count(Transaction_type) in the first select query and getting sum of that field in the second select query. I guess my problem is in this process only.

add cast(CallStartTime as DATE) in your GROUP BY:

;with grp (Sdate,TransactionType,tot)
as
(select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type],
(count ([Transaction_type])) as [Total Count]
from TBL_AGENT_TRANSACTIONS
where cast(CallStartTime as DATE)>='2015-04-27'
    and cast(CallEndTime as Date)<='2015-04-27'
    and Transaction_type in ('Debit_Card')
group by cast(CallStartTime as DATE), Transaction_type)

select grp.Sdate,grp.TransactionType,sum(grp.tot) as totalCount
from grp
group by TransactionType,grp.Sdate,grp.tot


How can I connect to a network MySQL server using ODBC? Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC.

I need to connect to FOO in a MySQL client rather than SAS. Unfortunately for me, I'm not able to figure out how to do so using Oracle's MySQL Workbench, Microsoft SQL Server Management Studio (if even possible with Oracle rather than MS SQL), or SQL Workbench J using an Oracle driver.

The information required by Oracle's MySQL Workbench and others seems to go beyond the information that's available in Windows' ODBC Administrator profile for FOO (or any other ODBC DSN). The New Connection wizards request an IP address and port number, whereas the information I have on the ODBC connection is just:

DSN: FOO
User: Username
Password: Somepassword
Schema: Schemaname
Type/driver: Oracle
TNS Service Name: Somename

I downloaded and installed MySQL Connector/ODBC, but it seems that's mainly for creating ODBC connections rather than using them. I did figure out how to use ODBC to migrate a server in MySQL Workbench, but that's not what I want to do -- I just want to query the server.

How can I connect a SQL client (ideally MySQL Workbench, but I'm flexible) using only this ODBC connection/information?

Is FOO a DSN or a server name? on workbench you need the server name, mysql username and the mysql password. Thats all you need to connect to your mysql server using mysql workbench.


Using the same primary key as a foreign key for every table? I'm trying to design a database to hold information that is compiled from different lists. I'd like to record each person listed and assign an ID but due to the nature of the lists and their lack of accuracy, a person could be listed as having multiple names, aliases or misspellings, also they may have no date of birth listed or multiple dates of birth. There are about 8 different attributes that can have no information for one person person can have multiple records for another person.

My question is.. if I have one table that lists the person's ID and where I got the information, would it make sense to have 8 other tables that use that ID as a foreign and primary key and also have columns such as the date(s) of birth associated with that person? Or is this overusing the primary key from the other table?

For example

person
person_id | list_name | list_source

date_of_birth
person_id | year | month | day

name
person_id | first | middle | last

I'm very new to all of this and don't know enough to even start my google search on different methods of setting up a database or what a database of this type would be called (if that's a thing?) so even proper terminology for what I'm doing what be appreciated!

In general it is bad schema design to have two tables in a 1:1 relationship.  (There are exceptions, but yours is not one.)

In general, birthdate should be a single column of type DATE, not 3 columns.


Managing ibtmp1 File Growth on MySQL 5.7 We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where we thought we might fill up disks and whatnot.

From the (slightly vague) docs on the subject, it looks like the file can only grow in size, and the only way to reduce it is to restart the server (I take note of the comment about bad queries using up lots of temp space being the cause of the problem).

Is there actually any way to manage this file, whilst keeping the server online? I know disk space isn't such a limiting factor these days, but in the cloudy world we now live in, it costs money to keep extending disks without ever being able to shrink them again.

Unfortunately, all you can do is what said in my earlier post

SET GLOBAL innodb_fast_shutdown = 0;
Shutdown mysqld
delete ibtmp1
Start MySQL DB

ALTERNATIVE
Attach another volume to the VM and map ibtmp1 to that other volume using

innodb_temp_data_file_path (As of MySQL 5.7.1)
innodb_tmpdir (As of MySQL 5.7.11)

Please be sure to read these docs carefully. Also, try to optimize your queries to avoid make large tmp tables.


Multiple self Join on Mysql 5.7 - Poor Perfomance I have a table containing a lot of geonames and I am querying it with self join in order to get the cross referenced information.

The only issue is that it takes 7/10 seconds for the query to be executed (and locks the table) representing a bottle neck of the app.

I checked other answers but I could not find out a solution for me. I guess I created all the Index that could help me. I am not an expert in MySql so please if you need more info for a better understand feel free to ask.

This is the query:

select  gp.*,
                        gp1.description as desc_adm1,
                        gp2.description as desc_adm2,
                        gp3.description as desc_adm3,
                        gp4.description as desc_adm4
                 from
                 geonames_table gp left join
                 geonames_table gp1 on gp1.country = gp.country
                                        and gp1.geonames_code = 'ADM1'
                                        and gp1.adm1 = gp.adm1
                                        left join
                 geonames_table gp2 on gp2.country = gp.country
                                        and gp2.geonames_code = 'ADM2'
                                        and gp2.adm2 = gp.adm2
                                        left join
                 geonames_table gp3 on gp3.country = gp.country
                                        and gp3.geonames_code = 'ADM3'
                                        and gp3.adm3 = gp.adm3
                                        left join
                 geonames_table gp4 on gp4.country = gp.country
                                        and gp4.geonames_code = 'ADM4'
                                        and gp4.adm4 = gp.adm4
                 where 1  and LOWER(gp.description) like 'lo%'  limit 10;

And it is launched against this table structure:

id  bigint(20)  NO  PRI
description varchar(200)    NO  MUL
alternative_names   varchar(5001)   NO
country varchar(2)  NO  MUL
adm1    varchar(20) NO  MUL
adm2    varchar(80) NO  MUL
adm3    varchar(20) YES MUL
adm4    varchar(20) YES MUL
latitudine  decimal(65,7)   NO
longitudine decimal(65,7)   NO
geonames_class  varchar(1)  NO
geonames_code   varchar(10) NO  MUL

Thank you for your help in advance.

Here the Show Create Table output:

CREATE TABLE `31_geonames_places` (
   `id` bigint(20) NOT NULL,
   `description` varchar(200) NOT NULL,
   `alternative_names` varchar(5001) NOT NULL,
   `country` varchar(2) NOT NULL,
   `adm1` varchar(20) NOT NULL,
   `adm2` varchar(80) NOT NULL,
   `adm3` varchar(20) DEFAULT NULL,
   `adm4` varchar(20) DEFAULT NULL,
   `latitudine` decimal(65,7) NOT NULL,
   `longitudine` decimal(65,7) NOT NULL,
   `geonames_class` varchar(1) NOT NULL,
   `geonames_code` varchar(10) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_nome_place` (`description`),
   KEY `Sigla_paese_place` (`country`),
   KEY `idx_geonames_code` (`geonames_code`),
   KEY `idx_adm1` (`adm1`),
   KEY `idx_adm2` (`adm2`),
   KEY `idx_adm3` (`adm3`),
   KEY `idx_adm4` (`adm4`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

You need 4 composite indexes such as

INDEX(country, geonames_code, adm2)

(The columns can be in any order.)

Please provide SHOW CREATE TABLE; my following two comment would be clarified by it.

LOWER(gp.description) like 'lo%' needs for the collation of description to be ..._ci.  If it is, then this would work much better:

`gp.description like 'lo%'`

That is, a "case insensitive" collation obviates the need for LOWER().

If you don't have an index starting with description, add such.

For lat/lng, decimal(65,7) is much too large -- it takes 33 bytes!  This discusses much smaller alternatives.


Run an SSIS package located on a linked server from a stored procedure I am creating a stored procedure on my First machine named Server1 and

would like to call an SSIS package named mypackage.dtsx located on my

Second machine named Server2

Noting that I already linked my Second machine to my First
machine and I am using SQL Server 2012

on both of them. How can I accomplish this?

I would recommend creating a SQL Agent job on server 2 then running sp_start_job over the linked server to execute it. That stored proc is documented here.

If your SSIS package is deployed to SSISDB then you could execute it directly via stored proc if you prefer. See the answer on this question.


Database in emergency mode The database is in emergency mode and I have tried fixing it. But when I used the statement DBCC CheckDB (TestDB, REPAIR_ALLOW_DATA_LOSS) the log was not rebuilt. The log file is not present now. How can i bring the database online?

Do you have a backup and restore disaster recovery solution in place?  If so, restore the database from the most recent backup.

Assuming you don't have a backup, you may be able to use CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, (see the Microsoft Docs page for details)

The general syntax for the command is:

CREATE DATABASE <db-name>
ON (NAME = '<logical-name>', FILENAME = '<source-to-mdf>')
FOR ATTACH_REBUILD_LOG;

Be aware that if the mdf file is corrupt, you may lose everything.  Ensure you keep a copy of the mdf prior to attempting to attach it in this way.

An example of creating a database, setting it to "emergency" mode, detaching it, then re-attaching it without an existing log file:

CREATE DATABASE AttachTest
ON (NAME = 'sys', FILENAME = 'C:\temp\AttachTest.mdf')
LOG ON (NAME = 'log', FILENAME = 'C:\temp\AttachTest.ldf');

ALTER DATABASE AttachTest SET EMERGENCY;

Detach the database:

EXEC sys.sp_detach_db @dbname = 'AttachTest';

Delete the log file from the filesystem:

EXEC sys.xp_cmdshell 'del "C:\temp\AttachTest.ldf"';

Attach the database, recreating the missing log file:

CREATE DATABASE AttachTest
ON (NAME = 'sys', FILENAME = 'C:\temp\AttachTest.mdf')
FOR ATTACH_REBUILD_LOG

You'll see this message:

  File activation failure. The physical file name "C:\temp\AttachTest.ldf" may be incorrect.
  New log file 'C:\somepath\AttachTest_log.ldf' was created.

Cleanup the test database:

DROP DATABASE AttachTest;


SQL Server Configuration Manager is not showing SQL Services I have newly installed following things on my system

     - Microsoft SQL Server Management Studio 2014
     - SQL Server 2017
     - Visual Studio 2015

When I tried to open SSMS for the first time, it asked me for the "Server Name" when I surfed I came to know that I have to check the instance name in Configuration manager-->Sql Services, but I couldn't find any services running in Configuration Manager, it is showing "There are no items to show"

Secondly, I also tried giving the permissions to my SQL Server services as mentioned in article here,  but couldn't solve my issue.

Also I tried to check find the SQL Server instance name using the sqlcmd -L command, but it didn't show any servers, even though SQL Server is already installed.

What am I doing wrong?

SQLExpress is installed as a named instance. Try connecting to  <server name>\SQLEXPRESS

Or localhost\SQLEXPRESS will work as well.


Validation warning in Data Flow Task in the SSIS I am trying to load data into my DimEmployee table using the Data Flow Task. However, I noticed when I run the SSIS, the DimEmployee filled with 4 records out of 9 and also the EmpKey starts counting from -1 then generates random numbers after that! Also I have  a warning associated when running the package:

  Warning   1   Validation warning. Data Flow Task:
  {DC10B0CA-A6DE-4036-98A6-496811C09D4A}: Truncation may occur due to
  inserting data from data flow column "TitleOfCourtesy" with a length
  of 25 to database column "Title" with a length of 8.
    DimEmployee.dtsx    0   0

I created a procedure in my SSMS which supposes to fill the nulls in the columns by 'UNKNOWN'

USE [NorthWindDW]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Insert_New_Employee]

AS
BEGIN

    SET NOCOUNT ON;
    IF NOT EXISTS
    (
        SELECT * FROM [dbo].DimEmployee
        WHERE EmpKey = -1
    )

    BEGIN
    SET IDENTITY_INSERT [dbo].[DimEmployee] ON
    INSERT [dbo].[DimEmployee] (EmpKey, EmpId,Title, EmpFirstName, EmpLastName, HireDate,Country, StoreCode, Telephone,JobTitle) VALUES
    (-1,-1,'UNKNOWN','UNKNOWN','UNKNOWN','1900-01-01', 'UNKNOWN','UNKNOWN', 'UNKNOWN','UNKNOWN');
    SET IDENTITY_INSERT [dbo].[DimEmployee] OFF
    END
END

GO

This is my DimEmployee Table

Create Table [dbo].[DimEmployee]
(EmpKey        INT IDENTITY NOT NULL,
EmpId          INT NOT NULL,
EmpFirstName   NVARCHAR(50) NOT NULL,
EmpLastName    NVARCHAR(50) NOT NULL,
Title          NVARCHAR(8) NOT NULL,
HireDate       DATE        NOT NULL,
Country        NVARCHAR(50) NOT NULL,
StoreCity      NVARCHAR(50) NOT NULL,
Telephone      NVARCHAR(25) NOT NULL,
JobTitle       NVARCHAR(50) NOT NULL);
Go

Your warning looks to stem from your output column(s) database mapping.  The 'TitleOfCourtesy' output column datatype/length needs to match whichever column in DimEmployee it is mapped to.

As far as the 4 records go, I'd see what the data is in the 5th one, since that seems to be where the error is.  Whenever I have your issue, it's always a datatype mismatch.


Why does the size of the datafile on disk not decrease after deleting records from a table? When I delete rows from a table in SQL Server, the size of the database file does not get smaller.  Why is that?  Even if I delete every row from every table, the file size remains just as large as before the delete.

I'm interested in understanding why, not how to shrink the database file(s).

Space in database files is allocated as needed.  When rows are removed from a table, that space is simply marked as unused inside the datafile.  Datafiles do not shrink automatically (unless auto-shrink is turned on) because that requires extra disk I/O that is typically counter productive for performance.  The assumption is that any unused space will at some point be re-used by new rows.

To summarize, space allocated to the file on disk, and the logical size of tables within the database are not tightly coupled.

In more detail (although still very simplified):

They are definitely related. Rows in a rowstore live on pages, which are 8k in size and arranged into extents. Extents are groupings of 8 contiguous pages on disk.

Rowstore rows lives on pages and when "deleted" are not physically removed from the file - just marked as "free" inside of SQL Server through the use of bitmap and bytemap allocation structures and SQL Server will re-use the space accordingly.


PostgreSQL (10) show all NOTICE messages I use https://console.bluemix.net/docs/services/ComposeForPostgreSQL/index.html#about-compose-for-postgresql

And for some select with long column alias ex:

select id as "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
from tableName

I receive the following message:

NOTICE:  identifier "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" will be truncated to "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

Successfully run. Total query runtime: 166 msec.

Where can I find all these messages?

PS
I use sequelize and I met this problem  https://github.com/sequelize/sequelize/issues/2084
after migration from MySQL.

And I want to monitor if there are no selects in the application with such issue.

You can change log_min_messages  to notice which will then write all notices to the server's log file.

Note that a client can overwrite that setting any time, so it's not 100% reliable.


Create a MySQL Database using a MS SQL database dump (.CSV) I have a dump of a MS SQL database in the form of CSV files for every table in the database. There are 679 .csv files each for every table. The highest size of the table extract is 9 GB and the lowest is 12 bytes.

I want to replicate the entire database on MySQL keeping all the table structure and relationships intact. The purpose is to replicate the database on MySQL so I can query the results when required.

How can I do that please?

Many Thanks,
Jitendra

Hmm... you say that you have a .csv dump of the tables of a database? What version of SQL Server was this database extracted from? 679 tables is a largish system and this will require a lot of work.

Where are the inter-table relationships stored? You can't store FOREIGN KEY relationships in a .csv dump. Neither can you store CHECK CONSTRAINTs which MySQL doesn't support anyway (although this can be done programmatically through triggers - but can be tricky and error-prone). `

More importantly, a .csv won't tell you what the datatype is - it might give a clue, but a column with a run of INTEGERs could actually require a FLOAT/REAL type. Also, how do you know the real length of a VARCHAR field? Just because all of yours are < 30 CHARs, who's to say that a greater length won't be required?

This is to say nothing of TRIGGERs and STORED PROCEDUREs which are a completely different kettle of fish and could (depending on your database complexity) require months. In the area of these, MySQL is very much a lightweight in terms of its capabilities in this area.

MS SQL Server is a much more powerful RDBMS than MySQL - having the data is only a first step - but there'll be way more to do than just "simply" loading tables.

Your 1st step should be to get hold of the DDL and DML of this database, because without it, your task is going to be virtually impossible.
You should take a look at this article - it gives you three different methods of converting (a running) Microsoft SQL Server to MySQL.

It appears to particularly recommend MySQL Workbench - which I also recommend as an excellent GUI tool for such work. This depends on having ongoing access to a running server. You'll also need an ODBC connection to the SQL Server.

A question here gives you two options to extract DML and DDL from a SQL Server database to build a script (that won't work immediately on MySQL) - but with this, at least you have a fighting chance. You may also find this thread helpful.

Even then, you'll have a lot of testing to do - you're going to need Unix (or Windows Powershell) scripting or some programming (ideally Python or Perl) skills.

After you have basic table scripts - don't expect many of them to work first time - determine all tables which don't have any FOREIGN KEYs - usually lookup/reference tables (countries, cities, codes &c.) and make sure they load successfully. Then find the tables that have more and more FOREIGN KEYs and gradually build a cohesive system.

I would suggest a staging approach - you'll have to go back and check that your data in the MySQL database corresponds exactly to the data in your SQL Server database.

Another suggestion that I would have is to do a smaller project first to get a realistic idea of your timeframe. You could start here maybe.

You need to get a serious project plan in place and if you're doing this on your own, you need to plan for a serious amount of time - if you're not really a database person, you need to bring somebody on board. Best of luck with your project!


Postgres 'could not write to hash-join temporary file: No space left on device' I was getting this error a while ago and I was curious about two things -

1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in reference to the creation of the temporary file that the db creates? I know it's related to creating a temp file that is out of scope of the memory allocation for the database but I don't know why it's called a hash-join.

2- How can I predict and prevent errors like this (related to performance and memory) occurring in the future?

1- How do the internals of postgres work that cause this message to occur in the first place?

When joining two very large data sets, one strategy PostgreSQL can use is to scan one table and hash its join columns. Then scan the other table and compare the hashes of the join columns to hashes calculated for the second table.

See https://en.wikipedia.org/wiki/Hash_join

It works really well when you're joining a small table against a much larger one, especially where no indexes suit the join conditions.

PostgreSQL can use a hash join strategy for bigger tables where it writes the hash table partitions out to disk first. This is presumably what you got.

  What is the hash-join in reference to the creation of the temporary file that the db creates?

Hash tables. I don't really understand this question.

  2- How can I predict and prevent errors like this (related to performance and memory) occurring in the future?

Have more spare disk space for temporary storage. It doesn't have to be in the main tablespace; see the temp_tablespaces setting.

As far as I know PostgreSQL doesn't have the ability to estimate hash table sizes and decide the hash table will be too big for the available disk space.


MongoDb Upgrade from 2.6 to 3.0 Issue For some reasons I cannot install MongoDb 3.0 version. What am I missing?

It doesn't look like MongoDB provides a repo for Saucy: http://repo.mongodb.org/apt/ubuntu/dists/

So if you installed via Ubuntu-provided repository you have a few options:

Remove the RPM and install via binaries
Upgrade to Trusty
Wait for Ubuntu to update its repo.


MySQL Point in Time Recovery For Just a Single Schema? Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back?

I can think of a lengthy method of backing up each schema individually at present time, bringing the database back three hours and restoring all other schemas (except for the one you want to roll back) back to present time.

Is there any other method? Or am I not understanding binary logs correctly?

You can extract the SQL from a specific schema with the
Use mysqlbinlog utility against all binary logs that have the data and time ranges. You will have to name the database you are extracting:

start-datetime
stop-datetime
database (-d)

For example, suppose you have the following scenario

You have these binary logs
mysql-bin.000019
mysql-bin.000020
mysql-bin.000021
mysql-bin.000022
Binary Logs span from 2013-01-30 09:30:00 to 2013-02-04 12:00:00
You have a full backup from 2013-02-03 00:00:00 (mysqldata-20130203.sql)
You want to restore MyProdDB from 02/03/13 at 2:45 PM 2013-02-03 14:45:00

#STEP 01) Get all DB Changes from the Binary Logs
DB=MyProdDB
BEG_DT="2013-02-03 00:00:00"
END_DT="2013-02-03 14:45:00"
BINLOGS="mysql-bin.000019 mysql-bin.000020 mysql-bin.000021 mysql-bin.000022"
mysqlbinlog -d ${DB} --start-datetime="${BEG_DT}" --stop-datetime="${END_DT}" ${BINLOGS} > mysqldata-delta.sql

#STEP 02) Load Data into Staging Database called MyProdDBStaging
mysql -u... -p... -DMyProdDBStaging < mysqldata-20130203.sql

#STEP 03) Load Delta into Staging Database called MyProdDBStaging
mysql -u... -p... -DMyProdDBStaging < mysqldata-delta.sql

Now, the target database will be the point and time instance for MyProdDB at 2:45 PM on Feb 3, 2013.
Give it a Try !!!


Postgres denormalize tables into json objects for node Heres my problem. I have the following tables
CREATE TABLE inventory(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    owner text NOT NULL references profile(name)
);

CREATE TABLE inventory_item(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    description text,
    amount bigint,
    inventory uuid nOT NULL references table(id)
);

Now I would like to query for a single inventory

all items in that inventory
id and owner of that inventory

So I basically get a result of the following form, to send to the client:
{
   "id":"1234123-1234-1234",
   "owner":"tom",
   "items":[
      {
         "id":"2344-123412-34",
         "description":"sword",
         "amount":33
      },
      {
         "id":"6434-123412-34",
         "description":"apple",
         "amount":23
      },
      {
         "id":"45454-1123412-34",
         "description":"bow",
         "amount":32
      },
      {
         "id":"234234-1232412-34",
         "description":"arrow",
         "amount":1
      }
   ]
}

I was thinking of querying id and owner directly and then adding the items as a subquery using ARRAY(). Unfortunately, ARRAY() only works with columns of the same type.
So how can I denormalize my table structure into a JSON array for node using a postgres query?

The answer was using json_agg instead of array_agg


Compare all attributes in M:N relationship I am new to database development and I'm sure my current problem is quite common. Unfortunately I couldn't find a solution yet - mainly because I'm not sure how to name it correctly ;) So if there is a solution available already or there is a better way to describe it, please tell me and I'll rename this post.

My problem is that I have a database with an M:N relationship and I'm looking for a way to check if a record already exists.

Example:

Books can have multiple places of publication as well as authors. To avoid duplicates, the database should be checked before a new book is added.
If there is only one author and place of publication the query will return just one record:

But if there are for example two authors, 2 rows of the same book will be returned. To make it even more complicated, let's say there are two books with same title, year of publication and pagecount but different authors, this will be returned:

Thats where my problem starts: I want to check if the record with book_id "2" already exists (obviously without knowing the book_id) and if it exists what its book_id is. In this example the only way to achieve this is to check if both authors names (or IDs to make it a little bit shorter) are associated with one of the books having the destinct values in title, year and pagecount - and I don't have any idea how to do that (ideally in SQL).

So thank you in advance for your help :)

Well, after some experiments and close reading of multiple SQL tutorials, I found this is a quite good solution:

SELECT DISTINCTROW book_id, title, year_of_publication FROM
((book
INNER JOIN (SELECT * FROM link_author_book
                 INNER JOIN author ON link_author_book.author = author.auth_id
                 WHERE author.firstname = "Jeff" AND author.lastname = "Carpenter") AS author
ON author.book = book.book_id)

INNER JOIN (SELECT * FROM link_place_book
                 INNER JOIN pub_place ON link_place_book.p_place = pub_place.place_id
                 WHERE pub_place.placename = "Sebastopol" OR pub_place.placename = "Tokyo") AS place
ON place.book = book.book_id)

WHERE title = "Cassandra: The Definitive Guide, 2nd Edition" AND
year_of_publication = 2016 AND pagecount = 370;

This should return exactly the required book and nothing else. Obviously, hard coded values can be replaced with values from frontend forms or something, to make this more flexible ... Any suggestions to optimize this solution are welcome.


impdp stuck on index import impdp import is stuck on:

  Processing object type
  SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

How can you debug this?

It's not stuck. It just does not print every rebuilded index.
You can view status of the process using DBA_RESUMABLE.

  select * from dba_resumable;


SQL Server 2016 Temporal Tables Has anyone tried prepopulating temporal tables with old history data?  Before we decide to use temporal tables we need to be able to import our old history into them.

Yes, you can import old history into history tables. Here is a quick example that shows how to do this - you need to turn off system versioning temporarily.

CREATE TABLE dbo.TemporalExampleHistory
(
  TemporalID int NOT NULL,
  UserName   sysname,
  ValidFrom  datetime2 NOT NULL,
  ValidTo    datetime2 NOT NULL
);
GO

CREATE TABLE dbo.TemporalExample
(
  TemporalID int PRIMARY KEY,
  UserName   sysname,
  ValidFrom  datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo    datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
  SYSTEM_VERSIONING = ON
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory
  )
);

Now, insert some data:

INSERT dbo.TemporalExample(TemporalID, UserName)
  VALUES(1,N'Bob'),(2,N'Frank');

-- update a row to make some history:

UPDATE dbo.TemporalExample
  SET UserName = N'Frankie'
  WHERE TemporalID = 2;

Validate there is a system-versioned row in history table:

SELECT * FROM dbo.TemporalExampleHistory;

Now, to insert rows into history. Execute these steps one at a time; the parser will prevent the insert if these are all in a single batch.

BEGIN TRANSACTION; -- may want SERIALIZABLE here

ALTER TABLE dbo.TemporalExample SET (SYSTEM_VERSIONING = OFF);

INSERT dbo.TemporalExampleHistory(TemporalID, UserName, ValidFrom, ValidTo)
  SELECT TOP (1) TemporalID, N'Little Frankie', '19000101', ValidFrom
    FROM dbo.TemporalExampleHistory
    WHERE TemporalID = 2
    ORDER BY ValidFrom;

ALTER TABLE dbo.TemporalExample SET
(
  SYSTEM_VERSIONING = ON
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory,
    DATA_CONSISTENCY_CHECK = ON
  )
);

COMMIT TRANSACTION;

Now validate there are now two versioned rows of id = 2 in history table:

SELECT * FROM dbo.TemporalExampleHistory;


How can I set block size on Oracle 11 when creating a database instance Is there any way to configure Oracle 11g's default block size on installation?
I have tried Oracle 11g r2 and r3, during both installations block size is 8k and it is disabled (and can not be modified).

I shall restore a data-pump backup from a 16k block size database, so I need to make mine 16k too.

I know I can do this by assigning block size to individual tablespaces (data files), but I am looking for a way to set the default data file size on the whole oracle instance.

You need to choose "Custom Database" in the "Select Template" screen of the Database Creation Assistant, and not one of the templates that includes datafiles as in those the block size is fixed and cannot be changed.


How do I use an Ispell dictionary with Postgres text search? Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.

This example uses the Canadian English dictionary, but you can try it with others as well.

These are the steps needed for Windows:

Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic
Select all of the text, copy it, and paste it to Text Mechanic: http://textmechanic.co/Sort-Text-Lines.html. Add a line break at the end.
Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic_delta
Select all of the text, copy it, and paste it below the previously pasted text in Text Mechanic.
Scroll to the top, and select and cut the first line (should be a five digit number), and get rid of the line break.
Click the Alphabetical button, and wait for the text to sort.
Select all of the text and copy it to the clipboard
Open Windows Notepad as an administrator
Paste the text from Step 7 into Notepad
Save the file as en_ca.dict (with UTF-8 encoding) to your Postgres text search folder. Mine is C:\Program Files\PostgreSQL\9.3\share\tsearch_data .
Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.aff , select all, copy, and paste to Notepad. Save the file as en_ca.affix to your Postgres text search folder.

In PgAdmin, run the following SQL:

create text search dictionary ispell_en_ca (
  template  =   ispell,
  dictfile  =   en_ca,
  afffile   =   en_ca,
  stopwords =   english
);

--make sure it works:
select * from ts_lexize('ispell_en_ca', 'colours');

/*
result:
ts_lexize
text[]
{coloured,colour}
*/

You will need to create a new text search configuration to use the dictionary.


Determine free space on "nonfixed" drive I am writing a stored procedure that needs to send an email when a dvd rw drive is almost full.  I found
master.sys.xp_fixeddrives
but it only shows free disk space for fixed hard drives.  I have not been able to find something that will tell me the free space for "nonfixed" drives.  Is there a undocumented sp or function that I don't know about?

Alternative could be to use PowerShell to grab the information. All you need is a few commands tied together:

$measure = Get-WmiObject Win32_LogicalDisk | Where {$_.DriveType -eq 5}
if ($measure.FreeSpace -lt 20 { "More logic"} else {"something else"}

You could add in the logic to measure for your limit (value is in bytes). Then use Send-MailMessage cmdlet to actually send out your message. Wrap all that up and then have a SQL Agent job call the script on a scheduled basis.


dynamic sql - assigning variable a value from result of dynamic sql query Been struggling with this one for a good while now.  I'm trying to use sp_executesql below:

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVariable int;
DECLARE @fulldbtablename nvarchar(500);
DECLARE @ID bigint;

SET @fulldbtablename = QUOTENAME(@databasename) + '.[DBO].' + QUOTENAME(@tablename)

SET @SQLString = N'SELECT TOP 1 @IDOut = ID FROM' + @fulldbtablename + 'WHERE LastModified < DATEADD(DAY, @daystokeep, GETDATE()) order by 1 desc';
SET @ParmDefinition = N'@daystokeep int,
@IDOut bigint OUTPUT';
SET @IntVariable = -1;

EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@daystokeep = @IntVariable
,@IDOut = @ID OUTPUT;

I'm getting the error Must declare the scalar variable "@ID"

I've reviewed the help below, but I can't shake this error.  Has anyone any ideas?  I am declaring @ID in DECLARE @ID bigint;

To help me debug, I split the query into a new window and ran the query below which completes successfully so I am pulling my hair out:

DECLARE @Databasename varchar(500)
DECLARE @Tablename varchar(500)
DECLARE @ID bigint;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVariable int;
DECLARE @fulldbtablename nvarchar(500);

SET @databasename = 'DatabaseName'
Set @Tablename = 'TableName'
SET @fulldbtablename = QUOTENAME(@databasename) + '.[DBO].' + QUOTENAME(@tablename)

SET @SQLString = N'SELECT TOP 1 @IDOut = ID FROM' + @fulldbtablename + 'WHERE LastModified < DATEADD(DAY, @daystokeep, GETDATE()) order by 1 desc';
SET @ParmDefinition = N'@daystokeep int,
@IDOut bigint OUTPUT';
SET @IntVariable = -1;

EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@daystokeep = @IntVariable
,@IDOut = @ID OUTPUT;

SELECT @ID

Anyone any ideas??????  Thank you!

You were missing the variable assignment inside the dynamic SQL batch.

DECLARE @sqlcommand NVARCHAR(MAX) = 'SELECT TOP 1 @ID = (ID) --Assign me!
                                     FROM ' + QUOTENAME(@tablename) + '
                                     WHERE LastModified < DATEADD(DAY,' + @NumberOfKeepDays + ', GETDATE())
                                    ORDER BY 1 DESC';

EXECUTE sys.sp_executesql @sqlcommand, N'@ID BIGINT OUTPUT', @ID = @ID OUTPUT;

The example from the sp_executesql doc is pretty clear, though suffers from formatting.

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber) --Assign me!
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;

EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;

If you want to learn a whole heck of a lot about dynamic SQL, SQL injection (and you should, because what you're executing now is potentially unsafe), head over here


Does Azure SQL Managed Instance offer SQL Server 2016? I want to create a SQL Server Managed Instance in Azure that is SQL Server 2016 so I can backup and restore databases across on prem and Azure - i.e. in case we want to back out of Azure, we can do a backup and restore rather than create scripts procedure.

I see the @@VERSION of my current managed instance is Microsoft SQL Azure (RTM) - 12.0.2000.8

If I try to restore a database from Azure to a 2016 server, I get the message '15.00.2000. That version is incompatible with this server, which is running version 13.00.5492' - so Azure is SQL Server 2019. Can it be 2016? Struggling to find clear answers when it comes to Azure Managed Instance.

You cannot restore a backup from a Managed Instance to an on-prem version of SQL Server.  Even if you had an on-prem 2019, it wouldn't work.  During our migration this was one of the things that gave me pause as well, that it was going to be one-way without a lot of work.

Additionally (as pointed out by David Browne) you can use Snapshot Replication between Managed Instances and On-Prem SQL.  So that is a viable method of migrating as well as the horror that I had envisioned internally of doing bulk ETL from MI to on-prem.

Also, to answer the question directly asked, Managed Instances offer one version of the SQL Engine.  You can select a 2016 compatibility level for your databases if you need to limit yourself to those features.


Group records by different date range I need to run a query against a database and group the records by different date ranges. The data type of the date field is varchar and in the format dd-MMM-yyyy e.g. 19-Aug-2016.

The date ranges are as follow: 13–18, 19–35, 36–55 and >55. This range is determined by subtracting the current date from the date stored in the field.
e.g. if current date is 19-Aug-2016 and date stored in the database field is 18-Aug-2003, the year difference will be 13. So all the date differences between 13 and 18 will be grouped together, all date differences between 19 and 35 will be grouped together etc.

First let me say storing dates in a character column is a horrible horrible idea.  Sorts won't work correctly, any form of date calculations are going to require actually casting back to a date anyway, and best of all you probably have bad data in there.

But assuming your table isn't overly large (performance on this is going to kill you because of the cast) and you don't have any bad data.  This will work

CREATE TABLE #temp (chardate varchar(11))
INSERT INTO #temp values ('19-Aug-2016'),('19-Mar-2010'),('10-Feb-1927'),
        ('10-Dec-2013'),('19-Mar-2000'),('10-Jan-1958')

SELECT CASE WHEN datediff(year,CAST(chardate as date),getdate()) < 13 THEN '<13'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 13 and 18 THEN '13-18'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 19 and 35 THEN '19-35'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 36 and 55 THEN '36-55'
            WHEN datediff(year,CAST(chardate as date),getdate()) > 55 THEN '>55' END AS [Range],
    COUNT(1)
FROM #temp
GROUP BY CASE WHEN datediff(year,CAST(chardate as date),getdate()) < 13 THEN '<13'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 13 and 18 THEN '13-18'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 19 and 35 THEN '19-35'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 36 and 55 THEN '36-55'
            WHEN datediff(year,CAST(chardate as date),getdate()) > 55 THEN '>55' END

V2.0 You'll probably get a bit better performance with this:

WITH FixDate AS (SELECT CAST(chardate as date) AS realdate
                FROM #temp),
        MyRange AS (SELECT CASE WHEN datediff(year,RealDate,getdate()) < 13 THEN '<13'
                WHEN datediff(year,RealDate,getdate()) between 13 and 18 THEN '13-18'
                WHEN datediff(year,RealDate,getdate()) between 19 and 35 THEN '19-35'
                WHEN datediff(year,RealDate,getdate()) between 36 and 55 THEN '36-55'
                WHEN datediff(year,RealDate,getdate()) > 55 THEN '>55' END AS [Range]
                FROM FixDate)
SELECT [Range], count(1)
FROM MyRange
GROUP BY [Range]


How to reinstall PostgreSQL over an existing installation I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4.

The old version on of the DB used to be an MSI installer but they have switched to a Windows EXE with the later version. The old version was able to install over a existing installation. The new version breaks and gives me a very general error that I can't use to pinpoint the issue.

Error:

  The installation directory must be an absolute path, containing only
  letters, numbers and the characters '-', '/', '.' and '_', and must be
  writable.

This is my install command:

  postgresql-9.4.4-1-windows-x64.exe --prefix "C:\postgres" --datadir
  "C:\postgres\data" --enable_acledit 1 --install_runtimes 0
  --serverport 5432 --superpassword "XXXX" --servicepassword "XXXX" --unattendedmodeui minimal --mode unattended --debuglevel 0 --serviceaccount "postgres" --create_shortcuts 0

The Bitrock installer only provides the above error with no path detail or anything to act on.

Any clues or suggestions? (Thanks)

Actually I've found out the problem. At least at my end with the same error.

I was creating an installer for my software with NSIS and it uses the postgresql database, in order to use the postgresql silent install I previously installed it on the target machine to test which parameters I should use, after tested it successfully I then uninstalled it so it could be installed by my software.

Then problem appears when I run my installer and the same error shows:

  The installation directory must be an absolute path, containing only letters, numbers and the characters '-', '/', '.' and '_', and must be writable.

I start to test everything from users, to user permissions, folder permissions, length of folder names.

Then I went to windows registry and found out the my first installation of postgresql left some registries for it even though I uninstalled it from the control panel also it left the postgres user on the machine.

So I delete all postgresql (Edit->find->postgresql) entries from the registry and also deleted the Windows user (postgres) and then everithing works great.

The only problem now is how to do it through the NSIS program.

Hope it helps.


how to force oracle 12c to create histograms I installed TPC-H on Oracle 12c and my goal is to get histograms on column groups (if anyone knows for sure that this is not possible anyway, please prove and I'll stop testing...). So I used

select   dbms_stats.CREATE_EXTENDED_STATS(NULL,'H_LINEITEM','(l_shipdate, l_receiptdate)')
from dual;

for creating extended statistics. Afterwards I gathered statistics:

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'H_LINEITEM', OPTIONS=>'GATHER AUTO');

I also queried some stuff on the respective columns like

SELECT COUNT(*)
FROM H_LINEITEM
WHERE L_RECEIPTDATE > L_SHIPDATE + 5;

or

select l_shipdate, l_receiptdate, count(*)
from h_lineitem
group by (l_shipdate, l_receiptdate)
order by 3 desc
fetch first 10 rows only;

and gathered statistics again and again.
But everytime I look at the statistics with

select column_name, num_distinct, num_nulls, histogram
from user_tab_col_statistics
where table_name = 'H_LINEITEM'

there's not one histogram present.
Is there any parameter to change or do I need to write a whole script with lots of queries so that the optimizer starts considering making histograms?

A friend of mine told me today, that using the following command

begin
  DBMS_STATS.GATHER_TABLE_STATS (
    ownname => USER,
    tabname => 'H_LINEITEM',
    estimate_percent => 100,
    method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 SYS_STU3ZM$X#P01D3JFWNOWKQ7DD$'
  );
end;
/

where SYS_STU3ZM$X#P01D3JFWNOWKQ7DD$ is the automatically generated name of the column group,

one can manually create a histogram for a given column / column group.

A description can be found here.


Oracle 11g Rman restore with another database name from backup in windows platform Can anyone share the proper method of RMAN restore to another server with another database name in oracle 11g(Windows platform). I'm totally confused with all the RMAN restore methods which I've found. I've taken the backup using the command

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Please confirm whether is it required to take control file backup separately.

Any help will be appreciated.

By default, the controlfile is backed up with your data. This is turned on or off by this command:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

But as a matter of habit, I usually follow my data backups with:

backup current controlfile ;
backup spfile ;

I've had a great deal of trouble using duplicate database on Windows. Typically, I run into NTS permissions failures for the target login, which prevents the auxiliary db from creating. My efforts to troubleshoot these errors... well, I gave up.

This has led me to drafting a "playbook" document with all of the various OS and RMAN commands needed to manually restore a database to a new server with a new instance name. This also lets me tweak things to the new host with more control.

These scripts were tested in my Oracle 12.1 Windows environment, but may not be ideal for yours. I know RMAN is more willing to run SQL statements in 12c than in 11g, for example. Hopefully they help.

These scripts were created over several trial and error efforts, based on this link for the restore and this for changing the SID.
The steps are:

Make sure everything is backed up.

I like to get a fresh copy of the spfile and controlfile, as it makes it easier later.

backup device type disk incremental level = 0 section size 1g database plus archivelog;
backup current controlfile tag='Controlfile Level 0';
backup spfile tag='SPFile Level 0';

If your old and new servers do not use the same file paths for Oracle data, then run this:

select '  Set Newname For Datafile ' || file# || ' to ''' || name || ''';' from v$datafile;
select '  Alter Database Rename File ''' || Member || ''' To ''' || Member || ''';' from v$logfile;

take the output and change the paths / filenames as needed to match your new environment.

Copy backups from old to new host

Optional but I like the RMAN backup files to be on the box I'm restoring to. So log into the new server, and copy the full backups, archivelogs, and autobackup folders from the old server's fast recovery area. Note that you only need to go back to the most recent full backup, no need to get weeks worth of files.

Stage the new instance

On the new host, create your data folder, your fast recovery folder, and your admin folder. Make sure Oracle is installed and the Oracle user accounts have full control of these folders.

Create an empty instance, with the same name as the old:

oradim -new -sid <<old sid name>>

Set ORACLE_SID=<<old sid name>>
RMAN target /

Use RMAN to recover the SPFILE and Control Files.
inside RMAN, set the DBID to match the old instance's DB ID, and the file names and paths to match your environment:

SET DBID=1234567;
startup nomount;
restore spfile to 'C:\Oracle\Product\12.1.0\dbhome_1\database\init<<Old sid>>/ora' from 'f:\fast_recovery_area\<<old sid>>\BackupSet\2017_07_17\O1_MF_NNSNF_SPFILE_LEVEL_0_DOSTOJT8_.BKP';
create pfile='c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<old sid>>.ora' from spfile='c:\oracle\product\12.1.0\dbhome_1\Database\Init<<old sid>>.ora';
shutdown immediate;

Do not close RMAN yet. Edit the pfile from the previous step in NotePad or NotePad++. Adjust the paths and memory requirements as needed to reflect the new environment. Don't change the name of the database, but change the folder paths to the new instance name.

Go back to your RMAN session and restore the control files. Again, fix paths as needed.

startup nomount pfile = 'c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<old sid>>.ora’;
restore controlfile from 'F:\fast_recovery\<<old sid>>\BACKUPSET\2017_07_05\O1_MF_NCNNF_CONTROLFILE_LEVEL_0_DOSTO715_.BKP';
Alter Database Mount;

Restore your database files

Still in RMAN, inventory the backup files you copied over.

 catalog start with 'f:\fast_recovery_Area\<<old sid>>';

Now move your redo log files to their new folders in RMAN. You generated these scripts earlier.

Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO03.LOG' To 'f:\ORADATA\<<NEW sid>>\REDO03.LOG';
Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO02.LOG' To 'f:\ORADATA\<<new sid>>\REDO02.LOG';
Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO01.LOG' To 'f:\ORADATA\<<NEW Sid>>\REDO01.LOG';

And now we do the restore and recover, substitute the NewName scripts created earlier.

run {
    Set Newname For Datafile 1 to 'f:\ORADATA\<<new sid>>\SYSTEM01.DBF';
    Set Newname For Datafile 3 to 'f:\ORADATA\<<new sid>>\SYSAUX01.DBF';
    Set Newname For Datafile 5 to 'f:\ORADATA\<<new sid>>\UNDOTBS01.DBF';
    Set Newname For Datafile 6 to 'f:\ORADATA\<<new sid>>\USERS01.DBF';

    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
}

Once RMAN finishes the base restore and recovery, create a new SPFile.

create spfile from pfile = 'c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<OLD sid>>.ora’;
alter database open resetlogs;

Oracle does not backup or restore TEMP tablespace. This will prevent the full recovery from succeeding. Create a new temp tablespace, drop the old, and then rebuild the original one.

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE 'f:\OraData\<<NEW Sid>>\TempNew1.dbf' SIZE 5m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
DROP TABLESPACE TEMP including contents;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f:\OraData\<<NEW Sid>>\Temp01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP_NEW including contents and datafiles;

Change the instance name

At this point, Oracle should be running a fully restored copy of your database. But you want a new name. In RMAN:

Shutdown immediate;
Startup mount;

Don't close RMAN, but from a new CMD window running as administrator:

 set ORACLE_SID=<<old sid>>
 NID target=/ DBNAME=<<new sid>>

Note the DBID this gives you, as you'll need it later for disaster recovery (notice that was our first RMAN command at the top).

Back in the RMAN window:

Shutdown Immediate;
Startup Mount;
Alter System Set DB_NAME=<<New sid>> scope=SPFILE;
Shutdown Immediate;
exit;

Start up your new DB!

From the other CMD window, create a new password file, and recreate the Windows services. Note, this utility does not use quotes around the path, unlike most cases in Oracle.

orapwd file=c:\oracle\product\12.1.0\dbhome_1\Database\pwd<<new SID>>.ora password=<SYS pwd> entries=25
oradim -delete -sid <<old sid>>
oradim -new -sid <<new sid>> -intpwd <SYS pwd> -startmode a

Start the database.

Set Oracle_SID=<<new sid>>
lsnrctl reload
sqlplus / as sysdba
Startup Mount;
Alter Database Open ResetLogs;

Back up your new database

At this point, your DB has no backups. Perform an RMAN backup before you do any work in this DB.


Error in running SQL Server script I copied oldDatabase as newDatabase. Modified newDatabase's schema.
Meanwhile, oldDatabase's data are processing and updating.

I want to copy oldDatabase's data to newDatabase.

What I have done is:

Clean the data of newDatabase
Generate data only script of oldDatabase
Run this script in newDatabase.

And I got a lot of error messages like:

Cannot insert explicit value for identity column in table 'MyTableName' when
IDENTITY_INSERT is set to OFF.

Invalid column name 'MyColumnName'.

Violation of PRIMARY KEY constraint 'PK_MyTableName'. Cannot insert duplicate
key in object 'dbo.MyTableName'.

Some tables and rows data are affected. But, tables with above error are not affected. How can I fix it? Is there a best way to make this stuff?

PS

I run my script using this command ,

sqlcmd -S myServerName -U userName -P passowrd -i PathOfMyScriptFile

What your script is doing is explicitly attempting to insert data into an IDENTITY column. You need to do one of two things:  Either set IDENTITY_INSERT to ON for that table prior to the insert, or rework the INSERT statement so that it doesn't have the IDENTITY column specified with a value.


Can we download SSRS 2017 to use with SQL Server 2016? We are using our organization's SQL Server 2014 SP3 servers.  (They also have 2016 SQL Servers, but we elected to skip 2016 and just move to the next SQL servers when they get installed.)
We have a need for the REST API access in SSRS 2017, so we would like to upgrade our current SSRS server to SSRS 2017.

The Windows server admin just set up a new/test SSRS server for us, but he used SSRS 2016, because that is the latest media that the organization has, and they are of the impression that we can only install SSRS for the SQL server version that we're using.

Am I correct in thinking that it should be possible for us to download (for free) and install SSRS 2017 and then point it to the existing 2016 SQL servers?  Or do we need to purchase a license for SQL Server 2017 to be able to use SSRS 2017?

(This seems similar to existing question SSRS 2016 on SQL Server 2014, but that question and answer seem to focus on SQL Server Express, which is not our situation.)

You can download and install SSRS 2017, and use an existing SQL Server to store the Report Server database.  The databases can be on any supported version of SQL Server, as documented here.

The server running SSRS 2017 must, of course, be licensed per the licensing guide here.

Alternatively you can get this functionality (and more), with Power BI Report Server, which can be acquired via Power BI Premium, or run using SQL Server Enterprise Edition Core Licenses covered by active Software Assurance.


SQL Server Table Design for Report - Is OLAP Schema Suitable A work colleague is developing SQL Server database tables for a reporting project. Basically, the only activity on the table will be populating it from a ETL process (SSIS).

He insists on creating a complex normalized design but I do not think this is necessary as there will be no activity on the table other than INSERT from the ETL, and SELECT for viewing the report data.

From what I can tell this is more suitable to OLAP rather than OLTP. Or since all the data is just output to Excel anyway, one table should be enough to store it? There is no aggregation either. Why the need for multiple tables?

Any suggestions on this? Is a simple table the best way forward or is it better to use normalized design?

For databases of a significant size, the normalised logical design is typically created first. Following this, sample queries are run and the database is then denormalised for performance if need be (the physical design). If the gains are minimal, you may elect to stick with the logical design for the simple reason that it is easier to understand and is more efficient from a storage point of view.

It would be somewhat presumptuous to go for a denormalised design from the off without any sort of performance testing - even if this is clearly an OLAP system.


AWS RDS MariaDB replication to GCP Cloud SQL or Azure SQL DB For disaster recovery, I need to replicate an Amazon Multi-AZ RDS instance currently using MariaDB to a different platform like Google Cloud Platform or Azure. If AWS is down for more than 30 minutes, I want to point my apps over to the other platform.

I know replication could more easily be achieved with an EC2 database, but if it's possible to use RDS I would stick with it.

How can RDS replicate to another cloud platform?

I don't think it can be done solely in RDS, at least not directly between 2 DBaaS platforms.  Both vendors would need interoperability in their APIs.  I think your stuck standing up a Linux/Windows server in AWS/GCP/Azure to be a "on-prem" host to work in between the DBaaS platforms.

This may be helpful: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

Of course...

  Replication to an instance of MySQL running external to Amazon RDS is only supported during the time it takes to export a database from a MySQL DB instance. The replication should be terminated when the data has been exported and applications can start accessing the external instance.

Hopefully you need to do this for a migration, and not ongoing replication, or you don't care about being in a "supported" configuration.


Fetch wikipedia articles query optimization in mysql wikipedia Database I have imported the Wikipedia database to a local MySQL database and I wrote a query to fetch wiki pages.

My query is as follows :

SELECT
  p.page_title,
  p.page_id,
  t.id,
  text.old_text
FROM wikipedia_en.page p
  INNER JOIN wikipedia_en.categorylinks cl
         ON  cl.cl_from = p.page_id AND cl.cl_type = 'page'
         AND p.page_namespace = 0
         AND p.page_is_redirect = 0
  INNER JOIN wikipedia_en.revision ON revision.rev_id = p.page_latest
  INNER JOIN wikipedia_en.text ON text.old_id = revision.rev_text_id

   -- filter out pages with no topic
  INNER JOIN topics.topics t  ON cl.cl_to = t.topic
LIMIT 520000, 10000;

The query takes about 2s if the LIMIT offset starts at 0 but as the offset grows up (e.g 520000) the query execution takes a remarkable time ( e.g for and offset of 520000 it takes about 4min )

Here is the query Explain Plan :

+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
| id | select_type | table    | type   | possible_keys                                  | key        | key_len | ref                               | rows   | Extra                    |
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
|  1 | SIMPLE      | t        | ALL    | topics_topic_index                             | NULL       | NULL    | NULL                              | 141413 |                          |
|  1 | SIMPLE      | cl       | ref    | PRIMARY,cl_timestamp,cl_sortkey                | cl_sortkey | 258     | topics.t.topic,const              |     14 | Using where; Using index |
|  1 | SIMPLE      | p        | eq_ref | PRIMARY,name_title,page_redirect_namespace_len | PRIMARY    | 4       | wikipedia_en.cl.cl_from           |      1 | Using where              |
|  1 | SIMPLE      | revision | eq_ref | PRIMARY                                        | PRIMARY    | 4       | wikipedia_en.p.page_latest        |      1 |                          |
|  1 | SIMPLE      | text     | eq_ref | PRIMARY                                        | PRIMARY    | 4       | wikipedia_en.revision.rev_text_id |      1 |                          |
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+

I'm asking if there is a way to optimize this query using subqueries or temporary tables.

OFFSET must locate, but skip over, the rows.  If you can "remember where you left off", performance may be significantly better.

But there is a worse problem with your query.  Since there is no ORDER BY, the optimizer is free to provide the rows in any order it likes, thereby leading to missing or duplicated rows.

Even if you add an ORDER BY, OFFSET can give miss or duplicate rows if the table is being added to or deleted from.

Read my blog on this problem, then start over on your query.


Deleted MySQL "root" user (using phpMyAdmin)...how to restore? I'm test-running a Joomla installation (locally, using WAMP) with an MySQL database supporting it...this is in preparation to moving everything online once I have it configured.

I was working in phpMyAdmin to change the password of a superuser I had set up with a login-name other than "root."  While doing so, I saw three additional "root" users, and on the phpMyAdmin home page, a warning about these root users not having a password.  I thought that since I created my own superuser with "All Privileges," I didn't need the root users...so I deleted them.  Immediately things stopped working...and now, when I click on the "Users" tab in phpMyAdmin, it simply returns a red bar that says "No Privileges"...my specific superuser is gone.  However, it seems I can still move around in phpMyAdmin (maybe because I'm still logged in?).

Could someone please tell me step-by-step how (if?) I can re-add the root users using phpMyAdmin?  I see instructions posted in various places, but they seem to address users who are using terminal windows or some other program.  I'd  hate to see months of work go down the drain!

EDIT:  Using Windows, I've tried the following:

There is no file called "resetroot.bat" anywhere in the wamp/sql folders, as someone suggested...so that option is out.
I've tried to access MySQL using a terminal connection:  in the MySQL root folder there is a file called mysql.exe that seems to grant this when executed (there is no "mysql_safe.exe" as some solutions have suggested).  However, I have been unsuccessful in starting mysql with the --skip-grant-tables option from outside mysql.exe, and when I try to do it from within mysql.exe, I just get an odd prompt ("->") and no other commands seem to work.

Please keep in mind that I am a newbie at this!  I am trying different things, but without any success...that is why I'll probably need step-by-step instructions.

OK, I finally found a solution.  To give proper credit, I had to blend answers provided by two people:  RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X.
For Wamp Users:

Click on the Wamp system tray icon to bring up the tray menu.  Click
MySQL > Service > Stop Service.

Edit your MySQL's my.ini file...that's your configuration file for your SQL database, and it's generally located in the root folder for your MySQL database (try looking in wamp > bin > mysql > mysql5.5 [or whatever version]).  Use a text editor, and scroll down in the my.ini file to [wampmysqld].  Immediately after this entry, insert a new line containing the following command:
skip-grant-tables
This tells the MySQL database that you want to override any restrictions such as root user or password parameters.  This is a major security hole, so once we are done with our repairs, we'll need to undo this!  Save the my.ini file.

Resume MySQL services in Wamp (click MySQL > Service > Start/Resume Service).

Now open the MySQL console:  click MySQL > MySQL Console

A "DOS-like" terminal window will open up (if it asks you for a password, simply press enter).  You should see a MySQL prompt.  Copy the following block of code and paste it at the prompt.
INSERT INTO mysql.user
SET user = 'root',
host = 'localhost',
password = '',
Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y';

Press enter.  You should get some sort of message saying that one line (of database code) was affected, with some warnings (probably because you are messing with the root user...I think you can safely ignore these).  Close the MySQL terminal.

Remember that security hole we opened in step two?  Time to fix that.  Reopen your my.ini SQL configuration file, and remove the skip-grant-tables line you added.  Save the my.ini file and close.

RESTART your MySQL Service (same as before, but click on MySQL > Service > Restart Services instead).

THAT should do the trick!
A note on the root user in MySQL for newbies:  for some strange reason, even if you create another user as a superuser, MySQL seems to need this user to operate...however, if you don't set up a password for root, it notes this as a security hole.  Whatever you do, don't delete it:  you're better off just setting some wildly obscure password for it and leaving it alone if you want to use another superuser account.  It would seem that there should be more protections to help prevent unwary users from doing this (a warning, or something)....that's my twelve cents on the issue.


Which is better: one database per application, or just one database? I ask this specifically for SQL Server, the advantages that I can see for multiple databases are:

Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall)
A page corruption does not affect multiple applications (can this be isolated using filegroups?)

I can't seem to find any benefits from a single database architecture (maybe the code management is simpler).

Which architecture did you choose and why?

I agree with mrdenny to have 1 database per Application. There are many good reasons to do that :

Most importantly Log :
SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online.
Full Backups : Easy to manage per application and can be prioratize based on critical to less critical application databases.
DR situation : Easy to manage in scenarios like application crash, server down or failover scenarios. You have the flexiblity of just performing failover of 1 application or all - depending on if you want to test DR for just 1 application or all.
Performance consideration and Problem Isolation : When the application gets busy, it can easily be migrated to new server with less activity. Also, think about locking, blocking and deadlock situations where the same database is being accessed by multiple applications causing all sorts of issues for troubleshooting. It becomes easy to just concentrate on a single application performing bad when you have 1 database per application.
Capacity Planning : You can easily establish baselines to see what applications will require more disk space in the future and possibly migrate them to a new server having enough disk space.
Database Maintenance : The maintenance will be easy as you can concentrate on specific application's databases and give them priority as opposed to just one single database for all applications. The overall maintenance time can be reduced when performing checkdb, rebuild/reorg indexes, update stats, etc.

Above are just some advantages of using 1 database per application. There are more, like having different requirement of database recovery models, backup schedules, collation, etc as well.

Also, you can think of logically separating within the database using different schemas.


Is it possible to ignore query hints in SQL Server 2012? I know this sounds a bit odd... the thing is we have a piece of critical code in production which issues a query with a Hash Join hint which is causing high CPU usage on SQL Server.

Owing to some lack of change control in the past, we are not confident of being able to deploy a new version of this code without the HASH without potentially breaking other (much more critical) stuff.

I'd like to ignore this HASH (and even all hints, as I am confident none of them are required and I'd like to allow SQL Server to do its thing without interference!)

Is there a server-wide option to do this?

Thanks!

I spent a while messing with this. And while there's not a way to get SQL to ignore a forced join type at the table level (see Kendra Little's excellent video and related links), there is something kinda sneaky you might be able to do to get them to change the code.

In SQL Server 2012 (SP3+), there are hints to toggle Memory Grants. Since Hash Joins are memory consuming operators, you could use a Plan Guide to severely limit the memory your query acquires, slowing it down immensely.

Take this simple example query:

SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;

The plan looks pretty normal.

The Hash Join stays in memory, because the query acquires a ~2.1GB memory grant.

Creating a bad plan:

If we create a plan guide and use the MAX_GRANT_PERCENT hint to cap the memory at 0...

EXEC sp_create_plan_guide
@name = N'brimful of hash joins',
@stmt =  N'SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(MAX_GRANT_PERCENT = 0)'
GO

Our query gets a similar looking plan...

But now the Hash Join spills dismally to disk.

Because it got a nonsense memory grant of around ~8mb.

Further evidence of how awful this query got is in SET STATISTICS TIME, IO ON

No Guide:
Table 'Users'. Scan count 7, logical reads 113953,
Table 'Posts'. Scan count 7, logical reads 9777604,
Table 'Workfile'. Scan count 0, logical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0,

 SQL Server Execution Times:
   CPU time = 40001 ms,  elapsed time = 6992 ms.

Guided By Voices:
Table 'Users'. Scan count 7, logical reads 113953,
Table 'Posts'. Scan count 7, logical reads 9776298,
Table 'Workfile'. Scan count 3312, logical reads 362880, physical reads 56144, read-ahead reads 446000,
Table 'Worktable'. Scan count 0, logical reads 0,

 SQL Server Execution Times:
   CPU time = 66593 ms,  elapsed time = 34270 ms.

The query with the rotten plan guide is in decidedly worse shape.

Is this a good idea?

Well, no, but it doesn't seem like that join hint is so hot, either.

For what it's worth, I tried some other stuff to get the hint to ignored, but they didn't seem to work (no plan change):

@hints = N'OPTION(LOOP JOIN, MERGE JOIN, HASH JOIN, TABLE HINT(p, FORCESEEK))'

Or resulted in an error:

@hints = N'OPTION(LOOP JOIN, MERGE JOIN, TABLE HINT(p, FORCESEEK))'

Msg 1042, Level 16, State 1, Line 64
Conflicting JOIN optimizer hints specified.

Msg 10516, Level 16, State 1, Procedure sp_create_plan_guide, Line 20 [Batch Start Line 59]
Cannot create plan guide 'brimful of hash joins' because @module_or_batch can not be compiled.

I don't expect you to do this, but it was an interesting enough question for me to want to come up with some response (note: not answer!) to.

And, you know, I thought the results were funny.


Analysing SQL Server Network I/O A frequent request I get from clients is to help figure out what queries or clients are making the largest contribution to SQL Server saturating an Ethernet link.

Ideally I'd look at some counters or DMVs showing network I/O aggregated by query, client, application, database, etc.

Since there doesn't appear to be a direct way of doing that, as a substitute I usually look at the *_rows columns in sys.dm_exec_query_stats (these columns exist in 2008 R2+ only, I think) and also look for queries that appear to involve BLOBs.

Is there a better way of approaching this problem?

Your correct, 2008R2 and onwards has the *_rows added to sys.dm_exec_query_stats. I would say that's the way to go.

You'll have plenty of queries that can create massive disk IO but still have a small result set. However, it's not possible to have a huge result set without huge logical IOs.

So look at query stats ordered by logical io's first, then row count second. That will give you a pretty good starting point.

Before 2008 R2 I would look at which queries with large logical IOs have network related waitstats. The problem with this approach is. 1) Once the network is saturated, it's likely that not only your large queries, but pretty much all your queries will have network related waitstats. 2)Network related wait stats are in many cases not caused by network related issues ;-)

cheers,
Edward


Physically migrate MySQL without a dump A client ask me to migrate his MySQL DB:
The server doesn't have free space and it also has a very big table that is broken, so I can't dump it.

I can't REPAIR it because of lacking of free space.

Question : Is there a way to physically move MySQL DB data files to another server and use them for the "new" MySQL ?

Here is what you can do:

Old DB Server : 10.1.2.30
New DB Server : 10.1.2.40

STEP 01) On the Old Server, service mysql stop

STEP 02) On the Old Server, rsync -av --progress /var/lib/mysql root@10.1.2.40:/var/lib/mysql

STEP 03) On the New Server, chown -R mysql:mysql /var/lib/mysql

STEP 04) On the New Server, service mysql start

Make sure /var/lib/mysql on the New Server is on a much bigger disk mount


Audit Requirement - Capture Logins vs Database I have a question, I hope you can give me some guidance based on your high expertise level.

There is an audit requirement where we have to provide login information per databases on a given server. Basically, I’m talking about DB users that authenticate externally to a DB (containment type is None).

How should I proceed to capture this event to answer the following question? “Give me the last login for the DB User XYZ authenticated in DB ABC?”

I couldn’t find anything in SQL Server Audit Specification (SQL 2012). It only works when the DB  has the containment type set to Partial. But I also need to capture logins for SQL 2005 and 2008 databases.

Your help is greatly appreciated, Thank you in advance.

For this I would default to extended events for the 2008 server and up. There is a good blog post about it here.

Additionally, for the 2005 server, you would need to use a trace as the extended event functionality wasn't available. There was another post for this shown here.

Both of these options give you different ways of storing and what to capture such as login name, time, database name, Etc.


How to change a MySQL previous slave to be a master and remove slave status information? I have a master -> slave configuration where the master failed.  I've been able to reset the old-slave to be a master and the old-master to slave from it.  Fine.

What I can't seem to do is to remove the master information on the old-slave which is now the new-master.  I see:

mysql> show slave status \G
*************************** 1. row ***************************
           Slave_IO_State:
              Master_Host: 10.1.2.101
              Master_User: replicationSlave
              Master_Port: 3306
              ...
              Slave_IO_Running: No
              Slave_SQL_Running: No

I've read a lot of MySQL documentation but I still haven't found a way to clear the slave information from the new-master.  I've tried:

RESET SLAVE which does not seem to clear those settings. [[Actually it does remove the master.info file but not the memory settings.  See below.]]
CHANGE MASTER TO MASTER_HOST='' which just spits on an error since it was deprecated recently.
Checking my.cnf which does not have the master information since they were added programmatically.
RESET MASTER because some mysql docs recommended it.  That only resets the bin logs.
Poking around in the internal MySQL tables to see if I can find the fields to clear.

What is the proper way to do this on MySQL ~5.5.9?  Thanks for any help.

Edit:

So it turns out that RESET SLAVE removes the master.info file as @RolandoMySQLDBA implied.  However, you still need to restart the server before the slave information is removed.

Is there any way to remove this slave information without having to restart mysqld?

In MySQL 5.5.16 and later, you can use RESET SLAVE ALL to do all that RESET SLAVE does and reset the connection parameters from the memory, this way it doesn't require a mysqld restart.


Asynch Descriptor Resize explanation What do asynch descriptor resize wait events mean on an Oracle 11g database running on Windows 2008 R2?  According to My Oracle Support, this event applies to any platform, but the description provided includes the following information in Doc ID 1081977.1:

  This event is set when the number of
  asynch descriptor reserved inside the
  OS kernel has to be readjusted. It is
  signaled when the number of asynch
  I/O's submitted by a process has to be
  increased. The Linux kernel does not
  allow the limit to be increased when
  there are outstanding I/O's inside the
  kernel. Hence, all outstanding I/O's
  are reaped before the limit is
  increased. The wait to reap all the
  outstanding I/O's when the limit is
  increased uses this event.
  [Emphasis mine]

This isn't causing a significant problem on our systems, but it would be nice to know what it means on a Windows platform.

This seems similar to a comment on this blog post, particularly because it mentions Oracle 11g R2 with Windows 2008 and VMware.  While this indicates that the problem isn't unique to our systems, it does not help identify the cause or possible solutions.  The other comment about a Microsoft fix applies to Windows 2008 R1 rather than R2.

Just so there is some sort of answer to this, here is some information I have found.

On MOS there is a document called "High Numbers of 'Asynch Descriptor Resize' Wait Events Seen" (Doc ID 1273748.1)  This seems to indicate that the problem is really another wait event slowing the system down and in turn causing these, so they aren't really the source wait event.

Oracle-L has a thread on the wait event, but no mention of it on Windows.

OakTable has some interesting info on the problem, and says,

  ...the resize apparently sucks on Linux. Perhaps that’s why other ports also suffer and have seen the same wait event.


How to stop OSX MySQL filling up error logs I've noticed about every second, a new line the mysqld error log noting a login failure. The user isn't always the same, but it consistently happens every second when mysqld is running. I'd like to turn this behavior off or prevent it from logging. I suspect this has something to do with launchd / launchctl.

Suggestions??

e.g.

2016-03-12T16:50:28.126292Z 1567 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:30.298595Z 1568 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:32.130295Z 1569 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:34.131190Z 1570 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:36.131302Z 1571 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:39.211193Z 1572 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:40.322560Z 1573 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:42.130756Z 1574 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:44.131086Z 1575 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:46.130904Z 1576 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:49.064900Z 1577 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:52.814885Z 1578 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:54.130283Z 1579 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)
2016-03-12T16:50:56.132243Z 1580 [Note] Access denied for user '_spotlight'@'localhost' (using password: NO)

My install info:

mysql> SHOW VARIABLES LIKE '%version%';;
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.9                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.9-log                    |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | osx10.9                      |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

[This question is almost a year old, but anyways I just came across it while investigating the same issue, and although the answer is probably no longer relevant for you, it might be for someone else reading this.]

This can happen when using the standard MySQL installation including the MySQL preference pane. When opening the MySQL preference pane, it will periodically (about once per second) try to connect to localhost, presumably to find out if MySQL is running and display the status accordingly. Interestingly, these connections do not stop when going back to the System Preferences start screen, but they do stop when closing the System Preferences.

When initiating these connections, the preference pane uses various usernames (for whatever reason). On my macOS Sierra with MySQL 5.7.17, I observed _spotlight, _mysql and the current user’s username.

However, if you get those log entries even when System Preferences are closed, this is a different issue in your case.


How can I update a single field in an array of embedded documents? I would like to update a field in an array of embedded documents.

My sample document is:

db.students.insert(
    {
        first_name:'AA',
        last_name:'BB',
        cours_reussis:[{intitule:'PAI',note:0,name_school:'EPFC'}]
    }
)

I'd like to change the value of name_school to "ENSA" instead of "EPFC".

The answer from Kalhara is correct if you know the position of the embedded document in your array, however there is an alternative approach that can be used to update the first matching array element without knowing the position.

Assuming you update with a single array in your query criteria, the positional operator ($) can be used to refer to the matching array element:

db.students.update(
    // Match criteria
    {
        first_name: 'AA',
        last_name: 'BB',
        'cours_reussis.name_school': 'EPFC'
    },

    // Update first matching array element using the positional operator ($)
    {
        $set: {
            'cours_reussis.$.name_school': 'ENSA',
        }
    }
)


How to produce an output of fields that are NULL (combined from multiple columns+tables)? I manage an application that has multiple users entering data through a web front-end into an MSSQL database. Each individual 'record' can have around 100 database columns across multiple tables (sometime multiple rows in the same table). It is relatively trivial to write SQL queries that will output 1 row per 'record' with all the columns we require for reporting purposes, for example:

Assessor  Date    Length  Colour  Weight
Steve     2/4/17  23.4    NULL    45
John      4/4/17  NULL    Blue    NULL
Brenda    4/4/17  NULL    NULL    NULL

I want to produce a simple output that lists everything where data was not recorded i.e fields that remain NULL. For example:

Assessor  Date    Field
Steve     2/4/17  Colour
John      4/4/17  Length
John      4/4/17  Weight
Brenda    4/4/17  Length
Brenda    4/4/17  Length
Brenda    4/4/17  Colour
Brenda    4/4/17  Weight

Currently I've tried something along these lines:

 select
  assessor
  ,date
  ,Field    =  'Length'
 from
 dbo.table1
 where [Length] is NULL
UNION ALL
 select
  assessor
  ,date
  ,Field    =  'Colour'
 from
 dbo.table1
 where [Colour] is NULL
UNION ALL
...

Is there any way to produce my required output without building such a long list of unions? I've looked into UNPIVOT however this appears to exclude NULLs.

Try this:

WITH CTE(Assessor,Date,Field) AS (
select Assessor,Date,Field
from(
select Assessor,Date,ISNULL(Length,'Length') as Length,ISNULL(Colour,'Colour') as Colour,ISNULL(Weight,'Weight') as Weigth
from TestTable) as OP
unpivot(
Field for Fields IN (Length,Colour,Weigth)) as UP)
select Assessor,Date,Field from CTE
where Field in ('Colour','Length','Weigth')


Visual Studio SSDT Unresolved reference to SSISDB I have a stored procedure in Visual Studio SSDT Project which use SSISDB database. And I get a warnings like "unresolved reference to SSISDB". I tried to create a new reference but I can only select "master" or "msdb" database. Any ideas how to repair this reference to SSISDB issue ?

What we have done is create a new SSDT project, then import SSISDB and create a dacpac that can then be referenced in other projects.


Which is better MAX function or ORDER BY column_name DESC with LIMIT 1 I have one scenario where I want to have max value on date column,

eg. for table

CREATE TABLE IF NOT EXISTS `sample_table` (
  `id` TINYINT UNSIGNED NOTNULL,
  `name` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARYKEY(`id`))
ENGINE = InnoDB;

So I used a query as below:

SELECT MAX(created_at) AS latest FROM sample_table;

But I was suggested to index created_at column and use following query for faster result

SELECT created_at FROM sample_table ORDER BY created_at DESC LIMIT 1;

I am clueless which is best way to do so? If second query is better, than why there is inbuilt MAX function

Add an index on created_at and use your first query:

SELECT MAX(created_at) AS latest FROM sample_table;

The suggestion you got is good in other situations, like if you want a max value of a field that is smaller than certain value. i.e.

SELECT created_at FROM sample_table WHERE created_at<"2014-01-01 00:00:00" ORDER BY created_at DESC LIMIT 1;


Decode everything that is not defined as 'internal' or 'Web' to 'external' (oracle) I'm trying to decode a columns with about 30 different possible values down to 3 values.

I want the decoded value to be "internal" if the original value is "Interal KS & others",  or "Web" if the original field is "Webshop", all other values i want to decode as "external".

Is this possible? or do i have to define a really long decode definition. I know it is possible to use > or < for numbers but how do i do "everything else" for text values?

Oh... I actually read the answer before i posted the question, but didn't realize it. ><

Just use the "else" variable at the end.

DECODE(supplier_id, 10000, 'IBM',
                10001, 'Microsoft',
                10002, 'Hewlett Packard',
                'Gateway')

the last variable 'Gateway' is assigned to everything that is not defined with a new value in the decode function. So in my question above i used 'External' after defining Internal and Web.

Answer and example form here:
Oracle / PLSQL: DECODE Function | techonthenet.com